Post reply

sysperfinfo user connections != sysprocesses

  • September 30, 2009 at 2:45 pm

    #226692

    Hey all,

    A little stumped. I wrote a quick audit procedure to basically monitor connections to every instance in my environment by simply querying sysperfinfo. Well, for the most part the data is spot on. Except for two instances. The cntr_value in sysprocesses for counter_name = user connections is showing about 2,000 for both of these servers, yet sysprocesses has no more than 30-40 at a time. I have verified that using perfmon on the server, does indeed report the same number that sysperfinfo does.

    I am at a loss here. I am not seeing this type of behavior on any other environment, except these. To add to the mystery, these servers are replicas of each other. One is a virtual and one is a physical. The virtual was to be a proof of concept, yet they both have this same problem in regards to sysperfinfo not being anywhere near the actual number of connections.

    Any thoughts here? I'm thinking I want to restart both instances ... but I'd like to better understand this first. Think this is just something wrong with WMI?

  • October 1, 2009 at 8:33 am

    #1060477

    Any thoughts? I am at a loss on this one.

  • October 1, 2009 at 8:50 am

    #1060489

    I just ran this across all the servers I manager (from sql 2000 to 2008)

    select (

    select count(*) from master..sysprocesses where spid > 49) sysprocval,(

    select cntr_value from master..sysperfinfo where counter_name = 'User Connections' ) sysperfval

    They all matched but one, and it was off by one. After checking, there is just a lot of users going in an out. It matches after running again.

    So if I had this problem, I would focus on the fact that perfmon is giving the wrong values and trust the sysprocesses results; perhaps try rebuilding the Perf mon counters (http://support.microsoft.com/default.aspx?scid=kb;en-us;300956).

  • October 1, 2009 at 8:56 am

    #1060496

    So restarting the instances appears to have fixed it ... crazy.

  • October 1, 2009 at 9:01 am

    #1060502

    Interesting.. If restarting fixed it then I certainly wouldn't rebuild the perf counters 😛

    Hope if doesn't reoccur, that could end up being a real intermittent pain..

Viewing 5 posts - 1 through 4 (of 4 total)

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