BT

New Early adopter or innovator? InfoQ has been working on some new features for you. Learn more

MVP Erland Explores SQL Server Performance Concepts

| by Roopesh Shenoy Follow 0 Followers on Jul 13, 2011. Estimated reading time: 1 minute |

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

Adoption Stage
Style

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

Discuss

Login to InfoQ to interact with what matters most to you.


Recover your password...

Follow

Follow your favorite topics and editors

Quick overview of most important highlights in the industry and on the site.

Like

More signal, less noise

Build your own feed by choosing topics you want to read about and editors you want to hear from.

Notifications

Stay up-to-date

Set up your notifications and don't miss out on content that matters to you

BT