SQL Server

Execution Plans, SQL Server

Don’t make me spill Table variables get a lot of bad press, and they deserve it. They are to query performance what the TSA is to air travel. No one’s sure what they’re doing, but they’ve been taking forever to do it. One particular thing that irks me about them (table variables, now) is that…

[Video] Office Hours 2016/11/23 (With Transcriptions)

Last Updated November 27, 2016

This week, Brent and Richie discuss their hardware setups, lazy spools, SQL 2016 Service Pack 1, Always On Availability Groups, Database IDs, GroupBy.org, SQL on Linux, and new features they would like to see in the new version of SQL. Here’s the video on YouTube: You can register to attend next week’s Office Hours, or subscribe to…

SQL Server 2016 SP1 Bug with Graphical Execution Plans

Last Updated February 13, 2017

Execution Plans, SQL Server

Early adopters: if you’re rolling on SQL Server 2016 SP1, you may have noticed that when you click on query plans in sp_BlitzCache or sp_WhoIsActive, they sometimes render as ugly XML rather than the beautiful graphical showplan. Here’s a 3-minute video showing the bug: To work around it, save the XML plan as a file…

Index Scans: Not Nearly As Bad As You Might Think

Last Updated February 13, 2017

Execution Plans, SQL Server

Using our good old buddy the StackOverflow demo database, let’s look at the Users table. I want to get the first 10 users in the table ordered by Id, the primary clustered key, which is an identity field. Here’s the actual execution plan: https://www.brentozar.com/pastetheplan/?id=HyuSDxZye Ooo – clustered index scan – that’s bad, right? Not so…

What’s the Difference Between Estimated and Actual Execution Plans?

Last Updated January 8, 2019

Execution Plans, Memory Grants, SQL Server

I’m going to use the dbo.Users table in the StackOverflow demo database and run a pretty simple query: Transact-SQL SELECT * FROM dbo.Users WHERE DisplayName LIKE 'Brent%'; 123 SELECT *FROM dbo.UsersWHERE DisplayName LIKE 'Brent%'; First, hit Control-L in SSMS and get the estimated execution plan. Here it is: Estimated execution plan: https://www.brentozar.com/pastetheplan/?id=H1TeRlZke Click on the…

The Brighter Side of SQL Server’s future

Last Updated November 22, 2016

SQL Server

Believe it or not, I sometimes like things SP1 for 2016 has a bunch of cool new tricks: I’m pretty psyched on the Wait Stats stuff in actual execution plans. I hope it eventually comes in some form to cached plans, or at least Query Store plans, but this is a good start! Even if…

Initial Thoughts on 2016 SP1

Last Updated February 13, 2017

In-Memory OLTP (Hekaton), Licensing, Partitioning, SQL Server

A really odd choice I’ve talked a bit about my annoyance with hardware limits for Standard Edition here and here. If you’re a bunch of SQL-savvy developers who understand the Enterprise Edition features that are now available in 2016 SP1, great. You’re a very small minority of shops. I’m talking about places like StackOverflow and…

[Video] Office Hours 2016/11/16 (With Transcriptions)

Last Updated November 19, 2016

This week Richie, Tara, and Erik discuss replication, mirroring, memory grants, sp_BlitzCache, failover cluster instances, VLFs, restoring databases, qualifying schema and object names, multiple instances, and table variables. Here’s the video on YouTube: You can register to attend next week’s Office Hours, or subscribe to our podcast to listen on the go. Enjoy the Podcast? Don’t miss…

A Little Fun With Math

Last Updated February 14, 2017

SQL Server, T-SQL

I’ve never been much at math But I’ve always liked reading about it. It’s super interesting, and it always blows my mind. Like, guaranteed. Once in a while I’ll even try my hand at solving problems I read about in SQL. Not because SQL is a particularly good language for it; but just because sometimes…

Using Plan Guides to Remove OPTIMIZE FOR UNKNOWN Hints

Last Updated May 26, 2020

Execution Plans, Parameter Sniffing, SQL Server

Say you’ve got an application that has tons of OPTIMIZE FOR UNKNOWN hints in the T-SQL, and you’re getting bad query plans. We’re going to use the same StackOverflow query (and the same index on Reputation) that I demoed in the post Why Is This Query Sometimes Fast and Sometimes Slow? This technique produces a…

SQL Server 2016 Standard Edition Now Has Many Enterprise Edition Features.

Last Updated February 13, 2017

In-Memory OLTP (Hekaton), Licensing, SQL Server

Starting with today’s release of SQL Server 2016 Service Pack 1, Standard Edition now has a lot more of the features of Enterprise Edition. Here’s the list from Microsoft’s announcement post: Performance features – in-memory OLTP (Hekaton), in-memory columnstore, operational analytics Data warehousing features – partitioning, compression, CDC, database snapshots Some security features – Always Encrypted,…

Why Is This Query Sometimes Fast and Sometimes Slow?

Last Updated May 26, 2020

Execution Plans, Parameter Sniffing, SQL Server

You swear you didn’t change anything, but all of a sudden the SQL Server is going doggone slow. What happened? Parameter sniffing might be the problem, and to explain it, let’s see how it works. I’m going to use the StackOverflow database – particularly, the Users table that I demo in How to Think Like the…

Updated First Responder Kit: sp_Blitz, sp_BlitzCache, sp_BlitzIndex

Last Updated November 15, 2016

First Responder Kit, SQL Server

These improvements go great with cranberries. The food, not the b – actually, they go pretty well with the band, too. sp_Blitz Improvements #568 – @RichBenner added a check for the default parallelism settings sp_BlitzCache Improvements by @BlitzErik #495 – add warning for indexed views with missing stats #557 – bug fix – don’t alert…

Stupid Things I’ve Done With SQL

Last Updated February 13, 2017

Bad Idea Jeans, Humor, SQL Server

I Always Enjoy Reminiscing And that header is a lie. I do enjoy thinking about stuff I used to have to do at work, because I don’t have to do it anymore. While we mostly gripe about the on-call stuff, the late night maintenance windows, etc., we don’t usually talk about the goofy stuff we’ve…

[Video] Office Hours 2016/11/09 (With Transcriptions)

Last Updated November 12, 2016

This week, Richie and Erik discuss log backups, migrating from SQL 2008 R2 to 2014, shrinking databases, tuning long-running queries, execution plans, sequencing, encryption, and much more! Here’s the video on YouTube: You can register to attend next week’s Office Hours, or subscribe to our podcast to listen on the go. Enjoy the Podcast? Don’t miss an…

Migrating from SQL Server to MySQL, PostgreSQL, and Aurora

Last Updated November 11, 2016

Cloud Computing, SQL Server

I’m getting an increasing number of requests these days that say: “We’re considering migrating our application from SQL Server to a cheaper, easier-to-scale database. Can you help?” Well, kinda: why are you doing it? If you’re doing it to get easier scalability, we start by taking a step back and looking at the performance problems…

Top 5 Overlooked Index Features

Last Updated February 13, 2017

Indexing, SQL Server

I see a lot of indexes Most of them are pretty vanilla. Some are pretty memorable, like one that I saw with 93 INCLUDES. A lot of things that people could do with indexes get overlooked, either because they don’t know about them, or they’re not sure when they’ll help. So, here goes my attempt…

Can Indexes My Query Doesn’t Use Help My Query?

Last Updated February 13, 2017

Indexing, SQL Server

This came up during Office Hours And I love questions like this, because they reveal some interesting underpinnings of how the cardinality estimator works. It was something along the lines of “I have a slow query, and when I add an index the query goes faster even though it doesn’t use the index I created.”…

[Video] Office Hours 2016/11/02 (With Transcriptions)

Last Updated November 5, 2016