1、內存使用分類

按用途分類
1）Database cache（數據頁面）。SQL Server中的頁面都是以8KB為一個頁面存儲的。當SQL Server需要用到某個頁面時，它會將該頁面讀到內存中，使用完后會緩存在內存中。在內存沒有壓力的情況下，SQL Server不會將頁面從內存中刪除。如果SQL Server感覺到內存的壓力時，會將最長時間沒有使用的頁面從內存中刪除來空出內存。
2）各類Consumer（功能組件）
Connection的連接信息
General：一組大雜燴。語句的編譯、范式化、每個鎖數據結構、事務上下文、表格和索引的元數據等
Query Plan：語句和存儲過程的執行計劃。和Database cache類似，SQL Server也會將執行計劃緩存以供將來使用，減少編譯時間。
Optimizer：生成執行計劃的過程中消耗的內存。
Utilities：像BCP、Log Manager、Backup等比較特殊的操作消耗的內存。
3）線程內存：存放進程內每個線程的數據結構和相關信息消耗的內存，每個線程需0.5MB的內存。
4）第三方代碼消耗的內存：SQL Server的進程里，會運行一些非SQL Server自身的代碼。例如：用戶定義的CLR或Extended Stored PRocedure代碼。
按申請方式分類
1）預先Reserve一塊大的內存，然后在使用的時候一塊一塊的Commit。Database Page是按這種方式申請的。
2）直接用Commit方式申請的內存，成為Stolen方式。除了Database Page之外其他內存基本都是按這種方式申請的。
按申請內存的大小分類
1）申請小于等于8KB為一個單位的內存，這些內存稱為Buffer Pool

2）申請大于8KB為一個單位的內存，這些內存稱為Multi-Page（或MemToLeave）

 
SQL Server對于Database cache都是采用先Reserved后Commit的方式申請的，而數據頁都是以8KB為單位進行申請的。
對于Consumer中的內存申請，一般都是按Stolen方式申請的，且大多數的執行計劃的大小都是小于8KB的，少數特別復雜的存儲過程的執行計劃會超過8KB，默認的連接的數據包是4KB，除非客戶端特別設置了超過8KB（不建議）
第三方代碼的內存申請一般是按Stolen方式申請的，個別比如CLR中可能會用Reserved/Commit的方式申請。
線程的內存每個都以0.5MB的方式申請，自然是放在MemToLeave中。
之所以花了這么大篇幅來講SQL Server的內存分類，是因為SQL Server尤其是32位的SQL Server對不同種類的內存的申請大小是不一樣的，對Commit、Stolen和MemTOLeave等類型的內存是有限制的。因此會出現系統中還有空閑內存，但是SQL Server不會申請使用的現象。
2、SQL Server內存使用情況的分析
一般來說有兩種方式，第一種就是用來分析系統內存情況時使用的用性能計數器來分析，第二種是使用動態管理視圖（DMV，只適用于SQL Server2005和2008）
1）SQL Server性能計數器
SQLServer：Memory Manager：Total Server Memory（KB)：SQL Server緩沖區提交的內存。不是SQL Server總的使用內存，只是Buffer Pool中的大小。
SQLServer：Memory Manager：Target Server Memory（KB）：服務器可供SQL Server使用的內存量。一般是由SQL Server能訪問到的內存量和SQL Server的sp_Configure配置中的Max Server Memory值中的較小值算得。
SQLServer：Memory Manger：Memory Grants Pending：等待內存授權的進程總數。如果該值不為0，說明當前有用戶的內存申請由于內存壓力被延遲，這意味著比較嚴重的內存瓶頸。
SQLServer：Buffer Manager：Buffer Cache Hit Ratio：數據從緩沖區中找到而不需要從硬盤中去取的百分比。SQL Server在運行一段時間后，該比率的變化應該很小，而且都應該在98%以上，如果在95%以下，說明有內存不足的問題。
SQLServer：Buffer Manager：Lazy Writes/Sec：每秒鐘被惰性編輯器（Lazy writer）寫入的緩沖數。當SQL Server感覺到內存壓力的時候，會將最久沒有使用的數據頁面和執行計劃從緩沖池中清理掉，做這個動作的就是Lazy Writer。
SQLServer：Buffer Manager：Page Life Expectancy：頁面不被引用后，在緩沖池中停留的秒數。在內存沒有壓力的情況下，頁面會一直待在緩沖池中，Page Life Expectancy會維持在一個比較高的值，如果有內存壓力時，Page Life Expectancy會下降。所以如果Page Life Expectancy不能維持在一個值上，就代表SQLServer有內存瓶頸。
SQLServer：Buffer Manager：Database Pages ：就是Database Cache的大小。
SQLServer：Buffer Manager：Free Pages：SQL Server中空閑可用的大小。
SQLServer：Buffer Manager：Stolen Pages：Buffer Pool中Stolen的大小。
SQLServer：Buffer Manager：Total Pages： Buffer Pool的總大?。ǖ扔贒atabase Pages+Free Pages+Stolen Pages）。該值乘以8KB，應該等于Memory Manager：Total Server Memory的值。
從上面這些計數器中我們就能了解SQL Server的內存使用情況，結合前面說的系統層的計數器大概能看出是否存在內存瓶頸。
2）內存動態管理視圖
在SQL Server 2005以后，SQL Server的內存管理是使用Memory Clerk的方式統一管理。所有的SQL Server的內存的申請或釋放，都需要通過它們的Clerk，SQL Server也通過這些Clerk的協調來滿足不同需求。通過查詢這些DMV，可以得到比用性能計數器更加詳細的內存使用情況。
我們可以通過下面的查詢語句來檢測SQL Server的Clerk的內存使用情況。
使用sys.dm_os_memory_clerks查看內存使用情況
SELECT type, --Clerk的類型 
    sum(virtual_memory_reserved_kb) as vm_Reserved_kb, -- 保留的內存 
    sum(virtual_memory_committed_kb) as vm_Committed_kb, --提交的內存 
    sum(awe_allocated_kb) as awe_Allocated_kb, -- 開啟AWE后使用的內存 
    sum(shared_memory_reserved_kb) as sm_Reserved_kb, -- 共享的保留內存 
    sum(shared_memory_committed_kb) as sm_Committed_kb, -- 共享的提交內存 
    sum(single_pages_kb) as SinlgePage_kb, --Buffer Pool中的Stolen的內存 
    sum(multi_pages_kb) as Multipage_kb -- MemToLeave的內存 
FROM sys.dm_os_memory_clerks  
GROUP BY type 
ORDER BY type
從上面的查詢語句，我們可以算出前面提到的內存大小
Reserved/Commit = sum(virtual_memory_reserved_kb) / sum(virtual_memory_committed_kb) 
Stolen = sum(single_pages_kb) + sum(multi_pages_kb) 
Buffer Pool = sum(virtual_memory_committed_kb) + sum(single_pages_kb) 
MemToLeave = sum(multi_pages_kb)
通過上面的介紹我們可以知道SQL Server總體和各部分內存的使用情況，如果我想知道數據頁的緩存中到底緩存了哪些數據，這些數據是屬于哪個數據庫的哪個表中的呢？執行計劃又是緩存了哪些語句的執行計劃呢？這也可以通過DMV查看的到。
查看內存中的數據頁面緩存的是哪個數據庫的哪個表格的數據
declare @name nvarchar(100) 
declare @cmd nvarchar(1000) 
declare dbnames cursor for
select name from master.dbo.sysdatabases 
open dbnames 
fetch next from dbnames into @name
while @@fetch_status = 0 
begin
set @cmd = 'select b.database_id, db=db_name(b.database_id),p.object_id,p.index_id,buffer_count=count(*) from '  
--這里的object_id代表是SQL Server中的對象號,index_id代表是索引號,buffer_count代表的是頁面數 
+ @name + '.sys.allocation_units a, '
+ @name + '.sys.dm_os_buffer_descriptors b, ' + @name + '.sys.partitions p 
where a.allocation_unit_id = b.allocation_unit_id 
and a.container_id = p.hobt_id 
and b.database_id = db_id(''' + @name + ''') 
group by b.database_id,p.object_id, p.index_id 
order by b.database_id, buffer_count desc'  
exec (@cmd) 
fetch next from dbnames into @name
end
close dbnames 
deallocate dbnames 
go
-- 根據上面取出來的@object_id找出是哪個數據庫的哪個表
SELECT    s.name AS table_schema, o.name as table_name --使用的就是table_schema.table_name表 
FROM    sys.sysobjects AS o INNER JOIN
          sys.schemas AS s ON o.uid = s.schema_id 
WHERE    (o.id = @object_id)
-- 根據上面取出來的@object_id和@index_id找出索引的名稱
SELECT    id, indid, name as index_name -- index_name就是索引的名稱 
FROM    sys.sysindexes 
WHERE    (id = @object_id) AND (indid = @index_id)
-- 根據上面取出來的表名table_schema.table_name和索引的名稱index_name，還可以找出該索引是建立在哪些字段上的
EXEC sp_helpindex 'table_schema.table_name'
查看內存中緩存的執行計劃，以及執行計劃對應的語句：
-- 輸出可能較大，請小心使用
SELECT   usecounts, refcounts, size_in_bytes, cacheobjtype, objtype, text
FROM    sys.dm_exec_cached_plans cp CROSS APPLY sys.dm_exec_sql_text(plan_handle)  
ORDER BY objtype DESC