Tool for finding the database bottlenecks in sql server

Question

I would like a tool that monitors sql server while I test my application. THe applicatino will of course run different queries.

From the tool I would like an output like: - this query was slow because index is missing on this field

and this kind of info.

But I'd like to have a way to highlight crucial data, there can be for example 100 index missing, but 99 of them would improve the performance of 0.05%, while one of those would give a 6000% benefit on a query. So Highlighting hot spots is important too.

Also a commercial product (with full features - no limited - trial) is desired.

Answer 1

• SQL Server Profiler
• SQL Database Advisor

Answer 2

You don't exactly need a tool.. I personally use the following query to show my what indexes SQL Server thinks it needs, this shows how often SQL Server looked for the index, estimated improvement etc etc...

SELECT user_seeks * avg_total_user_cost * (avg_user_impact * 0.01) AS index_advantage, migs.last_user_seek, mid.statement as 'Database.Schema.Table',
    mid.equality_columns, mid.inequality_columns, mid.included_columns,
    migs.unique_compiles, migs.user_seeks, migs.avg_total_user_cost, migs.avg_user_impact
    FROM sys.dm_db_missing_index_group_stats AS migs WITH (NOLOCK)
    INNER JOIN sys.dm_db_missing_index_groups AS mig WITH (NOLOCK)
    ON migs.group_handle = mig.index_group_handle
    INNER JOIN sys.dm_db_missing_index_details AS mid WITH (NOLOCK)
    ON mig.index_handle = mid.index_handle
    ORDER BY index_advantage DESC;

If you want to identify slow queries SQL Profiler is your friend, just set a filter on duration and you're set. Then run this query through the Database Engine Tuning Advisor (both come with SQL Server)