February 19, 2010 at 7:30 am
Hi all - I'm hoping someone can give me some tips on collecting evidence to troubleshoot a machine we have running SQL Server 2000 (on Windows 2k3 R2 Standard). My first reaction is that it needs more memory, but there's enough going on that I'm not certain that will solve the problem.
It's clear (from user experience) that it doesn't take much for the server to really start slowing down. When I check perfmon, there are obvious problems. Most notably, page life expectancy will drop to 0 every couple minutes, then slowly climb back up to the 250-300 second range, then crash again. Unsurprisingly, these drops in PLE are accompanied by dips in the buffer cache hit ratio - sometimes down to ~80%.
Many times, this event is accompanied by massive spikes in logical disk queue length - up over 100, on occasion.
The biggest problem I've got is not being as familiar with the tools available to me in SQL 2000 for digging into what the server is doing - I've been lucky enough to be using SQL 2005 almost exclusively to date. I've been looking at syprocesses and syslocks (and, of course, sp_who2), but I'm not sure enough of what I'm seeing to get much out of them.
I'll be running some profiler traces today, trying to capture additional information, but I feel like I'm just sort of randomly flailing around. I'm fairly certain that more RAM (the server only has 3 GB) will help, but I'd rather have some evidence to support that - both so I know that I'm not simply patching around the real problem, and to help make the case for it to management.
Any help is much appreciated!
______
Twitter: @Control_Group
February 19, 2010 at 7:51 am
How many users, what are they doing, how is the data accessed?
Some possibilities: SELECT * queries on big tables; stored proc recompilations?
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply