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.
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)