Ian

What is a Query Plan Cache?

by

A query plan cache is an area of a database management system‘s memory that stores compiled execution plans for queries. When you execute a query, the database’s optimizer analyzes the query and creates an execution plan, which is basically a set of instructions for how to retrieve and process the requested data.

But compiling this plan requires computational resources, so database systems cache it in memory for reuse rather than recompiling the same plan repeatedly.

This caching mechanism is a fundamental performance optimization found in virtually all modern relational database systems. By reusing compiled plans, databases avoid the overhead of repeatedly analyzing the same queries, resulting in faster query execution and reduced CPU consumption.

Read more

Using Window Functions with DATEDIFF() to Calculate Moving Averages of Durations in SQL Server

by

SQL Server’s window functions allow you to perform calculations across sets of rows that are related to the current row, without collapsing those rows into a single result like traditional GROUP BY aggregates would. When combined with the DATEDIFF() function, they can open up many options for analyzing temporal patterns in your data.

Moving averages smooth out short-term fluctuations to reveal longer-term trends in your data. Unlike a simple overall average that treats all historical data equally, a moving average focuses on a sliding window of recent events. This can be quite relevant when analyzing process durations, response times, or any time-based metric where you want to understand current performance trends without being overly influenced by distant historical data.

Read more

What is a Savepoint in SQL?

by

When working with databases, there’s a good chance you’ve had to deal with transactions. Transactions are those “all or nothing” blocks of work that make sure your data stays consistent. But what happens if you’re halfway through a transaction and realize that only part of it needs to be undone, not the whole thing? That’s where savepoints can help.

In SQL, a savepoint is basically a checkpoint you can set inside a transaction. It lets you roll back to that specific point if something goes wrong, without undoing everything that came before it. If something gets messed up, you can load your last save instead of starting again from scratch.

Read more

SQL Server UNPIVOT Explained

by

Sometimes you need to do the reverse of pivoting – take data that’s spread across multiple columns and convert it back into rows. You might receive data in a wide format from Excel, need to normalize denormalized data for storage, or simply need to reshape data for a different type of analysis. Fortunately, SQL Server has the UNPIVOT operator which is designed for this very scenario.

Whereas PIVOT transforms rows into columns, UNPIVOT transforms column headers back into row values. This creates a narrower, longer dataset from a wide one.

Read more

What is a MERGE Statement in SQL?

by

The MERGE statement is SQL’s convenient tool for synchronizing data between two tables. It lets you perform INSERT, UPDATE, and DELETE operations in a single statement based on whether matching records exist. Instead of writing separate logic to check if a record exists and then deciding what to do with it, MERGE handles all of that in one go.

Most major database systems support MERGE, including SQL Server, Oracle, and DB2. PostgreSQL added native MERGE support in version 15, but if you’re on an older version, you can use INSERT … ON CONFLICT as an alternative. MySQL doesn’t have MERGE but offers INSERT … ON DUPLICATE KEY UPDATE for similar functionality.

Read more

How to Enable Query Store in SQL Server

by

Query Store is SQL Server’s built-in query performance tracking system that captures execution history, plans, and runtime statistics. From SQL Server 2022 it’s enabled by default for all newly created databases. But in earlier versions, it’s disabled by default, which means you’ll need to explicitly enable it on each database where you want to track query performance.

Read more