Query regression occurs when a query that previously performed well suddenly becomes slower, often due to a plan change. SQL Server’s Query Store makes it easy to identify these regressions by comparing recent performance against historical baselines.
SQL Server UNPIVOT Explained
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.
Finding the Most Resource-Intensive Queries in SQL Server
Identifying queries that consume the most resources can help you prioritize performance optimization efforts. In SQL Server you can use Query Store to track CPU time, duration, memory usage, and I/O for every query. This makes it easy to find the biggest resource consumers in your database.
What is a MERGE Statement in SQL?
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.
How to Enable Query Store in SQL Server
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.
How to Find Queries with Multiple Execution Plans in SQL Server
When enabled on a database, SQL Server’s Query Store tracks all execution plans that the optimizer generates for each query. When a query has multiple plans, it often indicates parameter sniffing issues, statistics changes, or index modifications that caused the optimizer to choose different execution strategies over time.
What is Parameter Sniffing in SQL Server?
Parameter sniffing is a feature in SQL Server where the query optimizer examines (or “sniffs”) the parameter values the first time a stored procedure or parameterized query executes. It uses these specific values to create an execution plan optimized for those particular parameters. The plan is then cached and reused for subsequent executions, even when different parameter values are passed in.
This behavior can be efficient if the initial parameters are representative of typical queries, but it can also cause performance problems if later calls use very different parameter values that make the cached plan inefficient.
How PIVOT Works in SQL Server
In SQL databases, a pivot operation transforms rows into columns, making it easier to summarize or compare data across categories. It’s commonly used to convert long, vertical datasets into a wider, more readable format. For example, turning a list of monthly sales records into a table where each month becomes its own column.
By applying aggregation functions like SUM(), COUNT(), or AVG() during the pivot, SQL can reorganize and summarize data for reporting or analysis.
In this article, we’ll take a look at SQL Server’s PIVOT operator, which is designed specifically for pivot operations.
What is the Query Store in SQL Server?
Query Store is SQL Server’s built-in query performance tracking system. It captures a history of queries, their execution plans, and runtime statistics, storing everything in the database itself. It constantly records what’s happening so you can analyze performance issues after the fact.
Query performance issues can be notoriously hard to debug. A query runs fine for weeks, then suddenly it’s slow, and by the time you check, the problem has vanished or the execution plan is no longer in cache. SQL Server 2016 introduced Query Store to address this. Once enabled on a database, it continuously records query execution history, giving you the data you need to investigate performance problems after they happen. It won’t tell you what’s wrong or how to fix it, but at least you’ll have evidence to work with instead of flying blind.
What is a Query Hint?
A query hint is a directive you add to your SQL statement that tells the database optimizer how to execute that query. You’re basically overriding the optimizer’s judgment with your own instructions.
Most of the time, your database’s query optimizer does a pretty solid job figuring out the best execution plan. It analyzes statistics, indexes, and table structures to determine the most efficient path. But sometimes you know better (or at least you think you do) and that’s where query hints can be useful.