MCTS 70 – 433 gathering performance information

gathering performance information

capture execution plans

When reviewing graphical execution plans watch for thick arrows; these represent a large number of rows moving between operations.

Hash operations which handle JOINs or GROUP BY clauses basically mean there is no index to optimise the query. If the plan has SORT operations which account for a high percentage of the plan then this could imply a index probably needed.

Table or clustered index scans indicates that no appropiate index exists to optimise the query (but in some cases it is more efficient to scan the index); ideally you should be aiming for index seeks. It could well be worth creating a index which covers the query i.e. a covered index

ON [Schema].[TableName] (Column) INCLUDE (column1, column2, etc.)

The nonclustered indexes key columns should be whatever is defined in the WHERE clause and the included columns should match the SELECT; remember anything defined in the clustered index key is automatically included in a nonclustered index.

If  you’re seeing index scans on a clustered index and the clustered index contains multiple columns, then you may not be querying the leading key column. In this instance a nonclustered index may improve performance.

SET SHOWPLAN_[XML|TEXT|ALL] – though TEXT and ALL are deprecated in SQL Server 2008. 

gather trace information by using SQL profiler

SQL Server profiler can be started by typing profiler.exe from the run box. The events to use for query tuning are SQL:BatchCompleted and RPC:Completed; the columns of interest are:

  • duration – the no. of milliseconds
  • reads – no. of 8KB pages read
  • writes – no. of 8KB pages written
  • CPU – total CPU time
  • textdata – this is what is being executed

To run profiler from a script configure the profiler event data and columns then select File > Export > Script Trace Definition > For SQL Server 2005 – 2008 R2…

Double click the .sql file, modify the output path ‘{path}\{file}.trc’ and click execute.

collect output from the database engine tuning advisor

The database tuning advisor (DTA) will advise the end user whether to create additional indexes or maybe even drop an index.

You can generate workloads for DTA by right clicking in the query window of the query you want to analyse, generate a script which contains the queries or using SQL profiler trace output (tuning table); the tuning table should be generated by using SQL profiler with the tuning template selected.

When the analysis is finished you can either view and apply the recommendations or save them for review.

collect information from system metadata

Dynamic management views (DMV) return server state information that can be used to monitor instance health, diagnose problems and tune performance. DMV for diagnosing and tuning indexes are:

sys.dm_db_index_usage_stats – index-usage information

sys.dm_db_index_physical_stats – index statistics

sys.dm_db_missing_index_details, sys.dm_db_missing_index_groups and sys.dm_db_missing_index_group_stats – missing index details


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s