sql server 当前内存占用、阻塞查询，以及索引优化

ice_bird

已于 2023-12-06 17:47:50 修改

阅读量4.9k

点赞数 5

分类专栏： sql server 文章标签：运维 sqlserver

于 2021-11-30 11:07:32 首次发布

本文链接：https://blog.csdn.net/ice_bird/article/details/121627166

版权

sql server 专栏收录该内容

4 篇文章 1 订阅

订阅专栏

查看当前数据库是否存在阻塞

SELECT TOP 10 
    [session_id],[request_id],[start_time] AS '开始时间',[status] AS '状态',
    [command] AS '命令',dest.[text] AS 'sql语句', DB_NAME([database_id]) AS '数据库名',
    [blocking_session_id] AS '正在阻塞其他会话的会话ID',[wait_type] AS '等待资源类型',
    [wait_time] AS '等待时间',[wait_resource] AS '等待的资源',[reads] AS '物理读次数',
    [writes] AS '写次数',[logical_reads] AS '逻辑读次数',[row_count] AS '返回结果行数'
FROM    
    sys.[dm_exec_requests] AS der 
CROSS APPLY     
    sys.[dm_exec_sql_text](der.[sql_handle]) AS dest 
WHERE     
    [session_id]>50 AND DB_NAME(der.[database_id])='datebasename' 
ORDER BY     
    [cpu_time] DESC

查看sql占用较大的语句

SELECT TOP 10
    dest.[text] AS 'sql语句',session_id,status,start_time
FROM 
    sys.[dm_exec_requests] AS der 
CROSS APPLY 
    sys.[dm_exec_sql_text](der.[sql_handle]) AS dest 
WHERE     
    [session_id]>50   
ORDER BY
    [cpu_time] DESC

一个小时内sql语句资源占用情况

-- 获取最近一个小时内执行的 SQL 语句的资源使用情况
DECLARE @start_time DATETIME = DATEADD(HOUR, -1, GETDATE());

SELECT
    r.session_id,
    s.text AS query_text,
    r.total_elapsed_time,
--total_elapsed_time 请求所花费的总时间，以毫秒为单位
    r.logical_reads,
    r.reads,
    r.writes
FROM
    sys.dm_exec_requests r
CROSS APPLY
    sys.dm_exec_sql_text(sql_handle) AS s
WHERE
    r.start_time >= @start_time
ORDER BY
    r.total_elapsed_time DESC;

查询cpu占用最高的语句

 SELECT TOP 10
    total_worker_time/execution_count AS avg_cpu_cost, plan_handle,execution_count,
     (SELECT SUBSTRING(text, statement_start_offset/2 + 1,
        (CASE WHEN statement_end_offset = -1
           THEN LEN(CONVERT(nvarchar(max), text)) * 2
           ELSE statement_end_offset
        END - statement_start_offset)/2)
       FROM sys.dm_exec_sql_text(sql_handle)) AS query_text
 FROM 
    sys.dm_exec_query_stats
 ORDER BY 
    [avg_cpu_cost] DESC

查询当前死锁

select a.text seesion_text,a.wait_type,a.wait_time,a.session_id,a.blocking_session_id,b.text blocking_session_text from  
 (select text,wait_resource,wait_type,wait_time,session_id,blocking_session_id from sys.dm_exec_requests  
 cross apply sys.dm_exec_sql_text(sql_handle) where wait_time>1000) a left join 
 (select c.session_id,t.text from sys.dm_exec_connections as c cross apply sys.dm_exec_sql_text(most_recent_sql_handle) as t) b  
 on b.session_id=a.blocking_session_id;

索引缺失查询，可考虑对查询结果添加索引

  SELECT 
      DatabaseName = DB_NAME(database_id)
      ,[Number Indexes Missing] = count(*) 
  FROM sys.dm_db_missing_index_details
  GROUP BY DB_NAME(database_id)
  ORDER BY 2 DESC;
  SELECT  TOP 10 
          [Total Cost]  = ROUND(avg_total_user_cost * avg_user_impact * (user_seeks + user_scans),0) 
          , avg_user_impact
         , TableName = statement
         , [EqualityUsage] = equality_columns 
         , [InequalityUsage] = inequality_columns
         , [Include Cloumns] = included_columns
 FROM        sys.dm_db_missing_index_groups g 
 INNER JOIN    sys.dm_db_missing_index_group_stats s 
        ON s.group_handle = g.index_group_handle 
 INNER JOIN    sys.dm_db_missing_index_details d 
        ON d.index_handle = g.index_handle
 ORDER BY [Total Cost] DESC;

查询缓存使用次数少，且cpu占用高的语句


SELECT TOP 100 
    usecounts, objtype, p.size_in_bytes,[sql].[text] 
FROM sys.dm_exec_cached_plans p 
OUTER APPLY sys.dm_exec_sql_text (p.plan_handle) sql 
ORDER BY usecounts,p.size_in_bytes  desc

内存状态查询

SELECT type,
    sum(virtual_memory_reserved_kb)/ 1024  as '保留内存',
    sum(virtual_memory_committed_kb)/ 1024  as '提交的内存', 
    sum(awe_allocated_kb)/ 1024  as '开启AWE后使用的内存', 
    sum(shared_memory_reserved_kb)/ 1024  as '共享的保留内存' ,
    sum(shared_memory_committed_kb)/ 1024  as '共享的提交内存' 
FROM sys.dm_os_memory_clerks 
GROUP BY type
ORDER BY type

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

查询各数据库内存占用情况

SELECT count(*)*8/1024  as cached_pages_mb,CASE database_id
        WHEN 32767 THEN 'ResourceDb'
        ELSE db_name(database_id)
        END AS Database_name
FROM sys.dm_os_buffer_descriptors
GROUP BY db_name(database_id) ,database_id
ORDER BY cached_pages_mb DESC

查看表或者索引占用的缓存空间状态

SELECT count(*)*8/1024 AS cached_pages_mb,obj.name ,obj.index_id,b.type_desc,b.name
FROM sys.dm_os_buffer_descriptors AS bd
    INNER JOIN
    (
        SELECT object_name(object_id) AS name
            ,index_id ,allocation_unit_id,object_id
        FROM sys.allocation_units AS au
            INNER JOIN sys.partitions AS p
                ON au.container_id = p.hobt_id
                    AND (au.type = 1 OR au.type = 3)
        UNION ALL
        SELECT object_name(object_id) AS name  
            ,index_id, allocation_unit_id,object_id
        FROM sys.allocation_units AS au
            INNER JOIN sys.partitions AS p
                ON au.container_id = p.partition_id
                    AND au.type = 2
    ) AS obj
        ON bd.allocation_unit_id = obj.allocation_unit_id
        LEFT JOIN sys.indexes b on b.object_id = obj.object_id AND b.index_id =obj.index_id
WHERE database_id = db_id()
GROUP BY obj.name, obj.index_id ,b.name,b.type_desc
ORDER BY cached_pages_kb DESC;

查询sql 语句缓存中的内存占用情况

SELECT  usecounts ,
        refcounts ,
        size_in_bytes/1024 as size_in_mb ,
        cacheobjtype ,
        objtype ,
        TEXT
FROM    sys.dm_exec_cached_plans cp
        CROSS APPLY sys.dm_exec_sql_text(plan_handle)
ORDER BY size_in_mb DESC ;