服务器等待的原因
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;
读和写
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;
数据库缺失索引
SELECt
DatabaseName=DB_NAME(database_id)
,[Number Indexes Missing]=count(*)
FROM sys.dm_db_missing_index_details
GROUP BY DB_NAME(database_id)
ORDER BY 2 DESC;
缺失索引列表信息
SELECt DatabaseName=DB_NAME(database_id),* FROM sys.dm_db_missing_index_details Order BY DB_NAME(database_id)
高开销的缺失索引
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;
确定开销最高的未使用索引
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;
确定最高开销索引所使用的脚本并显示结果。
-- Create required table structure only.
-- Note: this SQL must be the same as in the Database loop given in the following step.
SELECt TOP 1
[Maintenance cost]=(user_updates + system_updates)
,[Retrieval usage]=(user_seeks + user_scans + user_lookups)
,DatabaseName=DB_NAME()
,TableName=OBJECT_NAME(s.[object_id])
,IndexName=i.name
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
WHERe s.database_id=DB_ID()
AND OBJECTPROPERTY(s.[object_id], 'IsMsShipped')=0
AND (user_updates + system_updates) > 0 -- only report on active rows.
AND s.[object_id]=-999 -- Dummy value to get table structure.
;
-- Loop around all the databases on the server.
EXEC sp_MSForEachDB 'USE [?];
-- Table already exists.
INSERT INTO #TempMaintenanceCost
SELECT TOP 10
[Maintenance cost]=(user_updates + system_updates)
,[Retrieval usage]=(user_seeks + user_scans + user_lookups)
,DatabaseName=DB_NAME()
,TableName=OBJECT_NAME(s.[object_id])
,IndexName=i.name
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
WHERe s.database_id=DB_ID()
AND i.name IS NOT NULL -- Ignore HEAP indexes.
AND OBJECTPROPERTY(s.[object_id], ''IsMsShipped'')=0
AND (user_updates + system_updates) > 0 -- only report on active rows.
ORDER BY [Maintenance cost] DESC
;
'
-- Select records.
SELECT TOP 10 * FROM #TempMaintenanceCost
ORDER BY [Maintenance cost] DESC
-- Tidy up.
DROp TABLE #TempMaintenanceCost
显示索引已被使用的次数，并按“使用率”排序。
-- Create required table structure only.
-- Note: this SQL must be the same as in the Database loop given in the -- following step.
SELECT TOP 1
[Usage]=(user_seeks + user_scans + user_lookups)
,DatabaseName=DB_NAME()
,TableName=OBJECT_NAME(s.[object_id])
,IndexName=i.name
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
WHERe s.database_id=DB_ID()
AND OBJECTPROPERTY(s.[object_id], 'IsMsShipped')=0
AND (user_seeks + user_scans + user_lookups) > 0
-- only report on active rows.
AND s.[object_id]=-999 -- Dummy value to get table structure.
;
-- Loop around all the databases on the server.
EXEC sp_MSForEachDB 'USE [?];
-- Table already exists.
INSERT INTO #TempUsage
SELECt TOP 10
[Usage]=(user_seeks + user_scans + user_lookups)
,DatabaseName=DB_NAME()
,TableName=OBJECT_NAME(s.[object_id])
,IndexName=i.name
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
WHERe s.database_id=DB_ID()
AND i.name IS NOT NULL -- Ignore HEAP indexes.
AND OBJECTPROPERTY(s.[object_id], ''IsMsShipped'')=0
AND (user_seeks + user_scans + user_lookups) > 0 -- only report on active rows.
ORDER BY [Usage] DESC
;
'
-- Select records.
SELECT TOP 10 * FROM #TempUsage ORDER BY [Usage] DESC
-- Tidy up.
DROp TABLE #TempUsage
逻辑上最零碎的索引所使用的脚本
-- Create required table structure only.
-- Note: this SQL must be the same as in the Database loop given in the -- following step.
SELECT TOP 1
DatbaseName=DB_NAME()
,TableName=OBJECT_NAME(s.[object_id])
,IndexName=i.name
,[Fragmentation %]=ROUND(avg_fragmentation_in_percent,2)
INTO #TempFragmentation
FROM sys.dm_db_index_physical_stats(db_id(),null, null, null, null) s
INNER JOIN sys.indexes i ON s.[object_id]=i.[object_id]
AND s.index_id=i.index_id
WHERe s.[object_id]=-999 -- Dummy value just to get table structure.
;
-- Loop around all the databases on the server.
EXEC sp_MSForEachDB 'USE [?];
-- Table already exists.
INSERT INTO #TempFragmentation
SELECt TOP 10
DatbaseName=DB_NAME()
,TableName=OBJECT_NAME(s.[object_id])
,IndexName=i.name
,[Fragmentation %]=ROUND(avg_fragmentation_in_percent,2)
FROM sys.dm_db_index_physical_stats(db_id(),null, null, null, null) s
INNER JOIN sys.indexes i ON s.[object_id]=i.[object_id]
AND s.index_id=i.index_id
WHERe s.database_id=DB_ID()
AND i.name IS NOT NULL -- Ignore HEAP indexes.
AND OBJECTPROPERTY(s.[object_id], ''IsMsShipped'')=0
ORDER BY [Fragmentation %] DESC
;
'
-- Select records.
SELECT TOP 10 * FROM #TempFragmentation ORDER BY [Fragmentation %] DESC
-- Tidy up.
DROp TABLE #TempFragmentation
获得IO高的查询
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;
获得I/O统计
Select wait_type, waiting_tasks_count, wait_time_ms from sys.dm_os_wait_stats where wait_type like 'PAGEIOLATCH%' order by wait_type
查询当前I/O锁
