Resource semaphore waits/queries not running when there is seemingly enough memory for them to run

http://blogs.msdn.com/b/sqlqueryprocessing/archive/2010/02/16/understanding-sql-server-memory-grant.aspx

•Resource Semaphore checks for waiting queries in its queue.
•If it finds a waiting query, it puts the new query into a wait queue for fairness.
•The wait queue is designed on first-come-first-served basis with small weight to favour small queries.

Small queries are classed as queries requesting than 5MB

Example: if there is 10GB memory free on the server then the following will happen

•A query requesting 11GB is executed it will wait for memory to become available with a resource_semaphore wait
•A query requesting 1GB is then executed, it will see that the above query is already waiting and so go into the queue. This query is not small (not less than 5MB) and there is already something in the queue ahead of it so the query will also go into resource_semaphore wait status.

The query goes into a wait status despite there being enough memory available for the query to run because 1. queue is first come first served 2. Query is not classed as small

Advertisements

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: