Troubleshooting SQL Server performance issues using wait statistics – Part 2

In the previous article we described native SQL Server capabilities for troubleshooting SQL Server performance issues using wait statistics and especially the query wait statistic.

In this part, we’ll focus on a 3rd party solution – ApexSQL Monitor. ApexSQL Monitor is a SQL Server and system performance monitoring tool designed for monitoring of operating system, SQL Server, and database performance metrics in real time including wait statistics as well as query wait statistic on cumulative and individual query level. Besides being able to track wait statistics, ApexSQL Monitor allows a database administrator to configure and receive alert notifications about wait statistics as well.

May 21, 2015

Troubleshooting SQL Server performance issues using wait statistics – Part 1

The ability to successfully solve a problem and pinpoint the root cause of an issue that affects SQL Server performance depends on knowledge of the particular SQL Server system and environment, but also on personal experience which can help in determining where to start SQL Server performance troubleshooting.

May 20, 2015

How to detect SQL Server performance issues using baselines – Part 2 – Collecting metrics and reporting

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
May 8, 2015

How to detect SQL Server performance issues using baselines – Part 1 – Introduction

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 statistics and we’ll also show how to baseline performance “out of the box” with a 3rd party tool

May 4, 2015

Top SQL Server Memory Pressure Counters

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

March 3, 2015

SQL Server deadlock notifications – Part 2

In the previous part of this article, we’ve described a way of notifying a database administrator when a SQL Server deadlock occurs by using SQL Server Management Studio and SQL Server Agent. As it can be seen, setting the SQL Deadlock alerting in such way requires an additional level of knowledge as well as multiple actions performed. Even after the alerting is set, a database administrator will be required to perform additional actions in order to identify the deadlock and the victim.

In this part, we will present a solution that can alert on a deadlock and provides deadlock details in a user-friendly GUI with just a few mouse clicks

March 2, 2015

SQL Server deadlock notifications – Part 1

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

March 2, 2015

Monitor SQL Server queries – find poor performers – the out-of-the-box solution

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.

October 14, 2014