Search

Monday, March 23, 2015

Queries waiting for memory

You can use below query to find out the queries that require a memory grant to execute or have acquired a memory grant. The queries that do not have to wait on a memory grant will not appear in the result. If this query returns a lot of rows than it could be a indication of internal memory pressure. This will help you to identify the queries which are requsting larger memory grants. There are various reason for this. The quiry might be poorly written. That may require some index for optimization. You should run this query periodically and check for the resource hungry queries. The user who runs below query must have View SErver STate permission on the server.