October 20, 2008 at 11:00 pm
Hi,
I have a SQL server 2005 box that does virtually nothing. There is only around 3 users at the most at one given time. Unless they are running a report the CPU usually just bounces about 0% and 10% however the Processor Queue Length is any where between 5 and 10 very rarely goes below 5 and it doesn't matter if someone is using it at all.
SQL sits on VMWare which is configured as Intel(R) Xeon(R) CPU 5150 @ 2.66GHz 2.66GHz, 1GB of RAM.
Can anyone shed some light onto why this would happen?
Thanks
Kris
Kris
October 21, 2008 at 8:48 am
Do you have Full-Text indexing jobs running?
They can take a lot of CPU cycles and SQL won't see it because it has been delegated to Windows.
The SQL job appears to be running for a second, but is just the request for the indexing.
Cheers,
JohnA
MCM: SQL2008
October 21, 2008 at 3:52 pm
Maybe I'm not understanding but my issue is not with the CPU. It's doing everything I would expect it's the fact that the Processor Queue Length won't go below 5 regardless of what the server is doing (which is virtually nothing). From all things I've read it should be 0 or 1.
Thanks
Kris
Kris
October 21, 2008 at 3:57 pm
I assume that you are using the system performance object in PerfMon, correct? What collection interval are you using?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
October 21, 2008 at 5:03 pm
I'm not sure what you mean but it is sampling data every second on vertical scale max 100 and min 0. Is that what you meant?
Kris
October 22, 2008 at 9:45 am
In my experience this is a synchronicity problem.
The queue size that you see is not supportable by the CPU% value, therefore one of them must be inaccurate. CPU% is a system accounted value, but Processor Queue Length is only sampled by your monitoring process. Therefore it is extremely likely that your sampled queue length does not accurately reflect the actual average queue length. In other words: this is a sampling artifact.
What could cause such a sampling artifact? Primarily the fact that the performance sampling of PerfMon (and virtually all other Windows performance monitors) is not at all random: rather it happens at the beginning of every "X" seconds where "X" is the "collection interval" that you specify. The default is "1" which means that the states of every process/thread on your system is sampled at the beginning of every second to determine how many are in the processor queue.
The problem with this is that there are many other monitoring programs, utilities and applications that work in a very similar way: they wake up at the beginning of every second (or minute, or hour) to poll whatever application source they are monitoring, record it and then go back to sleep. Which means that even though the CPU may be idle for the remaining 95% of the second, at the beginning of the second, when your monitor wakes up to check CPU states, so does a whole bunch of other monitors: thus fouling your queue length statistics.
This does not affect most of the other performance statistics because they are accounted for and tracked by the operating system, rather than by the collection monitor.
You can mask this problem in two ways: Either lower your PerfMon collections process's priority below the priority of the other monitors, or raise their priority above yours. This should result in the apparent Processor Queue Length lowering down to (N-1) where N is the number of processor cores. OF course you still will not have accurate numbers, because you have only masked the problem.
If you really need accurate Processor Queue Length numbers, you will need to find a collection monitor that can run in sub-second intervals and then run it at an elevated priority.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply