SQL Server Performance Forum – Threads Archive

Debugging a pegged cpu on sql server 2005.

Our DB tends to get pegged, about once a day. This means that connections are rejected, and the service can#%92t be stopped. This has resulted in using remote desktop to the machine and using the task manger to kill the process.
Other than the annoyance that this causes, it also means that I have no clue what is causing the CPU to be pegged, there is no way for me to log into the DB to see what is eating all the CPU that I#%92ve been able to find. As far as I know the DB is only inserting data in a non-indexed (the indexes were removed and the behavior stayed) table of ~30 million rows. The table has a fair number of columns and the data being inserted is in the order of millions of rows.
When I start the loading process the CPU (HT Xeon 3.00GHz) runs at ~15%, the ram (6.00 GB) slowly climbs up to 5 gigs. It will stay like this for hours, and I can#%92t predict when it will lock up. The DB is assigned 600 Gigs and shows 90% unallocated. This seems to rule out extends of something along those lines.
There are a minimal number of reads happening against the DB, the reads that do happen are run with nolock. Any help, or hints on where to look will be much appreciated.

Are you using the DAC via SQLCMD or SSMS to gain access to the DB?

How you are inserting the data? Is it within the sql server or from text files? Is parallalism enabled?
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

the data is being inserted using logparser ( link) parallalism is enabled. We are not using DAC. let me know if there are other questions I can answer.

Hamerb, use this link<a target="_blank" href=http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=21638>http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=21638</a> and create the proc on that db. When the memory is going up, use the proc and determine what is using it.. also it will show you what is using up any cpu. It will show you the exact statments running at the time. I know you dont know when it will lock up, but this is all I can give at this time. Any linked server activity that could be slow? Also, nolock is bad.<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> Means bad db design. hope you didn’ write it<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> Michael MCDBA "The statistics on sanity are that one out of every four persons is suffering from some sort of mental illness. Think of your three best friends — if they’re okay, then it’s you!"

]]>