Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I have a SQL Server (quad core x86, 4GB RAM) that constantly has almost the same values for "SQLServer:SQL Statistics: SQL compilations/sec" and "SQLServer:SQL Statistics: SQL batches/sec". This could be interpreted as a server running 100% ad hoc queries, each one of which has to be recompiled, but this is not the case here. The sys.dm_exec_query_stats DMV lists hundreds of query plans with an execution_count much larger than 1.

Does anybody have any idea how to interpret / troubleshoot this phenomenon? BTW, the server's general performance counters (CPU,I/O,RAM) all show very modest utilization.

share|improve this question
    
have you run a trace with profiler? –  Nick Kavadias Apr 4 '10 at 14:22
    
Not yet; what would I look out for in the trace? –  Sleepless Apr 6 '10 at 6:57
    
I did a trace now; it has now SP:Recompiles, but lots and lots of SP:Cache Misses. This is also confirmed by a low Plan Cache Hit Ratio in Perfmon. Any ideas? –  Sleepless Apr 7 '10 at 14:00
add comment

2 Answers

Sleepless, there are some things that can cause a procedure/query to recompile, like when a table have a new statistics, but the most common case is when your procedure/query contains a dynamic statement, like this:

DECLARE @sqlCommand varchar(1000)
DECLARE @columnList varchar(75)
DECLARE @city varchar(75)
SET @columnList = 'CustomerID, ContactName, City'
SET @city = '''London'''
SET @sqlCommand = 'SELECT ' + @columnList + ' FROM customers WHERE City = ' + @city
EXEC (@sqlCommand)

Since the statement is not repeatable between the different calls , the query plan is recompiled every time. Your solution have a lot of procedures, or it issues the queries against the DB?

share|improve this answer
add comment

If you see a lot of "cache miss" (and you're not using many dynamic queries/EXEC statements) your SQL Server may be configured to use too few mb of ram.

You can check this configuration using sql management studio, right click on the server and select properties. In the "memory" page you can check the min and max limit your server is configured with.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.