BT

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. 

Hello stranger!

You need to Register an InfoQ account or 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

Discuss

Educational Content

General Feedback
Bugs
Advertising
Editorial
InfoQ.com and all content copyright © 2006-2013 C4Media Inc. InfoQ.com hosted at Contegix, the best ISP we've ever worked with.
Privacy policy
BT