I want to find out what is causing the high SQL Compilations (not re-compilations) I am seeing in performance monitor counters.
Here is my take on it: If I am seeing a lot of SQl compilations, then it means that the queries on our system are not getting cached for following reasons:
Many adhoc queries
Running queries which SQl doesn’t cache e.g. :
UPDATE table1 SET col1= 'String longer than 8000 characters .....' WHERE key_column = some int
Plans are timing out and being removed from the cache because: Cache is running out of space or plans are not being used long enough.
The only thing which goes near capturing cache inserts in profiler is Stored Procedures->SP:CacheInserts but it only looks after stored procedure cache.
So I tried the following to get adhoc queries:
SELECT [cp].[refcounts] -- when Refcounts becomes 0, plan is excluded from cache.
, [cp].[usecounts]
, [cp].[objtype]
, st.[dbid]
, st.[objectid]
, st.[text]
, [qp].[query_plan]
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text ( cp.plan_handle ) st
CROSS APPLY sys.dm_exec_query_plan ( cp.plan_handle ) qp ;
I thought queries that caused the compiles should be the ones with objtype= Adhoc but this could also relate to re-compilations. Now I have to run profiler, capture queries causing re-compilations and then exculde it from the above list.
Am I going in the right direction?
Is there a single query can I can use to achive just SQL compilations without too much work?
Resources which helped me in achiving the above knowledge:
http://social.msdn.microsoft.com/Forums/en/sqldatabaseengine/thread/954b4fba-3774-42e3-86e7-e5172abe0c83
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=143946
http://technet.microsoft.com/en-nz/library/cc966425(en-us).aspx
http://www.sqlservercentral.com/Forums/Topic914951-360-1.aspx
Any help is really appreciated.