The AlwaysOn Availability Group was introduced as a new feature in the SQL Server 2012 Enterprise edition and is designed to ensure a more advanced and reliable option for SQL Server high availability and disaster recovery.

To create a custom system for performance base lining we need the following:

• A metric to track
• A database and tables to store our collected data and processed information
• A means to collect performance data
• A means to process the performance data into meaningful information e.g. Mean, Standard deviation
• A line chart of the data
• A query to create a report of the measurements that exceeded the threshold, we set, for raising alerts

In the following series of articles, we will discuss what baselines are, how they work and how to apply them to everyday SQL Server performance monitoring. This article will provide a brief overview of baselines and the statistical calculations behind them. Later we’ll apply this to real information gathering techniques to allow DBAs to create their own baseline.

Even a superficial reading of books and articles on SQL Server performance monitoring will tell you that SQL Server memory pressure is the first thing you should monitor. Not the only thing, far from it, and some CPU, I/O and internal SQL Server counters are critically important to get the complete picture but it’s the memory counters that often show us the first indication of suboptimal performance

The existence of a deadlock may be indicated by application slowness, error messages or cases when a SELECT statement takes a long time to finish or terminates prematurely with the message:

“Transaction (Process ID xx) was deadlocked on resources with another process and has been chosen as the deadlock victim. Rerun the transaction”

All this indicates that SQL Server encountered a deadlock. A deadlock is a common issue with SQL Server. It is a circular blocking chain when two or more processes holding locks on data where each process wants to lock the data already locked by another process, and each process is waiting on the other process to complete before moving forward

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.