Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
laddu
Constraint Violating Yak Guru
332 Posts |
 Posted - 2008-03-12 : 17:37:30
|
| wwwwww Service Desk server. The database is housed there as well. Over the last couple of days, the SQL Server process has been spiking at 100%. It's so bad now that I can't log into the box at all. I'm trying to remotely stops services now, but it's a struggle. The only thing that I know that's changed recently is that some OS patches were applied on Sunday. Any help is appriciated |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-03-12 : 21:55:00
|
| Run SQL Profiler to determine what is going on inside SQL Server. If you capture a workload during the 100% issue, you can figure out what the problem is by checking out which query has the most cumulative CPU time over how many times it was called. Here's an example workload:EXEC Proc1 ... with CPU 10000EXEC Proc2 ... with CPU 10500EXEC Proc3 ... with CPU 350EXEC Proc1 ... with CPU 1100So Proc1 used 11,100 for CPU and the sum of all of CPU is 21950. This means it used 38% CPU for that workload. It would be the highest in my example. You'd also want to order your result set by Duration and check out any queries that are taking longer than expected:SELECT TOP 1000 Duration, TextDataFROM ... <TraceTable or TraceFile via the trace function>WHERE TextData IS NOT NULLORDER BY Duration DESCTara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
 |
|
|
|
|
|
SQL Server Process at 100%