SQL SERVER 記憶體配置設定及常見記憶體問題 DMV查詢

記憶體動态管理視圖（DMV）：  

從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的名稱，可以知道記憶體的用途。

對于得出的資料：  

emoryclerk_sqlbufferpool:正常來說這個彙總值最大。  

CACHESTORE_OBJECP：觸發器、存儲過程、函數的執行計劃緩存。  

CACHESTORE_SQLCP：動态T-SQL語句、預編譯TSQL語句的執行計劃緩存。  

CACHESTORE_PHDR：緩存視圖、使用者自定義函數資訊，幫助SQL更快生成執行計劃。  

CACHESTORE_XPROC：緩存擴充存儲過程，sp_executesql，sp_cursor*,sp_Trace*等。  

CACHESTORE_TEMPTABLES：緩存臨時對象。local temp table 、global temp table 、table variable等。  

CACHESTORE_CLRPROC：SQLCLR過程緩存。  

CACHESTORE_EVENTS：存儲Service Broker的時間和消息。  

CACHESTORE_CURSORS：存儲所有的遊标，包括LocalTSQLcursors、Global TSQL cursor和API cursors等。  

USERSTORE_TOKENPERM：儲存所有使用者的安全上下文及各種跟安全相關的令牌，這些緩存條目用于檢查查詢累積性的權限。  

USERSTORE_SXC：暫時存放正在執行中的語句的PRC參數，如果參數過長，這部分記憶體的使用量會比較大。    

記憶體中的資料頁由哪些表格組成，各占多少？

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  

會緩存執行計劃的對象：

proc：存儲過程

prepared：預定義語句

Adhoc：動态查詢

ReplProc：複制篩選過程

Trigger：觸發器

View：視圖

Default：預設值

UsrTab：使用者表

SysTab：系統表

Check：Check限制

Rule：規則

可以檢視各種對象各占多少記憶體：

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  

分析具體存儲哪些對象：

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 ;  

--使用DMV分析SQL SERVER 啟動以來做read最多的語句

--按照實體讀的頁面數排序，前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   

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

        qs.total_logical_reads ,  

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

ORDER BY qs.total_logical_reads DESC  

--用DBCC強制釋放部分SQL SERVER 記憶體緩存：  

DBCC FREESYSTEMCACHE  

DBCC FREESESSIONCACHE  

DBCC FREEPROCCACHE  

--檢視作業系統記憶體狀況  

SELECT  total_physical_memory_kb / 1024 AS [實體記憶體(MB)] ,  

        available_physical_memory_kb / 1024 AS [可用實體記憶體(MB)] ,  

        system_cache_kb / 1024 AS [系統緩存記憶體總量(MB)] ,  

        ( kernel_paged_pool_kb + kernel_nonpaged_pool_kb ) / 1024 AS [核心池記憶體總量(MB)] ,  

        total_page_file_kb / 1024 AS [作業系統報告的送出限制的大小(MB)] ,  

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

        system_memory_state_desc AS [記憶體狀态說明]  

FROM    sys.dm_os_sys_memory

原文：http://blog.csdn.net/dba_huangzj/article/details/7531384

本文轉自歡醉部落格園部落格，原文連結http://www.cnblogs.com/zhangs1986/p/3672810.html如需轉載請自行聯系原作者

歡醉