Product has been added to your cart.

Search Results for “execution plan”

Pocket Square

Plan Guides No Longer Valid

Brent Ozar
sp_Blitz® Result: Plan Guides No Longer Valid Plan guides let you guide SQL Server towards using a particular execution plan for a query. It’s great when you’re smarter than SQL Server, and you know exactly which indexes should be used in which order. Unfortunately, if one of those indexes are disabled, or something else causes the…
Read More
Pocket Square

Trivial Plans

Jeremiah Peschka
sp_BlitzCache™ Result: Trivial Execution Plans Trivial Plans occur when a query is so simple, there’s no reason for SQL Server to optimize the statement. Think of something like SELECT * FROM dbo.Users WHERE UserId = 1. When UserID is the clustered key, there’s nothing to do apart from seek to that record and return the data. Normally,…
Read More
Pocket Square

Forced Plans

Jeremiah Peschka
sp_BlitzCache™ Result: Forced Plans There are executions plans in the plan cache with a forced execution plan. This is typically accomplished through USE PLAN or  other forms of query hinting that force the order of the execution plan. No matter how you look at it, this isn’t a good thing – it’s worth investigating whether…
Read More
Pocket Square

Top Queries in the Plan Cache

Brent Ozar
sp_BlitzFirst® Result: Top Queries in the Plan Cache While sp_BlitzFirst® runs, it takes a sample of SQL Server’s execution plan cache. It identifies queries that used the most resources during that time span – like the most reads, most CPU time, most duration, and most execution count. You can click on each query to review its execution…
Read More
Pocket Square

Multiple Plans

Jeremiah Peschka
sp_BlitzCache Finding: Queries with Multiple Execution Plans Queries have been found with multiple execution plans. This can be caused by multiple things, but the two primary causes are a lack of parameterization or improper parameterization. When queries aren’t parameterized, SQL Server will end up creating a separate plan for each set of literal values. This…
Read More
Pocket Square

Query Plan Warnings

Jeremiah Peschka
sp_BlitzCache™ Result: Warnings in Execution Plans SQL Server has included additional information in the execution plan to warn you about a potential problem with the query. This can be anything from tempdb spills to bad cardinality estimates to missing indexes. How to Fix the Problem Investigate what’s going on in SQL Server. The following query…
Read More
Pocket Square

Plan Cache Erased Recently

Brent Ozar
Whenever you pass in a query, SQL Server has to turn your beautifully written T-SQL into an execution plan – a way of picking which indexes to use and in which order. It caches those plans in memory to reduce CPU requirements later if that T-SQL shows up again. According to our diagnostics, your server’s…
Read More
Pocket Square

Single-Use Plans in the Procedure Cache

Brent Ozar
Blitz Result: Single-Use Plans in the Procedure Cache When SQL Server gets a query, it has to do a lot of work to build an execution plan.  To save CPU, it caches these execution plans in memory.  When a similar query comes in, SQL Server may be able to reuse that plan. This part of…
Read More
Pocket Square

Query Plan Guides Enabled

Brent Ozar
Blitz Result: Query Plan Guides Enabled Some people think they know more about how to execute a query than the SQL Server, and they want to boss SQL Server around.  SQL Server’s plan guide feature lets you influence a query’s execution plan without changing the query itself – useful if you need to tune a…
Read More

[Video] Office Hours: Career & Data Q&A

Brent Ozar
Videos
2 Comments
A few career and security questions found their way into the queue at https://pollgab.com/room/brento for this episode: Here’s what we covered: 00:00 Start 02:10 Ethan: What percent of your constant care shops require encryption to connect with SQL server? What are your thoughts on mandatory encryption? 03:24 Miles: Hi Brent, an app that spawns multiple…
Read More
How to Think Like the SQL Server Engine

Tomorrow’s the Big Day: Free Live Webcasts!

Brent Ozar
Conferences and Classes
0
No fooling here. Tomorrow, I’m teaching my very favorite class live online, for free, two different ways – one with all slides, and one with all demos. Whatever way you like to learn, I’ve got you covered. So, what’s the subject? You’re comfortable writing queries to get the data you need. But you’re uncomfortable if someone…
Read More

[Video] Office Hours: The Long One

Brent Ozar
Videos
2 Comments
You posted a lot of great questions at https://pollgab.com/room/brento and I spent almost an hour covering these: Here’s what we covered: 00:00 Start 02:05 Kulstad: I’m using Ola Hallengren’s maintenance scripts for my db maintenance on Saturday evenings, and I’ve noticed my memory consumption go from approx 35% usage during the business week to 85%…
Read More

Does Your GROUP BY Order Matter?

Brent Ozar
Execution Plans
5 Comments
Sometimes when you do GROUP BY, the order of the columns does matter. For example, these two SELECT queries produce different results: Transact-SQL CREATE INDEX Location_DisplayName ON dbo.Users(Location, DisplayName); SELECT TOP 100 Location, DisplayName, COUNT(*) AS Duplicates FROM dbo.Users GROUP BY Location, DisplayName ORDER BY Location, DisplayName; SELECT TOP 100 DisplayName, Location, COUNT(*) AS Duplicates…
Read More
Brent Ozar

Hi! I’m Brent Ozar.

I make Microsoft SQL Server go faster. I love teaching, travel, cars, and laughing. I’m based out of Las Vegas. He/him. I teach SQL Server training classes, or if you haven’t got time for the pain, I’m available for consulting too.

Want to advertise here and reach my savvy readers?