SQL Server Single Query Memory Usage

Question

I would like to know, or at least estimate how much memory a single request (a specific request) makes at runtime. It makes no sense to post a query here, as I would like to do this for several queries and see if there is a change in different databases. Is there any way to get this information?

Using SQL Server 2008 R2

thanks

Gilad.

Answer 1

Maybe you should take a look at DMV (Dynamic Management Views) and, in particular, sys.dm_exec_query_memory_grants . See for example this query (taken from here ):

DECLARE @mgcounter INT SET @mgcounter = 1 WHILE @mgcounter <= 5 -- return data from dmv 5 times when there is data BEGIN IF (SELECT COUNT(*) FROM sys.dm_exec_query_memory_grants) > 0 BEGIN SELECT * FROM sys.dm_exec_query_memory_grants mg CROSS APPLY sys.dm_exec_sql_text(mg.sql_handle) -- shows query text -- WAITFOR DELAY '00:00:01' -- add a delay if you see the exact same query in results SET @mgcounter = @mgcounter + 1 END END 

When issuing the above request, it will wait for the execution of some request and will collect memory data. To use it, just run the query above and then query which one you want to track.

Answer 2

What do you mean by "the amount of memory from which the request is read?", And why exactly do you want to know?

I don’t think that memory in SQL Server works as you can imagine - memory management in SQL Server is an incredible complex topic - you can easily write whole books about SQL Server memory management. I cannot say that I know so much about SQL Server memory management, but I know that to a large extent there is no useful information that you can extrapolate, knowing how much memory a single query uses.

However, if you want to understand what happens to the memory when the query is executed, I will probably start by looking at the buffer pool. Almost all of the memory in SQL Server is organized into 8 KB chunks (the same size as the page) of memory that can be used to store something from the data page or index page in cached query plans. The buffer pool is a major memory component in SQL Server. All 8 KB pieces of memory that are not used elsewhere remain in the buffer pool, which will be used as a cache for data pages.

Note that in order for a data page or index page to be used, it must exist in memory - this means that if it does not already exist in memory in another place ready for use, a free buffer must be available to prepare the page in . The buffer pool serves as a pool of "consumable" free buffers, as well as the cache of pages already in memory.

You can verify that in the buffer pool using DMV, there is a suitable request specified on this page:

• What is floating in your buffer pool?

Having cleared the buffer pool using the DBCC DROPCLEANBUFFERS ( THIS IS NOT SHOULD BE PRODUCED BY SQL SERVER !!! ), and then execute your query, theoretically the new pages that appear in the buffer pool should be the pages that were used in the last request.

This may give you a general idea of ​​the data and index pages used in the query, but it does not apply to other areas of SQL Server where memory is used, for example, in the query plan cache, SQL Server employees, etc.

As I said, SQL Server memory management is complicated. If you really want to learn more, I recommend that you buy a book on the internal components of SQL Server.

Update:. You can also use query statistics to view aggregate statistics for the query, including “physical reads” (pages read from disk) and “logcal reads” (pages read from the buffer pool). See this page for a suitable request.

It may also give you some tips on how much memory the query uses, but be careful - playing around I found queries that performed far more logical reads than physical reads, which, as far as I can understand, means that they read the same pages over and over, i.e. 100 logical reads! = 100 pages used in the buffer pool.