Monday, June 30, 2008

SQL System tables : master..sysperfinfo

Using the sysperfinfo table for server statistics, broken down by database.
Not sure where a colleague got this script from, so apologies if it's yours.
If it is, get in touch and i'll link you!


select  DB = a.instance_name,
  'DBCC Logical Scans' = a.cntr_value,
  'Transactions/sec' = (select d.cntr_value 
                          from 
                            master..sysperfinfo d 
                          where 
                            d.object_name = a.object_name and 
                            d.instance_name = a.instance_name and 
                            d.counter_name = 'Transactions/sec'),
  'Active Transactions' = (select case when i.cntr_value < 0 then 0 else i.cntr_value end from master..sysperfinfo i 
                          where 
                            i.object_name = a.object_name and 
                            i.instance_name = a.instance_name and 
                            i.counter_name = 'Active Transactions'),
  'Bulk Copy Rows' =      (select b.cntr_value from master..sysperfinfo b 
                          where 
                            b.object_name = a.object_name and
       b.instance_name = a.instance_name and 
                            b.counter_name = 'Bulk Copy Rows/sec'),
  'Bulk Copy Throughput'= (select c.cntr_value from  master..sysperfinfo c 
                          where 
                            c.object_name = a.object_name and 
                            c.instance_name = a.instance_name and 
                            c.counter_name = 'Bulk Copy Throughput/sec'),
  'Log Cache Reads' =     (select e.cntr_value from master..sysperfinfo e 
                          where 
                            e.object_name = a.object_name and 
                            e.instance_name = a.instance_name and 
                            e.counter_name = 'Log Cache Reads/sec'),
  'Log Flushes' =         (select f.cntr_value  from   master..sysperfinfo f 
                          where
       f.object_name = a.object_name and 
                            f.instance_name = a.instance_name and 
                            f.counter_name = 'Log Flushes/sec'),
  'Log Growths' =         (select g.cntr_value  from  master..sysperfinfo g 
                          where 
                            g.object_name = a.object_name and 
                            g.instance_name = a.instance_name and 
                            g.counter_name = 'Log Growths'),
  'Log Shrinks' =         (select h.cntr_value  from  master..sysperfinfo h 
                          where 
                            h.object_name = a.object_name and 
                            h.instance_name = a.instance_name and 
                            h.counter_name = 'Log Shrinks')
from 
    master..sysperfinfo a
where   
    a.object_name like '%Databases%'  and
    a.instance_name <> '_Total' and
    a.counter_name = 'DBCC Logical Scan Bytes/sec'
order by 
    1 

No comments: