SQL Server 2005/2008 性能监控（续一）-蒲公英云

SQL Server 2005/2008 性能监控（续一）

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

-- 效果和”SQL Server 2005/2008 性能监控（待续）“ 中提到的一致。

SELECTDB_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

INNERJOIN sys.master_files mf ON mf.database_id = vfs.DbId

ANDmf.FILE_ID = vfs.FileId

-- 性能计数器动态视图, 和使用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'))
     )