the sqlserve.exe process is eating up all the cpu time all of the sudden on one server. I ran a profiler trace and no queries are jumping out at me as far as duration or cpu time. Does anyone have any suggestions on where to look? Thanks.
Is SQL server dedicated? Did you check if there is any scheduled job working? Use Peformance Monitor to check CPU, Memory and disk. Luis Martin Moderator SQL-Server-Performance.com One of the symptoms of an approaching nervous breakdown is the belief that one's work is terribly important Bertrand Russell All postings are provided “AS IS†with no warranties for accuracy.
These links have given good solutions. check whether those helps you http://www.sql-server-performance.com/forum/topic.asp?ARCHIVE=true&TOPIC_ID=1955 http://support.ipswitch.com/kb/WP-20050405-DM01.htm quote:Originally posted by patrick the sqlserve.exe process is eating up all the cpu time all of the sudden on one server. I ran a profiler trace and no queries are jumping out at me as far as duration or cpu time. Does anyone have any suggestions on where to look? Thanks.
Dear Mr. Pratik, Please check wether your SQL Server has patched with Latest Service Pack ? try installing Service Pack 3a for MS SQL Server, and the other issue would be from your application code,the connection opened and close ...? hsGoswami ghemant@gmail.com
It doesn't have to be just one query execution causing high cpu. High load can be because of many executions per second. When you analyze trace better group by sp name or large enough substring if your queries are not sp executions only.
The following update statement runs constantly under the same SPID 56: UPDATE STATUS SET Duration = 392, EndTime = 1115902989 WHERE ID = 7432805 It updates a table for phone logs. It currently has 7432647 records in it, and no primary or foreign keys or indexes. The spid has a CXPACKET waittype and is using 133498081 cpu time. Could this be the cause? Is there anything I can do to alleviate this? Any other info anyone needs? Thanks. Pat
quote:Could this be the cause?It could. quote:Is there anything I can do to alleviate this?Create clustered pk on ID column. Let us know if it helped. Thanks.
A table with no primary key and no indexes? Not clever at all. Could the update statement be issued by a client app?
What you need is to trace the activity on your sql server with profiler (not using GUI) for some time and then summarize the activity on sql server for CPU. Then you can come up with the information about query or procedure taking maximum CPU on your server and attack that. Jigar Patel
Dear Sir, The problem arise here when u have some around 74lacks of records in a table that used to update,insert,delete and select statement and it doesn't have PK or INDEX ... it will definately down your server's performance... would u let us know how often this table used ? more often is use will affect more the performance of your SQLSERVER hsGoswami ghemant@gmail.com
