SQLTRACE_BUFFER_FLUSH, Performance Metrics on SQL Server 2005
10 December 2007SQL Server 2005 gathers metrics data for you automatically out of the box! I discovered this bit of information today, as I was looking into why my DotNetNuke database was starting to drag down. (remember my previous post about How to reset a DNN password at the Database)
I started by looking at some of the hidden metrics data that SQL Server 2005 collects out of the box for you. The first thing to look at was to run a query about the causes of the wait times, to do this execute the following query.
select top 10
wait_type "Wait Type",
wait_time_ms / 1000 "Wait time (s)",
Convert(Decimal(12,2), wait_time_ms * 100.0
/ Sum(wait_time_ms) over ()) "% Waiting"
from sys.dm_os_wait_stats
where wait_type not like ‘%SLEEP%’
order by wait_time_ms desc
This query did not tell me a very much, except that SQLTRACE_BUFFER_FLUSH was at the top of the scale with 99.92% of the wait time.
So I moved on to a more complex query to show a list of missing indexes. This is an interesting query, and it proved to be the one that fixed the slowness problems on my DotNetNuke database.
Here is the query I used to show the cost of missing Indexes
select top 10
round(avg_total_user_cost * avg_user_impact
* (user_seeks + user_scans), 0) "Total Cost",
avg_user_impact, statement "Table Name",
equality_columns "Equality Columns",
inequality_columns "Inequality Columns",
included_columns "Include Columns"
from sys.dm_db_missing_index_groups g
join sys.dm_db_missing_index_group_stats s
on s.group_handle = g.index_group_handle
join sys.dm_db_missing_index_details d
on d.index_handle = g.index_handle
order by "Total Cost" desc;
Read this for an excellent book on SQL Server 2005 Performance Tuning For more information on Performance Tuning Another great article on the impact of SQLTRACE_BUFFER_FLUSH is found Here
No comments yet
Leave a Reply
You must be logged in to post a comment.