监控数据库运行情况（四）

--性能检测 
--等待
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 in 
('LAZYWRITER_SLEEP','REQUEST_FOR_DEADLOCK_SEARCH','SQLTRACE_BUFFER_FLUSH', 'XE_TIMER_EVENT',
'FT_IFTS_SCHEDULER_IDLE_WAIT','LOGMGR_QUEUE','CHECKPOINT_QUEUE', 'SLEEP_TASK','BROKER_IO_FLUSH',
'BROKER_TASK_STOP','BROKER_TO_FLUSH','BROKER_EVENTHANDLER','CLR_AUTO_EVENT')
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