How to detect whether index fragmentation affects SQL Server performance 

Background

After initial index creation in a SQL Server database, everything is properly ordered, which means that the logical index page order perfectly matches the physical index page order within the datafile. This is the ideal scenario and it allows for maximum query performance. If the table contains data that never changes, the index will remain perfectly ordered.

March 27, 2017

How to detect a high level of SQL Server database index fragmentation and fix it automatically

Introduction

When working with databases, availability and performance are of paramount importance. Performance of a database is based on multiple factors, key among them is being able to process queries quickly. That can be facilitated using indexes. Indexes are like the table of contents in a book, providing shortcuts to where the information is located. Otherwise, you would have to start reading the book from start until you find the information neededIt’s the same with databases, if it weren’t for indexes, for every query there would need to be an entire table scan.

February 8, 2017

How to monitor SQL Server failover events automatically

Failover event overview

In general, the term “failover” refers to switching from a previously active machine, other hardware component, or network to a passive (or unused) one, to sustain high availability and reliability. In most cases, a failover event is an automatic process, while a the similar event, switchover, requires manual intervention in switching between the active/passive elements.

January 13, 2017

How to programmatically monitor system availability

System availability overview

Overall stability and availability of a particular 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