February 17, 2004 at 3:47 pm
We have a performance problem on our servers and we have isolated it down to one particular application. However, the question asked in meetings is how does SQL Server divvy up CPU between SPIDs?
For example, between 10:32:36am and 10:48:02am, the server's CPU was maxed out by the service sqlservr.exe. Looking in Enterprise Manager at the Current Activity, we see that one particular SPID has high numbers in the CPU column (4 avging 780,000). Of course there are several other SPIDS occurring that are no where near using as much processing power. When other applications/users try to perform an action on the server, we usually get a time out error. Not until the offending SPID is finished or kill, will things get back to normal.
So, the question is if a SPID requests a query let's say, that is a resource hog, does SQL process for the SPID only and let the others wait until that SPID is done, or does it do some type of sharing of the CPU?
If you need a better explanation of my question, please let me know. Thanks for any light you can shed.
February 18, 2004 at 7:02 pm
My guess would be, assuming the SPID has no wait conditions (no disk waits, or resource waits) it would continue to process the SPID. I saw no mention of pre-emptive time sharing or anything of sorts in the SQL Server, like there is in Win.. OS. What are the offending SPIDs doing anyway? Could you be maxing on the CPU power of the system?
February 19, 2004 at 5:44 am
This is a ill written query that takes a few mintues to finish executing. The programmer of the query is saying that SQL cannot do time sharing or something similair and I'm looking to prove he is right or wrong. No doubt the query needs to be examined and made better, but I still would like to know how SQL handles processes.
Thanks for your reply.
February 19, 2004 at 9:58 am
You might do some research on the "NT Fibers" server option to get more information on how SQL handles concurrent execution. I think that by default, it spawns a thread for each item it is running and allows the operating system to handle the details. When you turn on fibers, SQL instead manages task switching between the query processes itself (this generally isn't recommended except under specific circumstances).
Matthew Galbraith
February 27, 2004 at 9:06 am
Do you know parallelism been use within this SQL ? Check your query plan ? I believed SQL task may swapped out too fast cause high CPU utilization.
February 27, 2004 at 9:22 am
query plan shows lots of parralleism. What do you mean by swapping out too fast causes high CPU utilization?
February 27, 2004 at 9:58 am
I believed the SQL task manager is keep checking the incoming queries request and cpu utlization to decide the cpu resource allocation. During the heavy load, the query that use parallelism may suffer the task been using high cpu and task manager may force the query to a 'wait' status and allow other queries get execute first. This feature was controller by cpu grace time and timeslice (I don't think is changeable anymore)
You may need to try disable paralelism on your SQL query to solve your problem.
February 28, 2004 at 8:27 am
I am interested in what is the real cause of your cpu problem, I think you haven’t gone deep enough into the way SQL Server utilizes CPU's, The main cause is most likely not a serialized transaction the parallel transaction you describe as never completing in an acceptable time frame, my advice is analyze the code and check the execution plan, if needed force it to run in a serialized transaction OPTION(MAXDOP 1) to restrict the plan to 1 CPU and see if it completes. in regard to the CPU utilization the reason I stated the above is a large portion of these problems trace to when the optimizer decides to parallelize (instead of serialize) the process, in this case the several tasks run in streaming aggregates and are never fully materialized (word - term usage may be wrong) at the end of the compilation. I have encountered a bug where the parallel processing and Hyper Threading cause an even more exasperating example of this.
Hope this helps!
Jim Babington
Jbabington
Jbabington@hotmail.com
March 1, 2004 at 7:05 am
I do agree with the HTT may be the cause of the problem. I saw several cases that enabling HTT cause problemduring heavy load.
March 1, 2004 at 1:21 pm
John, If your server is HTT enabled then before changing the BIOS to turn off HTT check the code with the Option stated in my previous email to verify that this completes in a timely manner. I have also noticed on HTT enable systems that certain jobs (automated DBCC's and Index Defrags seem to take a tad longer). Hope this helps!
Jim Babington
Jbabington
Jbabington@hotmail.com
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply