Archive

Archive for February, 2011

SQL Server having high RESOURCE_SEMAPHORE_QUERY_COMPILE waits

February 16, 2011 Leave a comment

If your SQL server is experiencing not so high CPU, but queries are getting timed out, it may be worthwhile to look at activity monitor resource waits (compilation), or run the following SQL

select * from sys.dm_os_wait_stats
where wait_type like ‘resource%’
order by wait_type

If the wait time very high (e.g the first in the list of resource waits), you might want to look at recent queries and see what’s being executed (Activity Monitor -> Processes, right click then details)

Chances are you will see quite a fair number of queries which looks more or less the same.

One easy way to solve this issue is to run dbcc freeproccache, which should effective drop your cpu to normal values once the query is completed. However in the long run, its best to look at the query causing this and see how to make it better

The usual cause of this is when there is alot of SQL which looks more or less the same being executed at the same time. But because the parameters are hard coded, it causes SQL Server to create an execution plan for each and every query.

To solve this, you can either create plan guides, or else find ways to parameterize your queries so that it does not have so many recompiles

 

Categories: Performance, SQL Server