Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Tool for finding the database bottlenecks in sql server

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.

like image 504
LaBracca Avatar asked Sep 02 '10 09:09

LaBracca


2 Answers

You don't exactly need a tool. I personally use the following query to show me what indexes SQL Server thinks it needs, this shows how often SQL Server looked for the index, estimated improvement 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).

like image 174
F.B. ten Kate Avatar answered Sep 21 '22 22:09

F.B. ten Kate


  • SQL Server Profiler
  • SQL Database Advisor
like image 38
leppie Avatar answered Sep 21 '22 22:09

leppie