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/