March 20, 2006 at 10:19 am
Hi, I am trying to analyze a SQL 2K server that the user is compalining is slow. I fire up the perfmon, and I look at several of my favorite counters, and I see what seems to me strange - low CPU util, but CPU que going up to 6 (though never sustained). What could be causing this behaviour?
March 20, 2006 at 2:54 pm
Hello Mordechai,
What counters did you use? I know you say they are favorite counters, but if you can specify which ones.
If you looked at processor / DPCs Queued / sec than they should have some numbers because they are "Deferred procedure calls" that run at low priority. My server shows them too.
If you selected System /processor queue length than maybe the numbers are for a particular moment, I was observing a value of 2 momentarily on the server that is doing nothing and then it was back to 0. Processor / % processor time is the average value, so overall average could be low but particular request may generate moments of activity
Regards,Yelena Varsha
March 20, 2006 at 3:08 pm
Below explans this counter: (<10 is considered OK)
Processor Queue Length is the number of threads in the processor queue. Unlike the disk counters, this counter counters, this counter shows ready threads only, not threads that are running. There is a single queue for processor time even on computers with multiple processors. Therefore, if a computer has multiple processors, you need to divide this value by the number of processors servicing the workload. A sustained processor queue of less than 10 threads per processor is normally acceptable, dependent of the workload.
March 20, 2006 at 3:09 pm
I use
Processor: % Processor Time (total and for each processor)
System: processor Queue Length (on a dual processor machine spikes up to 9)
Physical Disk: Disk Write (and Read) Bytes/sec
System: Context switches (max 7000)
Also, it is being weird in that it uses one processor at a time. I.e. when one processor is pegged, the other one is nearly idle, and then they switch...
March 20, 2006 at 4:29 pm
You may want to read about the processor affinity and UMS Scheduler. The scheduler sort of assigns a query to a certain processor.
There is an excellent article on the subject by Ken Henderson
Inside the SQL Server 2000 User Mode Scheduler
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqldev/html/Sqldev_02252004.asp
Ken says: "When a client connects to SQL Server, it is assigned to a specific UMS scheduler. ...... Once a connection is associated with a scheduler, it never leaves that scheduler"
To Check on UMS Processes:
DBCC sqlperf(umsstats)
What you are saying makes a lot of sence: you are probably running intensive queries so one query adhere to one processor with the queue length involved. Once the query is completed the CPU is idle again and the average processor time is low.
Yelena
Regards,Yelena Varsha
December 30, 2009 at 5:10 pm
I think I am having a similar issue. I am not really a Microsoft SQL 2008 user but I am using an application written in SQL 2008. I am trying to figure out how to speed up application reports without modifying the queries (i.e. if they are written inefficiently I am stuck with them as I don't have access to change them).
Can you tell me if 2 dual core processors will run the same query twice as fast as a single dual core processor? I don't seem to have a hard drive read / write issue as the database size is 8GB and the memory allocated to SQL is also 8GB. It takes about 30 seconds to run the report.
Also, is there a way to force the database to be cached to ram vs. having it reside only on the hard drive? It seems this would also help the operating performance of the database.
Any assistance you can provide would be greatly appreciated.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply