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 |
|
sureshot
Yak Posting Veteran
72 Posts |
 Posted - 2005-09-14 : 12:30:27
|
| Having a weird issue where at times (not even peak load times, but avg load), SQL Server seems to stop processing. On an 8 CPU machine CPU usage drops from 40-50% to < 5%. Transection per sec drop to 10% of usual. Active Transactions/sec jumps from 0-4 to 100+. But what the heck are these transactions doing w/o increasing CPU load and why are they not counted in transaction/sec (because they're not actually finishing?). Would love to figure out just why they're getting stuck yet using no CPU. No log growths going on.Total latch wait time does jump dramatically (what does that infer?). User connections jump to triple normal yet no work is seemingly done. On the frontends (all 3) we get timeout simply opening a connection or doing anything even simple stuff.No deadlocks, no jump in lock requests or lock wait time. Lock requests/sec actually drops.Recompilations per sec 0-2 as usual, compilations/sec drops quite a bit.When we wait maybe 30 minutes or turn off a frontend (releasing connections I imagine), the system starts to recover. I've not ruled out an intermittent networking issue but haven't been able to catch it in the act.Any ideas or things to check or more info needed? |
|
|
KLang23
Posting Yak Master
115 Posts |
Posted - 2005-09-14 : 13:03:51
|
| Hi,Is there: A database or log backup being executed during these times? Is the server a publisher or subscriber? Any Analysis services, cube building, full-text indexing operations? Are there any third-party performance or management utilties executing? Does Profiler, sp_who2, sysprocesses show what is being executed?Best of luck tracking this down. |
 |
|
|
ryanston
Microsoft SQL Server Product Team
89 Posts |
Posted - 2005-09-14 : 13:04:17
|
| Your processes are likely stuck waiting on something. What do the wait types look like in sysprocesses? Is there any IO going on at this point?Thanks,----------------------Ryan StonecipherDeveloper, Microsoft SQL Server Storage Engine, DBCC(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.) |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-09-14 : 13:04:39
|
| You should run a trace in SQL Profiler to determine what queries are being run. You can then compare your Perf Mon counters to this trace to see what queries correlate to the time you are having this problem.Tara |
|
|
|
sureshot
Yak Posting Veteran
72 Posts |
Posted - 2005-09-14 : 14:38:58
|
| no backups,replication,analysis,cube building,fulltext or anything 3rd party going onI'll check on wait types in sysprocesses when it next occursWe generate 1GB of trace data in 45 minutes of runtime. We can't leave it running the whole day to "catch it in the act" due to the data volume. If we can't figure any other way, we might have to do hourly traces and just delete them every 3 hours. Still the amount of data to analyze is enormous. We have a DB setup to process trace data that makes performance aggregates per SP but without finding out which one went first, it's going to be hard to see what's a cause of slowness and what's an effect. Once it gets to an almost non-responsive state, almost no SQL executes in a reasonable time and we can't even always open a connection w/o a timeout. |
|
|
|
MuadDBA
628 Posts |
Posted - 2005-09-15 : 15:48:26
|
| Filter your traces a little bit...make a long running queries trace, that tracks queries that take longer than normal. |
|
|
|
|
|
|
|
|
SQL Server Stops Processing