Perfomance issue in SQL Server 2000 | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Perfomance issue in SQL Server 2000

My Server is suffering from 100% CPU utilization, this is because of SQL Server only. during this time my SQL server is getting hung. SQL server is utilizing the 100% CPU, because of my application, which is trying to fetch or update the database. query exectuon time is very less… but the database is accessed more frequently. there are 4 processors present in this server and in server configuration the parallelism processor is enabled to use all the processor at a time. if we change the parallelism processor to use one or two processor whether the CPU utilization will come down??
I don’t agree that is because of SQL Server, it is because the queries are not optimized for performance. Take a look athttp://sqlserver-qa.net/blogs/perftune/archive/2007/04/26/high-cpu-spikes-affecting-performance.aspx for complete investigation. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
Thanks for the information… Do i need to go for any service pack further… version of my SQL sevrer is Standard edition with service pack 3.
Based on the changes from SP3a to SP4 in SQL 2000 you might need to test for your application in this case. Not always you will gain having upto date service pack if the underlying queries are poorly performing with a bad design. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
one more doubt.. I could see four processes are accessing the database heavily in sysporcesses, all the for processor id are same. Its Sysprocesses result set…. SPID …….. CPU
54 x
54 x
54 x
54 x In this four processes only one is executting, the rest are all sleeping and all the processes are utilizing the CPU. is this because of Query parallelism set to use all processesors? There are 4 processesor available in the server

Do you see any blocking intermittently?<br />If so check the waitresource is for the blocked processes. If it’s due to recompiles then the wait resource will read something like TAB 11:1<img src=’/community/emoticons/emotion-12.gif’ alt=’:x’ />xxxxxxxxxxxxxxx (Compile).<br /><br />Fyi, if parallelism set to 0 will not cause recompiles. 0 means that SQL can use all the processors for parallel if it chooses. If a server can run parallel threads then all queries are compiled with both a parallel and non-parallel query plan.<br /><br /><br /><br /><b>Satya SKJ</b><br />Microsoft SQL Server MVP<br />Writer, Contributing Editor & Moderator<br /<a target="_blank" href=http://www.SQL-Server-Performance.Com>http://www.SQL-Server-Performance.Com</a><br /><center><font color="teal"><font size="1">This posting is provided AS IS with no rights for the sake of <i>knowledge sharing. <hr noshade size="1">Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.</i></font id="size1"></font id="teal"></center>
Of course the system could also be suffering from unwanted parallel execution. If you can pinpoint this to a specific query, you might try adding OPTION (MAXDOP 1) as the last part of the query statement.
Below are the sysprocess resulf for the processes which is causing the high cpu utilization SPID KPID Blocked waittype waitresource waittime lastwaittype dbid uid cpu memusage 126 12168 0 0x0000 0 MISCELLANEOUS 7 1 469844 348 126 11360 0 0x0208 0 MISCELLANEOUS 7 1 466750 348 126 7768 0 0x0208 0 MISCELLANEOUS 7 1 459282 348 126 4256 0 0x0000 0 MISCELLANEOUS 7 1 448890 348 there is no wait resource value…. there is no blocking also…. In server property Use all avialble processor is enabled and the minimum query plan threshold for considering queries for parallel execution(cost estimate) is set to 5
You have to find out those SPID by using DBCC INPUTBUFFER and see what they are executing. Also take help of traces as mentioned on the above link. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
I already tried to get the query from inpubuffer, but i could get only half of it…. Its an insert statement into a temp table from some other tables….. not able to find full query.. This a program which was executted by the applcation. Now its almost looks to be a poor performance with query… Now the only way to get the query is by TRACE, is it?
http://sqljunkies.com/Article/7F8518F9-FDAA-4FF3-8FC5-25E8946C8D0C.scuk fyi. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
]]>