查詢內存溢出

2024-07-21 02:46:41

字體：大中小

來源：轉載

供稿：網友

查詢內存溢出

首先我們來看一個帶排序的查詢，點擊工具欄的顯示包含實際的執行計劃。

1 SELECT * FROM AdventureWorks2008R2.Person.Person WHERE FirstName LIKE 'w%' ORDER BY 1

從執行計劃里可以看出，SELECT運算符包含了內存授予（Memory Grant）信息（一般情況下不會出現，這里是因為我們的語句包含排序操作）。內存授予是KB為單位，是當執行計劃中的一些運算符（像Sort/Hash等運算符）的執行，需要使用內存來完成——因此也被稱為查詢內存（Query Memory） 。

在查詢正式執行前，查詢內存必須被SQL Server授予才可以。對于提供的查詢，查詢優化器根據查詢對象的對應統計信息來決定需要多少查詢內存?，F在的問題就是，當統計信息過期了，SQL Server就會低估要處理的行數。在這個情況下，SQL Server對于提供的查詢還是會請求更少的查詢內存。但當查詢真正開始后，SQL Server就不能改變授予的內存大小，也不能請求更多的內存。查詢必須在授予的查詢內存里完成操作。在這個情況下，SQL Server需要把Sort/Hash運算符涌進TempDb，這就意味我們原先在內存里快速操作變成物理磁盤上慢速操作。SQL Server PRofiler可以通過Sort Warnings和Hash Warning這2個事件來跟蹤查詢內存溢出（Query Memory Spills）。

很遺憾在SQL SERVER 2008(R2)沒有提供這樣的擴展事件來跟蹤內存溢出事件。在SQL Server 2012里才有來解決這個問題。在這個文章里我會向你展示一個非常簡單的例子，由于統計信息過期，你是如何產生內存溢出（Query Memory Spills）。我們來創建一個新的數據庫，在里面創建一個表：

 1 SET STATISTICS IO ON 2 SET STATISTICS TIME ON 3 GO 4  5 -- Create a new database 6 CREATE DATABASE InsufficientMemoryGrants 7 GO 8  9 USE InsufficientMemoryGrants10 GO11 12 -- Create a test table13 CREATE TABLE TestTable14 (15    Col1 INT IDENTITY PRIMARY KEY,16    Col2 INT,17    Col3 CHAR(4000)18 )19 GO20 21 -- Create a Non-Clustered Index on column Col222 CREATE NONCLUSTERED INDEX idxTable1_Column2 ON TestTable(Col2)23 GO

TestTable表包含第1列的主鍵，第2列的非聚集索引，第3列的CHAR(4000)列。接下來我們要用第3列來做ORDER BY，因此在執行計劃里，查詢優化器必須生成明確的排序運算符。下一步我會往表里插入1500條記錄，表里數據的所有值在第2列會平均分布——在表里每個值只出現一次。

 1 -- Insert 1500 records 2 DECLARE @i INT = 1 3 WHILE (@i <= 1500) 4 BEGIN 5     INSERT INTO TestTable VALUES 6     ( 7          @i , 8         REPLICATE('x',4000) 9     )10     11     SET @i += 112 END13 GO

有了這樣的數據準備，我們可以執行一個簡單的查詢，會在執行計劃里好似用獨立的排序運算符：

1 DECLARE @x INT2  3 SELECT @x = Col2 FROM TestTable4 WHERE Col2 = 25 ORDER BY Col36 GO

當我們在SQL Server Profiler里嘗試跟蹤Sort Warnings和Hash Warning這2個事件時，會發現跟蹤不到。

你也可以使用DMV sys.dm_io_virtual_file_stats，看下num_of_writes列和num_of_bytes_written列，來看下剛才查詢在TempDb是否有活動。當然，這個只有你一個人在使用當前數據庫時有效。

 1 -- Check the activity in TempDb before we execute the sort Operation. 2 SELECT num_of_writes, num_of_bytes_written FROM  3 sys.dm_io_virtual_file_stats(DB_ID('tempdb'), 1) 4 GO 5  6 -- Select a record through the previous created Non-Clustered Index from the table. 7 -- SQL Server retrieves the record through a Non-Clustered Index Seek operator. 8 -- SQL Server estimates for the sort operator 1 record, which also reflects 9 -- the actual number of rows.10 -- SQL Server requests a memory grant of 1024kb - the sorting is done inside11 -- the memory.12 DECLARE @x INT13 14 SELECT @x = Col2 FROM TestTable15 WHERE Col2 = 216 ORDER BY Col317 GO18 19 -- Check the activity in TempDb after the execution of the sort operation.20 -- There was no activity in TempDb during the previous SELECT statement.21 SELECT num_of_writes, num_of_bytes_written FROM 22 sys.dm_io_virtual_file_stats(DB_ID('tempdb'), 1)23 GO

可以發現，查詢執行前后沒有任何改變。這個查詢在我的系統里花費了1毫秒。

現在我們有了1500條記錄的表，這就是說我們需要修改20% + 500的數據行才可以觸發SQL Server來更新統計信息。我們來計算下，就可以知道我們需要需要修改800條行數據（500 + 300）。因此讓我們來插入第2列值為2的799條數據。這樣我們就改變了數據的分布情況，當SQL Server還是不會更新統計信息，因為還有一條數據沒有更新，直到這條數據更新了才會觸發SQL Server內部的統計信息自動更新！

我們再次執行剛才的查詢：

 1 -- Check the activity in TempDb before we execute the sort operation. 2 SELECT num_of_writes, num_of_bytes_written FROM  3 sys.dm_io_virtual_file_stats(DB_ID('tempdb'), 1) 4 GO 5  6 -- Select a record through the previous created Non-Clustered Index from the table. 7 -- SQL Server retrieves the record through a Non-Clustered Index Seek operator. 8 -- SQL Server estimates for the sort operator 1 record, which also reflects 9 -- the actual number of rows.10 -- SQL Server requests a memory grant of 1024kb - the sorting is done inside11 -- the memory.12 DECLARE @x INT13 14 SELECT @x = Col2 FROM TestTable15 WHERE Col2 = 216 ORDER BY Col317 GO18 19 -- Check the activity in TempDb after the execution of the sort operation.20 -- There was no activity in TempDb during the previous SELECT statement.21 SELECT num_of_writes, num_of_bytes_written FROM 22 sys.dm_io_virtual_file_stats(DB_ID('tempdb'), 1)23 GO

SQL Server就會把排序運算符涌進TempDb，因為SQL Server只申請了1K的查詢內存授予（Query Memory Grant），它的估計行數是1——內存授予和剛才的一樣。

DMV sys.dm_io_virtual_file_stats顯示在TempDb里有活動，這是SQL Server把排序運算符涌進TempDb的證據。

SQL Server Profiler也顯示了Sort Warning的事件。

我們檢查下執行計劃里的估計行數（Estimated Number of Rows），和實際行數（Actual Number of Rows）完全不一樣。

這里的執行時間花費了184毫秒，和剛才的1毫秒完全不一樣。

現在我們往表里再插入1條記錄，再次執行查詢，一切正常，因為SQL Server會觸發統計信息更新并正確估計查詢內存授予（Query Memory Grant）：

 1 -- Insert 1 records into table TestTable 2 SELECT TOP 1 IDENTITY(INT, 1, 1) AS n INTO #Nums 3 FROM master.dbo.syscolumns sc1 4   5 INSERT INTO TestTable (Col2, Col3) 6 SELECT 2, REPLICATE('x', 2000) FROM #nums 7 DROP TABLE #nums 8 GO 9  10 -- Check the activity in TempDb before we execute the sort operation.11 SELECT num_of_writes, num_of_bytes_written FROM12 sys.dm_io_virtual_file_stats(DB_ID('tempdb'), 1)13 GO14  15 -- SQL Server has now accurate statistics and estimates 801 rows for the sort operator.16 -- SQL Server requests a memory grant of 6.656kb, which is now enough.17 -- SQL Server now spills the sort operation not to TempDb.18 -- Logical reads: 57719 DECLARE @x INT20  21 SELECT @x = Col2 FROM TestTable22 WHERE Col2 = 223 ORDER BY Col324 GO25  26 -- Check the activity in TempDb after the execution of the sort operation.27 -- There is now no activity in TempDb during the previous SELECT statement.28 SELECT num_of_writes, num_of_bytes_written FROM29 sys.dm_io_virtual_file_stats(DB_ID('tempdb'), 1)30 GO