Geek Answers Handbook

Tool for finding bottlenecks in sql server database

I need a tool that monitors the sql server while I am testing my application. Testing, of course, will run different requests.

From the tool, I would like to get the conclusion: - This query was slow, because there is no index in this field.

and this kind of information.

But I would like to be able to highlight important data, maybe, for example, 100 indexes, but 99 of them will increase productivity by 0.05%, while one of them will give 6000% of the benefits of the query. Therefore, it is important to emphasize the presence of hot spots.

A commercial product is also needed (with a full range of functions - without restrictions).

+6
profiling sql-server
source share
3 answers

You do not need a tool. I personally use the following query to show me which SQL Server indexes are considered necessary, this shows how often SQL Server looked for the index, evaluated the 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, then SQL Profiler is your friend, just set the filter to duration and you will set. Then run this query through the Database Engine Tuning Advisor (both come with SQL Server).

+11
source share
  • SQL Server Profiler
  • SQL Database Advisor
+2
source share

Take a look at SQL Copilot. There is a free version, and the full version is cheap (around $ 50).
Please note that although I use it and found it useful to raise some issues and as a learning tool, I am still looking for other tools.
There is much to learn about this complex subject (un) successfully.

0
source share

More articles:


All Articles