使用DMV和DMF分析數據庫性能

 　　服務器等待的原因

　　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)