SQL Server DMVs in Action 学习笔记

发现一本挺好的书《SQL Server DMVs in Action》，里面介绍了些常用视图、常用监控语句写法、还有大量sql脚本。

 

dmv、dmf、目录视图（例如sys.indexes,sys.columns）
DMVs contain dynamic data and catalog views contain static data
Catalog views contain both server- and database-level objects and tend to be
more user friendly

查sqlserver有哪些dmv视图
SELECT name, type_desc FROM sys.system_objects WHERE name LIKE 'dm_%' ORDER BY name;

sp_who sp_who2区别

简单阻塞监控思路

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
WAITFOR TIME '19:00:00'
GO
PRINT GETDATE()
EXEC master.dbo.dba_BlockTracer
IF @@ROWCOUNT > 0
BEGIN
SELECT GETDATE() AS TIME
EXEC master.dbo.dba_WhatSQLIsExecuting
END
WAITFOR DELAY '00:00:15'
GO 500

查top慢sql

SELECT TOP 50
CAST(qs.total_elapsed_time / 1000000.0 AS DECIMAL(28, 2))
AS [Total Elapsed Duration (s)]
, qs.execution_count
, SUBSTRING (qt.text,(qs.statement_start_offset/2) + 1,
((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) + 1) AS [Individual Query]
, qt.text AS [Parent Query]
, DB_NAME(qt.dbid) AS DatabaseName
, qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY total_elapsed_time DESC

限制只查某DB
SELECT SUM(qs.total_logical_reads) AS [Total Reads]
, SUM(qs.total_logical_writes) AS [Total Writes]
, DB_NAME(qt.dbid) AS DatabaseName
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
WHERE DB_NAME(qt.dbid) = 'ParisDev'
GROUP BY DB_NAME(qt.dbid);

查询缺失索引
SELECT TOP 20
ROUND(s.avg_total_user_cost * s.avg_user_impact *
(s.user_seeks + s.user_scans),0) AS [Total Cost]
, s.avg_user_impact
, d.statement AS TableName
, d.equality_columns
, d.inequality_columns
, d.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

Find the most missing indexes

SELECT
DB_NAME(database_id) AS DatabaseName
, COUNT(*) AS [Missing Index Count]
FROM sys.dm_db_missing_index_details
GROUP BY DB_NAME(database_id)
ORDER BY [Missing Index Count] DESC

根据sql文本从cache plan中找到其执行计划，还能看到该执行计划被用次数
SELECT TOP 20
st.text AS [SQL]
, cp.cacheobjtype
, cp.objtype
, COALESCE(DB_NAME(st.dbid),
DB_NAME(CAST(pa.value AS INT))+'*',
'Resource') AS [DatabaseName]
, cp.usecounts AS [Plan usage]
, qp.query_plan
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp
OUTER APPLY sys.dm_exec_plan_attributes(cp.plan_handle) pa
WHERE pa.attribute = 'dbid'
AND st.text LIKE '%CREATE PROCEDURE%'

查询dmv与dmf所需权限
There are two levels of DMV and DMF usage, namely, server-scoped and database-scoped.
Server-scoped requires VIEW SERVER STATE permission on the server, and databasescoped requires VIEW DATABASE STATE permission on the database.

clear DMVs所需权限（勿在生产环境操作）
ALTER SERVER STATE permission on the server.

最简单方法：重启sqlserver服务
在server级清理 DBCC FREEPROCCACHE
2008及以上版本，DBCC FREEPROCCACHE可以指定plan_handle, sql_handle, or pool_name参数
DB级清理
DECLARE @DB_ID INT
SET @DB_ID = DB_ID('NameOfDatabaseToClear') -- Change this to your DB
DBCC FLUSHPROCINDB(@DB_ID)

cross apply用于关联表和表值函数TVF，类似INNER JOIN；OUTER APPLY则类似OUTER JOIN

f the right table source returns a row when
it accepts a parameter from the left table source. A CROSS APPLY can be viewed as similar to an INNER JOIN. The related OUTER APPLY returns a row from the left table
source irrespective of whether the parameter passed to the right table source returns a
row; in this respect it acts like an OUTER JOIN.

例如
SELECT TOP 20 qt.text, qs.last_execution_time
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt

输出每个DB名
EXEC sp_MSForEachDB 'PRINT ''?'';'

输出DB中每个表行数
EXEC sp_MSForEachTable 'PRINT ''?''; SELECT ''?'' as [TableName],
COUNT(*) AS [RowCount] FROM ?;'

遍历实例中所有数据库

SELECT
DB_NAME() AS DatabaseName
, SCHEMA_NAME(o.Schema_ID) AS SchemaName
, OBJECT_NAME(s.[object_id]) AS TableName
, i.name AS IndexName
, (s.user_seeks + s.user_scans + s.user_lookups) AS [Usage]
, s.user_updates
, i.fill_factor
INTO #TempUsage
FROM sys.dm_db_index_usage_stats s
INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id]
AND s.index_id = i.index_id
INNER JOIN sys.objects o ON i.object_id = O.object_id
WHERE 1=2
EXEC sp_MSForEachDB 'USE [?];
INSERT INTO #TempUsage
SELECT TOP 10
DB_NAME() AS DatabaseName
, SCHEMA_NAME(o.Schema_ID) AS SchemaName
, OBJECT_NAME(s.[object_id]) AS TableName
, i.name AS IndexName
, (s.user_seeks + s.user_scans + s.user_lookups) AS [Usage]
, s.user_updates
, i.fill_factor
FROM sys.dm_db_index_usage_stats s
INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id]
AND s.index_id = i.index_id
INNER JOIN sys.objects o ON i.object_id = O.object_id
WHERE s.database_id = DB_ID()
AND i.name IS NOT NULL
AND OBJECTPROPERTY(s.[object_id], ''IsMsShipped'') = 0
ORDER BY [Usage] DESC'
SELECT TOP 10 * FROM #TempUsage ORDER BY [Usage] DESC
DROP TABLE #TempUsage

计算两次快照间差值

-- 1. Get pre-work snapshot
SELECT sql_handle, plan_handle, total_elapsed_time
, execution_count, statement_start_offset, statement_end_offset
INTO #PreWorkSnapShot
FROM sys.dm_exec_query_stats

-- 2. Run queries
EXEC dbo.IWSR
SELECT * FROM dbo.appdate

-- 3. Get post-work snapshot
SELECT sql_handle, plan_handle, total_elapsed_time
, execution_count, statement_start_offset, statement_end_offset
INTO #PostWorkSnapShot
FROM sys.dm_exec_query_stats

-- 4. Extract delta
SELECT
p2.total_elapsed_time - ISNULL(p1.total_elapsed_time, 0) AS [Duration]
, SUBSTRING (qt.text,p2.statement_start_offset/2 + 1,
((CASE WHEN p2.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
ELSE p2.statement_end_offset
END - p2.statement_start_offset)/2) + 1) AS [Individual Query]
, qt.text AS [Parent Query]
, DB_NAME(qt.dbid) AS DatabaseName
FROM #PreWorkSnapShot p1
RIGHT OUTER JOIN -- 注意是RIGHT OUTER JOIN，因为新快照中可能有新运行的sql
#PostWorkSnapShot p2 ON p2.sql_handle =
ISNULL(p1.sql_handle, p2.sql_handle)
AND p2.plan_handle = ISNULL(p1.plan_handle, p2.plan_handle)
AND p2.statement_start_offset =
ISNULL(p1.statement_start_offset, p2.statement_start_offset)
AND p2.statement_end_offset =
ISNULL(p1.statement_end_offset, p2.statement_end_offset)
CROSS APPLY sys.dm_exec_sql_text(p2.sql_handle) as qt
WHERE p2.execution_count != ISNULL(p1.execution_count, 0)
ORDER BY [Duration] DESC
DROP TABLE #PreWorkSnapShot
DROP TABLE #PostWorkSnapShot

从执行计划看是否缺少统计信息
In the XML version of the
cached plan, missing column statistics are identified by the XML element name
<ColumnsWithNoStatistics>. In the graphical version of the cached plan, they’re represented as a yellow triangle containing an exclamation mark

动态sql
-- 查询当前库中表名
SELECT
TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME
INTO #TableDetails
FROM INFORMATION_SCHEMA.tables
WHERE TABLE_TYPE = 'BASE TABLE'

-- 构造动态sql，统计库中每个表行数
DECLARE @DynamicSQL NVARCHAR(MAX)
SET @DynamicSQL = ''
SELECT
@DynamicSQL = @DynamicSQL + CHAR(10)
+ ' SELECT COUNT_BIG(*) as ['
+ TABLE_CATALOG + '.' + TABLE_SCHEMA + '.' + TABLE_NAME
+ + '] FROM ' + QUOTENAME(TABLE_CATALOG) + '.'
+ QUOTENAME(TABLE_SCHEMA) + '. ' + QUOTENAME(TABLE_NAME)
FROM #TableDetails

PRINT @DynamicSQL
EXECUTE sp_executesql @DynamicSQL
DROP TABLE #TableDetails

动态sql+输出 large variables 内容（循环输出动态sql文本）

SELECT
TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME
INTO #TableDetails
FROM INFORMATION_SCHEMA.tables
WHERE TABLE_TYPE = 'BASE TABLE'

-- 构造动态sql
DECLARE @DynamicSQL NVARCHAR(MAX)
SET @DynamicSQL = ''
SELECT
@DynamicSQL = @DynamicSQL + CHAR(10)
+ ' SELECT COUNT_BIG(*) as ['
+ TABLE_CATALOG + '.' + TABLE_SCHEMA + '.' + TABLE_NAME
+ + '] FROM ' + QUOTENAME(TABLE_CATALOG) + '.'
+ QUOTENAME(TABLE_SCHEMA) + '. ' + QUOTENAME(TABLE_NAME)
FROM #TableDetails

--EXECUTE sp_executesql @DynamicSQL

DECLARE @StartOffset INT
DECLARE @Length INT
SET @StartOffset = 0
SET @Length = 4000
WHILE (@StartOffset < LEN(@DynamicSQL))
BEGIN
PRINT SUBSTRING(@DynamicSQL, @StartOffset, @Length)
SET @StartOffset = @StartOffset + @Length
END
PRINT SUBSTRING(@DynamicSQL, @StartOffset, @Length)
DROP TABLE #TableDetails

索引fill_factor

SELECT DB_NAME() AS DatabaseName
, SCHEMA_NAME(o.Schema_ID) AS SchemaName
, OBJECT_NAME(s.[object_id]) AS TableName
, i.name AS IndexName
, i.fill_factor
FROM sys.dm_db_index_usage_stats s
INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id]
AND s.index_id = i.index_id
INNER JOIN sys.objects o ON i.object_id = O.object_id
WHERE s.database_id = DB_ID()
AND i.name IS NOT NULL
AND OBJECTPROPERTY(s.[object_id], 'IsMsShipped') = 0
ORDER BY fill_factor DESC

索引碎片程度
SELECT i.name AS IndexName
, ROUND(s.avg_fragmentation_in_percent,2) AS [Fragmentation %]
FROM sys.dm_db_index_physical_stats(DB_ID('parisdev'),
OBJECT_ID('currency'), NULL, NULL, NULL) s
INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id]
AND s.index_id = i.index_id

使用很少的索引（所有DB）

SELECT
DB_NAME() AS DatabaseName
, SCHEMA_NAME(o.Schema_ID) AS SchemaName
, OBJECT_NAME(s.[object_id]) AS TableName
, i.name AS IndexName
, s.user_updates
, s.system_seeks + s.system_scans + s.system_lookups
AS [System usage]
INTO #TempUnusedIndexes
FROM sys.dm_db_index_usage_stats s
INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id]
AND s.index_id = i.index_id
INNER JOIN sys.objects o ON i.object_id = O.object_id
WHERE 1=2
EXEC sp_MSForEachDB 'USE [?];
INSERT INTO #TempUnusedIndexes
SELECT TOP 20
DB_NAME() AS DatabaseName
, SCHEMA_NAME(o.Schema_ID) AS SchemaName
, OBJECT_NAME(s.[object_id]) AS TableName
, i.name AS IndexName
, s.user_updates
, s.system_seeks + s.system_scans + s.system_lookups
AS [System usage]
FROM sys.dm_db_index_usage_stats s
INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id]
AND s.index_id = i.index_id
INNER JOIN sys.objects o ON i.object_id = O.object_id
WHERE s.database_id = DB_ID()
AND OBJECTPROPERTY(s.[object_id], ''IsMsShipped'') = 0
AND s.user_seeks = 0
AND s.user_scans = 0
AND s.user_lookups = 0
AND i.name IS NOT NULL
ORDER BY s.user_updates DESC'
SELECT TOP 20 * FROM #TempUnusedIndexes ORDER BY [user_updates] DESC
DROP TABLE #TempUnusedIndexes

完全没用过的索引
SELECT
DB_NAME() AS DatbaseName
, SCHEMA_NAME(O.Schema_ID) AS SchemaName
, OBJECT_NAME(I.object_id) AS TableName
, I.name AS IndexName
INTO #TempNeverUsedIndexes
FROM sys.indexes I INNER JOIN sys.objects O ON I.object_id = O.object_id
WHERE 1=2

EXEC sp_MSForEachDB 'USE [?];
INSERT INTO #TempNeverUsedIndexes
SELECT
DB_NAME() AS DatbaseName
, SCHEMA_NAME(O.Schema_ID) AS SchemaName
, OBJECT_NAME(I.object_id) AS TableName
, I.NAME AS IndexName
FROM sys.indexes I INNER JOIN sys.objects O ON I.object_id = O.object_id
LEFT OUTER JOIN sys.dm_db_index_usage_stats S ON S.object_id = I.object_id
AND I.index_id = S.index_id
AND DATABASE_ID = DB_ID()
WHERE OBJECTPROPERTY(O.object_id,''IsMsShipped'') = 0
AND I.name IS NOT NULL
AND S.object_id IS NULL'
SELECT * FROM #TempNeverUsedIndexes
ORDER BY DatbaseName, SchemaName, TableName, IndexName
DROP TABLE #TempNeverUsedIndexes

维护消耗高的索引

SELECT
DB_NAME() AS DatabaseName
, SCHEMA_NAME(o.Schema_ID) AS SchemaName
, OBJECT_NAME(s.[object_id]) AS TableName
, i.name AS IndexName
, (s.user_updates ) AS [update usage]
, (s.user_seeks + s.user_scans + s.user_lookups)
AS [Retrieval usage]
, (s.user_updates) -
(s.user_seeks + s.user_scans + s.user_lookups) AS [Maintenance cost]
, s.system_seeks + s.system_scans + s.system_lookups AS [System usage]
, s.last_user_seek
, s.last_user_scan
, s.last_user_lookup
INTO #TempMaintenanceCost
FROM sys.dm_db_index_usage_stats s
INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id]
AND s.index_id = i.index_id
INNER JOIN sys.objects o ON i.object_id = O.object_id
WHERE 1=2
EXEC sp_MSForEachDB 'USE [?];
INSERT INTO #TempMaintenanceCost
SELECT TOP 20
DB_NAME() AS DatabaseName
, SCHEMA_NAME(o.Schema_ID) AS SchemaName
, OBJECT_NAME(s.[object_id]) AS TableName
, i.name AS IndexName
, (s.user_updates ) AS [update usage]
, (s.user_seeks + s.user_scans + s.user_lookups)
AS [Retrieval usage]
, (s.user_updates) -
(s.user_seeks + user_scans +
s.user_lookups) AS [Maintenance cost]
, s.system_seeks + s.system_scans + s.system_lookups AS [System usage]
, s.last_user_seek
, s.last_user_scan
, s.last_user_lookup
FROM sys.dm_db_index_usage_stats s
INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id]
AND s.index_id = i.index_id
INNER JOIN sys.objects o ON i.object_id = O.object_id
WHERE s.database_id = DB_ID()
AND i.name IS NOT NULL
AND OBJECTPROPERTY(s.[object_id], ''IsMsShipped'') = 0
AND (s.user_seeks + s.user_scans + s.user_lookups) > 0
ORDER BY [Maintenance cost] DESC'
SELECT top 20 * FROM #TempMaintenanceCost ORDER BY [Maintenance cost] DESC
DROP TABLE #TempMaintenanceCost

按时间记录索引使用增长情况
-- Get pre-index counter values
SELECT
SchemaName = ss.name
, TableName = st.name
, IndexName = ISNULL(si.name, '')
, IndexType = si.type_desc
, user_updates = ISNULL(ius.user_updates, 0)
, user_seeks = ISNULL(ius.user_seeks, 0)
, user_scans = ISNULL(ius.user_scans, 0)
, user_lookups = ISNULL(ius.user_lookups, 0)
, ssi.rowcnt
, ssi.rowmodctr
, si.fill_factor
INTO #IndexStatsPre
FROM sys.dm_db_index_usage_stats ius
RIGHT OUTER JOIN sys.indexes si ON ius.[object_id] = si.[object_id]
AND ius.index_id = si.index_id
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 ius.database_id = DB_ID()
AND OBJECTPROPERTY(ius.[object_id], 'IsMsShipped') = 0;

-- Run routine or native SQL
SELECT COB, COUNT(*) FROM dbo.request GROUP BY COB;

-- Get post-index counter values
SELECT
SchemaName = ss.name
, TableName = st.name
, IndexName = ISNULL(si.name, '')
, IndexType = si.type_desc
, user_updates = ISNULL(ius.user_updates, 0)
, user_seeks = ISNULL(ius.user_seeks, 0)
, user_scans = ISNULL(ius.user_scans, 0)
, user_lookups = ISNULL(ius.user_lookups, 0)
, ssi.rowcnt
, ssi.rowmodctr
, si.fill_factor
INTO #IndexStatsPost
FROM sys.dm_db_index_usage_stats ius
RIGHT OUTER JOIN sys.indexes si ON ius.[object_id] = si.[object_id]
AND ius.index_id = si.index_id
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 ius.database_id = DB_ID()
AND OBJECTPROPERTY(ius.[object_id], 'IsMsShipped') = 0;

-- Determine which index counters have changed
SELECT
DB_NAME() AS DatabaseName
, po.[SchemaName]
, po.[TableName]
, po.[IndexName]
, po.[IndexType]
, po.user_updates - ISNULL(pr.user_updates, 0) AS [User Updates]
, po.user_seeks - ISNULL(pr.user_seeks, 0) AS [User Seeks]
, po.user_scans - ISNULL(pr.user_scans, 0) AS [User Scans]
, po.user_lookups - ISNULL(pr.user_lookups , 0) AS [User Lookups]
, po.rowcnt - pr.rowcnt AS [Rows Inserted]
, po.rowmodctr - pr.rowmodctr AS [Updates I/U/D]
, po.fill_factor
FROM #IndexStatsPost po LEFT OUTER JOIN #IndexStatsPre pr
ON pr.SchemaName = po.SchemaName
AND pr.TableName = po.TableName
AND pr.IndexName = po.IndexName
AND pr.IndexType = po.IndexType
WHERE ISNULL(pr.user_updates, 0) != po.user_updates
OR ISNULL(pr.user_seeks, 0) != po.user_seeks
OR ISNULL(pr.user_scans, 0) != po.user_scans
OR ISNULL(pr.user_lookups, 0) != po.user_lookups
ORDER BY po.[SchemaName], po.[TableName], po.[IndexName];

DROP TABLE #IndexStatsPre
DROP TABLE #IndexStatsPost

索引统计信息

SELECT
ss.name AS SchemaName
, st.name AS TableName
, s.name AS IndexName
, STATS_DATE(s.id,s.indid) AS 'Statistics Last Updated'
, s.rowcnt AS 'Row Count'
, s.rowmodctr AS 'Number Of Changes'
, CAST((CAST(s.rowmodctr AS DECIMAL(28,8))/CAST(s.rowcnt AS
DECIMAL(28,2)) * 100.0)
AS DECIMAL(28,2)) AS '% Rows Changed'
FROM sys.sysindexes s
INNER JOIN sys.tables st ON st.[object_id] = s.[id]
INNER JOIN sys.schemas ss ON ss.[schema_id] = st.[schema_id]
WHERE s.id > 100
AND s.indid > 0
AND s.rowcnt >= 500
ORDER BY SchemaName, TableName, IndexName

查询在哪里被用到
SELECT TOP 20
SUBSTRING (qt.text,(qs.statement_start_offset/2) + 1,
((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) + 1) AS [Individual Query]
, qt.text AS [Parent Query]
, DB_NAME(qt.dbid) AS DatabaseName
, qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
WHERE SUBSTRING (qt.text,(qs.statement_start_offset/2) + 1,
((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) + 1)
LIKE '%insert into dbo.deal%'

找到被block最长时间的sql

SELECT TOP 20
CAST((qs.total_elapsed_time - qs.total_worker_time) /
1000000.0 AS DECIMAL(28,2)) AS [Total time blocked (s)]
, CAST(qs.total_worker_time * 100.0 / qs.total_elapsed_time
AS DECIMAL(28,2)) AS [% CPU]
, CAST((qs.total_elapsed_time - qs.total_worker_time)* 100.0 /
qs.total_elapsed_time AS DECIMAL(28, 2)) AS [% Waiting]
, qs.execution_count
, CAST((qs.total_elapsed_time - qs.total_worker_time) / 1000000.0
/ qs.execution_count AS DECIMAL(28, 2)) AS [Blocking average (s)]
, SUBSTRING (qt.text,(qs.statement_start_offset/2) + 1,
((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) + 1) AS [Individual Query]
, qt.text AS [Parent Query]
, DB_NAME(qt.dbid) AS DatabaseName
, qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
WHERE qs.total_elapsed_time > 0
ORDER BY [Total time blocked (s)] DESC

最耗IO的sql

SELECT TOP 20
[Total IO] = (qs.total_logical_reads + qs.total_logical_writes)
, [Average IO] = (qs.total_logical_reads + qs.total_logical_writes) /
qs.execution_count
, qs.execution_count
, SUBSTRING (qt.text,(qs.statement_start_offset/2) + 1,
((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) + 1) AS [Individual Query]
, qt.text AS [Parent Query]
, DB_NAME(qt.dbid) AS DatabaseName
, qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY [Total IO] DESC

找到缺少统计信息的sql

SELECT TOP 20
st.text AS [Parent Query]
, DB_NAME(st.dbid)AS [DatabaseName]
, cp.usecounts AS [Usage Count]
, qp.query_plan
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp
WHERE CAST(qp.query_plan AS NVARCHAR(MAX))
LIKE '%<ColumnsWithNoStatistics>%'
ORDER BY cp.usecounts DESC

找到缺少索引的sql

SELECT TOP 20
st.text AS [Parent Query]
, DB_NAME(st.dbid)AS [DatabaseName]
, cp.usecounts AS [Usage Count]
, qp.query_plan
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp
WHERE CAST(qp.query_plan AS NVARCHAR(MAX))
LIKE '%<MissingIndexes>%'
ORDER BY cp.usecounts DESC

对比sql平均及最后一次执行各项消耗
-- 1. Get raw values
SELECT TOP 100
qs.execution_count AS [Runs]
, (qs.total_worker_time - qs.last_worker_time) / (qs.execution_count - 1)
AS [Avg time]
, qs.last_worker_time AS [Last time]
, (qs.last_worker_time - ((qs.total_worker_time - qs.last_worker_time) /
(qs.execution_count - 1))) AS [Time Deviation]
, CASE WHEN qs.last_worker_time = 0
THEN 100
ELSE (qs.last_worker_time - ((qs.total_worker_time -
qs.last_worker_time) / (qs.execution_count - 1))) * 100
END
/ (((qs.total_worker_time - qs.last_worker_time) /
(qs.execution_count - 1.0))) AS [% Worker Deviation]
,qs.last_logical_reads + qs.last_logical_writes + qs.last_physical_reads
AS [Last IO]
, ((qs.total_logical_reads + qs.total_logical_writes +
qs.total_physical_reads) -
(qs.last_logical_reads + last_logical_writes
+ qs.last_physical_reads))
/ (qs.execution_count - 1) AS [Avg IO]
, SUBSTRING (qt.text,(qs.statement_start_offset/2) + 1,
((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) + 1) AS [Individual Query]
, qt.text AS [Parent Query]
, DB_NAME(qt.dbid) AS [DatabaseName]
INTO #SlowQueries
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) qt
WHERE qs.execution_count > 1
AND qs.total_worker_time != qs.last_worker_time
ORDER BY [% Time Deviation] DESC

-- 2. Calculate IO Deviation and %IO Deviation
SELECT TOP 100 [Runs]
, [Avg time]
, [Last time]
, [Time Deviation]
, [% Time Deviation]
, [Last IO]
, [Avg IO]
, [Last IO] - [Avg IO] AS [IO Deviation]
, CASE WHEN [Avg IO] = 0
THEN 0
ELSE ([Last IO]- [Avg IO]) * 100 / [Avg IO]
END AS [% IO Deviation]
, [Individual Query]
, [Parent Query]
, [DatabaseName]
INTO #SlowQueriesByIO
FROM #SlowQueries
ORDER BY [% Time Deviation] DESC

-- 3. Calculate Impedance
SELECT TOP 100
[Runs]
, [Avg time]
, [Last time]
, [Time Deviation]
, [% Time Deviation]
, [Last IO]
, [Avg IO]
, [IO Deviation]
, [% IO Deviation]
, [Impedance] = [% Time Deviation] - [% IO Deviation]
, [Individual Query]
, [Parent Query]
, [DatabaseName]
FROM #SlowQueriesByIO
WHERE [% Time Deviation] - [% IO Deviation] > 20
ORDER BY [Impedance] DESC
DROP TABLE #SlowQueries
DROP TABLE #SlowQueriesByIO

合并DMV快照

-- Missing indexes counters
SELECT
index_group_handle, index_handle
, avg_total_user_cost, avg_user_impact, user_seeks, user_scans
INTO #PreWorkMissingIndexes
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

-- Query stats counters
SELECT
sql_handle, plan_handle, total_elapsed_time, total_worker_time
, total_logical_reads, total_logical_writes, total_clr_time
, execution_count, statement_start_offset, statement_end_offset
INTO #PreWorkQuerySnapShot
FROM sys.dm_exec_query_stats

-- OS counters
SELECT
[object_name], [counter_name], [instance_name]
, [cntr_value], [cntr_type]
INTO #PreWorkOSSnapShot
FROM sys.dm_os_performance_counters

-- Wait stats counters
SELECT
wait_type, waiting_tasks_count
, wait_time_ms, max_wait_time_ms, signal_wait_time_ms
INTO #PreWorkWaitStats
FROM sys.dm_os_wait_stats

-- Put your query or SQL batch here (or a time delay)
WAITFOR DELAY '00:05:00'

-- Post-work snapshots
SELECT wait_type, waiting_tasks_count, wait_time_ms
, max_wait_time_ms, signal_wait_time_ms
INTO #PostWorkWaitStats
FROM sys.dm_os_wait_stats

SELECT [object_name], [counter_name], [instance_name]
, [cntr_value], [cntr_type]
INTO #PostWorkOSSnapShot
FROM sys.dm_os_performance_counters

SELECT sql_handle, plan_handle, total_elapsed_time, total_worker_time
, total_logical_reads, total_logical_writes, total_clr_time
, execution_count, statement_start_offset, statement_end_offset
INTO #PostWorkQuerySnapShot
FROM sys.dm_exec_query_stats

SELECT index_group_handle, index_handle
, avg_total_user_cost, avg_user_impact, user_seeks, user_scans
INTO #PostWorkMissingIndexes
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

-- Calculate snapshot deltas
SELECT
p2.total_elapsed_time - ISNULL(p1.total_elapsed_time, 0) AS [Duration]
, p2.total_worker_time - ISNULL(p1.total_worker_time, 0) AS [Time on CPU]
, (p2.total_elapsed_time - ISNULL(p1.total_elapsed_time, 0)) -
(p2.total_worker_time - ISNULL(p1.total_worker_time, 0))
AS [Time blocked]
, p2.total_logical_reads - ISNULL(p1.total_logical_reads, 0) AS [Reads]
, p2.total_logical_writes - ISNULL(p1.total_logical_writes, 0)
AS [Writes]
, p2.total_clr_time - ISNULL(p1.total_clr_time, 0) AS [CLR time]
, p2.execution_count - ISNULL(p1.execution_count, 0) AS [Executions]
, SUBSTRING (qt.text,p2.statement_start_offset/2 + 1,
((CASE WHEN p2.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
ELSE p2.statement_end_offset
END - p2.statement_start_offset)/2) + 1) AS [Individual Query]
, qt.text AS [Parent Query]
, DB_NAME(qt.dbid) AS DatabaseName
FROM #PreWorkQuerySnapShot p1
RIGHT OUTER JOIN
#PostWorkQuerySnapShot p2 ON p2.sql_handle =
ISNULL(p1.sql_handle, p2.sql_handle)
AND p2.plan_handle = ISNULL(p1.plan_handle, p2.plan_handle)
AND p2.statement_start_offset =
ISNULL(p1.statement_start_offset, p2.statement_start_offset)
AND p2.statement_end_offset =
ISNULL(p1.statement_end_offset, p2.statement_end_offset)
CROSS APPLY sys.dm_exec_sql_text(p2.sql_handle) as qt
WHERE p2.execution_count != ISNULL(p1.execution_count, 0)
AND qt.text NOT LIKE '--ThisRoutineIdentifier%'
SELECT
p2.wait_time_ms - ISNULL(p1.wait_time_ms, 0) AS wait_time_ms
, p2.signal_wait_time_ms - ISNULL(p1.signal_wait_time_ms, 0)
AS signal_wait_time_ms
, ((p2.wait_time_ms - ISNULL(p1.wait_time_ms, 0))
- (p2.signal_wait_time_ms
- ISNULL(p1.signal_wait_time_ms, 0))) AS RealWait
, p2.wait_type
FROM #PreWorkWaitStats p1
RIGHT OUTER JOIN
#PostWorkWaitStats p2 ON p2.wait_type = ISNULL(p1.wait_type, p2.wait_type)
WHERE p2.wait_time_ms - ISNULL(p1.wait_time_ms, 0) > 0
AND p2.wait_type NOT LIKE '%SLEEP%'
AND p2.wait_type != 'WAITFOR'
ORDER BY RealWait DESC
SELECT
ROUND((p2.avg_total_user_cost - ISNULL(p1.avg_total_user_cost, 0))
* (p2.avg_user_impact - ISNULL(p1.avg_user_impact, 0)) *
((p2.user_seeks - ISNULL(p1.user_seeks, 0))
+ (p2.user_scans - ISNULL(p1.user_scans, 0))),0)
AS [Total Cost]
, p2.avg_total_user_cost - ISNULL(p1.avg_total_user_cost, 0)
AS avg_total_user_cost
, p2.avg_user_impact - ISNULL(p1.avg_user_impact, 0) AS avg_user_impact
, p2.user_seeks - ISNULL(p1.user_seeks, 0) AS user_seeks
, p2.user_scans - ISNULL(p1.user_scans, 0) AS user_scans
, d.statement AS TableName
, d.equality_columns
, d.inequality_columns
, d.included_columns
FROM #PreWorkMissingIndexes p1
RIGHT OUTER JOIN
#PostWorkMissingIndexes p2 ON p2.index_group_handle =
ISNULL(p1.index_group_handle, p2.index_group_handle)
AND p2.index_handle =
ISNULL(p1.index_handle, p2.index_handle)
INNER JOIN sys.dm_db_missing_index_details d
ON p2.index_handle = d.index_handle
WHERE p2.avg_total_user_cost - ISNULL(p1.avg_total_user_cost, 0) > 0
OR p2.avg_user_impact - ISNULL(p1.avg_user_impact, 0) > 0
OR p2.user_seeks - ISNULL(p1.user_seeks, 0) > 0
OR p2.user_scans - ISNULL(p1.user_scans, 0) > 0
ORDER BY [Total Cost] DESC
SELECT
p2.object_name, p2.counter_name, p2.instance_name
, ISNULL(p1.cntr_value, 0) AS InitialValue
, p2.cntr_value AS FinalValue
, p2.cntr_value - ISNULL(p1.cntr_value, 0) AS Change
, (p2.cntr_value - ISNULL(p1.cntr_value, 0)) * 100 / p1.cntr_value
AS [% Change]
FROM #PreWorkOSSnapShot p1
RIGHT OUTER JOIN
#PostWorkOSSnapShot p2 ON p2.object_name =
ISNULL(p1.object_name, p2.object_name)
AND p2.counter_name = ISNULL(p1.counter_name, p2.counter_name)
AND p2.instance_name = ISNULL(p1.instance_name, p2.instance_name)
WHERE p2.cntr_value - ISNULL(p1.cntr_value, 0) > 0
AND ISNULL(p1.cntr_value, 0) != 0
ORDER BY [% Change] DESC, Change DESC

-- Tidy up
DROP TABLE #PreWorkQuerySnapShot
DROP TABLE #PostWorkQuerySnapShot
DROP TABLE #PostWorkWaitStats
DROP TABLE #PreWorkWaitStats
DROP TABLE #PreWorkOSSnapShot
DROP TABLE #PostWorkOSSnapShot
DROP TABLE #PreWorkMissingIndexes
DROP TABLE #PostWorkMissingIndexes

重编译次数最多的sql
SELECT TOP 20
qs.plan_generation_num
, qs.total_elapsed_time
, qs.execution_count
, SUBSTRING (qt.text,(qs.statement_start_offset/2) + 1,
((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) + 1) AS [Individual Query]
, qt.text AS [Parent Query]
, DB_NAME(qt.dbid) AS DatabaseName
, qs.creation_time
, qs.last_execution_time
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
ORDER BY plan_generation_num DESC

reset sys.dm_os_wait_stats 视图
DBCC SQLPERF ('sys.dm_os_wait_stats', CLEAR)

查看等待原因
SELECT TOP 20
wait_type, wait_time_ms, signal_wait_time_ms
, wait_time_ms - signal_wait_time_ms AS RealWait
, CONVERT(DECIMAL(12,2), wait_time_ms * 100.0 / SUM(wait_time_ms) OVER())
AS [% Waiting]
, CONVERT(DECIMAL(12,2), (wait_time_ms - signal_wait_time_ms) * 100.0
/ SUM(wait_time_ms) OVER()) AS [% RealWait]
FROM sys.dm_os_wait_stats
WHERE wait_type NOT LIKE '%SLEEP%'
AND wait_type != 'WAITFOR'
ORDER BY wait_time_ms DESC

Column Description
wait_type: Name of the wait type.
wait_time_ms: Total time spent waiting in milliseconds.
signal_wait_time_ms: Total time spent waiting to get on the CPU, when no longer waiting on original cause of wait.
RealWait Total time: spent waiting on original resource. This is wait_time_ms less signal_wait_time_ms.
% Waiting: How much time this wait_type spent waiting as a percentage of all waits.
% RealWait: How much time this RealWait spent waiting as a percentage of all waits.

查sql阻塞信息
SELECT
Blocking.session_id as BlockingSessionId
, Sess.login_name AS BlockingUser
, BlockingSQL.text AS BlockingSQL
, Waits.wait_type WhyBlocked
, Blocked.session_id AS BlockedSessionId
, USER_NAME(Blocked.user_id) AS BlockedUser
, BlockedSQL.text AS BlockedSQL
, DB_NAME(Blocked.database_id) AS DatabaseName
FROM sys.dm_exec_connections AS Blocking
INNER JOIN sys.dm_exec_requests AS Blocked
ON Blocking.session_id = Blocked.blocking_session_id
INNER JOIN sys.dm_os_waiting_tasks AS Waits
ON Blocked.session_id = Waits.session_id
RIGHT OUTER JOIN sys.dm_exec_sessions Sess
ON Blocking.session_id = sess.session_id
CROSS APPLY sys.dm_exec_sql_text(Blocking.most_recent_sql_handle)
AS BlockingSQL
CROSS APPLY sys.dm_exec_sql_text(Blocked.sql_handle) AS BlockedSQL
ORDER BY BlockingSessionId, BlockedSessionId

死锁捕获及处理代码
DECLARE @CurrentTry INT = 1
DECLARE @MaxRetries INT = 3
DECLARE @Complete BIT = 0
WHILE (@Complete = 0)
BEGIN
BEGIN TRY
EXEC dbo.SomeRoutine
SET @Complete = 1
END TRY
BEGIN CATCH
DECLARE @ErrorNum INT
DECLARE @ErrorMessage NVARCHAR(4000)
DECLARE @ErrorState INT
DECLARE @ErrorSeverity INT
SET @ErrorNum = ERROR_NUMBER()
SET @ErrorMessage = ERROR_MESSAGE()
SET @ErrorState = ERROR_STATE()
SET @ErrorSeverity = ERROR_SEVERITY()
IF (@ErrorNum = 1205) AND (@CurrentTry < @MaxRetries)
BEGIN
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION
SET @CurrentTry = @CurrentTry + 1
WAITFOR DELAY '00:00:10'
END
ELSE
BEGIN
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION
SET @Complete = 1
RAISERROR ('An error has occurred'
, @ErrorSeverity
, @ErrorState)
END
END CATCH
END

隔离级别
Read Uncommitted: This causes SELECT statements to ignore most locks and not take out
any locks.
Read Committed: This is the default isolation level. A query will wait until data has been committed or rolled back before SELECTing it.
Repeatable: Read Any locks caused by SELECTs are held until the end of the transaction.
Serializable: Locks are taken out not only on the rows being selected/updated but
also on nearby rows; this prevents the insertion of data that might affect
the query.
Read Committed Snapshot: This relates to keeping a copy of any prechanged data, enabling better concurrency. Statement based.
Snapshot: This relates to keeping a copy of any prechanged data, enabling better
concurrency. Transaction oriented.

sessions, connections, and requests

Sessions contain information about the client and host.The associated DMV is named sys.dm_exec_sessions. A session can have one or more connections

A connection contains information about connections established to SQL Server.The
associated DMV is named sys.dm_exec_connections. A connection can have one or
more requests.

A request contains information about SQL queries executing on SQL Server. The associated DMV is named sys.dm_exec_requests

查看三者关系
SELECT *
FROM sys.dm_exec_sessions s
LEFT OUTER JOIN sys.dm_exec_connections c
ON s.session_id = c.session_id
LEFT OUTER JOIN sys.dm_exec_requests r
ON c.connection_id = r.connection_id
WHERE s.session_id > 50

查看当前锁持有情况
SELECT DB_NAME(resource_database_id) AS DatabaseName
, request_session_id
, resource_type
, CASE
WHEN resource_type = 'OBJECT'
THEN OBJECT_NAME(resource_associated_entity_id)
WHEN resource_type IN ('KEY', 'PAGE', 'RID')
THEN (SELECT OBJECT_NAME(OBJECT_ID)
FROM sys.partitions p
WHERE p.hobt_id = l.resource_associated_entity_id)
END AS resource_type_name
, request_status
, request_mode
FROM sys.dm_tran_locks l
WHERE request_session_id !=@@spid
ORDER BY request_session_id

定位争夺的资源（含sql语句）
SELECT
tl1.resource_type
, DB_NAME(tl1.resource_database_id) AS DatabaseName
, tl1.resource_associated_entity_id
, tl1.request_session_id
, tl1.request_mode
, tl1.request_status
, CASE
WHEN tl1.resource_type = 'OBJECT'
THEN OBJECT_NAME(tl1.resource_associated_entity_id)
WHEN tl1.resource_type IN ('KEY', 'PAGE', 'RID')
THEN (SELECT OBJECT_NAME(OBJECT_ID)
FROM sys.partitions s
WHERE s.hobt_id = tl1.resource_associated_entity_id)
END AS resource_type_name
, t.text AS [Parent Query]
, SUBSTRING (t.text,(r.statement_start_offset/2) + 1,
((CASE WHEN r.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), t.text)) * 2
ELSE r.statement_end_offset
END - r.statement_start_offset)/2) + 1) AS [Individual Query]
FROM sys.dm_tran_locks as tl1
INNER JOIN sys.dm_tran_locks as tl2
ON tl1.resource_associated_entity_id = tl2.resource_associated_entity_id
AND tl1.request_status <> tl2.request_status
AND (tl1.resource_description = tl2.resource_description
OR (tl1.resource_description IS NULL
AND tl2.resource_description IS NULL))
INNER JOIN sys.dm_exec_connections c
ON tl1.request_session_id = c.most_recent_session_id
CROSS APPLY sys.dm_exec_sql_text(c.most_recent_sql_handle) t
LEFT OUTER JOIN sys.dm_exec_requests r ON c.connection_id = r.connection_id
ORDER BY tl1.resource_associated_entity_id, tl1.request_status

找到open tran的inactive会话及最后执行的sql
SELECT es.session_id, es.login_name, es.host_name, est.text
, cn.last_read, cn.last_write, es.program_name
FROM sys.dm_exec_sessions es
INNER JOIN sys.dm_tran_session_transactions st
ON es.session_id = st.session_id
INNER JOIN sys.dm_exec_connections cn
ON es.session_id = cn.session_id
CROSS APPLY sys.dm_exec_sql_text(cn.most_recent_sql_handle) est
LEFT OUTER JOIN sys.dm_exec_requests er
ON st.session_id = er.session_id
AND er.session_id IS NULL

What’s blocked—active and idle sessions with open transactions

SELECT
Waits.wait_duration_ms / 1000 AS WaitInSeconds
, Blocking.session_id as BlockingSessionId
, DB_NAME(Blocked.database_id) AS DatabaseName
, Sess.login_name AS BlockingUser
, Sess.host_name AS BlockingLocation
, BlockingSQL.text AS BlockingSQL
, Blocked.session_id AS BlockedSessionId
, BlockedSess.login_name AS BlockedUser
, BlockedSess.host_name AS BlockedLocation
, BlockedSQL.text AS BlockedSQL
, SUBSTRING (BlockedSQL.text, (BlockedReq.statement_start_offset/2) + 1,
((CASE WHEN BlockedReq.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), BlockedSQL.text)) * 2
ELSE BlockedReq.statement_end_offset
END - BlockedReq.statement_start_offset)/2) + 1)
AS [Blocked Individual Query]
, Waits.wait_type
FROM sys.dm_exec_connections AS Blocking
INNER JOIN sys.dm_exec_requests AS Blocked
ON Blocking.session_id = Blocked.blocking_session_id
INNER JOIN sys.dm_exec_sessions Sess
ON Blocking.session_id = sess.session_id
INNER JOIN sys.dm_tran_session_transactions st
ON Blocking.session_id = st.session_id
LEFT OUTER JOIN sys.dm_exec_requests er
ON st.session_id = er.session_id
INNER JOIN sys.dm_os_waiting_tasks AS Waits
ON Blocked.session_id = Waits.session_id
CROSS APPLY sys.dm_exec_sql_text(Blocking.most_recent_sql_handle)
AS BlockingSQL
INNER JOIN sys.dm_exec_requests AS BlockedReq
ON Waits.session_id = BlockedReq.session_id

tempdb中的对象
User objects: Local temporary tables and indexes,Global temporary tables and indexes,Table variables,User-defined tables and indexes
Internal objects: Sort results,Hash joins,XML variables,Work tables for cursors
Version store objects: Temporary large object (LOB) storage,Spool operations that store intermediate results,Snapshot isolation,Triggers,Multiple active result sets (MARS),Online index build

tempdb已用及可用空间
SELECT SUM(user_object_reserved_page_count
+ internal_object_reserved_page_count
+ version_store_reserved_page_count
+ mixed_extent_page_count
+ unallocated_extent_page_count) * (8.0/1024.0)
AS [TotalSizeOfTempDB(MB)]
, SUM(user_object_reserved_page_count
+ internal_object_reserved_page_count
+ version_store_reserved_page_count
+ mixed_extent_page_count) * (8.0/1024.0)
AS [UsedSpace (MB)]
, SUM(unallocated_extent_page_count * (8.0/1024.0))
AS [FreeSpace (MB)]
FROM sys.dm_db_file_space_usage

tempdb使用详情（按类型）
SELECT
SUM (user_object_reserved_page_count) * (8.0/1024.0)
AS [User Objects (MB)],
SUM (internal_object_reserved_page_count) * (8.0/1024.0)
AS [Internal Objects (MB)],
SUM (version_store_reserved_page_count) * (8.0/1024.0)
AS [Version Store (MB)],
SUM (mixed_extent_page_count)* (8.0/1024.0)
AS [Mixed Extent (MB)],
SUM (unallocated_extent_page_count)* (8.0/1024.0)
AS [Unallocated (MB)]
FROM sys.dm_db_file_space_usage

会话使用tempdb情况
SELECT es.session_id
, ec.connection_id
, es.login_name
, es.host_name
, st.text
, su.user_objects_alloc_page_count
, su.user_objects_dealloc_page_count
, su.internal_objects_alloc_page_count
, su.internal_objects_dealloc_page_count
, ec.last_read
, ec.last_write
, es.program_name
FROM sys.dm_db_session_space_usage su
INNER JOIN sys.dm_exec_sessions es
ON su.session_id = es.session_id
LEFT OUTER JOIN sys.dm_exec_connections ec
ON su.session_id = ec.most_recent_session_id
OUTER APPLY sys.dm_exec_sql_text(ec.most_recent_sql_handle) st
WHERE su.session_id > 50

已完成会话tempdb空间使用及归还情况
SELECT CAST(SUM(su.user_objects_alloc_page_count
+ su.internal_objects_alloc_page_count) * (8.0/1024.0)
AS DECIMAL(20,3)) AS [SpaceUsed(MB)]
, CAST(SUM(su.user_objects_alloc_page_count
– su.user_objects_dealloc_page_count
+ su.internal_objects_alloc_page_count
– su.internal_objects_dealloc_page_count)
* (8.0/1024.0) AS DECIMAL(20,3)) AS [SpaceStillUsed(MB)]
, su.session_id
, ec.connection_id
, es.login_name
, es.host_name
, st.text AS [LastQuery]
, ec.last_read
, ec.last_write
, es.program_name
FROM sys.dm_db_session_space_usage su
INNER JOIN sys.dm_exec_sessions es ON su.session_id = es.session_id
LEFT OUTER JOIN sys.dm_exec_connections ec
ON su.session_id = ec.most_recent_session_id
OUTER APPLY sys.dm_exec_sql_text(ec.most_recent_sql_handle) st
WHERE su.session_id > 50
GROUP BY su.session_id, ec.connection_id, es.login_name, es.host_name
, st.text, ec.last_read, ec.last_write, es.program_name
ORDER BY [SpaceStillUsed(MB)] DESC

task使用的tempdb空间
SELECT es.session_id
, ec.connection_id
, es.login_name
, es.host_name
, st.text
, tu.user_objects_alloc_page_count
, tu.user_objects_dealloc_page_count
, tu.internal_objects_alloc_page_count
, tu.internal_objects_dealloc_page_count
, ec.last_read
, ec.last_write
, es.program_name
FROM sys.dm_db_task_space_usage tu
INNER JOIN sys.dm_exec_sessions es ON tu.session_id = es.session_id
LEFT OUTER JOIN sys.dm_exec_connections ec
ON tu.session_id = ec.most_recent_session_id
OUTER APPLY sys.dm_exec_sql_text(ec.most_recent_sql_handle) st
WHERE tu.session_id > 50

Space used and not reclaimed in tempdb for active batches

SELECT SUM(ts.user_objects_alloc_page_count
+ ts.internal_objects_alloc_page_count)
* (8.0/1024.0) AS [SpaceUsed(MB)]
, SUM(ts.user_objects_alloc_page_count
– ts.user_objects_dealloc_page_count
+ ts.internal_objects_alloc_page_count
– ts.internal_objects_dealloc_page_count)
* (8.0/1024.0) AS [SpaceStillUsed(MB)]
, ts.session_id
, ec.connection_id
, es.login_name
, es.host_name
, st.text AS [Parent Query]
, SUBSTRING (st.text,(er.statement_start_offset/2) + 1,
((CASE WHEN er.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), st.text)) * 2
ELSE er.statement_end_offset
END - er.statement_start_offset)/2) + 1) AS [Current Query]
, ec.last_read
, ec.last_write
, es.program_name
FROM sys.dm_db_task_space_usage ts
INNER JOIN sys.dm_exec_sessions es ON ts.session_id = es.session_id
LEFT OUTER JOIN sys.dm_exec_connections ec
ON ts.session_id = ec.most_recent_session_id
OUTER APPLY sys.dm_exec_sql_text(ec.most_recent_sql_handle) st
LEFT OUTER JOIN sys.dm_exec_requests er ON ts.session_id = er.session_id
WHERE ts.session_id > 50
GROUP BY ts.session_id, ec.connection_id, es.login_name, es.host_name
, st.text, ec.last_read, ec.last_write, es.program_name
, SUBSTRING (st.text,(er.statement_start_offset/2) + 1,
((CASE WHEN er.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), st.text)) * 2
ELSE er.statement_end_offset
END - er.statement_start_offset)/2) + 1)
ORDER BY [SpaceStillUsed(MB)] DESC

经常遇到行锁的索引
SELECT TOP 20
x.name AS SchemaName
, OBJECT_NAME(s.object_id) AS TableName
, i.name AS IndexName
, s.row_lock_wait_in_ms
, s.row_lock_wait_count
FROM sys.dm_db_index_operational_stats(db_ID(), NULL, NULL, NULL) s
INNER JOIN sys.objects o ON s.object_id = o.object_id
INNER JOIN sys.indexes i ON s.index_id = i.index_id
AND i.object_id = o.object_id
INNER JOIN sys.schemas x ON x.schema_id = o.schema_id
WHERE s.row_lock_wait_in_ms > 0
AND o.is_ms_shipped = 0
ORDER BY s.row_lock_wait_in_ms DESC

遇到最多锁升级的索引
SELECT TOP 20
x.name AS SchemaName
, OBJECT_NAME (s.object_id) AS TableName
, i.name AS IndexName
, s.index_lock_promotion_count
FROM sys.dm_db_index_operational_stats(db_ID(), NULL, NULL, NULL) s
INNER JOIN sys.objects o ON s.object_id = o.object_id
INNER JOIN sys.indexes i ON s.index_id = i.index_id
AND i.object_id = o.object_id
INNER JOIN sys.schemas x ON x.schema_id = o.schema_id
WHERE s.index_lock_promotion_count > 0
AND o.is_ms_shipped = 0
ORDER BY s.index_lock_promotion_count DESC

遇到最多锁升级失败的索引
SELECT TOP 20
x.name AS SchemaName
, OBJECT_NAME (s.object_id) AS TableName
, i.name AS IndexName
, s.index_lock_promotion_attempt_count – s.index_lock_promotion_count
AS UnsuccessfulIndexLockPromotions
FROM sys.dm_db_index_operational_stats(db_ID(), NULL, NULL, NULL) s
INNER JOIN sys.objects o ON s.object_id = o.object_id
INNER JOIN sys.indexes i ON s.index_id = i.index_id
AND i.object_id = o.object_id
INNER JOIN sys.schemas x ON x.schema_id = o.schema_id
WHERE (s.index_lock_promotion_attempt_count - index_lock_promotion_count)>0
AND o.is_ms_shipped = 0
ORDER BY UnsuccessfulIndexLockPromotions DESC

页分裂最多的sql
SELECT TOP 20
x.name AS SchemaName
, object_name(s.object_id) AS TableName
, i.name AS IndexName
, s.leaf_allocation_count
, s.nonleaf_allocation_count
FROM sys.dm_db_index_operational_stats(DB_ID(), NULL, NULL, NULL) s
INNER JOIN sys.objects o ON s.object_id = o.object_id
INNER JOIN sys.indexes i ON s.index_id = i.index_id
AND i.object_id = o.object_id
INNER JOIN sys.schemas x ON x.schema_id = o.schema_id
WHERE s.leaf_allocation_count > 0
AND o.is_ms_shipped = 0
ORDER BY s.leaf_allocation_count DESC

latch竞争最多的sql
SELECT TOP 20
x.name AS SchemaName
, OBJECT_NAME(s.object_id) AS TableName
, i.name AS IndexName
, s.page_latch_wait_in_ms
, s.page_latch_wait_count
FROM sys.dm_db_index_operational_stats(db_ID(), NULL, NULL, NULL) s
INNER JOIN sys.objects o ON s.object_id = o.object_id
INNER JOIN sys.indexes i ON s.index_id = i.index_id
AND i.object_id = o.object_id
INNER JOIN sys.schemas x ON x.schema_id = o.schema_id
WHERE s.page_latch_wait_in_ms > 0
AND o.is_ms_shipped = 0
ORDER BY s.page_latch_wait_in_ms DESC

how many rows are inserted/deleted/updated/selected

-- Get pre-work index counters
SELECT
sql_handle, plan_handle, total_elapsed_time, total_worker_time
, total_logical_reads, total_logical_writes, total_clr_time
, execution_count, statement_start_offset, statement_end_offset
INTO #PreWorkQuerySnapShot
FROM sys.dm_exec_query_stats
SELECT x.name AS SchemaName
, OBJECT_NAME (s.object_id) AS TableName
, i.name AS IndexName
, s.leaf_delete_count
, s.leaf_ghost_count
, s.leaf_insert_count
, s.leaf_update_count
, s.range_scan_count
, s.singleton_lookup_count
INTO #PreWorkIndexCount
FROM sys.dm_db_index_operational_stats(DB_ID(), NULL, NULL, NULL) s
INNER JOIN sys.objects o ON s.object_id = o.object_id
INNER JOIN sys.indexes i ON s.index_id = i.index_id
AND i.object_id = o.object_id
INNER JOIN sys.schemas x ON x.schema_id = o.schema_id
WHERE o.is_ms_shipped = 0

-- Do something here (query/time interval)
WAITFOR DELAY '01:00:00'

-- Get post-work index counters
SELECT
sql_handle, plan_handle, total_elapsed_time, total_worker_time
, total_logical_reads, total_logical_writes, total_clr_time
, execution_count, statement_start_offset, statement_end_offset
INTO #PostWorkQuerySnapShot
FROM sys.dm_exec_query_stats
SELECT x.name AS SchemaName
, OBJECT_NAME (s.object_id) AS TableName
, i.name AS IndexName
, s.leaf_delete_count
, s.leaf_ghost_count
, s.leaf_insert_count
, s.leaf_update_count
, s.range_scan_count
, s.singleton_lookup_count
INTO #PostWorkIndexCount
FROM sys.dm_db_index_operational_stats(DB_ID(), NULL, NULL, NULL) s
INNER JOIN sys.objects o ON s.object_id = o.object_id
INNER JOIN sys.indexes i ON s.index_id = i.index_id
AND i.object_id = o.object_id
INNER JOIN sys.schemas x ON x.schema_id = o.schema_id
WHERE o.is_ms_shipped = 0

-- Calculate changes in index counters
SELECT
p2.SchemaName
, p2.TableName
, p2.IndexName
, p2.leaf_delete_count - ISNULL(p1.leaf_delete_count, 0)
AS leaf_delete_countDelta
, p2.leaf_ghost_count - ISNULL(p1.leaf_ghost_count, 0)
AS leaf_ghost_countDelta
, p2.leaf_insert_count - ISNULL(p1.leaf_insert_count, 0)
AS leaf_insert_countDelta
, p2.leaf_update_count - ISNULL(p1.leaf_update_count, 0)
AS leaf_update_countDelta
, p2.range_scan_count - ISNULL(p1.range_scan_count, 0)
AS range_scan_countDelta
, p2.singleton_lookup_count - ISNULL(p1.singleton_lookup_count, 0)
AS singleton_lookup_countDelta
FROM #PreWorkIndexCount p1
RIGHT OUTER JOIN
#PostWorkIndexCount p2 ON p2.SchemaName =
ISNULL(p1.SchemaName, p2.SchemaName)
AND p2.TableName = ISNULL(p1.TableName, p2.TableName)
AND p2.IndexName = ISNULL(p1.IndexName, p2.IndexName)
WHERE p2.leaf_delete_count - ISNULL(p1.leaf_delete_count, 0) > 0
OR p2.leaf_ghost_count - ISNULL(p1.leaf_ghost_count, 0) > 0
OR p2.leaf_insert_count - ISNULL(p1.leaf_insert_count, 0) > 0
OR p2.leaf_update_count - ISNULL(p1.leaf_update_count, 0) > 0
OR p2.range_scan_count - ISNULL(p1.range_scan_count, 0) > 0
OR p2.singleton_lookup_count - ISNULL(p1.singleton_lookup_count, 0) > 0
ORDER BY leaf_delete_countDelta DESC
SELECT
p2.total_elapsed_time - ISNULL(p1.total_elapsed_time, 0) AS [Duration]
, p2.total_worker_time - ISNULL(p1.total_worker_time, 0) AS [Time on CPU]
, (p2.total_elapsed_time - ISNULL(p1.total_elapsed_time, 0)) –
(p2.total_worker_time - ISNULL(p1.total_worker_time, 0))
AS [Time blocked]
, p2.total_logical_reads - ISNULL(p1.total_logical_reads, 0) AS [Reads]
, p2.total_logical_writes - ISNULL(p1.total_logical_writes, 0)
AS [Writes]
, p2.total_clr_time - ISNULL(p1.total_clr_time, 0) AS [CLR time]
, p2.execution_count - ISNULL(p1.execution_count, 0) AS [Executions]
, SUBSTRING (qt.text,p2.statement_start_offset/2 + 1,
((CASE WHEN p2.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
ELSE p2.statement_end_offset
END - p2.statement_start_offset)/2) + 1) AS [Individual Query]
, qt.text AS [Parent Query]
, DB_NAME(qt.dbid) AS DatabaseName
FROM #PreWorkQuerySnapShot p1
RIGHT OUTER JOIN
#PostWorkQuerySnapShot p2 ON p2.sql_handle =
ISNULL(p1.sql_handle, p2.sql_handle)
AND p2.plan_handle = ISNULL(p1.plan_handle, p2.plan_handle)
AND p2.statement_start_offset =
ISNULL(p1.statement_start_offset, p2.statement_start_offset)
AND p2.statement_end_offset =
ISNULL(p1.statement_end_offset, p2.statement_end_offset)
CROSS APPLY sys.dm_exec_sql_text(p2.sql_handle) as qt
WHERE p2.execution_count != ISNULL(p1.execution_count, 0)
ORDER BY [Duration] DESC
DROP TABLE #PreWorkIndexCount
DROP TABLE #PostWorkIndexCount
DROP TABLE #PreWorkQuerySnapShot
DROP TABLE #PostWorkQuerySnapShot

自动重编译慢于平均值的sql
-- Get raw values
SELECT TOP 100
qs.execution_count AS [Runs]
, (qs.total_worker_time - qs.last_worker_time) /
(qs.execution_count - 1) AS [Avg time]
, qs.last_worker_time AS [Last time]
, (qs.last_worker_time - ((qs.total_worker_time - qs.last_worker_time)
/ (qs.execution_count - 1))) AS [Time Deviation]
, CASE WHEN qs.last_worker_time = 0
THEN 100
ELSE (qs.last_worker_time - ((qs.total_worker_time –
qs.last_worker_time) / (qs.execution_count - 1))) * 100
END
/ (((qs.total_worker_time - qs.last_worker_time)
/ (qs.execution_count - 1))) AS [% Time Deviation]
, qs.last_logical_reads + qs.last_logical_writes
+ qs.last_physical_reads AS [Last IO]
, ((qs.total_logical_reads + qs.total_logical_writes +
qs.total_physical_reads) - (qs.last_logical_reads +
qs.last_logical_writes + qs.last_physical_reads))
/ (qs.execution_count - 1) AS [Avg IO]
, SUBSTRING (qt.text,(qs.statement_start_offset/2) + 1,
((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) + 1) AS [Individual Query]
, qt.text AS [Parent Query]
, DB_NAME(qt.dbid) AS [DatabaseName]
INTO #SlowQueries
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) qt
WHERE qs.execution_count > 1
AND qs.total_worker_time != qs.last_worker_time
ORDER BY [% Time Deviation] DESC

-- Calculate IO Deviation and % IO Deviation
SELECT TOP 100 [Runs]
, [Avg time]
, [Last time]
, [Time Deviation]
, [% Time Deviation]
, [Last IO]
, [Avg IO]
, [Last IO] - [Avg IO] AS [IO Deviation]
, CASE WHEN [Avg IO] = 0
THEN 0
ELSE ([Last IO]- [Avg IO]) * 100 / [Avg IO]
END AS [% IO Deviation]
, [Individual Query]
, [Parent Query]
, [DatabaseName]
INTO #SlowQueriesByIO
FROM #SlowQueries
ORDER BY [% Time Deviation] DESC

-- Calculate Impedance
SELECT TOP 100
[Runs]
, [Avg time]
, [Last time]
, [Time Deviation]
, [% Time Deviation]
, [Last IO]
, [Avg IO]
, [IO Deviation]
, [% IO Deviation]
, [Impedance] = [% Time Deviation] - [% IO Deviation]
, [Individual Query]
, [Parent Query]
, [DatabaseName]
INTO #QueriesRunningSlowerThanNormal
FROM #SlowQueriesByIO
WHERE [% Time Deviation] - [% IO Deviation] > 20
ORDER BY [Impedance] DESC

-- Build recompilation SQL
SELECT DISTINCT
' EXEC sp_recompile ' + '''' + '[' + [DatabaseName] + '].'
+ dbo.ExtractSQLRoutineName([Parent Query]) + ''''
AS recompileRoutineSQL
INTO #RecompileQuery
FROM #QueriesRunningSlowerThanNormal
WHERE [DatabaseName] NOT IN ('master', 'msdb', '')
DECLARE @RecompilationSQL NVARCHAR(MAX)
SET @RecompilationSQL = ''

-- Amalgamate recompilation SQL
SELECT @RecompilationSQL = @RecompilationSQL
+ recompileRoutineSQL + CHAR(10)
FROM #RecompileQuery
WHERE recompileRoutineSQL IS NOT NULL

-- Debug
DECLARE @StartOffset INT
DECLARE @Length INT
SET @StartOffset = 0
SET @Length = 4000
WHILE (@StartOffset < LEN(@RecompilationSQL))
BEGIN
PRINT SUBSTRING(@RecompilationSQL, @StartOffset, @Length)
SET @StartOffset = @StartOffset + @Length
END

-- print and run
PRINT SUBSTRING(@RecompilationSQL, @StartOffset, @Length)
EXECUTE sp_executesql @RecompilationSQL
DROP TABLE #SlowQueries
DROP TABLE #SlowQueriesByIO
DROP TABLE #QueriesRunningSlowerThanNormal
DROP TABLE #RecompileQuery

Rebuild/reorganize for a given database

CREATE TABLE #FragmentedIndexes(
DatabaseName SYSNAME
, SchemaName SYSNAME
, TableName SYSNAME
, IndexName SYSNAME
, [Fragmentation%] FLOAT)
INSERT INTO #FragmentedIndexes
SELECT
DB_NAME(DB_ID()) AS DatabaseName
, ss.name AS SchemaName
, OBJECT_NAME (s.object_id) AS TableName
, i.name AS IndexName
, s.avg_fragmentation_in_percent AS [Fragmentation%]
FROM sys.dm_db_index_physical_stats(db_id(),NULL, NULL, NULL, 'SAMPLED') s
INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id]
AND s.index_id = i.index_id
INNER JOIN sys.objects o ON s.object_id = o.object_id
INNER JOIN sys.schemas ss ON ss.[schema_id] = o.[schema_id]
WHERE s.database_id = DB_ID()
AND i.index_id != 0 -- Ignore heaps
AND s.record_count > 0 -- Only indexes with data
AND o.is_ms_shipped = 0 -- User tables only

-- Build rebuild/reorg SQL
DECLARE @RebuildIndexesSQL NVARCHAR(MAX)
SET @RebuildIndexesSQL = ''
SELECT
@RebuildIndexesSQL = @RebuildIndexesSQL +
CASE
WHEN [Fragmentation%] > 30
THEN CHAR(10) + 'ALTER INDEX ' + QUOTENAME(IndexName) + ' ON '
+ QUOTENAME(SchemaName) + '.'
+ QUOTENAME(TableName) + ' REBUILD;'
WHEN [Fragmentation%] > 10
THEN CHAR(10) + 'ALTER INDEX ' + QUOTENAME(IndexName) + ' ON '
+ QUOTENAME(SchemaName) + '.'
+ QUOTENAME(TableName) + ' REORGANIZE;'
END
FROM #FragmentedIndexes
WHERE [Fragmentation%] > 10
DECLARE @StartOffset INT
DECLARE @Length INT
SET @StartOffset = 0
SET @Length = 4000
WHILE (@StartOffset < LEN(@RebuildIndexesSQL))
BEGIN
PRINT SUBSTRING(@RebuildIndexesSQL, @StartOffset, @Length)
SET @StartOffset = @StartOffset + @Length
END
PRINT SUBSTRING(@RebuildIndexesSQL, @StartOffset, @Length)
EXECUTE sp_executesql @RebuildIndexesSQL
DROP TABLE #FragmentedIndexes

Rebuild/reorganize for all databases on a given server

CREATE TABLE #FragmentedIndexes(
DatabaseName SYSNAME
, SchemaName SYSNAME
, TableName SYSNAME
, IndexName SYSNAME
, [Fragmentation%] FLOAT)

-- Loop around all databases
EXEC sp_MSForEachDB 'USE [?];
INSERT INTO #FragmentedIndexes
SELECT
DB_NAME(DB_ID()) AS DatabaseName
, ss.name AS SchemaName
, OBJECT_NAME (s.object_id) AS TableName
, i.name AS IndexName
, s.avg_fragmentation_in_percent AS [Fragmentation%]
FROM sys.dm_db_index_physical_stats(db_id(),NULL, NULL, NULL,
''SAMPLED'') s
INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id]
AND s.index_id = i.index_id
INNER JOIN sys.objects o ON s.object_id = o.object_id
INNER JOIN sys.schemas ss ON ss.[schema_id] = o.[schema_id]
WHERE s.database_id = DB_ID()
AND i.index_id != 0
AND s.record_count > 0
AND o.is_ms_shipped = 0
;'
DECLARE @RebuildIndexesSQL NVARCHAR(MAX)
SET @RebuildIndexesSQL = ''
SELECT
@RebuildIndexesSQL = @RebuildIndexesSQL +
CASE
WHEN [Fragmentation%] > 30
THEN CHAR(10) + 'ALTER INDEX ' + QUOTENAME(IndexName) + ' ON '
+ QUOTENAME(DatabaseName) + '.'+ QUOTENAME(SchemaName) + '.'
+ QUOTENAME(TableName) + ' REBUILD;'
WHEN [Fragmentation%] > 10
THEN CHAR(10) + 'ALTER INDEX ' + QUOTENAME(IndexName) + ' ON '
+ QUOTENAME(DatabaseName) + '.'+ QUOTENAME(SchemaName) + '.'
+ QUOTENAME(TableName) + ' REORGANIZE;'
END
FROM #FragmentedIndexes
WHERE [Fragmentation%] > 10
DECLARE @StartOffset INT
DECLARE @Length INT
SET @StartOffset = 0
SET @Length = 4000
WHILE (@StartOffset < LEN(@RebuildIndexesSQL))
BEGIN
PRINT SUBSTRING(@RebuildIndexesSQL, @StartOffset, @Length)
SET @StartOffset = @StartOffset + @Length
END
PRINT SUBSTRING(@RebuildIndexesSQL, @StartOffset, @Length)
EXECUTE sp_executesql @RebuildIndexesSQL
DROP TABLE #FragmentedIndexes

智能更新统计信息（简单版）
-- Select indexes to update
SELECT
ss.name AS SchemaName
, st.name AS TableName
, si.name AS IndexName
, si.type_desc AS IndexType
, STATS_DATE(si.object_id,si.index_id) AS StatsLastTaken
, ssi.rowcnt
, ssi.rowmodctr
INTO #IndexUsage
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 -- User indexes only
AND si.index_id != 0 -- Ignore heaps
AND ssi.rowcnt > 100 -- Indexes with 100 or more rows
AND ssi.rowmodctr > 0 -- Indexes with some changed data

-- Build SQL to update statistics
DECLARE @UpdateStatisticsSQL NVARCHAR(MAX)
SET @UpdateStatisticsSQL = ''
SELECT
@UpdateStatisticsSQL = @UpdateStatisticsSQL
+ CHAR(10) + 'UPDATE STATISTICS '
+ QUOTENAME(SchemaName) + '.' + QUOTENAME(TableName)
+ ' ' + QUOTENAME(IndexName) + ' WITH SAMPLE '
+ CASE
WHEN rowcnt < 500000 THEN '100 PERCENT'
WHEN rowcnt < 1000000 THEN '50 PERCENT'
WHEN rowcnt < 5000000 THEN '25 PERCENT'
WHEN rowcnt < 10000000 THEN '10 PERCENT'
WHEN rowcnt < 50000000 THEN '2 PERCENT'
WHEN rowcnt < 100000000 THEN '1 PERCENT'
ELSE '3000000 ROWS '
END
+ '-- ' + CAST(rowcnt AS VARCHAR(22)) + ' rows'
FROM #IndexUsage

-- Debug
DECLARE @StartOffset INT
DECLARE @Length INT
SET @StartOffset = 0
SET @Length = 4000
WHILE (@StartOffset < LEN(@UpdateStatisticsSQL))
BEGIN
PRINT SUBSTRING(@UpdateStatisticsSQL, @StartOffset, @Length)
SET @StartOffset = @StartOffset + @Length
END

-- Execute SQL
PRINT SUBSTRING(@UpdateStatisticsSQL, @StartOffset, @Length)
EXECUTE sp_executesql @UpdateStatisticsSQL
DROP TABLE #IndexUsage

智能更新统计信息（time-based版本）
-- Do stats need updating
IF EXISTS
(SELECT 1
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
AND ssi.rowcnt > 100
AND ssi.rowmodctr > 0)
BEGIN
DECLARE @StatsMarker NVARCHAR(2000)
DECLARE @SamplingComplete BIT
DECLARE @RowsToBenchMark BIGINT
SET @RowsToBenchMark = 500
SET @SamplingComplete = 0
DECLARE @TotalStatsTime BIGINT
DECLARE @StartTime DATETIME
DECLARE @TimePerRow FLOAT

-- How long to update one row
WHILE (@SamplingComplete = 0)
BEGIN
SELECT TOP 1 @StatsMarker = 'UPDATE STATISTICS '
+ QUOTENAME(ss.name) + '.' + QUOTENAME(st.name)
+ ' ' + QUOTENAME(si.name) + ' WITH SAMPLE '
+ CAST(@RowsToBenchMark AS VARCHAR(22)) + ' ROWS'
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 -- User tables only
AND si.index_id != 0 -- ignore heaps
AND ssi.rowcnt > @RowsToBenchMark
ORDER BY ssi.rowcnt
IF @@ROWCOUNT > 0
BEGIN
PRINT 'Testing sampling time with: ' + @StatsMarker
SET @StartTime = GETDATE()
EXECUTE sp_executesql @StatsMarker
SET @TotalStatsTime = DATEDIFF(SECOND, @StartTime, GETDATE())
PRINT '@TotalStatsTime: ' + CAST(@TotalStatsTime AS VARCHAR(22))
IF (@TotalStatsTime > 5) -- Want at least 5 sec of statistics
BEGIN
SET @TimePerRow = @TotalStatsTime / (@RowsToBenchMark * 1.0) -- Calculate stats time per row
PRINT @TimePerRow
SET @SamplingComplete = 1
END
ELSE -- Get a bigger sample
SET @RowsToBenchMark = @RowsToBenchMark * 10
END
ELSE -- Not enough rows to sample, exit
BEGIN
DECLARE @ErrorMsg VARCHAR(200)
SET @ErrorMsg = 'No indexes found with @RowsToBenchMark > '
+ CAST(@RowsToBenchMark AS VARCHAR(22))
RAISERROR(@ErrorMsg, 16, 1)
RETURN
END
END

-- Get indexes with stats to update
DECLARE @RowsToSample BIGINT
SET @RowsToSample = 0
SELECT
ss.name AS SchemaName
, st.name AS TableName
, si.name AS IndexName
, si.type_desc AS IndexType
, STATS_DATE(si.object_id,si.index_id) AS StatsLastTaken
, ssi.rowcnt
, ssi.rowmodctr
, @RowsToSample AS RowsToSample
INTO #IndexUsage
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
AND ssi.rowcnt > 100
AND ssi.rowmodctr > 0

DECLARE @MaxSamplingTimeInSeconds INT
SET @MaxSamplingTimeInSeconds = 600 -- 10 mins
DECLARE @WorkIsWithinTimeLimit BIT
SET @WorkIsWithinTimeLimit = 0
DECLARE @TotalTimeForAllStats INT
DECLARE @ReduceFraction FLOAT
SET @ReduceFraction = 1.0
DECLARE @ReduceFractionSmall FLOAT
SET @ReduceFractionSmall = 1.0
UPDATE #IndexUsage
SET RowsToSample =
CASE
WHEN rowcnt < 100000000 THEN rowcnt
ELSE 3000000
END
WHILE (@WorkIsWithinTimeLimit = 0)
BEGIN
-- Apply weighting factor
UPDATE #IndexUsage
SET RowsToSample =
CASE
WHEN rowcnt < 500000 THEN rowcnt * @ReduceFractionSmall
WHEN rowcnt < 1000000 THEN rowcnt / 2 * @ReduceFractionSmall
WHEN rowcnt < 5000000 THEN rowcnt / 4 * @ReduceFractionSmall
WHEN rowcnt < 10000000 THEN rowcnt / 10 * @ReduceFraction
WHEN rowcnt < 50000000 THEN rowcnt / 50 * @ReduceFraction
WHEN rowcnt < 100000000 THEN rowcnt / 100 * @ReduceFraction
ELSE 3000000 * @ReduceFraction
END

-- Calc total time for stats update
SELECT @TotalTimeForAllStats = SUM(RowsToSample) * @TimePerRow
FROM #IndexUsage
PRINT '@TotalTimeForAllStats: '
+ CAST(@TotalTimeForAllStats AS VARCHAR(22))
IF (@TotalTimeForAllStats < @MaxSamplingTimeInSeconds)
SET @WorkIsWithinTimeLimit = 1
ELSE -- Sampling time exceeded
BEGIN -- Decrease rows to sample
SET @ReduceFraction = @ReduceFraction - 0.01
SET @ReduceFractionSmall = @ReduceFractionSmall - 0.001
END
END

-- Build SQL to update statistics
DECLARE @UpdateStatisticsSQL NVARCHAR(MAX)
SET @UpdateStatisticsSQL = ''
SELECT
@UpdateStatisticsSQL = @UpdateStatisticsSQL
+ CHAR(10) + 'UPDATE STATISTICS ' + QUOTENAME(SchemaName)
+ '.' + QUOTENAME(TableName)
+ ' ' + QUOTENAME(IndexName) + ' WITH SAMPLE '
+ CAST(RowsToSample AS VARCHAR(22)) + ' ROWS '
FROM #IndexUsage

-- Debug
DECLARE @StartOffset INT
DECLARE @Length INT
SET @StartOffset = 0
SET @Length = 4000
WHILE (@StartOffset < LEN(@UpdateStatisticsSQL))
BEGIN
PRINT SUBSTRING(@UpdateStatisticsSQL, @StartOffset, @Length)
SET @StartOffset = @StartOffset + @Length
END

-- Execute SQL
PRINT SUBSTRING(@UpdateStatisticsSQL, @StartOffset, @Length)
EXECUTE sp_executesql @UpdateStatisticsSQL
DROP TABLE #IndexUsage
END

Automatically implement missing indexes

-- Build SQL for most-important missing indexes
SELECT TOP 20
'CREATE NONCLUSTERED INDEX '
+ QUOTENAME('IX_AutoGenerated_'
+ REPLACE(REPLACE(CONVERT(VARCHAR(25), GETDATE(), 113)
, ' ', '_'), ':', '_')
+ '_' + CAST(d.index_handle AS VARCHAR(22))
)
+ ' ON ' + d.[statement] + '('
+ CASE
WHEN d.equality_columns IS NULL THEN d.inequality_columns
WHEN d.inequality_columns IS NULL THEN d.equality_columns
ELSE d.equality_columns + ',' + d.inequality_columns
END
+ ')'
+ CASE
WHEN d.included_columns IS NOT NULL THEN
' INCLUDE ( ' + d.included_columns + ')'
ELSE ''
END AS MissingIndexSQL
, ROUND(s.avg_total_user_cost * s.avg_user_impact
* (s.user_seeks + s.user_scans),0) AS [Total Cost]
, d.[statement] AS [Table Name]
, d.equality_columns
, d.inequality_columns
, d.included_columns
INTO #MissingIndexes
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

-- Amalgamate missing indexes SQL
DECLARE @MissingIndexesSQL NVARCHAR(MAX)
SET @MissingIndexesSQL = ''
SELECT
@MissingIndexesSQL = @MissingIndexesSQL + MissingIndexSQL + CHAR(10)
FROM #MissingIndexes

-- Debug
DECLARE @StartOffset INT
DECLARE @Length INT
SET @StartOffset = 0
SET @Length = 4000
WHILE (@StartOffset < LEN(@MissingIndexesSQL))
BEGIN
PRINT SUBSTRING(@MissingIndexesSQL, @StartOffset, @Length)
SET @StartOffset = @StartOffset + @Length
END

-- Execute SQL
PRINT SUBSTRING(@MissingIndexesSQL, @StartOffset, @Length)
EXECUTE sp_executesql @MissingIndexesSQL
DROP TABLE #MissingIndexes

Automatically disable or drop unused indexes

SELECT
DB_NAME() AS DatabaseName
, SCHEMA_NAME(o.Schema_ID) AS SchemaName
, OBJECT_NAME(s.[object_id]) AS TableName
, i.name AS IndexName
, s.user_updates
, s.system_seeks + s.system_scans + s.system_lookups
AS [System usage]
INTO #TempUnusedIndexes
FROM sys.dm_db_index_usage_stats s
INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id]
AND s.index_id = i.index_id
INNER JOIN sys.objects o ON i.object_id = O.object_id
WHERE 1=2

-- Identify most-important unused indexes
EXEC sp_MSForEachDB 'USE [?];
INSERT INTO #TempUnusedIndexes
SELECT TOP 20
DB_NAME() AS DatabaseName
, SCHEMA_NAME(o.Schema_ID) AS SchemaName
, OBJECT_NAME(s.[object_id]) AS TableName
, i.name AS IndexName
, s.user_updates
, s.system_seeks + s.system_scans + s.system_lookups
AS [System usage]
FROM sys.dm_db_index_usage_stats s
INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id]
AND s.index_id = i.index_id
INNER JOIN sys.objects o ON i.object_id = O.object_id
WHERE s.database_id = DB_ID()
AND OBJECTPROPERTY(s.[object_id], ''IsMsShipped'') = 0
AND user_seeks = 0
AND user_scans = 0
AND user_lookups = 0
AND i.name IS NOT NULL
ORDER BY user_updates DESC'
DECLARE @DisableOrDrop INT
SET @DisableOrDrop = 1
DECLARE @DisableIndexesSQL NVARCHAR(MAX)
SET @DisableIndexesSQL = ''

-- Amalgamate unused indexes SQL
SELECT
@DisableIndexesSQL = @DisableIndexesSQL +
CASE  -- Build the disable or drop index SQL
WHEN @DisableOrDrop = 1
THEN CHAR(10) + 'ALTER INDEX ' + QUOTENAME(IndexName) + ' ON '
+ QUOTENAME(DatabaseName) + '.'+ QUOTENAME(SchemaName) + '.'
+ QUOTENAME(TableName) + ' DISABLE;'
ELSE CHAR(10) + 'DROP INDEX ' + QUOTENAME(IndexName) + ' ON '
+ QUOTENAME(DatabaseName) + '.'+ QUOTENAME(SchemaName) + '.'
+ QUOTENAME(TableName)
END
FROM #TempUnusedIndexes

-- Debug
DECLARE @StartOffset INT
DECLARE @Length INT
SET @StartOffset = 0
SET @Length = 4000
WHILE (@StartOffset < LEN(@DisableIndexesSQL))
BEGIN
PRINT SUBSTRING(@DisableIndexesSQL, @StartOffset, @Length)
SET @StartOffset = @StartOffset + @Length
END

-- Execute SQL
PRINT SUBSTRING(@DisableIndexesSQL, @StartOffset, @Length)
EXECUTE sp_executesql @DisableIndexesSQL
DROP TABLE #TempUnusedIndexes

Finding everyone’s last-run query

SELECT c.session_id, s.host_name, s.login_name, s.status
, st.text, s.login_time, s.program_name, *
FROM sys.dm_exec_connections c
INNER JOIN sys.dm_exec_sessions s ON c.session_id = s.session_id
CROSS APPLY sys.dm_exec_sql_text(most_recent_sql_handle) AS st
ORDER BY c.session_id

Determining the performance impact of a system upgrade

-- Get pre-work counters
SELECT
total_elapsed_time, total_worker_time, total_logical_reads
, total_logical_writes, total_clr_time, execution_count
, statement_start_offset, statement_end_offset, sql_handle, plan_handle
INTO #prework
FROM sys.dm_exec_query_stats

-- Do something here (query/time interval)
EXEC PutYourWorkloadHere

-- Get post-work counters
SELECT
total_elapsed_time, total_worker_time, total_logical_reads
, total_logical_writes, total_clr_time, execution_count
, statement_start_offset, statement_end_offset, sql_handle, plan_handle
INTO #postwork
FROM sys.dm_exec_query_stats

-- Get totals by database
SELECT
SUM(p2.total_elapsed_time - ISNULL(p1.total_elapsed_time, 0))
AS [TotalDuration]
, SUM(p2.total_worker_time - ISNULL(p1.total_worker_time, 0))
AS [Total Time on CPU]
, SUM((p2.total_elapsed_time - ISNULL(p1.total_elapsed_time, 0)) –
(p2.total_worker_time - ISNULL(p1.total_worker_time, 0)))
AS [Total Time Waiting]
, SUM(p2.total_logical_reads - ISNULL(p1.total_logical_reads, 0))
AS [TotalReads]
, SUM(p2.total_logical_writes - ISNULL(p1.total_logical_writes, 0))
AS [TotalWrites]
, SUM(p2.total_clr_time - ISNULL(p1.total_clr_time, 0))
AS [Total CLR time]
, SUM(p2.execution_count - ISNULL(p1.execution_count, 0))
AS [Total Executions]
, DB_NAME(qt.dbid) AS DatabaseName
FROM #prework p1
RIGHT OUTER JOIN
#postwork p2 ON p2.sql_handle = ISNULL(p1.sql_handle, p2.sql_handle)
AND p2.plan_handle = ISNULL(p1.plan_handle, p2.plan_handle)
AND p2.statement_start_offset =
ISNULL(p1.statement_start_offset, p2.statement_start_offset)
AND p2.statement_end_offset =
ISNULL(p1.statement_end_offset, p2.statement_end_offset)
CROSS APPLY sys.dm_exec_sql_text(p2.sql_handle) as qt
WHERE p2.execution_count != ISNULL(p1.execution_count, 0)
GROUP BY DB_NAME(qt.dbid)

-- Get totals by Parent Query
SELECT
SUM(p2.total_elapsed_time - ISNULL(p1.total_elapsed_time, 0))
AS [TotalDuration]
, SUM(p2.total_worker_time - ISNULL(p1.total_worker_time, 0))
AS [Total Time on CPU]
, SUM((p2.total_elapsed_time - ISNULL(p1.total_elapsed_time, 0))
- (p2.total_worker_time - ISNULL(p1.total_worker_time, 0)))
AS [Total Time Waiting]
, SUM(p2.total_logical_reads - ISNULL(p1.total_logical_reads, 0))
AS [TotalReads]
, SUM(p2.total_logical_writes - ISNULL(p1.total_logical_writes, 0))
AS [TotalWrites]
, SUM(p2.total_clr_time - ISNULL(p1.total_clr_time, 0))
AS [Total CLR time]
, SUM(p2.execution_count - ISNULL(p1.execution_count, 0))
AS [Total Executions]
, DB_NAME(qt.dbid) AS DatabaseName
, qt.text AS [Parent Query]
FROM #prework p1
RIGHT OUTER JOIN
#postwork p2 ON p2.sql_handle = ISNULL(p1.sql_handle, p2.sql_handle)
AND p2.plan_handle = ISNULL(p1.plan_handle, p2.plan_handle)
AND p2.statement_start_offset =
ISNULL(p1.statement_start_offset, p2.statement_start_offset)
AND p2.statement_end_offset =
ISNULL(p1.statement_end_offset, p2.statement_end_offset)
CROSS APPLY sys.dm_exec_sql_text(p2.sql_handle) as qt
WHERE p2.execution_count != ISNULL(p1.execution_count, 0)
GROUP BY DB_NAME(qt.dbid), qt.text
ORDER BY [TotalDuration] DESC

-- Get totals by Individual Query
SELECT
p2.total_elapsed_time - ISNULL(p1.total_elapsed_time, 0)
AS [TotalDuration]
, p2.total_worker_time - ISNULL(p1.total_worker_time, 0)
AS [Total Time on CPU]
, (p2.total_elapsed_time - ISNULL(p1.total_elapsed_time, 0))
- (p2.total_worker_time - ISNULL(p1.total_worker_time, 0))
AS [Total Time Waiting]
, p2.total_logical_reads - ISNULL(p1.total_logical_reads, 0)
AS [TotalReads]
, p2.total_logical_writes - ISNULL(p1.total_logical_writes, 0)
AS [TotalWrites]
, p2.total_clr_time - ISNULL(p1.total_clr_time, 0) AS [Total CLR time]
, p2.execution_count - ISNULL(p1.execution_count, 0)
AS [Total Executions]
, SUBSTRING (qt.text,p2.statement_start_offset/2 + 1,
((CASE WHEN p2.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
ELSE p2.statement_end_offset
END - p2.statement_start_offset)/2) + 1) AS [Individual Query]
, qt.text AS [Parent Query]
, DB_NAME(qt.dbid) AS DatabaseName
FROM #prework p1
RIGHT OUTER JOIN
#postwork p2 ON p2.sql_handle = ISNULL(p1.sql_handle, p2.sql_handle)
AND p2.plan_handle = ISNULL(p1.plan_handle, p2.plan_handle)
AND p2.statement_start_offset =
ISNULL(p1.statement_start_offset, p2.statement_start_offset)
AND p2.statement_end_offset =
ISNULL(p1.statement_end_offset, p2.statement_end_offset)
CROSS APPLY sys.dm_exec_sql_text(p2.sql_handle) as qt
WHERE p2.execution_count != ISNULL(p1.execution_count, 0)
ORDER BY [TotalDuration] DESC
DROP TABLE #prework
DROP TABLE #postwork

预估系统任务结束时间
支持以下操作：
■ ALTER INDEX REORGANIZE
■ AUTO_SHRINK option with ALTER DATABASE
■ BACKUP DATABASE
■ CREATE INDEX
■ DBCC CHECKDB
■ DBCC CHECKFILEGROUP
■ DBCC CHECKTABLE
■ DBCC INDEXDEFRAG
■ DBCC SHRINKDATABASE
■ DBCC SHRINKFILE
■ KILL (Transact-SQL)
■ RESTORE DATABASE
■ UPDATE STATISTICS

SELECT r.percent_complete
, DATEDIFF(MINUTE, start_time, GETDATE()) AS Age
, DATEADD(MINUTE, DATEDIFF(MINUTE, start_time, GETDATE()) /
percent_complete * 100, start_time) AS EstimatedEndTime
, t.Text AS ParentQuery
, SUBSTRING (t.text,(r.statement_start_offset/2) + 1,
((CASE WHEN r.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), t.text)) * 2
ELSE r.statement_end_offset
END - r.statement_start_offset)/2) + 1) AS IndividualQuery
, start_time
, DB_NAME(Database_Id) AS DatabaseName
, Status
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(sql_handle) t
WHERE session_id > 50
AND percent_complete > 0
ORDER BY percent_complete DESC

获取os信息
SELECT cpu_count AS [Logical CPUs]
,cpu_count / hyperthread_ratio AS [Physical CPUs]
FROM sys.dm_os_sys_info

-- 2005版本
SELECT DATEADD(ss, -(ms_ticks / 1000), GetDate()) AS [Start dateTime]
FROM sys.dm_os_sys_info

--每个库使用的内存
SELECT
ISNULL(DB_NAME(database_id), 'ResourceDb') AS DatabaseName
, CAST(COUNT(row_count) * 8.0 / (1024.0) AS DECIMAL(28,2))
AS [Size (MB)]
FROM sys.dm_os_buffer_descriptors
GROUP BY database_id
ORDER BY DatabaseName

-- 每个对象使用的内存
SELECT
OBJECT_NAME(p.[object_id]) AS [TableName]
, (COUNT(*) * 8) / 1024 AS [Buffer size(MB)]
, ISNULL(i.name, '-- HEAP --') AS ObjectName
, COUNT(*) AS NumberOf8KPages
FROM sys.allocation_units AS a
INNER JOIN sys.dm_os_buffer_descriptors AS b
ON a.allocation_unit_id = b.allocation_unit_id
INNER JOIN sys.partitions AS p
INNER JOIN sys.indexes i ON p.index_id = i.index_id
AND p.[object_id] = i.[object_id]
ON a.container_id = p.hobt_id
WHERE b.database_id = DB_ID()
AND p.[object_id] > 100
GROUP BY p.[object_id], i.name
ORDER BY NumberOf8KPages DESC

I/O waits at the database level

SELECT DB_NAME(database_id) AS [DatabaseName]
, SUM(CAST(io_stall / 1000.0 AS DECIMAL(20,2))) AS [IO stall (secs)]
, SUM(CAST(num_of_bytes_read / 1024.0 / 1024.0 AS DECIMAL(20,2)))
AS [IO read (MB)]
, SUM(CAST(num_of_bytes_written / 1024.0 / 1024.0 AS DECIMAL(20,2)))
AS [IO written (MB)]
, SUM(CAST((num_of_bytes_read + num_of_bytes_written)
/ 1024.0 / 1024.0 AS DECIMAL(20,2))) AS [TotalIO (MB)]
FROM sys.dm_io_virtual_file_stats(NULL, NULL)
GROUP BY database_id
ORDER BY [IO stall (secs)] DESC

I/O waits at the file level

SELECT DB_NAME(database_id) AS [DatabaseName]
, file_id
, SUM(CAST(io_stall / 1000.0 AS DECIMAL(20,2))) AS [IO stall (secs)]
, SUM(CAST(num_of_bytes_read / 1024.0 / 1024.0 AS DECIMAL(20,2)))
AS [IO read (MB)]
, SUM(CAST(num_of_bytes_written / 1024.0 / 1024.0 AS DECIMAL(20,2)))
AS [IO written (MB)]
, SUM(CAST((num_of_bytes_read + num_of_bytes_written)
/ 1024.0 / 1024.0 AS DECIMAL(20,2))) AS [TotalIO (MB)]
FROM sys.dm_io_virtual_file_stats(NULL, NULL)
GROUP BY database_id, file_id
ORDER BY [IO stall (secs)] DESC

Average read/write times per file, per database

SELECT DB_NAME(database_id) AS DatabaseName
, file_id
, io_stall_read_ms / num_of_reads AS 'Average read time'
, io_stall_write_ms / num_of_writes AS 'Average write time'
FROM sys.dm_io_virtual_file_stats(NULL, NULL)
WHERE num_of_reads > 0 and num_of_writes > 0
ORDER BY DatabaseName