May 28, 2008 at 4:42 am
i want to run the blocked process report as part of a background sever trace, so that when users report blocking, i can confirm that there was blocking and how long it was for ( just to see if the problem is actually blocking and not network issues/user issues).
how do i find out how much resources a backend server trace uses? is it much lighter than running profiler? will it have much impact at all?
May 28, 2008 at 4:45 am
Much lighter than profiler. Make sure that the trace is writing to a fast drive that does not contain your database's data or log files or tempdb.
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
May 28, 2008 at 6:20 am
anyway i can work out cold hard numbers or have more concrete evidence of how easy server traces are on resources? i need to be able to justify it to the team before it can be implemented into production.
May 28, 2008 at 6:28 am
Take a test server, take baseline readings of the CPU usage, IO usage, etc. Implement some traces, take new readings.
If you're running SQL 2005, you already have a serverside trace running constantly, the default trace.
Providing you're not tracing very frequent events (lock aquired, stmt_completed, etc) you shouldn't notice much change.
If it makes the team feel any better, profiler uses the same trace procedures that the server-side trace does, but then forces the event data to be sent over a network and to wait for the client machine to display them, whereas the serverside trace just writes the data to file
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
May 28, 2008 at 6:35 am
great. thanks for all your help Gail.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply