How to get notifications on SQL Server performance issues

While Performance Monitor can be used for monitoring performance parameters of SQL Server, it doesn’t provide an option to get notifications when certain performance metrics values breach specified thresholds. The main reason why this is important is to be able to identify SQL Server performance issues as soon as possible, and react before they affect too many users. In addition, notifications on potential SQL Server performance issues could help avoid more serious problems by forecasting when they are likely to occur in order to avoid them. Therefore, one of the primary tasks for the database administrator is to establish a system that will ensure that he/she is going to be notified in case when certain performance metrics values are beyond the expected/desired range

September 8, 2016

Performance monitoring of AlwaysOn Availability Groups

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.

May 19, 2016

Troubleshooting SQL Server performance issues using wait statistics

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.

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

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

How to monitor your SQL Server instances and databases

Monitoring SQL Server instances and databases provide 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.

May 26, 2014

How to synchronize large SQL Server databases

When synchronizing large databases that contain millions of records, you will come across several challenges The first challenge is to just compare such databases. When the database tables contain millions of records, their comparison through the ApexSQL Data Diff graphical interface will be very slow and in such cases, it is strongly recommend using the ApexSQL Data Diff command line interface. As there’s no need to show millions of records visually in the tool’s grid, such large databases are processed faster.

April 29, 2013

Improve the performance of an ETL process

Due to the sheer volume of data usually involved in an Extract – Transform – Load (ETL) process, performance is positioned very high on the list of requirements which need to be met in order for the process to go as smoothly as possible. Here are some guidelines which will help you speed up your high volume ETL processes

April 23, 2013

Use SQL database backups to reduce the performance impact of heavy reporting

Depending on your particular environment, database reporting can have a heavy impact on the database performance, can execute queries which run for dozens of minutes or both. This is usually the case with reports which require complex queries having multiple calls to SQL Server’s aggregate functions to be executed against very large data sets. The effect on the database performance is particularly severe in scenarios where the production database stores data which is changed often – as data modification operations require exclusive locks, in order to preserve the integrity of the database, SQL Server will go ahead with the data modification operation until the query initiated by the report is still running. This increases the chances of a deadlock occurring; especially in cases where another set of data modification instructions, dependent on the ones which are waiting for the reporting to finish has already been applied. Therefore, reporting can cause performance degradation in a production environment. So, how can reporting be optimized to prevent such a heavy load on production databases?

April 4, 2013