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.
mssql
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.
Using DATEDIFF() with Window Aggregate Functions to Calculate Time from Event Baselines in SQL Server
When you combine SQL Server’s aggregate functions like MIN() and MAX() with the OVER clause, you can use them as window functions that calculate across partitions while still maintaining individual row data. When combined with DATEDIFF(), it lets you calculate how much time has elapsed from a baseline date within each partition. This can be useful for doing stuff like measuring durations from the start of a process, tracking time since the first event in a group, or calculating age from an initial reference point.
The main advantage of using MIN() or MAX() as a window function is that you can compare every row in a partition against the earliest or latest date in that same partition without needing a self-join or subquery. Each row gets access to the aggregate value while still maintaining its individual row data.
Building Dynamic Reports with Month and Weekday Labels in SQL Server
When you’re building reports in SQL Server, there’s a good chance you’ll need to display dates in a more human-readable format than the ISO 8601 standard that will likely be returned in the absence of any formatting. Nobody wants to see “2024-03-15” when “March” or “Friday” would make the report instantly clearer. SQL Server gives you several ways to extract and format these labels, and knowing which approach fits your situation can save you time and make your queries cleaner.
Using Subqueries Inside DATEADD() for Dynamic Date Calculations
SQL Server’s DATEADD() function doesn’t just accept literal values or column references – it can work with subqueries too. This means you can calculate date offsets based on aggregated data, lookups from other tables, or any scalar subquery that returns a single numeric value. The technique is particularly useful when you need to derive both the base date and the offset from your data rather than having them readily available in the current row.
The main requirement is that each subquery must return exactly one value. DATEADD() expects a scalar for both the interval amount and the base date, so your subqueries need to use aggregation functions, TOP 1, or other methods to ensure a single-row result.
Creating Calendar View Reports in SQL Server
Calendar views are one of those report formats that instantly make data more digestible. Instead of scrolling through rows of dates, you get a grid that shows patterns or trends at a glance. You can instantly see which days of the week are busiest, which months see the most activity, or how different time periods compare. SQL Server doesn’t have a built-in calendar view function, but with pivoting techniques and a bit of creativity, you can build exactly what you need.