April 19, 2009 at 11:07 pm
I am trying to optimize a query to make it execute faster. I am going by the formula "Response time = service time + wait time" (refer http://www.simple-talk.com/sql/performance/sql-server-wait-events-taking-the-guesswork-out-of-performance-profiling/)
Now I am seeing that only 2 types of wait types are constituting the 90% of the waits (SQLTRACE_BUFFER_FLUSH=48% and OLEDB=42%).
When I see the IO stalls, I see that the tempdb has got very large IO stalls (io_stall_write_ms = 25509517). The database server has been restarted just 4 days back.
Dont you think that there needs to be some kind of wait states corresponding to this large tempdb IO such as IO_COMPLETION
or PAGEIOLATCH_SH etc..
Why is there no wait states showing up related to the IO. Or is the SQLTRACE_BUFFER_FLUSH itself some kind of an IO wait state.
If the trace is stopped will the performance increase by 48%?
April 20, 2009 at 1:06 am
The trace wait is from running profiler. Don't run profiler (GUI) on a busy production system. Use the server-side traces instead.
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 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply