TSQL: Find Queries causing too many SQL Compilations and SQL ReCompilations seperately

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.

I don't think you can find this by an easy way but it is possible anyway to get through this. Profiler offers many event class types that can be used in analyzing the performance of a query. Start a new Profiler session and check following events:

Performance: Performance statistics
Stored Procedures: RPC:Completed
TSQL: SQL:BatchCompleted
TSQL: SQL: BatchStarting

Check to Show all columns and select each one of the columns under Performance: Performance statistics event only. The rest of events can be left with default setting.

Next, Select Column Filters and filter by DatabaseName and/or LoginName/ApplicationName/HostName etc.., if you know them. The purpose is to limit the number of rows dispalyed in Profiler and concentrate only on your needs.

Next, press Run and let it run for a while (2-3 min as long as you need).
Analyse the results dispalyed looking primarily at: Performance statistics event.

If Performance Statistics will occur often it means that the plan of a query was cached for the first time, compiled, re-compiled or evicted from PlanCache. From my knowledge if a query does not have its query plan in Plan Cache - you will see 2 rows of PerformanceStatistics event and followed by SQL:BatchStarting, then SQL:BatchCompleted. It means that the Query Plan was first compiled, cached and then the query started and completed.

Look at following columns under Performance Statistics event:

SPID - ID of the session on which the event occurred. You can use it to identify the       
       row on SQL:BatchCompleted event which will display the SQL Query text and other  
       usefull information (Read/Writes, StartTime/EndTime)
Duration - Total time, in microseconds, spent during compilation.
EventSubClass - 0 = New batch SQL text that is not currently present in the cache.
                1 = Queries within a stored procedure have been compiled.
                2 = Queries within an ad hoc SQL statement have been compiled.
                3 = A cached query has been destroyed and the historical performance         
                    data associated with the plan is about to be destroyed.
                4 = A cached stored procedure has been removed from the cache and the  
                    historical performance data associated with it is about to be 
                    destroyed.

                5 = A cached trigger has been removed from the cache and the historical  
                    performance data associated with it is about to be destroyed.

Considering the EventSubClass number you can find out what happened with the Query Plan and take specific measures. Additionally you can add other columns to Stored Procedures and TSQL Event Classes if you are interseted in HostName, WindowsUser or other info from Profiler trace.
Also the trace can be stored in a SQL table making the analyse more easy and much more customizable. Here is a link describing more the Performance Statistics Event Class.

StackExchange Database Administrators Q#2124, answer score: 7