SQL Server Performance

very basic

Discussion in 'Performance Tuning for DBAs' started by joe kingston, Feb 13, 2007.

  1. joe kingston New Member

    Can anybody help with the following super basic questions:
    1. I've seen/read there is a table in sql server 2000, namely master.dbo.sysperfinfo
    a) can someone tell me how to turn on some performance meterics and direct them to this table? is turning on capture of perf metrics/ direction of them to this table a SQL Server operation or an OS operation?
    b) It appears perfmon has perf metric captures registered with it. When a "measurement session" is set up in perfmon and directed to a SQL Server db does it have anything to do with the table mentioned above?

    Thanks for any help.
  2. Adriaan New Member

    Not really a very basic question, I would think! As with all so-called system tables, it is not necessarily an actual table.

    It looks like you can read statistics for the whole server, and for the databases that are currently attached. Any database that gets attached should pop up here.

    From BOL:
    quote:Contains a Microsoft® SQL Server™ representation of the internal performance counters that can be displayed through the Windows NT Performance Monitor.

    Note The Windows NT Performance Monitor is available only when using Microsoft Windows NT® 4.0 as the operating system.

    Performance condition alerts are only available for the first 99 databases. Any databases created after the first 99 databases will not be included in the sysperfinfo system table, and using the sp_add_alert procedure will return an error.
  3. joe kingston New Member

    Thank you Adriaan. I looked up sysperfinfo in BOL (Books on Line for us newbies), plus your remarks and infer the following:
    1) you do not "turn on" performance metrics and "direct" them to sysperfinfo; what is there is automatic - "instance_name" column appears to be database name except sometimes it's blank, sometimes just a number
    2) maybe metrics in sysperfinfo are cumulative, maybe reset on instance startup.

    BOL does not seem to give explicit assertions along the lines of 1 and 2.

    If anyone can further explain this table sysperfinfo, maybe know a link that explains it well, I would appreciate it.

  4. Luis Martin Moderator

    Check:

    http://support.microsoft.com/kb/283886

    Luis Martin
    Moderator
    SQL-Server-Performance.com

    All in Love is Fair
    Stevie Wonder


    All postings are provided “AS IS” with no warranties for accuracy.



  5. FrankKalis Moderator

Share This Page