图片 2

有关索引的DMV

但是如何得到某个数据库所有的表的记录数,你要是用上面的方法估计得累死了。呵呵
下面提供如何借用sysindexes和sysobjects表来得到某个数据库每个表记录数的方法:
先给出SQL Server 2000版本的: 复制代码
代码如下: SELECT o.NAME, i.rowcnt FROM sysindexes AS i INNER JOIN
sysobjects AS o ON i.id = o.id WHERE i.indid 2 AND OBJECTPROPERTY(o.id,
‘IsMSShipped’) = 0 ORDER BY o.NAME SQL Server2005/8版本的SQL 语句: 复制代码 代码如下: SELECT o.name,
ddps.row_count FROM sys.indexes AS i INNER JOIN sys.objects AS o ON
i.OBJECT_ID = o.OBJECT_ID INNER JOIN sys.dm_db_partition_stats AS
ddps ON i.OBJECT_ID = ddps.OBJECT_ID AND i.index_id = ddps.index_id
WHERE i.index_id 2 AND o.is_ms_shipped = 0 ORDER BY o.NAME
赶快试试吧,肯定和你count(*)得到的结果相同。

1.查询数据库所有表

有关索引的DMV

SELECT ”” + s.name + ”’,’ FROM sysobjects s WHERE s.xtype = ‘U’ ORDER
BY s.name

1.查看那些被大量更新,却很少被使用的索引

2.根据第一查询所有表,查询表数据量

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT                                                   
    DB_NAME() AS DatabaseName
    , SCHEMA_NAME(o.Schema_ID) AS SchemaName
    , OBJECT_NAME(s.[object_id]) AS TableName
    , i.name AS IndexName
    , s.user_updates
    , s.system_seeks + s.system_scans + s.system_lookups
                          AS [System usage]
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
INNER JOIN sys.objects o ON i.object_id = O.object_id   
WHERE 1=2
EXEC sp_MSForEachDB ‘USE [?];                          
INSERT INTO #TempUnusedIndexes
SELECT TOP 20
    DB_NAME() AS DatabaseName
    , SCHEMA_NAME(o.Schema_ID) AS SchemaName
    , OBJECT_NAME(s.[object_id]) AS TableName
    , i.name AS IndexName
    , s.user_updates
    , s.system_seeks + s.system_scans + s.system_lookups
                                         AS [System usage]
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
INNER JOIN sys.objects o ON i.object_id = O.object_id   
WHERE s.database_id = DB_ID()
AND OBJECTPROPERTY(s.[object_id], ”IsMsShipped”) = 0
AND s.user_seeks = 0
    AND s.user_scans = 0
    AND s.user_lookups = 0
AND i.name IS NOT NULL
ORDER BY s.user_updates DESC’                           
SELECT TOP 20 * FROM #TempUnusedIndexes ORDER BY [user_updates]
DESC
DROP TABLE #TempUnusedIndexes

SELECT o.name,
ddps.row_count
FROM sys.indexes AS i
INNER JOIN sys.objects AS o
ON i.OBJECT_ID = o.OBJECT_ID
INNER JOIN sys.dm_db_partition_stats AS ddps
ON i.OBJECT_ID = ddps.OBJECT_ID
AND i.index_id = ddps.index_id
WHERE i.index_id < 2
AND o.is_ms_shipped = 0
AND o.name IN (‘表1’, ‘表2’, ‘表3’, ‘表4’, ‘表5’, ‘表6’,)
ORDER BY
o.name,
ddps.row_count

 

 

结果如图:

图片 1

 

这类索引应该被Drop掉

 

 

最高维护代价的索引

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT                                                    
    DB_NAME() AS DatabaseName
    , SCHEMA_NAME(o.Schema_ID) AS SchemaName
    , OBJECT_NAME(s.[object_id]) AS TableName
    , i.name AS IndexName
    , (s.user_updates ) AS [update usage]
    , (s.user_seeks + s.user_scans + s.user_lookups) AS [Retrieval
usage]
    , (s.user_updates) –
      (s.user_seeks + s.user_scans + s.user_lookups) AS [Maintenance
cost]
    , s.system_seeks + s.system_scans + s.system_lookups AS [System
usage]
    , s.last_user_seek
    , s.last_user_scan
    , s.last_user_lookup
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
INNER JOIN sys.objects o ON i.object_id = O.object_id   
WHERE 1=2
EXEC sp_MSForEachDB ‘USE [?];                             
INSERT INTO #TempMaintenanceCost
SELECT TOP 20
    DB_NAME() AS DatabaseName
    , SCHEMA_NAME(o.Schema_ID) AS SchemaName
    , OBJECT_NAME(s.[object_id]) AS TableName
    , i.name AS IndexName
    , (s.user_updates ) AS [update usage]
    , (s.user_seeks + s.user_scans + s.user_lookups)
                    AS [Retrieval usage]
    , (s.user_updates) –
(s.user_seeks + user_scans +
                         s.user_lookups) AS [Maintenance cost]
    , s.system_seeks + s.system_scans + s.system_lookups AS [System
usage]
    , s.last_user_seek
    , s.last_user_scan
    , s.last_user_lookup
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
INNER JOIN sys.objects o ON i.object_id = O.object_id   
WHERE s.database_id = DB_ID()
    AND i.name IS NOT NULL
    AND OBJECTPROPERTY(s.[object_id], ”IsMsShipped”) = 0
    AND (s.user_seeks + s.user_scans + s.user_lookups) > 0
ORDER BY [Maintenance cost] DESC’                       
SELECT top 20 * FROM #TempMaintenanceCost ORDER BY [Maintenance
cost] DESC
DROP TABLE #TempMaintenanceCost

 

结果如图:

图片 2

 

Maintenance cost高的应该被Drop掉