performance tuning

The Law Of The Minimum

Last Updated February 13, 2017

I like science fiction Not enough to go to conventions, or memorize every line, but I have some opinions on things. Dune is probably my favorite series of stories in the genre. Yes, I think Paul would beat Luke in a fight. Sorry, Star Wars people. He’s just kind of a wimp. Throw grenade, digress One…

What To Do If sp_BlitzFirst Warns About High Compilations

Last Updated November 16, 2017

Erik Darling

First Responder Kit, sp_BlitzFirst, SQL Server

Compiles Aren’t The Same As Recompiles If you’re seeing high RECOMPILES, this post isn’t for you. We’ll talk about the differences between compiles and recompiles, but not how to troubleshoot recompiles. Recompiles mean one of two obvious things: You have a RECOMPILE hint at the stored procedure or statement level, or SQL found a reason…

Partitioned Views: A How-To Guide

Last Updated February 13, 2017

Erik Darling

Indexing, Partitioning, SQL Server

This is not about table partitioning If you want to learn about that, there’s a whole great list of links here, and the Best Blogger Alive has a tremendous post on why table partitioning won’t make your queries any faster over here. With that out of the way, let’s talk about partitioned views, and then…

DBA Days: Scripts from Downtime Train

Last Updated August 31, 2016

Erik Darling

SQL Server

ostress is so much fun It’s totally free to download and use as part of Microsoft’s RML Utilities. What else is in there? Stuff I’ve never used! I hear you can read trace files or something else perfectly adequate. Even though it’s a CLI, it’s still a bit less insane and confusing than HammerDB. Plus…

Memory Grants Added to sp_BlitzCache and sp_BlitzFirst

Last Updated January 8, 2019

Erik Darling

First Responder Kit, Memory Grants, sp_BlitzCache, sp_BlitzFirst

Exciting New Doodads When SP3 for 2012 dropped, we were all super excited by the new query tuning-centric features that were at our disposal. Now all we had to do was get people to install SP3! Great features like this make patching an easier sell. Now with SP2 for 2014 out, a lot of those…

Why Not Just Create Statistics?

Last Updated February 13, 2017

Erik Darling

Indexing, SQL Server, Statistics

Here at Brent Ozar Unlimited We have a proud tradition of not blaming index fragmentation for everything. There are points you should deal with it, but they’re probably not 5% and 30% and 1000 pages. But that’s not what this blog post is about. I’m hoping to clarify why we’re more interested in up to…

SQL Server 2014 SP2: DBCC CLONEDATABASE

Last Updated July 12, 2016

Erik Darling

SQL Server

Getting developers production data stinks DBAs are stuck in this annoying cycle where they need to give developers production, or production-like data, but… Production data can be huge. Hundreds of gigabytes if you’re lucky, several terabytes if you’re not. Then once it gets there, you have other considerations. Either you lock down Dev, or you…

TDE and Backup Compression: Together At Last

Last Updated September 21, 2017

Erik Darling

SQL Server

Note: THERE ARE BUGS IN THIS FEATURE. Make sure you read all the way through the post to catch the updates from Microsoft as more bugs were found. TDE is one of those things! You either need it, and quickly learn how many things it plays the devil with, or you don’t need it, and…

Implicit vs. Explicit Conversion

Last Updated March 12, 2018

Erik Darling

SQL Server

Everyone knows Implicit Conversion is bad It can ruin SARGability, defeat index usage, and burn up your CPU like it needs some Valtrex. But what about explicit conversion? Is there any overhead? Turns out, SQL is just as happy with explicit conversion as it is with passing in the correct datatype in the first place.…

Stats Week: Messin’ With Statistics

Last Updated March 23, 2017

Erik Darling

SQL Server, Statistics

If there’s one thing living in Texas has taught me It’s that people are very paranoid that you may Mess With it. Even in Austin, where the citizenry demand weirdness, they are vehemently opposed to any form of Messing, unless it results in mayonnaise-based dipping sauce. Me? I like Messing With stuff. Today we’re going…

Stats Week: Do Query Predicates Affect Histogram Step Creation?

Last Updated May 26, 2020

Erik Darling

Execution Plans, Parameter Sniffing, SQL Server

Auto Create Statistics is your friend It’s not perfect, but 99% of the time I’d rather have imperfect statistics than no statistics. This question struck me as interesting, because the optimizer will totally sniff parameters to compile an initial plan. If you don’t have index statistics, or system statistics already on a column in a…

Indexes Helping Indexes

Last Updated February 13, 2017

Erik Darling

Indexing, SQL Server

This post isn’t going to solve any problems for you It was just something I stumbled on that struck me as funny, while working on a demo for something else. Brent didn’t believe it at first, so I thought I’d share with the class. Blog. You, Your Name Here. Hiya! So there I was, creating…

Unique Indexes and Row Modifications: Weird

Last Updated March 12, 2018

Erik Darling

Indexing, SQL Server

Confession time This started off with me reading a blurb in the release notes about SQL Server 2016 CTP 3.3. The blurb in question is about statistics. They’re so cool! Do they get fragmented? NO! Stop trying to defragment them, you little monkey. Autostats improvements in CTP 3.3 Previously, statistics were automatically recalculated when the…

Still Serial After All These Years

Last Updated March 29, 2018

Erik Darling

SQL Server

With each new version of SQL comes a slew of new stuff While some changes are cosmetic, others bewildering, and the rest falling somewhere between “who cares about JSON?” and “OH MY GOD TAKE MY MONEY!”, but not really my money, because I only buy developer edition. Aaron Bertrand has done a better job finding,…

Improved diagnostics for query execution plans that involve residual predicate pushdown

Last Updated February 13, 2017

Erik Darling

Execution Plans, SQL Server

I love stuff like this! Even though it’s not on my list of dream features, it’s pretty neat. Getting new views into what SQL is doing when queries execute is pretty cool. You can read the short and gory details at the KB here: Improved diagnostics for query execution plans that involve residual predicate pushdown…

Filtered Indexes: Just Add Includes

Last Updated May 9, 2018

Erik Darling

Indexing, SQL Server

I found a quirky thing recently While playing with filtered indexes, I noticed something odd. By ‘playing with’ I mean ‘calling them horrible names’ and ‘admiring the way other platforms implemented them‘. I sort of wrote about a similar topic in discussing indexing for windowing functions. It turns out that a recent annoyance could also…

The case for Query Store in tempdb

Last Updated December 4, 2020

Erik Darling

Execution Plans, Query Store (QDS), TempDB

Query Store is so cool Billed as a flight data recorder for SQL Server, the Query Store is a repository of execution plan information, like the plan cache, except a bit more actionable. And it has a GUI. “Try looking into that place where you dare not look! You’ll find me there, staring out at you!”…

CTEs, Inline Views, and What They Do

Last Updated March 12, 2018

Erik Darling

SQL Server

By now, you have probably heard of CTEs And you may have even heard them referred to as Inline Views. Really, an Inline View can be any type of derived table. It’s very easy to illustrate when one may turn into a performance problem with CTEs, if you aren’t careful. A lot of people think…

The Fastest Query is the One You Never Make

Last Updated February 9, 2017

Brent Ozar

Licensing, SQL Server

When someone brings me a query to tune, I start with a few questions: How long does it take to run now? I’m looking for a time at first, and later as I progress through tuning, I’ll measure it more scientifically with logical reads and CPU cycles. How long would you like it to take? …

Introducing the SQL Server Plan Cache (and a Better sp_Blitz®)

Last Updated February 13, 2017

Brent Ozar

Execution Plans, SQL Server, Videos

When you send your beautifully hand-crafted organic T-SQL statement to SQL Server, the database engine takes a moment to appreciate the poetry of your work. Before it turns your request into results, it has to build an execution plan. SQL Server considers: Which tables it needs to join together What subqueries it needs to execute…