SQL Server 2005/2008 性能监控二

-- 数据库文件性能,文件io性能统计(必须重启sql server服务,才能清零该计数器),

 

SELECT DB_NAME(vfs.DbId) DatabaseName, mf.name,

mf.physical_name, vfs.BytesRead, vfs.BytesWritten,

vfs.IoStallMS, vfs.IoStallReadMS, vfs.IoStallWriteMS,

vfs.NumberReads, vfs.NumberWrites,

(Size*8)/1024 Size_MB

FROM ::fn_virtualfilestats(NULL,NULL) vfs

INNER JOIN sys.master_files mf ON mf.database_id = vfs.DbId

AND mf.FILE_ID = vfs.FileId

GO

 

 

 

-- 性能计数器动态视图,  和使用windows性能计数器效果一致。

 SELECT [counter_name], [cntr_value] FROM sys.dm_os_performance_counters

  WHERE ([instance_name] = '' OR [instance_name] = '_Total') AND (

         ([object_name] LIKE ('%Plan Cache%') AND [counter_name] IN

          ('Cache Hit Ratio', 'Cache Hit Ratio Base')) OR

         ([object_name] LIKE ('%Buffer Manager%') AND [counter_name] IN

          ('Buffer Cache Hit Ratio', 'Buffer Cache Hit Ratio Base', 'Page reads/sec', 'Page writes/sec')) OR

         ([object_name] LIKE ('%General Statistics%') AND [counter_name] IN

          ('Active Temp Tables', 'User Connections')) OR

         ([object_name] LIKE ('%Databases%') AND [counter_name] IN

          ('Transactions/sec', 'Log Cache Hit Ratio', 'Log Cache Hit Ratio Base', 'Log Flushes/sec',

            'Log Bytes Flushed/sec', 'Backup/Restore Throughput/sec')) OR

         ([object_name] LIKE ('%Access Methods%') AND [counter_name] IN

          ('Full Scans/sec', 'Range Scans/sec', 'Probe Scans/sec', 'Index Searches/sec', 'Page Splits/sec')) OR

         ([object_name] LIKE ('%Memory Manager%') AND [counter_name] IN

          ('Target Server Memory (KB)', 'Target Server Memory(KB)', 'Total Server Memory (KB)')) OR

         ([object_name] LIKE ('%SQL Statistics%') AND [counter_name] IN

          ('SQL Compilations/sec', 'SQL Re-Compilations/sec'))

         )