MVP Erland Explores SQL Server Performance Concepts

by Roopesh Shenoy on Jul 13, 2011 |

In a recent article, Slow in the Application, Fast in SSMS?, SQL Server MVP, Erland Sommarskog, explores the various things that affect Query Plan, compilation of Dynamic SQL and other things that ultimately affect your SQL Server Database Performance. He covers various concepts and scenarios like parameter sniffing, query plan caching, Blocking, saved settings, issues with linked servers and more.

Some interesting quotes from the article -

The parameter values of the first execution of the procedure have a huge impact for subsequent executions. If this first set of values for some reason is atypical, the cached plan may not be optimal for future executions. This is why parameter sniffing is such a big deal.

Occasionally, I see people in the forums or the newsgroups that tell me that their stored procedure is slow, but when they run the same query outside the procedure it's fast.To troubleshoot the query on its own, they have replaced the variables with constants. But as we have seen, the resulting stand-alone query is quite different, and SQL Server can make more accurate estimates with constants instead of variables, and therefore arrive at a better plan.

(For linked servers) what matters is the permissions on the remote server, not the local server where the query runs. Also, the problem is specific when the linked server is another SQL Server instance, and the same problem may not appear when the linked server is Oracle, MySQL or Access.

The article also explains the various ways to locate bottlenecks and potential problems as well as the related SQL queries -

  • Getting Query Plans and Parameters from within Management Studio
  • Getting them directly from the Query Cache
  • Getting them from a Trace
  • Getting Table and index definitions
  • Finding information about statistics

Erland also lists out various ways to fix parameter-sniffing issues like forcing recompilation, reviewing indexing, using OPTIMIZE FOR and finally just fixing Bad SQL, which could apply in different scenarios.

Finally, the article covers Dynamic SQL and how similar principles affect the performance for Dynamic SQL queries. This is an article definitely worth reading for everyone who works with SQL Server, developers and DBAs alike. 

Rate this Article


Hello stranger!

You need to Register an InfoQ account or or login to post comments. But there's so much more behind being registered.

Get the most out of the InfoQ experience.

Tell us what you think

Allowed html: a,b,br,blockquote,i,li,pre,u,ul,p

Email me replies to any of my messages in this thread
Community comments

Allowed html: a,b,br,blockquote,i,li,pre,u,ul,p

Email me replies to any of my messages in this thread

Allowed html: a,b,br,blockquote,i,li,pre,u,ul,p

Email me replies to any of my messages in this thread

General Feedback
Marketing and all content copyright © 2006-2016 C4Media Inc. hosted at Contegix, the best ISP we've ever worked with.
Privacy policy

We notice you're using an ad blocker

We understand why you use ad blockers. However to keep InfoQ free we need your support. InfoQ will not provide your data to third parties without individual opt-in consent. We only work with advertisers relevant to our readers. Please consider whitelisting us.