开发学院数据库MSSQL Server SQL Server: 揭开隐藏数据的面纱,优化应用程序性... 阅读

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

 2009-02-07 10:20:32 来源:WEB开发网   
核心提示: Figure7Identifying Most-Used Indexes -- Create required table structure only.-- Note: this SQL must be the same as in the Database loop given in

SQL Server: 揭开隐藏数据的面纱,优化应用程序性能Figure7Identifying Most-Used Indexes

-- 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

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

上一页  5 6 7 8 9 10 

Tags:SQL Server 揭开

编辑录入:爽爽 [复制链接] [打 印]
[]
  • 好
  • 好的评价 如果觉得好,就请您
      0%(0)
  • 差
  • 差的评价 如果觉得差,就请您
      0%(0)
赞助商链接