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

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

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

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

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

 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，因此在執(zhí)行計劃里，查詢優(yōu)化器必須生成明確的排序運算符。下一步我會往表里插入1500條記錄，表里數(shù)據(jù)的所有值在第2列會平均分布——在表里每個值只出現(xiàn)一次。

 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

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

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

當(dāng)我們在SQL Server Profiler里嘗試跟蹤Sort Warnings和Hash Warning這2個事件時，會發(fā)現(xiàn)跟蹤不到。

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

 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

可以發(fā)現(xiàn)，查詢執(zhí)行前后沒有任何改變。這個查詢在我的系統(tǒng)里花費了1毫秒。

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

我們再次執(zhí)行剛才的查詢：

 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的查詢內(nèi)存授予（Query Memory Grant），它的估計行數(shù)是1——內(nèi)存授予和剛才的一樣。

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

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

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

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

現(xiàn)在我們往表里再插入1條記錄，再次執(zhí)行查詢，一切正常，因為SQL Server會觸發(fā)統(tǒng)計信息更新并正確估計查詢內(nèi)存授予（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

嗯，這是個非常簡單的例子，向你展示在SQL Server內(nèi)部如何產(chǎn)生Sort Warning，其實一點也不神秘！