September 4, 2008 at 8:11 am
We've got a customer's SQL64 server that going back and forth between ~10% cpu and 100% cpu for the sql service and the DB is slow to respond.
Quad core system with ~12gigs of ram, seperate mdf/ldf drives. Taskman claims 131k-KB(128meg) being allocated by sql and PF usuage at 15.5GB. This is the processess with the most memory usage and it's a dedicated SQL server with almost no extra processes. It takes about 15 minutes for the Server to come online.
sp_who2 shows everything idle or suspended and profiler shows no activity.
I noticed that kernel activity(red lines) is eating about 70% of whatever the cpu% is at.
What's the best place to start?
September 4, 2008 at 9:57 am
Task manager's a bad way to see SQL server's memory. Use perfmon rather and look at the total server memory and target server memory (under SQL server:Memory manager) and the process's working set (under Process, pick out the sqlservr process)
15 minutes for SQL to start up is way excessive. I had a busy system with a terabyte DB (and a couple others in the hundreds of GB) that took under 2 min from service start to full recovery and server online. Can you look at the error log please and, if possible, post the details from the beginning, up to the message Recovery Complete
I would look at your disk stats. Also via perfmon, look at the following counters (perhaps do a trace for 24 hours or so)
Physical disk: avg sec/read
Physical disk: avg sec/write
Physical disk: avg disk queue length
Physical disk: % idle time
What's the SQL service's max memory set to?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 4, 2008 at 11:31 am
The customer is checking their raid array and will get back to us. They think there may be some sort of hardware issue; but we won't know until they get time.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply