SQL Server2005 表的使用情况监控

-------------------------查询表的使用情况，读写情况、锁的情况、页面情况

SET NOCOUNT ON
CREATE TABLE #tab(database_id INT     
     ,object_id int
     ,index_id int
     ,leaf_insert_count int
     ,leaf_delete_count int
     ,leaf_update_count int     
                    ,nonleaf_insert_count int
     ,nonleaf_update_count int
     ,leaf_allocation_count int
     ,nonleaf_allocation_count int
     ,leaf_page_merge_count int
     ,nonleaf_page_merge_count int
     ,range_scan_count int
     ,row_lock_count int
     ,row_lock_wait_count int
     ,row_lock_wait_in_ms int
     ,page_lock_count int
     ,page_lock_wait_count int
     ,page_lock_wait_in_ms int
     ,index_lock_promotion_attempt_count int
     ,index_lock_promotion_count int
     ,page_latch_wait_count int
     ,page_latch_wait_in_ms int
     ,page_io_latch_wait_count int
     ,page_io_latch_wait_in_ms int
     ,dtime datetime)
DECLARE @db_name VARCHAR(30)
DECLARE @db_id INT
DECLARE @table_name VARCHAR(35)
DECLARE @sqlcmd NVARCHAR(4000)
 DECLARE curdb CURSOR FOR SELECT name,database_id FROM sys.databases WHERE database_id >=5
OPEN curdb
FETCH curdb INTO @db_name,@db_id
WHILE (@@fetch_status = 0)
BEGIN
 SET @sqlcmd='USE  '+@db_name
 EXEC sp_executesql @sqlcmd 
 DECLARE cur CURSOR  FOR SELECT name FROM SYS.tables WHERE TYPE='U'
 AND NAME NOT IN ('sysarticles'
     ,'sysarticlecolumns'
     ,'sysschemaarticles'
     ,'syspublications'
     ,'syssubscriptions'
     ,'sysarticleupdates'
     ,'MSpub_identity_range'
     ,'systranschemas'
     ,'MSpeer_lsns'
     ,'MSpeer_request'
     ,'MSpeer_response'
     ,'sysreplservers')
 OPEN cur
 FETCH cur INTO @table_name
 WHILE (@@fetch_status = 0)
 BEGIN
  DECLARE @object_id INT
--  SET @sqlcmd='SELECT OBJECT_ID('''+@table_name+''')'
--  EXEC sp_executesql @sqlcmd
   INSERT INTO #tab
      SELECT database_id
     ,object_id
     ,index_id
     ,leaf_insert_count '叶级插入的累积计数'
     ,leaf_delete_count '叶级删除的累积计数'
     ,leaf_update_count '叶级更新的累积计数'
     ,nonleaf_insert_count '叶级以上的插入累积计数'
     ,nonleaf_update_count '叶级以上的更新累积计数'
     ,leaf_allocation_count '索引或堆中的叶级页分配的累积计数'
     ,nonleaf_allocation_count '叶级以上由页拆分引起的页分配的累积计数'
     ,leaf_page_merge_count '叶级页合并的累积计数'
     ,nonleaf_page_merge_count '叶级以上页合并的累积计数'
     ,range_scan_count '从索引或堆开始的范围和表扫描的累积计数'
     ,row_lock_count '请求的行锁的累积数量'
     ,row_lock_wait_count '数据库引擎等待行锁的累积次数'
     ,row_lock_wait_in_ms '数据库引擎等待行锁的总毫秒数'
     ,page_lock_count '请求的页锁的累积数量'
     ,page_lock_wait_count '数据库引擎等待页锁的累积次数'
     ,page_lock_wait_in_ms '数据库引擎等待页锁的总毫秒数'
     ,index_lock_promotion_attempt_count '数据库引擎尝试升级锁的累积次数'
     ,index_lock_promotion_count '数据库引擎升级锁的累积次数'
     ,page_latch_wait_count '数据库引擎由于闩锁争用而等待的累积次数'
     ,page_latch_wait_in_ms '数据库引擎由于闩锁争用而等待的累积毫秒数'
     ,page_io_latch_wait_count '数据库引擎等待 I/O 页闩锁的累积次数'
     ,page_io_latch_wait_in_ms '数据库引擎等待页 I/O 闩锁的累积毫秒数'
     ,GETDATE()
  FROM sys.dm_db_index_operational_stats(@db_id, @object_id, NULL, NULL)
   FETCH cur INTO @table_name
   END
   CLOSE cur
   DEALLOCATE cur
FETCH curdb INTO @db_name,@db_id
   END
   CLOSE curdb
   DEALLOCATE curdb
    
SELECT * FROM #tab WHERE
-------------同样也可以利用上面的方法保存到文本文件，也可以单独存储到一个表中。