SQL Server查询慢实战手册
问题一:按主键索引查询都慢
1. 先排查锁, 按锁名进行统计当前数据库的锁对象
select objname,count(objname) from (
select object_name(object_id) as objname , * from sys.dm_tran_locks l
inner join sys.partitions p on l.resource_associated_entity_id = p.hobt_id
where resource_database_id = DB_ID()
) as g group by g.objname
sys.dm_tran_locks : 记录当前事务锁 sys.partitions: 表的存储分区
该查询能查询当前数据库的锁对象名称, 比如表或主键, 这样能便于判断是不是因为锁而导致查询慢
2.如果第一步找到有这样的锁,则进一步查出对应的session即sql语句
select object_name(object_id) as objname ,
sq.text,
c.most_recent_sql_handle,
t.blocking_session_id,
l.request_type,
l.request_session_id,
* from sys.dm_tran_locks l
inner join sys.partitions p on l.resource_associated_entity_id = p.hobt_id
inner join sys.dm_os_waiting_tasks t on l.lock_owner_address = t.resource_address
inner join sys.dm_exec_connections c on t.blocking_session_id = c.session_id
cross apply sys.dm_exec_sql_text(c.most_recent_sql_handle) as sq
where resource_database_id = DB_ID()
sys.dm_os_waitting_tasks : 当前等待的任务
l.lock_owner_address = t.resource_address 匹配锁锁定的地址与等待任务等待的资源地址
该查询能判断当前等待的任务哪些被锁阻塞,且给出对应的session和sql语句
3. 通过上面一步查找到sql语句后基本可以定位到原因,这时候可以对sql语句或调用sql语句的代码进行调优,确定是什么原因造成对锁长时间占用不释放。对于当时已经阻塞的session, 可以使用kill <session id>的方式强行杀死会话。
问题二:SQL Server的CPU消耗占比非常高
使用以下语句判断当前会话,消耗CPU最高前10名的SQL查询
SELECT s.session_id,
r.status,
r.blocking_session_id 'Blk by',
r.wait_type,
wait_resource,
r.wait_time / (1000 * 60) 'Wait M',
r.cpu_time,
r.logical_reads,
r.reads,
r.writes,
r.total_elapsed_time / (1000 * 60) 'Elaps M',
Substring(st.TEXT,(r.statement_start_offset / 2) + 1,
((CASE r.statement_end_offset
WHEN -1
THEN Datalength(st.TEXT)
ELSE r.statement_end_offset
END - r.statement_start_offset) / 2) + 1) AS statement_text,
Coalesce(Quotename(Db_name(st.dbid)) + N'.' + Quotename(Object_schema_name(st.objectid, st.dbid)) + N'.' +
Quotename(Object_name(st.objectid, st.dbid)), '') AS command_text,
r.command,
s.login_name,
s.host_name,
s.program_name,
s.last_request_end_time,
s.login_time,
r.open_transaction_count
FROM sys.dm_exec_sessions AS s
JOIN sys.dm_exec_requests AS r
ON r.session_id = s.session_id
CROSS APPLY sys.Dm_exec_sql_text(r.sql_handle) AS st
WHERE r.session_id != @@SPID
ORDER BY r.cpu_time desc
或者使用以下语句从统计数据里查找消耗CPU最高前十名的SQL查询
SELECT TOP(10)
creation_time
, last_execution_time
, (total_worker_time+0.0)/1000 AS total_worker_time
, (total_worker_time+0.0)/(execution_count*1000) AS [AvgCPUTime] , execution_count
, st.*
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(sql_handle) st
WHERE total_worker_time > 0
ORDER BY total_worker_time DESC
问题三:索引碎片高
查询索引碎片
select a.index_id, name, avg_fragmentation_in_percent, fragment_count, avg_fragment_size_in_pages
from sys.dm_db_index_physical_stats (DB_ID('AdventureWorks2012'), object_id('Sales.SalesOrderDetail'), NULL, NULL, NULL) as a
join sys.indexes as b on a.object_id = b.object_id and a.index_id = b.index_id
重组索引
ALTER INDEX IX_Employee_OrganizationalLevel_OrganizationalNode ON HumanResources.Employee REORGANIZE;
重建索引
ALTER INDEX PK_Employee_BusinessEntityID ON HumanResources.Employee
REBUILD
;
参考:
https://blogs.msdn.microsoft.com/docast/2017/07/30/sql-high-cpu-troubleshooting-checklist/
https://blog.sqlauthority.com/2014/07/29/sql-server-ssms-top-queries-by-cpu-and-io/
https://www.mssqltips.com/sqlservertip/1949/sql-server-sysdmoswaitstats-dmv-queries/
https://www.mssqltips.com/sqlservertip/4331/sql-server-index-fragmentation-overview/