SQL Server数据库查询慢优化方法总结

SQL Server数据库查询慢优化方法总结

--数据库查询慢检索步骤
--一、检查内存和CPU是否负荷运转
--数据库内存是否有设定上限，设置总内存的75%
--强制释放内存

DBCC DROPCLEANBUFFERS
exec sp_configure'show advanced options', 1
exec sp_configure'max server memory', 10240
EXEC ('RECONFIGURE')
WAITFOR DELAY '00:00:15'
EXEC  sp_configure'max server memory',102400
EXEC ('RECONFIGURE')
exec sp_configure'show advanced options',0

--二、索引是否有碎片
SELECT object_name(dt.object_id) Tablename,
        si.name IndexName,
        dt.avg_fragmentation_in_percent AS ExternalFragmentation,
        dt.avg_page_space_used_in_percent AS InternalFragmentation  
FROM(
    SELECT TOP 10 object_id, index_id,avg_fragmentation_in_percent,avg_page_space_used_in_percent
    FROM sys.dm_db_index_physical_stats (db_id('Develop'),null,null,null,'DETAILED')  
    WHERE index_id <>0 ) AS dt 
    INNER JOIN sys.indexes si 
    ON si.object_id=dt.object_id 
    AND si.index_id=dt.index_id 
    AND dt.avg_fragmentation_in_percent>10
    AND dt.avg_page_space_used_in_percent < 75 
ORDER BY avg_fragmentation_in_percent DESC
--使用下面的规则分析结果，你就可以找出哪里发生了索引碎片：
--　　1)ExternalFragmentation的值>10表示对应的索引发生了外部碎片;
--　　2)InternalFragmentation的值<75表示对应的索引发生了内部碎片。

--重组索引或重建索引

--找出占CPU过高的SQL语句
--找出占内存过大的SQL语句

SELECT s2.dbid, 
s1.sql_handle, 
(SELECT TOP 1 SUBSTRING(s2.text,statement_start_offset / 2+1 , 
( (CASE WHEN statement_end_offset = -1 
THEN (LEN(CONVERT(nvarchar(max),s2.text)) * 2) 
ELSE statement_end_offset END) - statement_start_offset) / 2+1)) AS sql_statement,
execution_count, 
plan_generation_num, 
last_execution_time, 
total_worker_time, 
last_worker_time, 
min_worker_time, 
max_worker_time,
total_physical_reads, 
last_physical_reads, 
min_physical_reads, 
max_physical_reads, 
total_logical_writes, 
last_logical_writes, 
min_logical_writes, 
max_logical_writes 
FROM sys.dm_exec_query_stats AS s1 
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2 
WHERE s2.objectid is null 
ORDER BY last_worker_time desc,s1.sql_handle, s1.statement_start_offset,s1.statement_end_offset;

-- 查询当前数据库缓存的所有数据页面，哪些数据表，缓存的数据页面数量
-- 从这些信息可以看出，系统经常要访问的都是哪些表，有多大？
select p.object_id, object_name=object_name(p.object_id), p.index_id, buffer_pages=count(*) 
from sys.allocation_units a, 
    sys.dm_os_buffer_descriptors b, 
    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()
group by p.object_id,p.index_id 
order by buffer_pages desc 

--三、统计信息更新 将经常访问的表的统计信息更新

--查看有索引的表有多少条数据需要更新统计信息
select
ss.name AS SchemaName
,st.name AS TableName
,si.name AS IndexName
,ssi.rowcnt  --表的总行数
,ssi.rowmodctr  --自上次更新统计信息后，增删改的行数，该值就是需要更新统计信息的count数
FROM sys.indexes si
INNER JOIN sys.sysindexes ssi ON si.object_id = ssi.id
AND si.name = ssi.name
INNER JOIN sys.tables st ON st.[object_id] = si.[object_id]
INNER JOIN sys.schemas ss ON ss.[schema_id] = st.[schema_id]
WHERE st.is_ms_shipped = 0
AND si.index_id != 0 --0代表的是堆表
AND ssi.rowcnt > 10

--显示统计信息
DBCC SHOW_STATISTICS(A表,INDX_1统计信息)

--更新指定表下的统计信息
UPDATE STATISTICS dbo.[T_SalesOrder]  WITH FULLSCAN

--更新指定的的统计信息
update statistics run.dbo.T1  [_WA_Sys_00000001_0EA330E9]