Post reply

SQL performance counts missing

  • July 6, 2004 at 8:08 am

    #86028

    SQl Server 2000/SP3 on Windows 2003 Server or Windows 2000 Server. No SQL counts show in Performance monitor. If I run:

    select * from master..sysperfinfo

    I get no records.

    Can anybody tell me how can I activate these counts so that I can monitor them?

    Any answer will be appreciated.

    Gabriela Nanau

  • July 6, 2004 at 8:22 am

    #513459

    This is a know bug. If SQL server is restarted with performance monitor turned on, SQL counters fail on start up. 

    Only solution I know is to stop performance monitors and re-boot server counters will now be available.

     

  • July 6, 2004 at 8:36 am

    #513462

    Thanks, I tried, is not working.

     

  • July 6, 2004 at 10:20 am

    #513481

    We had a problem like that too.  Solution was to have MS come on-site and get them fixed.  They had to try several different things to fix this.

    Upside: 

    1. Working on 1 of the nodes in our production cluster.
    2. Part of our annual agreement with MS.

    Downside:

    1. They were only able to fix it on 1 of the nodes.  IF we fail-over the other node still not showing.

    Because of this problem and some other issues we are re-building our production cluster........



    Good Hunting!

    AJ Ahrens


    webmaster@kritter.net

  • July 6, 2004 at 10:12 pm

    #513557

    My resolution document may come in handy you should only need to complete steps 4 through 8 however... I make no claim that this will resolve your issue but running steps 4 through 8 should fix the issue.

     

    XXX_XXXXXXX DATABASE

    DRAFT

    05-20-2004

    Summary / Description: 

    Due to the inability to resolve the Performance counters displaying accurate values on XXXXXXX which is running WIN2000 SP4 and MSSQL 2000 SP3a The following report relates to the issues and steps taken toward resolution.

    When viewing the Performance Monitor all SQL Server related Performance Counters show a value = 0

    The initial solution was to reload the SQL Performance Counters. After unloading and reloading the counters with all applicable steps (Twice) with no positive expect result I verified the system table; Master.dbo.sysperfinfo contained relevant counter data and interestingly enough no data <0 Rows> existed.

    NOTE:The sysperfinfo Table (secure)The sysperfinfo table contains the performance counter values for SQL Server objects.

     

    Common Cause:

    It is common to lose the performance counters after a crash or server miss-configuration. I have no instance that these events may have has in fact occurred. However the steps to reload the SQL Perf Counters are in the Solution Section of this document which will of course resolve the data missing in the system table sysperfinfo. This could also however be due to a particular program or application that may infact delete or disable this performance monitoring information <unlikely but possible>

    Solution:

    All SQL Counters were unloaded after noticing the system table was empty

     

    RELOADING SQL SERVER PERFORMANCE COUNTERS: <Failed to Resolve>

    There are many reasons when the SQL Server counters will disappear, such as Permission, Server Crash, Registry Modification and Virus. To work around the symptom, please take the following actions:

    STEPS:

    1. Check if you have full permission to access the local folder: C:\Program Files\Microsoft SQL Server\MSSQL\  Make sure that you can access this folder or your current account is located in local administrator group.

    2. Make sure there is no third-party application which add keys in Register to disable the performance counters.You can open the registry to check if there is a key named "Disable Performance Counters" valued "1" in:

    [HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\MSSQLSERVER\Performance]

    If it exists, please delete it.

    3. Under the register key:

    [HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\MSSQLSERVER\Performance]

    Make sure the following keys with the value DO EXIST. If not, please create a new one and add the value.

    # Note # : The "Last Counter", "Last Help", "First Help", "WbemAdapFileSignature", "WbemAdapFileTime", and "First Counter" values can vary between SQL Server installations.

    "Library"=" C:\PROGRA~1\MICROS~3\MSSQL$~1\BINN\SQLCTR80.DLL "

    "Collect"="CollectSQLPerformanceData"

    "Open"="OpenSQLPerformanceData"

    "Close"="CloseSQLPerformanceData"

    "Last Counter"=dword:000016ce

    "Last Help"=dword:000016cf

    "First Help"=dword:000015bb

    "WbemAdapFileSignature"=hex:d1,70,dc,f8,a7,75,5e,e4,9e,e6,dd,91,9e,cd,06,65

    "WbemAdapFileTime"=hex:d6,34,9d,95,64,0d,c2,01

    "WbemAdapFileSize"=dword:00008238

    "WbemAdapStatus"=dword:00000000dir

    "First Counter"=dword:000015ba

    4. Unload the SQL Server Counters. In command line, execute "unlodctr MSSQLServer" (Without quotation, the same as below)

    5. Reloading the SQL Server Counters

         (a) In command line, locate the current folder to C:\Program Files\Microsoft SQL Server\MSSQL\BINN

         (b) Execute "lodctr sqlctr.ini"

    6. Stop SQL Server Services and Restart the Services

        (a) In command line, execute "net stop mssqlserver"

        (b) Execute "net start mssqlserver"

        (Or you can perform it in Service Manager or in System Services)

    7. Restart Database Server

        (a)  close perfmon

        (b)  reboot Server

     8. Open PerfMon to check if the counters appear

        (a)  In command line, execute "perfmon"

        (b)  Add and check if the counter appear

     

    Jbabington
    Jbabington@hotmail.com

  • July 7, 2004 at 3:23 am

    #513586

    Hi,

    I'm also having troubles with the performance counters. (sysperfinfo stays empty)

    I've followed the resolution document of Jbabington, but with no results.

    Does anyone have an other idea of how I can solve this problem ?

    THANKS!!!

  • July 7, 2004 at 7:46 am

    #513645

    The following bug may be the cause of some of these problems:

    PSS ID Number: 812915 , BUG#: 363762 (SHILOH_bugs)

    FIX: "Performance Monitor Shared Memory Setup Failed: -1" Error Message When You Start SQL Server

    Also has the symptom: "You may notice that SQL Server-related objects do not appear in the Performance object list."

    "..........running SQL Server with Service Pack 3 (SP3).........."

    A hot-fix is available from Microsoft "contact Microsoft Product Support Services to obtain the fix"

    See TechNet for further details.

    HTH

    Robert

     

  • July 7, 2004 at 7:55 am

    #513648

    You can try this..

    From command prompt run this..

    unlodctr mssqlserver

    This will uninstall sql server performance monitor counters.

    From Command prompt again run this..

    lodctr \\sqlserver path\binn\sqlctr.ini

    Close and re-openPerformance monitor.You should see the counters now. If not restart the server.

  • July 13, 2004 at 3:18 am

    #514351

    Thanks for the help, but I'm afraid I've tried it all with no succes...

     

  • July 13, 2004 at 9:19 am

    #514413

    I had similiar problem earlier and what we had to do was reinstall the latest NT/Windows service pack on the same server and it all came back.

     

    mom

  • July 13, 2004 at 9:29 am

    #514417

    Can you post the result of select @@version.

    As someone mentioned, It is a bug of SQL Server and was fixed by hotfix 780. Call Microsoft to get the hotfix.

     

  • January 14, 2005 at 12:43 pm

    #537527

    Just wanted to mention I've been troubleshooting this issue for a few months with Microsoft on several sql servers (registry permissions, unloads/reloads, hotfixes, etc, etc.....)  We tried so many things mentioned in this thread but nothing worked.  Finally they sent & had me run pssdiag.exe (not sure if all versions of pssdiag are the same or they might need to be customized to each problem when they send you the tool?)  I got a message saying that pssdiag was installing the SQL counters.  The next time the server rebooted they were all there!  We're trying this on the other server over the weekend... hope it works on that one too!

  • January 17, 2005 at 8:19 am

    #537682

    Well the fix worked on our other server also.  After working for 2 months with Microsoft SQL support on this issue, here is a review of the steps that fixed it:

    1 - Add the location of the sqlctr80.dll to the path statement on the server.

    2 - Replace the sqlctr80.dll with the one from a similiar sql server that was okay.

    3 - Run pssdiag.exe  (This reinstalled the SQL performance counters which never helped when we did it manually.)

    4 - Reboot the server.

  • January 25, 2005 at 11:09 am

    #539016

     

    Hi- seeing this problem also.

    3 Questions:

    1.) your last post is dated 1/17, has the fix survived reboots? 

    2.) SQL Failovers?

    3.) Do you know if your pssdiag.exe was customized by MS to get the counters back in or would the downloaded version off MS do it? 

    Thanks!

  • July 18, 2008 at 6:09 am

    #844959

    I Also had this problem of not seeing (missing) any MS SQL counters in the perfmon tool,

    I followed many recommendations and at last this one

    and that did the trick.

    Thanks for sharing your solution, finaly 🙂

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply