Graphing SQL Server wait stats on Prometheus and Grafana

Mis en avant

Wait stats are essential performance metrics for diagnosing SQL Server Performance problems. Related metrics can be monitored from different DMVs including sys.dm_os_wait_stats and sys.dm_db_wait_stats (Azure).

As you probably know, there are 2 categories of DMVs in SQL Server: Point in time versus cumulative and DMVs mentioned previously are in the second category. It means data in these DMVs are accumulative and incremented every time wait events occur. Values reset only when SQL Server restarts or when you intentionally run DBCC SQLPERF command. Baselining these metric values require taking snapshots to compare day-to-day activity or maybe simply trends for a given timeline. Paul Randal kindly provided a TSQL script for trend analysis in a specified time range in this blog post. The interesting part of this script is the focus of most relevant wait types and corresponding statistics. This is basically the kind of scripts I used for many years when I performed SQL Server audits at customer shops but today working as database administrator for a company, I can rely on our observability stack that includes Telegraf / Prometheus and Grafana to do the job.

Lire la suite

Creating dynamic Grafana dashboard for SQL Server

Mis en avant

A couple of months ago I wrote about “Why we moved SQL Server monitoring to Prometheus and Grafana”. I talked about the creation of two dashboards. The first one is blackbox monitoring-oriented and aims to spot in (near) real-time resource pressure / saturation issues with self-explained gauges, numbers and colors indicating healthy (green) or unhealthy resources (orange / red). We also include availability group synchronization health metric in the dashboard. We will focus on it in this write-up.

Lire la suite

Extending SQL Server monitoring with Raspberry PI and Lametric

Mis en avant

First blog of this new year 2021 and I will start with a fancy and How-To Geek topic

In my last blog post, I discussed about monitoring and how it should help to address quickly a situation that is going degrading. Alerts are probably the first way to raise your attention and, in my case, they are often in the form of emails in a dedicated folder. That remains a good thing, at least if you’re not focusing too long in other daily tasks or projects. In work office, I know I would probably better focus on new alerts but as I said previously, telework changed definitely the game.

Lire la suite

Why we moved SQL Server monitoring on Prometheus and Grafana

Mis en avant

During this year, I spent a part of my job on understanding the processes and concepts around monitoring in my company. The DevOps mindset mainly drove the idea to move our SQL Server monitoring to the existing Prometheus and Grafana infrastructure. Obviously, there were some technical decisions behind the scene, but the most important part of this write-up is dedicated to explaining other and likely most important reasons of this decision.

Lire la suite

Monitoring Azure SQL Databases with Azure Monitor and Automation

Mis en avant

Supervising Cloud Infrastructure is an important aspect of Cloud administration and Azure SQL Databases are no exception. This is something we are continuously improving at my company.

On-prem, DBAs often rely on well-established products but with Cloud-based architectures, often implemented through DevOps projects and developers, monitoring should be been redefined and include some new topics as:

Lire la suite

dbachecks and AlwaysOn availability group checks

Mis en avant

When I started my DBA position in my new company, I was looking for a tool that was able to check periodically the SQL Server database environments for several reasons. First, as DBA one of my main concern is about maintaining and keeping the different mssql environments well-configured against an initial standard. It is also worth noting I’m not the only person to interact with databases and anyone in my team, which is member of sysadmin server role as well, is able to change any server-level configuration settings at any moment. In this case, chances are that having environments shifting from our initial standard over the time and my team and I need to keep confident by checking periodically the current mssql environment configurations, be alerting if configuration drifts exist and obviously fix it as faster as possible.

Lire la suite

SQL DB Azure, performance scaling thoughts

Mis en avant

Let’s continue with Azure stories and performance scaling …

155 - 0 - banner

A couple of weeks ago, we studied opportunities to replace existing clustered indexes (CI) with columnstore indexes (CCI) for some facts. To cut the story short and to focus on the right topic of this write-up, we prepared a creation script for specific CCIs based on the Niko’s technique variation (no MAXDOP = 1 meaning we enable parallelism) in order to get a better segment alignment.

Lire la suite

SQL Server sur Linux: Introduction à DBFS

Mis en avant

Il y a quelques mois, Microsoft annonçait 2 outils en lignes de commandes supplémentaires pour SQL Server avec notamment mssql-scripter et DBFS. Le dernier a tout particulièrement attiré mon attention car il expose les données en temps réels depuis les fameuses DMVs sur un pseudo système de fichiers virtuel à la façon Linux procfs.

> Lire la suite (en anglais)

David Barbarin
MVP & MCM SQL Server

SQL Saturdays 2013 à Paris : les slides

Mis en avant

Comme promis voici les slides de ma session sur les événements étendus. Malheureusement je n’ai pas pu effectuer toutes les démonstrations que je voulais .. eh oui il faut bien commencer par expliquer comment fonctionne les événements étendus et cela m’a pris un peu plus de temps que prévu :-) . Mais ce n’est que partie remise, je garde cette partie pour une session uniquement orientée démonstration. J’espère pouvoir vous la présenter rapidement.

Merci aux personnes qui ont voulu assister à ma présentation et de assister de manière générale à ce premier SQL Saturdays  Paris. Ce fut un bon moment d’échange et de rencontres !

David BARBARIN (Mikedavem)
MVP SQL Server