Back to top

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]

 

Back to top

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.

 

Back to top

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.