March 27, 2009 at 1:08 pm
NO!
Never run the Profiler gui against Production.
It uses a completely different mechanism for gathering the data to present from what the server-side trace uses. This mechanism can, and will, cause performance issues. Here's the research[/url] I did on this.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 27, 2009 at 1:30 pm
I Performed full Reindex on Database 2 weeks ago, Last night I explicitly ran the on-line index on 5 tables ..
Database is big its nearly 1 TB and used for SAP application. but these 5 tables are explicitly used by other application. based these 5 tables some other tables being synchronized .
Other obvious things: Have you looked at the execution plans for the slow queries to make sure there are no scans or key lookups that could be the cause?
March 27, 2009 at 1:38 pm
Hi
Application using only 1 record set like
select * from table name where ID='123' and Id2='456' . its selecting these results and putting in other mysql table.
ID and ID2 has primary index on both.
I am trying to trace database to find out what other transactions are taking place while this XYZ application making request and I am just worried about my production performance when i ran profiler.
Thanks
Ichbin
March 27, 2009 at 1:54 pm
Do not ever use the profiler gui when tracing busy production servers. It can kill th server. I've done that twice.
There's an article at Simple Talk that shows how to create and run a server-side trace. Server-side traces have much, much less impact on the server and won't kill 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
March 27, 2009 at 1:56 pm
Run profiler from your local computer only not by RDPing into the server.
Running sql profiler on prod by RDP can be tricky, But I would consider running it for a short period of time to capture logs when the dev team tries to recreate a situation, I would try to keep the trc as small as possible to choosing the needed profiler filters. It uses "C:\WINDOWS\Temp" folder to store the trc files initially which vanish as soon as you stop the profiler & goes to the destination you provided before. I had this issue on my prod servers where after running the profiler, the C:\ become full on both nodes of a cluster where I was running them, It bought prod to halt , The workaround is to keep the temp folder size in check by stopping & again restarting the profiler or Better still just run it on your local computer only.
March 27, 2009 at 2:28 pm
I think it's a bit harsh and perhaps even a bit academic to say never ever run profiler against a production server. Yes, it can create problems when used incorrectly, but so can a poorly written query.
It's a tool to be used by the right hands, with the right set of events and filters in place, and under the right circumstances. Would I use it when the CPU is pegged at 100% and everything's running dog slow? Of course not. But when the server's under light load and I know exactly what I'm looking for...I don't know that I have so much of a problem with that.
March 27, 2009 at 2:37 pm
I am trying to trace database to find out what other transactions are taking place while this XYZ application making request and I am just worried about my production performance when i ran profiler.
I wouldn't recommend running profiler in this set of circumstances then. A server trace is more appropriate. You can also consider looking at the DMVs to get more information. For example:
- sys.dm_exec_connections shows you connection information
- sys.dm_exec_requests shows you what's currently executing
March 27, 2009 at 6:38 pm
Kendal Van Dyke (3/27/2009)
I think it's a bit harsh and perhaps even a bit academic to say never ever run profiler against a production server. Yes, it can create problems when used incorrectly, but so can a poorly written query.It's a tool to be used by the right hands, with the right set of events and filters in place, and under the right circumstances. Would I use it when the CPU is pegged at 100% and everything's running dog slow? Of course not. But when the server's under light load and I know exactly what I'm looking for...I don't know that I have so much of a problem with that.
I feel better telling people who are just starting NEVER EVER. If I start equivocating, they'll use it inappropriately. Of course, if you really understand what it's doing, the way it works, what will happen, what won't happen, etc. It's a tool like any other. But like any other tool, it'll take off a limb before you realize you've screwed up.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 27, 2009 at 6:44 pm
In a 1TB database profiler should never be run in production but the real problem is architecture and design issue because data from the 10 tables can be automated to a different database for the two applications to use.
Kind regards,
Gift Peddie
March 27, 2009 at 7:44 pm
I feel better telling people who are just starting NEVER EVER. If I start equivocating, they'll use it inappropriately. Of course, if you really understand what it's doing, the way it works, what will happen, what won't happen, etc. It's a tool like any other. But like any other tool, it'll take off a limb before you realize you've screwed up.
Good point and I agree with the approach. You'd never give a loaded gun to someone who has never even held one before. But once they've been properly trained...
Viewing 10 posts - 16 through 24 (of 24 total)
You must be logged in to reply to this topic. Login to reply