SQL Server monitoring includes isolating processes that cause performance issues and fixing them. Commonly monitored performance metrics, such as page reads and writes per second, processor utilization, network usage, memory usage, etc. show the SQL Server status. For further analysis, it’s necessary to monitor what is happening on SQL Server and causes these metric values. This is where query monitoring comes into play.

In the previous part of this series, we showed how to use SQL Server profiler to create a SQL trace that provides enough information to find expensive SQL queries. In this part, we will show how to create a SQL trace without SQL Profiler and how to analyze the information captured.

In the previous parts of this series, we described how to find the most expensive recent queries using SQL Server Management Studio native features and dynamic management views and functions. In this part, we will show how to do that using SQL Server Profiler.

In the previous part of this series, we described why tracking most expensive queries is important and how to use SQL Server Management Studio native solutions. In this part, we will focus on dynamic management views and functions.

Monitoring and diagnosing SQL Server performance requires monitoring performance metric values, but also understanding these metrics and their relation to other metrics, knowing metric normal values, monitoring resource intensive processes and queries, etc.

Efficient SQL Server performance monitoring includes monitoring of operating system, SQL Server, and database performance. In How to monitor your SQL Server instances and databases, we presented tools for monitoring the latter two performance metrics, In this article, we will present tools that provide operating system performance monitoring.

When it comes to monitoring SQL Server performance, there are a few native SQL Server solutions that provide out of the box performance monitoring. We have written about some of them here: A DBA guide to SQL Server performance troubleshooting – Part 2 – Monitoring utilities.

Monitoring SQL Server instances and databases provides information necessary to diagnose and troubleshoot SQL Server performance issues, as well as to fine tune SQL Server. Optimal performance is not easy to define and set, as there is usually a trade-off between multiple software and hardware factors. It also depends on your environment, business requirements, and company policy.