How to programmatically monitor system availability

System availability overview

Overall stability and availability of a machine in a specific time range is usually called system uptime. This measurement represents a period (sometimes percentage) when the system is stable and performing without unattended reboots, except for maintenance and administrative purposes, and works without issues. The opposite, system downtime is a period when machine is turned off (on purpose), or encounters experiences problems that result is the system being unavailable to users and processes. The combination of these two measurements is called system availability which is both identified and tracked with Windows Event Viewer (System Log).

December 23, 2016

The process of elimination: How to analyze SQL Server performance with baselines and wait statistics

Wait statistics = Data

SQL Server’s built in abilities to track query execution via wait statistics is critical to resolving performance problems. Wait statistics or response time analysis are metrics that provide the ability to measure the time needed for the database to respond to executed queries. It doesn’t simply represent the time needed for a query to complete execution, but the wait statistic is also the measurement of the time the query has taken for each step in its execution. That information becomes the basis for identifying bottlenecks that are affecting the execution time.

December 20, 2016

Techniques to identify blocking queries and causes of SQL Server blocks

Blocked processes caused by locks on database objects are a common issue. Locking ensures the integrity of data stored within databases by forcing each executed SQL Server transaction to pass the ACID test. which considers that every transaction must meet the following requirements:

December 12, 2016

How to automate monitoring and alerting on SQL Server Agent status changes

SQL Server Agent overview

The main purpose of SQL Server Agent is executing administrative tasks within SQL Server, mostly as on-demand user actions. It is Windows service which runs continuously in the background, but it stores necessary information within SQL Server itself, in the msdb system database.

December 6, 2016

How to detect SQL Server performance issues using baselines – Part 3

Part 1 of this series describes some basics about performance baselining, while Part 2 explains how to establish and use baselining for detecting SQL Server performance issues in SQL Server using user defined tables, scripts and baseline calculations. In this final article, we’ll demonstrate how to implement baselining with a 3rd party tool, including some value added functionality

October 26, 2016

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

How to identify and troubleshoot slow-running queries in SQL Server

Probably the most common issue when maintaining SQL Servers are slow-running queries. It is not unusual that a DBA gets information that the application or user database is slow, or even that users are getting timeout messages when working with SQL Server or SQL Server applications. Generally, when such SQL Server performance related issues are encountered, the first step in troubleshooting such an issue is to quickly identify whether and what slow-running queries in SQL Server are the cause of such a problem. The next step is then to determine why these queries run slow and what is the root cause for such behavior.

September 8, 2016

Performance monitoring of AlwaysOn Availability Groups – Part 2

In Part 1 of this series using native SQL Server tools for monitoring AlwaysOn Availability Groups performance is described. In this part the focus will be on a third party solution – ApexSQL Monitor; designed to overcome most of the limitations of the aforementioned native SQL Server tools

August 9, 2016