SQL Server Performance Forum – Threads Archive

CPU# of choice

We are running MS SQL Server 2K on a machine with 4 Intel(R) Xeon(TM) MP CPU 3.33GHz. Sometimes we get a high 100% CPU utilization from only CPU#3. It was traced to be caused by a process in MS SQL Server. Is it possible for a process to be ran by a single CPU instead of being spread to all available CPU’s? The other CPU’s doesn’t even reach 1% utilization during these episodes. In MS SQL Server properties Process control; all Processors are checked and in the Parallelism option; it is set to Use all available processors. I would appreciate your comments and suggestions regarding this matter. be careful strive to be happy

if a query does not have a parallel execution plan, it will run on only one processor,
also, you do not want to let queries run with a parallel plan without careful testing is this a serious problem? if the query takes too long to run, find out why,
don’t worry about the fact that it runs on only 1 cpu

From my experience, if the parallelism is set default "Use all procs" meaning Max degree of parallelism is set to ZERO – We get random slowness in processes or queries run. When we monitored the execution, we found that those queries / processes were actually sleeping and waiting with a type CXPACKET (For a parallel process to start or complete) We have changed that setting to 2 (Use 2 procs ) and monitoring now. Hope this helps.
Thanks,
Anand.K Purity, patience, and perseverance are the three essentials to success
and, above all, love – Swami Vivekananda

http://www.sqljunkies.com/Article/7F8518F9-FDAA-4FF3-8FC5-25E8946C8D0C.scuk for a reference to tackle the high cpu activity. Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

can you somehow control wether your query would run with parallel execution? i mean via T-SQL ? I need to know where else can you enable or disable parallel execution. TIA [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] be careful strive to be happy

SQL makes that decision. You might want to reconsider the actual query and the objects (index, etc.) and see why the query is taking high CPU and not how to best execute the query (whether parallel or not). May the Almighty God bless us all!
www.empoweredinformation.com

I found the OPTION (MAXDOP <number&gt<img src=’/community/emoticons/emotion-5.gif’ alt=’

‘ /> to match what I have in mind but need to do some testing first. Thanks for all the comments and pointers, they’re all very much appreciated. be careful strive to be happy

]]>