從sys.dm_os_memory_clerks開始。

      SELECT  [type] ,

        SUM(virtual_memory_reserved_kb) AS [VM Reserved] ,

        SUM(virtual_memory_committed_kb) AS [VM Committed] ,

        SUM(awe_allocated_kb) AS [AWE Allocated] ,

        SUM(shared_memory_reserved_kb) AS [SM Reserved] ,

        SUM(shared_memory_committed_kb) AS [SM Committed] ,

        SUM(multi_pages_kb) AS [Multipage Allocator] ,

        SUM(single_pages_kb) AS [SinlgePage Allocator],

        SUM(virtual_memory_reserved_kb)/(CASE WHEN SUM(virtual_memory_committed_kb)=0 THEN 1 ELSE SUM(virtual_memory_committed_kb) END ) AS [Reserved/Commit],

        SUM(single_pages_kb)+SUM(multi_pages_kb) AS Stolen,

        SUM(virtual_memory_committed_kb)+SUM(single_pages_kb) AS [SinlgePage Allocator]        

FROM    sys.dm_os_memory_clerks

GROUP BY [type]

ORDER BY [type]
    

其中type為Memory Clerk的名稱，可以知道內(nèi)存的用途。

      對(duì)于得出的數(shù)據(jù)：
    

      Memoryclerk_sqlbufferpool:正常來說這個(gè)匯總值最大。
    

      CACHESTORE_OBJECP：觸發(fā)器、存儲(chǔ)過程、函數(shù)的執(zhí)行計(jì)劃緩存。
    

      CACHESTORE_SQLCP：動(dòng)態(tài)T-SQL語句、預(yù)編譯TSQL語句的執(zhí)行計(jì)劃緩存。
    

      CACHESTORE_PHDR：緩存視圖、用戶自定義函數(shù)信息，幫助SQL更快生成執(zhí)行計(jì)劃。
    

      CACHESTORE_XPROC：緩存擴(kuò)展存儲(chǔ)過程，sp_executesql，sp_cursor*,sp_Trace*等。
    

      CACHESTORE_TEMPTABLES：緩存臨時(shí)對(duì)象。local temp table 、global temp table 、table variable等。
    

      CACHESTORE_CLRPROC：SQLCLR過程緩存。
    

      CACHESTORE_EVENTS：存儲(chǔ)Service Broker的時(shí)間和消息。
    

      CACHESTORE_CURSORS：存儲(chǔ)所有的游標(biāo)，包括LocalTSQLcursors、Global TSQL cursor和API cursors等。
    

      USERSTORE_TOKENPERM：保存所有用戶的安全上下文及各種跟安全相關(guān)的令牌，這些緩存條目用于檢查查詢累積性的權(quán)限。
    

      USERSTORE_SXC：暫時(shí)存放正在執(zhí)行中的語句的PRC參數(shù)，如果參數(shù)過長(zhǎng)，這部分內(nèi)存的使用量會(huì)比較大。
    

    
    

      

    
     內(nèi)存中的數(shù)據(jù)頁由哪些表格組成，各占多少？ 
  
  

sys.dm_os_buffer_descriptors

      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 '

            + @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


    

可以查看各種對(duì)象各占多少內(nèi)存：

      SELECT  objtype ,

        SUM(size_in_bytes) / 1024 AS sum_size_in_KB ,

        COUNT(bucketid) AS cache_counts

FROM    sys.dm_exec_cached_plans

GROUP BY objtype
    

分析具體存儲(chǔ)哪些對(duì)象：

      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 ;

GO


    

--使用DMV分析SQL SERVER 啟動(dòng)以來做read最多的語句

    --按照物理讀的頁面數(shù)排序，前50名

SELECT TOP 50

? ? ? ? qs.total_physical_reads ,

? ? ? ? qs.execution_count ,

? ? ? ? qs.total_physical_reads / qs.execution_count AS [Avg IO] ,

? ? ? ? SUBSTRING(qt.text, qs.statement_start_offset / 2,

? ? ? ? ? ? ? ? ? ( CASE WHEN qs.statement_end_offset = -1

? ? ? ? ? ? ? ? ? ? ? ? ?THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2

? ? ? ? ? ? ? ? ? ? ? ? ?ELSE qs.statement_end_offset

? ? ? ? ? ? ? ? ? ? END - qs.statement_start_offset ) / 2) AS query_text ,

? ? ? ? qt.dbid ,

? ? ? ? dbname = DB_NAME(qt.dbid) ,

? ? ? ? qt.objectid ,

? ? ? ? qs.sql_handle ,

? ? ? ? qs.plan_handle

FROM ? ?sys.dm_exec_query_stats qs

? ? ? ? CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt

ORDER BY qs.total_physical_reads DESC?

--按照邏輯讀的頁面數(shù)排序，前50名

SELECT TOP 50

? ? ? ? qs.total_logical_reads ,

? ? ? ? qs.execution_count ,

? ? ? ? qs.total_logical_reads / qs.execution_count AS [Avg IO] ,

? ? ? ? SUBSTRING(qt.text, qs.statement_start_offset / 2,

? ? ? ? ? ? ? ? ? ( CASE WHEN qs.statement_end_offset = -1

? ? ? ? ? ? ? ? ? ? ? ? ?THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2

? ? ? ? ? ? ? ? ? ? ? ? ?ELSE qs.statement_end_offset

? ? ? ? ? ? ? ? ? ? END - qs.statement_start_offset ) / 2) AS query_text ,

? ? ? ? qt.dbid ,

? ? ? ? dbname = DB_NAME(qt.dbid) ,

? ? ? ? qt.objectid ,

? ? ? ? qs.sql_handle ,

? ? ? ? qs.plan_handle

FROM ? ?sys.dm_exec_query_stats qs

? ? ? ? CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt

ORDER BY qs.total_logical_reads DESC


  

  
    --用DBCC強(qiáng)制釋放部分SQL SERVER 內(nèi)存緩存：
  
  
    DBCC FREESYSTEMCACHE
  
  
    DBCC FREESESSIONCACHE
  
  
    DBCC FREEPROCCACHE
  
  

    

  
  

  
  
    --查看操作系統(tǒng)內(nèi)存狀況

SELECT  total_physical_memory_kb / 1024 AS [物理內(nèi)存(MB)] ,

        available_physical_memory_kb / 1024 AS [可用物理內(nèi)存(MB)] ,

        system_cache_kb / 1024 AS [系統(tǒng)緩存內(nèi)存總量(MB)] ,

        ( kernel_paged_pool_kb + kernel_nonpaged_pool_kb ) / 1024 AS [內(nèi)核池內(nèi)存總量(MB)] ,

        total_page_file_kb / 1024 AS [操作系統(tǒng)報(bào)告的提交限制的大小(MB)] ,

        available_page_file_kb / 1024 AS [未使用的頁文件的總量(MB)] ,

        system_memory_state_desc AS [內(nèi)存狀態(tài)說明]

FROM    sys.dm_os_sys_memory


  
  

  
  

  

    
      SQL SERVER 內(nèi)存分配及常見內(nèi)存問題（2）——DMV查詢