February 6, 2012 at 2:42 pm
Hi all,
This may not be the correct place for this, but it seems to fit.
Here's the situation, I've got a client with a SQL Server 2005 (build 3970, SP2. v9.00.4035.00 (x64)) that has taken a noticeable dip in performance since about 2 weeks ago. The processor tends to be pegged at 50% with just SQL Server, and when they run an import (external application) it pegs to 100% for the duration, of which about 25% processor is the download and 75% is SQL Server.
Server: VMWare Virtual server.
OS: Windows Server 2003 x64 SP 2 Standard Edition
RAM: 10 GB - 10 GB Pagefile
Processors: 1 CPU. CPU is recognized by Windows as Xeon E5405 @ 2.00 GHz. I'm assuming this is Quad Core or Dual Core with Hyperthreading as Windows sees 4 logical cores (i.e. 4 heartbeats in Task Manager)
Number of Users: appears to be at most 10 concurrent, but spread over about 5 separate databases.
The import process hits each database in turn and does not hit 2 DB's at the same time.
There are several databases, the largest of which is over 250 GB.
Question: How do I find what spid/process is causing SQL Server to run at such a high CPU utilization? I've run a server side trace and Activity Monitor but can't find anything that would seem to cause these issues.
February 6, 2012 at 2:50 pm
This is a decent place to start:
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
February 6, 2012 at 2:51 pm
Adam Machanic's very helpful procedure sp_whoisactive http://sqlblog.com/blogs/adam_machanic/archive/2011/04/27/who-is-active-v11-00-a-month-of-activity-monitoring-part-27-of-30.aspx can tell you a lot about what's running if you can run it while the process is happening.
February 7, 2012 at 6:29 am
Thank you both for your responses. I'm investigating our last update to see if any of those SP's/views are implicated. I've run a trace, but nothing appears out of order in terms of reads vs. time. a good portion of the system uses dynamic SQL so I'm having to adjust for that in the groupings.
February 7, 2012 at 8:38 am
Well, I found the issue. Turns out that the client had disabled the reporting tables and was running reports directly from the live data... Because each of the queries are dynamic, I would never have seen it but my co-worker noticed the reporting filtering procedure was way more costly than it should have been. Mystery solved.
February 7, 2012 at 9:09 am
Lol, yeah that'll do it. 🙂
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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply