/*
常规服务器动态管理对象包括：
dm_db_*：数据库和数据库对象
dm_exec_*：执行用户代码和关联的连接
dm_os_*：内存、锁定和时间安排
dm_tran_*：事务和隔离
dm_io_*：网络和磁盘的输入/输出
*/
--- 运行下面的 DMV 查询以查看 CPU、计划程序内存和缓冲池信息。
select
cpu_count,
hyperthread_ratio,
scheduler_count,
physical_memory_in_bytes / 1024 / 1024 as physical_memory_mb,
virtual_memory_in_bytes / 1024 / 1024 as virtual_memory_mb,
bpool_committed * 8 / 1024 as bpool_committed_mb,
bpool_commit_target * 8 / 1024 as bpool_target_mb,
bpool_visible * 8 / 1024 as bpool_visible_mb
from sys.dm_os_sys_info
--- 高I/O开销的查询 Identifying Most Costly Queries by I/O
SELECT TOP 10
[Average IO] = (total_logical_reads + total_logical_writes) / qs.execution_count
, [Total IO] = (total_logical_reads + total_logical_writes)
, [Execution count] = qs.execution_count
, [Individual Query] = 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)
,[Parent Query] = qt.text
, DatabaseName = DB_NAME(qt.dbid)
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
ORDER BY [Average IO] DESC;
--- 高CPU开销的查询 Identifying Most Costly Queries by CPU
SELECT TOP 10
[Average CPU used] = total_worker_time / qs.execution_count
, [Total CPU used] = total_worker_time
, [Execution count] = qs.execution_count
, [Individual Query] = 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)
, [Parent Query] = qt.text
, DatabaseName = DB_NAME(qt.dbid)
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
ORDER BY [Average CPU used] DESC;
--- 高开销的缺失索引 Cost of Missing Indexes
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;
--- 最常执行的查询 Identifying Queries that Execute Most Often
SELECT TOP 10
[Execution count] = execution_count
,[Individual Query] = 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)
,[Parent Query] = qt.text
,DatabaseName = DB_NAME(qt.dbid)
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
ORDER BY [Execution count] DESC;
--- 重复编译的查询（plan_generation_num 指示该查询已重新编译的次数）
select top 25
sql_text.text,
sql_handle,
plan_generation_num,
execution_count,
dbid,
objectid
from sys.dm_exec_query_stats a
cross apply sys.dm_exec_sql_text(sql_handle) as sql_text
where plan_generation_num > 1
order by plan_generation_num desc
--- 服务器等待的原因 SQL Query Records Causes of Wait Times
SELECT TOP 10
[Wait type] = wait_type,
[Wait time (s)] = wait_time_ms / 1000,
[% waiting] = CONVERT(DECIMAL(12,2), wait_time_ms * 100.0
/ SUM(wait_time_ms) OVER())
FROM sys.dm_os_wait_stats
WHERE wait_type NOT LIKE '%SLEEP%'
ORDER BY wait_time_ms DESC;
--- 读和写 Identifying the Most Reads and Writes
SELECT TOP 10
[Total Reads] = SUM(total_logical_reads)
,[Execution count] = SUM(qs.execution_count)
,DatabaseName = DB_NAME(qt.dbid)
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
GROUP BY DB_NAME(qt.dbid)
ORDER BY [Total Reads] DESC;
SELECT TOP 10
[Total Writes] = SUM(total_logical_writes)
,[Execution count] = SUM(qs.execution_count)
,DatabaseName = DB_NAME(qt.dbid)
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
GROUP BY DB_NAME(qt.dbid)
ORDER BY [Total Writes] DESC;
--- 运行下面的 DMV 查询以查找 I/O 闩锁等待统计信息。
select wait_type, waiting_tasks_count, wait_time_ms, signal_wait_time_ms, wait_time_ms / waiting_tasks_count
from sys.dm_os_wait_stats
where wait_type like 'PAGEIOLATCH%' and waiting_tasks_count > 0
order by wait_type
--- 查看数据库的阻塞信息
select * from sysprocesses a where a.program_name = '.Net SqlClient Data Provider' and blocked != 0
---查看所有会话的 找到活动事务对应的执行语句
select dc.session_id,
ds.login_name,
ds.login_time,
dc.connect_time,
dc.net_transport,
dc.client_net_address,
ds.host_name,
ds.program_name,
case ds.status when 'sleeping' then '睡眠 - 当前没有运行任何请求 '
when 'running' then '正在运行 - 当前正在运行一个或多个请求 '
when 'Dormancy' then '休眠 – 会话因连接池而被重置，并且现在处于登录前状态'
when 'Pre-connected' then '预连接 - 会话在资源调控器分类器中'
end as status ,
ds.cpu_time as cpu_time_ms,
ds.memory_usage*8 as memory_kb,
ds.total_elapsed_time as total_elapsed_time_ms,
case ds.transaction_isolation_level when 0 then '未指定'
when 1 then '未提交读取'
when 2 then '已提交读取'
when 3 then '可重复'
when 4 then '可序列化'
when 5 then '快照'
end '会话的事务隔离级别',
dt.text
from sys.dm_exec_connections dc --执行连接，最近执行的查询信息
cross apply sys.dm_exec_sql_text(dc.most_recent_sql_handle) dt
join sys.dm_exec_sessions ds on dc.session_id=ds.session_id
where ds.login_name= 'LCGS609999'
--where ds.program_name = '.Net SqlClient Data Provider'
ORDER BY dt.text
--kill 53;
-- 检查分析死锁信息
1、使用sql profiler 抓取死锁链及图
2、跟踪分析SQL日志
dbcc traceon (1204, 3605, -1)
go
dbcc tracestatus(-1)
go
参考资料：
http://support.microsoft.com/zh-cn/kb/832524
-- 检查分析阻塞信息
WHILE 1=1
BEGIN
EXEC master.dbo.sp_blocker_pss80 -- or sp_blocker_pss08
-- Or for fast mode
-- EXEC master.dbo.sp_blocker_pss80 @fast=1
-- Or for latch mode
-- EXEC master.dbo.sp_blocker_pss80 @latch=1
WAITFOR DELAY '00:00:15'
END
GO
cmd执行命令：
osql -E -Sserver -icheckblk.sql -ocheckblk.out -w2000
参考资料：
http://support.microsoft.com/zh-cn/kb/271509
揭开隐藏数据的面纱，优化应用程序性能
https://msdn.microsoft.com/zh-cn/magazine/cc135978.aspx
