SQL: Index Tuning Tools on-prem and In the Azure
SQL Server Tools
Database Engine Tuning Advisor
This tool embedded in SSMS. The main job is to analyze SQL queries and recommend indexes, statistics, etc. This tool provides two user interfaces, a GUI (Graphical User Interface) and a command prompt utility, called dta. For more details see here.
SQL Server Profiler
A very useful profiling tool which stores SQL queries in various ways ( Trace File, Template, Table, XML File, etc.) [Deprecated Feature] This is a user-friendly GUI tool which provides trace analysis results, this tool can help find and diagnose slow-running queries, audit actions etc. For more detail see here.
Activity Monitor
A tool that displays information about SQL Server Processes, Resource Waits, I/O, Expensive Queries. This tool gives the ability to dba's by open panes to monitor a SQL Server system. The panes are expandable and allow to see more details about SQL Server workloads. For more details see here.
SSMS
Provides abilities like Estimated Execution Plan, Live Execution Plan, Live Query Statistics. SQL Server Management Studio is an IDE for SQL Server service management. Provides a lot of tools for some of which are mentioned above. And also allow interacting with SQL Server engine via T-SQL. More details and Tool Download from here.
DMV (Dynamic Views)
These views and functions are able to help with health monitoring, diagnose and tune queries. There are two types of Dynamic Management Views, server-scoped and database-scoped. Both of them required different permission on the server. For the server-scoped, requires VIEW SERVER STATE and for the database-scoped requires VIEW DATABASE STATE permission. [Details about SQL Server Permissions see, here For System Dynamic Management Views, please check in this Microsoft document.
An example of DMV query
SELECT TOP (10)
[session_id],
[wait_type],
[waiting_tasks_count],
[wait_time_ms],
[max_wait_time_ms],
[signal_wait_time_ms]
FROM [sys].[dm_exec_session_wait_stats]
SQL Azure Tools
Microsoft Azure offers databases, and intelligent insights for table indexes. Based on these results it provides recommendations to improve better performance. This can be accomplished using: Automatic index management, Automatic plan correction, Adaptive query processing. For more details please check here
Automating tuning
By set ON the state of automating tuning options, is like we turn on the auto-pilot on an airplane. It sounds a little strangebut it's true!
Performance Recommendations
This feature provides tuning recommendations for the database/s to the end-user.
Query Performance Insight
This dashboard displays the TOP 5 queries ordered by CPU, Data IO, Log IO. And get the Query details.
Conclusion
Microsoft continues to be next to IT/DBAs and helps them by providing valuable tools that aim to immediately implement their projects. In this post, I tried to refer to the most popular SQL Tools, for performance improvement.