Tag: wait-stats

What Is the CPU Wait in Datadog SQL Server Monitoring? How Does It Compare to Waiting on CPU?

By Kendra Little on November 21, 2024

Category: monitoring-troubleshooting , sql-server

I use Datadog on a regular basis, and I’m a pretty huge fan. The monitoring pack for SQL Server (and its PAAS variants) is still pretty rudimentary, but it evolves regularly. That’s NOT what I’m a fan of, though.

What makes me a raving fan is the flexibility of Datadog’s notebooks and dashboards, combined with the ability to create all sorts of custom metrics and monitors. There are always things in SQL Server monitoring packs that I have strong opinions about. Datadog lets me take what I want, build what I need that isn’t contained in that, and ignore the rest. For a team that has the budget to afford Datadog paired with dedicated database staff with the time and resources to do this work, this can be a great fit.

Query Store Size Based Cleanup Causes Performance Problems - How to Avoid It

By Kendra Little on April 23, 2024

Category: query-performance , sql-server , azure-sql

Tags: query-store , availability-groups , azure-sql-managed-instance , execution-plan , wait-stats , sized-based-cleanup

I’m a huge fan of SQL Server’s Query Store feature. Query Store collects query execution plans and aggregate query performance metrics, including wait stats. Having Query Store enabled makes troubleshooting performance issues such as bad parameter sniffing, much, much easier. Because Query Store is integrated into SQL Server itself, it also can catch query plans in a lightweight way that an external monitoring system will often miss.

When performance matters, it’s important to ensure that you’re managing Query Store so that Query Store cleanup does not run during high volume times. Query Store cleanup could slow your workload down significantly.

PAGELATCH, PAGEIOLATCH, and LATCH Waits in SQL Server

By Kendra Little on September 12, 2023

Category: monitoring-troubleshooting , sql-server

Tags: wait-stats , pagelatch , pageiolatch , latch , sys-dm-os-wait-stats , performance-tuning , blocking

I’ve long found it tricky to remember and explain the differences between three similar-sounding waits in SQL Server that all have “LATCH” in the name: PAGELATCH, LATCH, and PAGEIOLATCH waits.

Here’s an illustration that explains these waits, along with wait subtypes.

A Case of Inconsistent Wait Stats and Query Duration

By Kendra Little on April 11, 2018

Category: execution-plans , query-tuning

Tags: wait-stats , query-duration , performance-analysis

You know how they say “don’t sweat the small stuff”?

That’s good advice for lots of things, but when it comes to query tuning - I WANT TO KNOW MORE when something is inconsistent. I just can’t let it go. I go total X-Files: The Truth Is Out There.

Collect and Baseline Wait Statistics (Dear SQL DBA Episode 14)

By Kendra Little on August 25, 2016

Category: monitoring-troubleshooting

Tags: dear-sql-dba , free-tools , wait-stats , sp-blitzfirst

What are the best tools to collect and baseline wait statistics? Should you write your own? Watch the 18 minute video or read the episode transcript below.

Why an Upgrade can Cause Performance Regressions (Dear SQL DBA Episode 9)

on July 21, 2016

Category: query-performance , database-administration

Tags: cpu-usage , database-administration , dear-sql-dba , migrations , performance-tuning , troubleshooting , upgrades , wait-stats

You finally got approval to move to new hardware and a fresher version of SQL Server. After months of work, you do the migration and then… performance gets worse. What can cause this, and what do you look for?