Press "Enter" to skip to content

Category: Performance Tuning

Applying the Pareto Principle to Query Store

Published 2026-04-16 by Kevin Feasel

Erik Darling channels an Italian economist:

In this video, I dive into an old-school approach to identifying SQL Server performance issues using SP Quickie Store and a novel method inspired by the Pomodoro [“Pareto” – ed] technique. Traditionally, Query Store surfaces queries that consumed a lot of CPU over the last seven days, but often these results are too broad for practical use. To address this, I’ve developed a multi-dimensional scoring system that evaluates queries based on their impact across several key metrics: CPU usage, duration, physical reads, writes, and executions. This approach helps pinpoint the most problematic queries more accurately, even when they run outside of typical working hours or are unparameterized. By sharing these insights, I hope to provide a practical tool for SQL Server administrators looking to optimize their databases without relying solely on modern monitoring tools.

The AI generated summary reminds me that I’ve been working for 25 minutes, so time to take a break.

I like the idea of calculating and calculating & displaying impact scores, as well as breaking it down into core components.

Leave a Comment

Increasing CPU Capacity or Tuning Queries

Published 2026-04-14 by Kevin Feasel

John Deardurff explains how to make a choice:

Recently while discussing the Task Execution Model and Thread Scheduling, I was asked the following question, “When discussing worker threads, how can we determine whether we should increase CPU capacity or focus on query tuning? This is when our worker threads are under pressure and the instance is becoming exhausted?“

In my brain, I thought, that is a great question, and it’s exactly the right way to think about worker thread pressure vs. real CPU starvation, especially when worker threads are getting tight. Let’s write a post.

John has a nice discussion of the trade-offs and signals associated with each approach. One third approach I might add is caching in the application(s), if applicable. This is especially useful if a significant fraction of the queries access static or nearly-static data.

Leave a Comment

Tracking Resource Utilization in Performance Monitor

Published 2026-04-06 by Kevin Feasel

Erik Darling has another video tutorial:

In this video, I delve into the resource metrics tabs of the full dashboard from my free SQL Server performance monitoring tool. I explain how these tabs provide more detailed information compared to the lighter version, including CPU utilization, TempDB usage, memory usage, and a selection of perfmon counters. The goal is to help you quickly identify when your server might be experiencing issues by showing real-time data and historical trends that can pinpoint specific performance bottlenecks. I also highlight how the weight stats section helps in understanding wait statistics, making it easier to diagnose problems related to resource contention and query compilation.

Click through for the video.

Comments closed

Query Performance Tabs in Performance Monitor

Published 2026-04-03 by Kevin Feasel

Erik Darling has a new video:

In this video, I dive into the query performance tabs within my full dashboard, a free and open-source SQL Server monitoring tool. I highlight key features such as the QueryStore subtab, which provides detailed insights into query execution plans and regressions over time, helping you identify and address performance issues more effectively. Additionally, I explain how the custom trace patterns tab offers valuable data for safeguarding against overlooked performance anomalies, ensuring a comprehensive view of your SQL Server’s health.

Click through to see these capabilities.

Comments closed

Performance Monitor Full Installation Notes

Published 2026-04-02 by Kevin Feasel

Erik Darling has a pair of documentation videos for Performance Monitor. First up is the full dashboard installation:

In this video, I delve into the installation process of my free SQL Server performance monitoring tool, addressing a common issue where users have questions about setup despite having access to detailed documentation. I explain that there are two main installers: one for command line use and another with a graphical user interface (GUI). The command line installer offers various options such as reinstalling the database, choosing encryption levels, managing server certificates, and handling password input in different ways. Additionally, I cover how the tool runs through 52 installation scripts, including community dependencies like SP WhoIsActive and my own scripts for data collection and analysis.

After that, Erik shows off the user interface:

In this video, I delve into the exciting world of free SQL Server monitoring tools, specifically focusing on a dashboard I’ve developed called Performance Monitor. This tool is designed to make life easier for DBAs and developers by providing real-time insights into server performance, resource usage, blocking issues, and more. With a user-friendly interface that includes various tabs like an overview, resource metrics, and query analysis, the dashboard aims to simplify complex monitoring tasks. I also highlight some of its unique features such as the plan viewer, which leverages Microsoft’s SQL Server extension for Visual Studio Code to provide detailed execution plans with actionable insights. Whether you’re a seasoned DBA or just starting out, this tool can help you keep your SQL Server environment running smoothly and efficiently.

Click through to check out what Erik has to offer.

Comments closed

Reviewing the Performance Monitoring Lite Dashboard

Published 2026-03-31 by Kevin Feasel

Erik Darling has another tutorial video:

In this video, I delve into the exciting world of SQL Server monitoring tools, focusing on the Lite version of my free open-source tool, which is a lightweight and secure solution for monitoring performance across multiple servers. I walk through how to download and set up the Lite edition, highlighting its unique features such as no server-side installations or databases, making it an ideal choice for environments like Azure SQL Database where agent jobs are used. The video covers the tool’s 20 collectors that run with minimal permissions, ensuring data collection is both efficient and secure. I also showcase the various tabs within the dashboard, including weight [sic] stats, query trends, CPU and memory usage graphs, blocking information, and performance monitor counters, all designed to provide a comprehensive view of SQL Server health without any heavy lifting on the server side.

I think my preference is still for the Full version (especially given that the price tag on both versions is zero) in most situations, but the Lite version does cover some neat bits of functionality.

Comments closed

Tips for Performance Tuning SQL Server

Published 2026-03-27 by Kevin Feasel

Paul Randal provides a framework:

There are a huge number of best practices around SQL Server performance tuning – I could easily write a whole book on the topic, especially when you consider the number of different database settings, server settings, coding practices, wait types, and so on that can affect performance. For this post I decided to step back a bit from a list of specifics and list some general recommendations for how to approach performance tuning so that you maximize effort and minimize distractions.

Click through for several recommendations to make your performance tuning operations a bit smoother. None of these relate to specific performance problems. Rather, the post serves as some recommendations around how to approach performance tuning in general.

Comments closed

SQL Server Performance Monitor Videos

Published 2026-03-27 by Kevin Feasel

Erik Darling continues a series of videos for his SQL Server Performance Monitor. First up is a breakdown of the two editions (both of which are free):

In this video, I delve into my new, completely free, open-source SQL Server monitoring tool, discussing which edition—full or light—you might find most useful based on your specific needs. The full edition creates a database and agent jobs to continuously collect data, offering complete control over the collection schedule and allowing for easy customization of stored procedures. It’s ideal for scenarios where you need constant data collection and want the flexibility to manage the monitoring tool as needed. On the other hand, the light edition uses an embedded DuckDB version inside itself, collecting data only when it’s open, making it perfect for quick triage or situations with limited server access, ensuring that no data is missed while you’re away from your computer. Both editions offer alerts and notifications for critical issues like blocking and high CPU usage, providing a seamless experience regardless of which edition you choose.

Erik also has a video covering how to communicate with the built-in MCP server:

In this video, I delve into the built-in MCP server feature of my FreeSQL server monitoring tool, Darling Data. This feature allows users to interact with their collected monitoring data in a conversational manner using language models like Claude or other compatible tools. The MCP server is designed for local use only and does not expose any data beyond what’s available within the performance monitor database, ensuring that no sensitive information is at risk. By enabling this feature, you can ask questions about your server’s performance directly to an LLM, receiving quick insights into issues such as deadlocks, query performance, and workload spikes. This approach simplifies the process of diagnosing problems without requiring extensive manual analysis or script writing.

Comments closed

Background on a Performance Monitoring Tool

Published 2026-03-25 by Kevin Feasel

Erik Darling has a new video:

In this video, I introduce my new free performance monitoring tool for SQL Server, which was developed after a bit of boredom during the holidays and a desire to create something simple yet powerful. The main issue I’ve noticed is that many existing tools are either overly complex or too expensive, making them less accessible to smaller teams or individuals. My goal with this tool was to provide a straightforward solution that doesn’t require setting up VMs, dealing with firewalls, or managing domain accounts—essentially streamlining the process for anyone looking to monitor SQL Server performance without the hassle of a full-blown enterprise setup. The tool supports various editions of SQL Server and even Azure SQL DB, offering both a full version with database logging and an easier-to-use light version that’s self-contained within DuckDB. I also highlight how it integrates machine learning models for those who prefer not to dive deep into dashboards, providing quick answers through natural language queries.

It’s good to have yet another example of the productive powers of boredom.

Also, I need to get back into this tool now that the Linux bug has been fixed.

Comments closed

Scan Types in PostgreSQL

Published 2026-03-06 by Kevin Feasel

Warda Bibi lays out four classes of scan in PostgreSQL:

To understand how PostgreSQL scans data, we first need to understand how PostgreSQL stores it.

  • A table is stored as a collection of 8KB pages (by default) on disk.
  • Each page has a header, an array of item pointers (also called line pointers), and the actual tuple data growing from the bottom up.
  • Each tuple has its own header containing visibility info: xmin, xmax, cmin/cmax, and infomask bits.

There are different ways PostgreSQL can read data from disk. Depending on the query and available indexes, it can choose from several scan strategies:

  1. Sequential Scan 
  2. Index Scan
  3. Index-Only Scan
  4. Bitmap Index Scan

Read on for a description of those types, as well as when it makes sense for the database engine to select a particular scan type.

Comments closed