Microsoft – SQLServer – SQL Re-compiles

Still looking under the hood of the MS SQL Server Engine, trying to answer the question how is the memory being used:

One of the areas that should be explored in terms of memory utilization is reviewing SQL Server Query Plan recompiles.

Invariably, Brent Ozar, has a good post on this topic:

http://www.brentozar.com/archive/2011/09/sysadmins-guide-microsoft-sql-server-memory/

Microsoft’s SQL Customer Advisory Team’s Top SQL 2005 OLTP Performance Issues says that if Compilations/sec is more than 10% of Batch Requests/sec, you may be experiencing CPU pressure because SQL Server has to build execution plans.  This one gets tricky, and frankly, it’s trickier than I want a sysadmin to hassle with.  This rule just doesn’t work in too many cases because it ignores the quantity of work being done.  If you’ve got a small number of queries coming in, and you’ve armed the server with big multi-core processors, then building execution plans is hardly any work at all even if you’re compiling every single statement from scratch.  However, if Compilations/sec is 25% or higher relative to Batch Requests/sec, and if you’ve got in-house developers, it’s time to start asking questions.  They’re probably using development tools like LINQ or dynamic SQL that can force SQL Server to build execution plans unnecessarily.  We have to work around that by educating the developers, because no amount of memory is going to fix that problem.

Tom Davidson writing for SQL Server Pro “walks the same line”…..

High ratio of compilations to batch requests. When you’re working with online transaction processing (OLTP) applications, you usually want SQL Server to reuse query plans as much as possible to reduce the length of queues. When SQL Server reuses query plans, it doesn’t need to compile the query before execution, thus reducing CPU utilization (which the System:Processor Queue Length counter shows) and shortening query-processing times. To get a ratio of compilations to batch requests, use the Performance Monitor counters SQLServer:SQL Statistics:Batch Requests/sec and SQLServer:SQL Statistics:SQL Compilations/sec.

When you find that SQL Server isn’t reusing query plans efficiently, poor memory utilization (i.e., memory pressure) or poor coding practices might be to blame. To diagnose the cause, you can look at the SQL Server Profiler events SP:CacheMiss, SP:CacheInsert, SP:CacheRemove, SP:Recompilation, and SP:CacheHit. These events report plan activity in the cache. SP:CacheHit events show plan reuse. If a compile occurs, Profiler records the SP:CacheMiss and SP:CacheInsert events. SQL Server sometimes discards query plans to free memory for other activities. In such cases, the SP:CacheRemove event occurs. SP:Recompilation indicates that a recompile has occurred during the execution of a stored procedure. And if you notice a low value in the SQL Buffer Mgr:Page Life Expectancy Performance Monitor counter, your SQL Server system might be suffering from memory pressure.

 

References:

1) A Sysadmin’s Guide to Microsoft SQL Server Memory
http://www.brentozar.com/archive/2011/09/sysadmins-guide-microsoft-sql-server-memory/

2) Opening Microsoft’s Performance-Tuning Toolbox

http://www.sqlmag.com/content1/topic/opening-microsoft-s-performance-tuning-toolbox/catpath/tsql3

2 thoughts on “Microsoft – SQLServer – SQL Re-compiles

  1. Howdy! I’m glad you found my material useful – that’s the best compliment I can get as a blogger.

    One quick favor though – when you’re quoting my material verbatim, can you put quotes around it? That just makes it a little more clear to the reader that the words are mine. The blockquote tag in WordPress also makes this look beautiful – it shows that the section is clearly a quote from the original post.

    Thanks!

    • Hey B:

      Sure thing. I was Googling for Information on “Memory in VLDB” database environments and ran into your post.

      Though, I have a few years of experience in MS SQL Server, I have never really quite made a good note on what
      OS Performance Counters to use to track recompiles.

      And, so finding your Post is a valuable help.

      In the days of old, I would have a notebook full of color-coded notes and have it handy every where I go.

      But, now with blogs and the sorts, those otherwise untidy notes are being kept for the old world to see.

      Thanks for contributing so much to the community. I have being in situations where people wanted to throw out SQL Server, but with people like you everyone has a chance to “come up” with good ideas.

      A couple of months ago, your post about CPU (SQL Server on Power Saving CPU – http://www.brentozar.com/archive/2010/10/sql-server-on-powersaving-cpus-not-so-fast/) got us out of a big jam.

      I have benefit immensely, but Corporate ethics & NDA are in place, and so we ‘keep secrets’. But, all the same, I along with many ‘scientists’ would have gone and stayed crazy if not for people like you – who make it easier for us not to have think so deeply.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s