Free Tools to Monitor SQL Server Performance

I have already mentioned in my previous post, that SQL Server performance tuning is a broad topic and cannot be completed in one blog post. My previous post is all about performance data capture and analysis. In this post, I will talk about the various tools that is available for most of the SQL Server versions and releases.

Here is my list of SQL Server tools which are free to use and can help you to manage and monitor SQL Server performance.

Performance Monitor
SQL Server Profiler
DMVs
Performance Dashboard 2012
Server Dashboard
Activity Monitor
Extended events
Database Tuning Advisor
SQL Diag
SQL Nexus
ReadTrace

Tools for measuring CPU Bottleneck:

Performance Monitor
SQL Server Profiler
DMVs
Extended Events
Data collector and the management data warehouse (MDW)

What to do?

Identify the queries taking High CPU using above tools

Check Stats info. Check Missing Index info

Check for DTA recommendations

Manually tune the query.

Memory Bottleneck:

General system and SQL Server state and memory-specific DMVs.
The DBCC MEMORYSTATUS command.
SQL Server ring buffers.
Performance counters.
The SQL Server error log, and Windows application and system logs.

Free Tools to Monitor SQL Server Performance

Windows Performance Monitor

Performance monitor is used to uncover resource limits in the following:

Disk IO
Processor
Memory
Network IO

SQL Internal Tools

SQL Performance Dashboard Reports. (Available till SQL 2012)
SQL Profiler (Available in all versions)
SQLDIAG.EXE
Dynamic Management Views (DMV)

Other Tools:

Activity Monitor
MDW/UCP (Deprecated in SQL Server 2016)

Disclaimer: The Questions and Answers provided on https://www.gigxp.com are for general information purposes only. We make no representations or warranties of any kind, express or implied, about the completeness, accuracy, reliability, suitability or availability with respect to the website or the information, products, services, or related graphics contained on the website for any purpose.