SQL Server: 揭开隐藏数据的面纱，优化应用程序性能

核心提示： -- Create required table structure only.-- Note: this SQL must be the same as in the Database loop given in the following step.SELECT TOP 1Databa

-- 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
　　　　DatabaseName = DB_NAME()
　　　　,TableName = OBJECT_NAME(s.[object_id])
　　　　,IndexName = i.name
　　　　,user_updates　　
　　　　,system_updates　　
　　　　-- Useful fields below:
　　　　--, *
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
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 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 #TempUnusedIndexes
SELECT TOP 10　　
　　　　DatabaseName = DB_NAME()
　　　　,TableName = OBJECT_NAME(s.[object_id])
　　　　,IndexName = i.name
　　　　,user_updates　　
　　　　,system_updates　　
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 = 0
　　AND user_scans = 0
　　AND user_lookups = 0
　　AND i.name IS NOT NULL　　-- Ignore HEAP indexes.
ORDER BY user_updates DESC
;
'
-- Select records.
SELECT TOP 10 * FROM #TempUnusedIndexes ORDER BY [user_updates] DESC
-- Tidy up.
DROP TABLE #TempUnusedIndexes