Sql-server – Tuning advisor with Extended events

database-tuningperformanceperformance-tuningsql-server-2012

With SQL traces I was able to analyze them with Database Engine Tuning Advisor to obtain basic recommendations for perf. tuning(missing indexes, statistics,…).

Now, with SQL 2012 and Extended Events, how can I do something similar?

Thanks

Best Answer

Unfortunately you cannot do this with Extended events unless the data from XE can be converted into the format that DTA uses or can be stored in the tables generated by DTA.

Not recommended though but Trace is is still available, so can be used if you want.

You can even use DMV's for finding missing indexes, etc.

Also worth mentioning Aaron's post : Don't just blindly create those "missing" indexes!, since you are using DTA for analysis.

Related Solutions

SQL Server – Book Suggestions for Performance Tuning

I like Kalen Delaney's internals book:

Microsoft SQL Server 2008 Internals

I also like the Brent Ozar / Christian Bolton book:

Professional SQL Server 2008 Internals and Troubleshooting

For T-SQL tuning, you can't get much a better T-SQL wizard than Itzik Ben-Gan:

And query tuning overall, Grant Fritchey and Sajal Dam:

SQL Server 2008 Query Performance Tuning Distilled

There are also some interesting solutions and opinions presented in various chapters in the two MVP Deep Dives titles (from which all proceeds go to two charities, War Child and Operation Smile):

SQL Server MVP Deep Dives Volume 1 (War Child)
SQL Server MVP Deep Dives Volume 2 (Operation Smile)

And a couple of the Red Gate titles @Kev left out, but which are both excellent:

Inside the SQL Server Query Optimizer (Ben Nevarez)
Troubleshooting SQL Server: A Guide for the Accidental DBA (Jonathan Kehayias / Ted Kreuger)

SQL Server Indexing – Should I Rely on Database Engine Tuning Advisor?

It can certainly be a good place to start. I used to rely on it pretty heavily. Another good way to do it is to look at the execution plan for your query. Often I have found the suggestions that it provides are incredibly useful. Another tool to help with indexing is to run this

SELECT
mid.statement
  ,migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) AS improvement_measure,OBJECT_NAME(mid.Object_id),
  'CREATE INDEX [missing_index_' + CONVERT (varchar, mig.index_group_handle) + '_' + CONVERT (varchar, mid.index_handle)
  + '_' + LEFT (PARSENAME(mid.statement, 1), 32) + ']'
  + ' ON ' + mid.statement
  + ' (' + ISNULL (mid.equality_columns,'')
    + CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN ',' ELSE '' END
    + ISNULL (mid.inequality_columns, '')
  + ')'
  + ISNULL (' INCLUDE (' + mid.included_columns + ')', '') AS create_index_statement,
  migs.*, mid.database_id, mid.[object_id]
FROM sys.dm_db_missing_index_groups mig
INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
WHERE migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) > 10
ORDER BY migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC

This should give you an idea of some indexes you could be missing. Hope that helps.

Best Answer

Related Solutions

SQL Server – Book Suggestions for Performance Tuning

SQL Server Indexing – Should I Rely on Database Engine Tuning Advisor?

Related Question