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:
Any help is really appreciated.