8

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.

4 Answers 4

7

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.

0
4

Well, first let's see if there is pressure on the cache.

select bpool_visible from sys.dm_os_sys_info
go

Multiply that number by 8 to get the memory in K. 75% of this from 0-4G + 10% of this from 4G-64G + 5% of any more is the plan cache pressure limit. If you reach 75% of this limit, SQL Server will start purging plans from the cache. This purge happens when a new query plan is added to the cache, so that thread will pause in order to do this work. The second thing that can cause plans to be purge is if the number of plans exceeds 4x the number of hash buckets (a hash table maps a plan_handle to a plan). There are 10,000 on a 32-bit system and 40,000 on a 64-bit system.

select type, sum(pages_allocated_count) as pages_used from sys.dm_os_memory_objects 
where type in ('MEMOBJ_CACHESTOREOBJCP', 'MEMOBJ_CACHESTORESQLCP', 'MEMOBJ_CACHESTOREXPROC')
group by type
go

The decision on what to purge is not made on usage, but on the cost of the plan, cheapest plans get purged first (cost to produce, not to execute). You can see this if you add the columns original_cost and current_cost to your query on sys.dm_exec_cached_plans. An ad-hoc plan starts at 0, and is incremented by 1 every time it is used. When cache pressure occurs, SQL Server subtracts half from each cost, then purges those that have reached 0.

If you have a lot of ad-hoc SQL try:

exec sp_reconfigure 'optimize for ad hoc workloads', 1
go
reconfigure
go

In this mode, SQL Server caches only a "stub", approximately 300 bytes in size (a normal query plan is 24k minimum), containing a hash and a pointer to the SQL text, the first time it sees a particular SQL statement, then subsequently caches the full plan if it is executed again. This won't necessarily cut down on compilations by itself, but it will relieve memory pressure on the plan cache.

Note: This works in 2008, not tried it in 2005.

Another trick is

alter database ... set parameterization forced
go

This will cause SQL Server to treat constants as parameters, which can help with the autoparameterization feature that usually caches plans for similar SQL statements. Ad-hoc SQL should have its query plans cached, unless your server is very short on memory, but this relies on exact textual matches unless it can be parameterized, in which case it behaves more like a prepared query.

1
  • Thanks! I knew of this "parameterization forced" option but was scared to use it. The only disadvantage that I can see in using this is that it will fill up the cache. Am I right?
    – Manjot
    Apr 9, 2011 at 22:33
3

Do you have many frequently running SQL Server jobs on this box? Note that in 2005 the agent job queries are NOT cached and can cause the cache bloat & sql compilations as well.

Look at the number of plans with low reuse counts. Those are your culprits.

Some related notes on Plan caching at below.

http://www.sqlskills.com/BLOGS/KIMBERLY/post/Plan-cache-adhoc-workloads-and-clearing-the-single-use-plan-cache-bloat.aspx

http://www.sqlskills.com/BLOGS/KIMBERLY/post/Clearing-the-cache-are-there-other-options.aspx

0
0

This effect is known as "query plan pollution", where many similar SQL queries generate separate, but equivalent execution plans.

Ad-Hoc queries cause overhead by individual parsing, but usually not query plan pollution, since their plans are not stored. This is different for queries with only one parameter (under MS SQL Server), these will be treated like a parameterized query.

There are some typical cases for query plan pollution:

  • SQL queries with only one hard coded literal parameter (like 'select id, name from person where id=1234')
  • especially if used with commands/stored procedures which force the database to store the query plan, such as 'sp_prepexec' or sp_executesql' under MSSQL (I think 'execute immediate' under Oracle works in a similar way)
  • partially parameterized queries, with great variance in 'hard coded' literal values, such as 'select * from SoccerMatches sm where sm.Date>?' and sm.Date<? and HomeClubId=5678 and GuestClubId=1234'. These will save query plans due to the parameters, but create a new query plan for every changed HomeClub or GuestClub (esp. since Date/Time values are a great occasion to introduce parameters in many DB APIs, when queries fail due to locally different date formats).
  • Another source of query plan pollution may be frameworks like ADO.NET with insufficient drivers, in combination with string/(n)varchar values. Some implementations/drivers will set the parameter sizes to the actual string length, causing a separate query plan for every different string parameter length in the query. Best practice seems to be the use of the maximum field size (e.g. varchar(4000)), or a driver that puts a correct length

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.