Microsoft SQL Server 2008 Performance Tuning Best Practices

How to read a SQL Profiler trace file?
Execute the following script in Query Editor to list the SQL Server Profiler trace file.

use AdventureWorks2008;

SELECT *

FROM :: fn_trace_gettable(‘F:\data\PerfLogs\ProfilerTrace\SlowQuery.trc’, 1)

ORDER BY StartTime

——————————————————————————————

How to find missing indexes?

Execute the following script in Query Editor to a list of missing indexes with seek statistics. To design a new index with CREATE INDEX statement : equality columns should be put before the (different) inequality columns, and together they should make the key of the index. Included columns should be added using the INCLUDE clause. Columns with low seek counts should be ignored.

use AdventureWorks2008;

select object_name(mid.object_id) as TableName,

migs.user_seeks as Seeks,

mid.equality_columns as Equality,

mid.inequality_columns as Inequality,

mid.included_columns as Included,

mid.statement as [Statement]

from sys.dm_db_missing_index_details mid

join sys.dm_db_missing_index_groups mig

on mid.index_handle=mig.index_handle

join sys.dm_db_missing_index_group_stats migs

on migs.group_handle=mig.index_group_handle

where database_id=db_id()

order by TableName

————————————————————————————-