none
Using WMI to Monitor SQL Server Performance

    Question

  • So I'm not sure if this is the right forum, but since this topic is SQL Server Analysis, and that's what I want to do, I figured this was the best spot to ask about this.

    I've been trying to write a C# program that would use WMI to monitor SQL Server performance including the transactions per second and data usage. I understand these counters are available using PerformanceCounter objects but unfortunately, I'm not able to use these after a mysterious problem crippled our perfmon tool. Using WMI, I'm able to gather monitoring data for general machine counters such as Processor, Memory, and Physical Disk.

    However, I seem to be having trouble figuring out how to get the data on Sql Server Counters to be reported to WMI. I read that I can use WMI Providers to interact with Sql Server but thus far, I haven't been able to find any good details on these providers.

    Using ManagedComputer, I'm able to find general information such as what SQL Server instances I have running, but nothing like the transactions per second on databases or anything like that.

    Can anyone provide a reference on what I could use to get this information or what forum would be best to post this on?
    • Moved by Darren GosbellMVP Thursday, April 08, 2010 4:01 AM this is not an Analysis Services OLAP thread (From:SQL Server Analysis Services)
    Wednesday, April 07, 2010 2:19 PM

Answers

  • Hi,


    Based on your description, you could view the system performance counters by using WMI, but not SQL Server performance counters. There are two known issues about it.
    One is:
     You cannot use WMI to collect SQL performance counters for all the instances of SQL Server 2000 on a computer that is running Windows Server 2003
    http://support.microsoft.com/kb/888990


    The other is:
    All SQL Server 2008 database engine performance counters appear to be missing when you try to monitor the performance counters through WMI interfaces. This problem occurs because the SQL Server database engine reports an invalid performance object count to WMI. WMI detects this invalid count. Then, WMI prevents WMI registration for all SQL Server database engine performance counters. To work around it:
    1. Install SQL Server 2008 Service Pack 1:
    http://support.microsoft.com/kb/968382
    2. Cumulative update package 3 for SQL Server 2008 Service Pack 1
    http://support.microsoft.com/kb/971491


    If there are any more questions, please let me know.
    Thanks.


    ***Xiao Min Tan***Microsoft Online Community***
    Friday, April 09, 2010 7:25 AM

All replies

  • Analysis Services is the SQL Server OLAP server. I am moving this thread to the Database Engine forum, where it is more likely to be answered.
    http://geekswithblogs.net/darrengosbell - please mark correct answers
    Thursday, April 08, 2010 4:03 AM
  • You may check some examples here http://msdn.microsoft.com/en-us/library/aa394597(VS.85).aspx, but for me I'll use WMI + SQL statements unless you can dive into powershell & smo
    yup
    Thursday, April 08, 2010 8:55 AM
  • Hi,


    Based on your description, you could view the system performance counters by using WMI, but not SQL Server performance counters. There are two known issues about it.
    One is:
     You cannot use WMI to collect SQL performance counters for all the instances of SQL Server 2000 on a computer that is running Windows Server 2003
    http://support.microsoft.com/kb/888990


    The other is:
    All SQL Server 2008 database engine performance counters appear to be missing when you try to monitor the performance counters through WMI interfaces. This problem occurs because the SQL Server database engine reports an invalid performance object count to WMI. WMI detects this invalid count. Then, WMI prevents WMI registration for all SQL Server database engine performance counters. To work around it:
    1. Install SQL Server 2008 Service Pack 1:
    http://support.microsoft.com/kb/968382
    2. Cumulative update package 3 for SQL Server 2008 Service Pack 1
    http://support.microsoft.com/kb/971491


    If there are any more questions, please let me know.
    Thanks.


    ***Xiao Min Tan***Microsoft Online Community***
    Friday, April 09, 2010 7:25 AM
  • Thanks for the array of responses.

     

    Xiao, from your descriptions, it sounds like it's not really possible to query SQL performance counters using WMI (unless you install to the latest version of SQL Server + Service Packs) did I read that right?

     

    Now I kept reading online about using the WMI Sql Provider to read SQL Server counters... is that a different way to get around this or is it just SOL in general for WMI when it comes to reading SQL information?

    Monday, April 12, 2010 8:02 PM
  • Hello,

    An example for using WMI can be found in the following link Using WMI Data Reader and WMI Event Watcher Tasks in SSIS

    Thanks
    Ashish

    Tuesday, April 13, 2010 5:08 AM
  • Hello,

    An example for using WMI can be found in the following link Using WMI Data Reader and WMI Event Watcher Tasks in SSIS

    Thanks
    Ashish

    Tuesday, April 13, 2010 5:08 AM
  • Hrm that page seems to give a 404 error.
    Tuesday, April 13, 2010 1:08 PM