tracing a runaway SQL process

[Jasen]

On the same server where somebody had the bright idea to build their production database inside Master, I'm finding that the sqlservr process will start to eat up increasing amounts of CPU until the box eventually becomes unusable. They've been restarting it a couple times a week because of this.
So far, I've made a perfmon log of the activity, and done a SQL profile trace during the same time period, attempting to correllate. But I'm not completely sure what I'm supposed to be looking for to determine what queries are the problem.
I see a couple processes in sysprocesses that have high CPU and high waittimes, but not sure if that means much.
Basically, I'm not exactly sure how to tell which SPIDs/queries are the problem from this point. Anything obvious to look for?

 

[mrdenny]

A high cpu number in SQL Profiler or in the sysprocesses table means that the process is using lots of CPU.

If sounds kind of like someone has written some code that is running in a never ending loop.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com