Home > Performance, SQL Server > SQL Server having high RESOURCE_SEMAPHORE_QUERY_COMPILE waits

SQL Server having high RESOURCE_SEMAPHORE_QUERY_COMPILE waits

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
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: