March 5, 2009 at 1:15 pm
We have a client server application that supports ~2000 users. The DB is close to 200GB in size and is located on a EMC Clarion SAN with lots of disk space (and underlying spindles) running in RAID10. As part of a study for performance on the application, I started a Sql Server 2005 (SP3) Profiler trace on the production server in the middle of the day using the standard TUNING trace template. Suddenly, every CPU (48 logical, 24 physical) on the machine went to 0% utilization. This is a machine that consistently runs around 20-30% average utilization 24 hours a day. This condition lasted for about 3 minutes when we noticed what was happening. I immediately stopped my trace and all the CPUs returned to normal. It was like the blocking process from hell had just hit my machine. My question is this: Has anyone ever seen this type of scenario before and if so, where do I start to try and determine the actual problem.
March 5, 2009 at 1:59 pm
I have never had tuning template, or any trace cause block on SQL Server. Yes SQL Server takes performance hit; but it doesn't shut down.
Maybe try running profiler with just the blocking event to see what is causing the blocking? That is where are the intersecting? did you try to log directly to a database?
Mohit.
Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE
* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]
March 5, 2009 at 3:43 pm
Jerry Sommerville (3/5/2009)
We have a client server application that supports ~2000 users. The DB is close to 200GB in size and is located on a EMC Clarion SAN with lots of disk space (and underlying spindles) running in RAID10. As part of a study for performance on the application, I started a Sql Server 2005 (SP3) Profiler trace on the production server in the middle of the day using the standard TUNING trace template. Suddenly, every CPU (48 logical, 24 physical) on the machine went to 0% utilization. This is a machine that consistently runs around 20-30% average utilization 24 hours a day. This condition lasted for about 3 minutes when we noticed what was happening. I immediately stopped my trace and all the CPUs returned to normal. It was like the blocking process from hell had just hit my machine. My question is this: Has anyone ever seen this type of scenario before and if so, where do I start to try and determine the actual problem.
Some Suggestions:
1. Don't run Profiler ON the server.
2. LIMIT your traced events (by duration,etc)
3. When tracing for long times use sqlTrace instead!
* Noel
March 5, 2009 at 4:11 pm
By SQLTrace you mean Server Side Trace?
Mohit.
Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE
* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]
March 6, 2009 at 6:20 am
Mohit -
Thanks for the suggestion. I am running a trace for 1 hour - blocking only. I am running the trace from a seperate server, storing the trace info in the database on the server from where I started the trace (not the production server that flat-lined). More as I know it.
... Jerry
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply