SQL Server 单查询内存使用情况
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6775384/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Server single query memory usage
提问by Gilad
I would like to find out or at least estimate how much memory does a single query (a specific query) eats up while executing. There is no point in posting the query here as I would like to do this on multiple queries and see if there is a change over different databases. Is there any way to get this info?
我想找出或至少估计单个查询（特定查询）在执行时消耗了多少内存。在这里发布查询没有意义，因为我想对多个查询执行此操作，并查看不同数据库是否有更改。有没有办法获得这些信息？
Using SQL Server 2008 R2
使用 SQL Server 2008 R2
thanks
谢谢
Gilad.
吉拉德。
采纳答案by MicSim
You might want to take a look into DMV (Dynamic Management Views) and specifically into sys.dm_exec_query_memory_grants. See for example this query (taken from here):
您可能想查看 DMV（动态管理视图），特别是sys.dm_exec_query_memory_grants。例如，请参阅此查询（取自此处）：
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
While issuing the above query it will wait until some query is running and will collect the memory data. So to use it, just run the above query and after that your query that you want to monitor.
在发出上述查询时，它将等到某个查询运行并收集内存数据。所以要使用它，只需运行上面的查询，然后运行你想要监控的查询。
回答by Justin
What do you mean by "how much memory a query eats up?", and why exactly do you want to know?
“一个查询占用了多少内存？”是什么意思，你到底为什么想知道？
I don't think memory in SQL Server works the way you might imagine - memory management in SQL Server is an incrediblycomplex topic - you could easily write entire books about SQL Servers memory management. I can't claim to know thatmuch about SQL Servers memory management, but I do know that there is pretty much no useful information that you can extrapolate from knowing how much memory a single query uses up.
我认为 SQL Server 中的内存不会像您想象的那样工作 - SQL Server 中的内存管理是一个非常复杂的主题 - 您可以轻松地编写关于 SQL Server 内存管理的整本书。我不能声称对 SQL Server 内存管理有太多了解，但我知道几乎没有有用的信息可以从了解单个查询使用了多少内存中推断出来。
That said, if you did want to have a go at understanding whats going on with memory when you execute a query then I would probably start with looking at the buffer pool. Nearly all memory in SQL Server is organised into 8KB chunks (the same size as a page) of memory that can be used to store anything from a data page or index page to a cached query plans. The buffer pool is the main memory component in SQL Server - All 8KB chunks of memory not in use elsewhere remains in the buffer pool to be used as a cache for data pages.
也就是说，如果您确实想了解在执行查询时内存发生了什么，那么我可能会从查看缓冲池开始。SQL Server 中的几乎所有内存都组织成 8KB 内存块（与页面大小相同），可用于存储从数据页或索引页到缓存查询计划的任何内容。缓冲池是 SQL Server 中的主要内存组件 - 其他地方未使用的所有 8KB 内存块都保留在缓冲池中，用作数据页的缓存。
Note that in order for a data page or index page to be used it must exist in memory - this means that if it doesn't already exist in memory elsewhere ready for use, a free buffer must be made available to ready the page in to. The buffer pool serves both as a pool of "expendable" free buffers, and a cache of pages already present in memory.
请注意，为了使用数据页或索引页，它必须存在于内存中——这意味着如果它不存在于其他地方准备使用的内存中，则必须提供一个空闲缓冲区来准备页面. 缓冲池既用作“可消耗的”空闲缓冲区池，也用作内存中已存在的页面缓存。
You can examine whats in the buffer pool using DMVs, there is a suitable query listed on this page:
您可以使用 DMV 检查缓冲池中的内容，此页面上列出了合适的查询：
By cleaning out your buffer pool using the command DBCC DROPCLEANBUFFERS(DONT DO THIS ON A PRODUCTION SQL SERVER!!!) and then executing your query, in theory the new pages that appear in the buffer pool should be the pages that were used in the last query.
通过使用命令DBCC DROPCLEANBUFFERS（DONT DO THIS ON A PRODUCTION SQL SERVER!!!）清理你的缓冲池，然后执行你的查询，理论上出现在缓冲池中的新页面应该是上次查询中使用的页面.
This can give you a rough idea of the data and index pages used in a query, however doesn't cover other areas of SQL Server where memory is used, such as in the query plan cache, SQL Server Workers etc..
这可以让您大致了解查询中使用的数据和索引页，但不包括使用内存的 SQL Server 的其他区域，例如查询计划缓存、SQL Server Workers 等。
Like I said, SQL Server memory management is complex - If you really want to know more I recommend that you buy a book on SQL Server internals.
就像我说的，SQL Server 内存管理很复杂——如果你真的想了解更多，我建议你买一本关于 SQL Server 内部原理的书。
Update:You can also use the query statistics to view the aggregate performance statistics for a query including "physical reads" (pages read from the disk) and "logcal reads" (pages read from the buffer pool). See this pagefor a suitable query.
更新：您还可以使用查询统计信息查看查询的汇总性能统计信息，包括“物理读取”（从磁盘读取的页面）和“日志读取”（从缓冲池读取的页面）。有关合适的查询，请参阅此页面。
This might also give you some more hints on how much memory a query is using, however beware - playing around I found queries that performed many more logical reads than they did physical reads, which as far as I can work out meant that they read the same pages over and over again, i.e. 100 logical reads != 100 pages used in the buffer pool.
这也可能会为您提供有关查询使用多少内存的更多提示，但要注意 - 我发现查询执行的逻辑读取比物理读取多得多，据我所知，这意味着它们读取了一遍又一遍地重复相同的页面，即 100 次逻辑读取！= 缓冲池中使用的 100 个页面。
