performance tuning

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

September 22, 2016

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

September 6, 2016

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

August 8, 2016

First Responder Kit, Memory Grants, sp_BlitzCache, sp_BlitzFirst

No Comments

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?

July 14, 2016

Indexing, SQL Server, Statistics

dba, performance tuning, sql server

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

July 12, 2016

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

July 5, 2016

backup, dba, performance tuning, 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

May 11, 2016

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

April 21, 2016

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?

April 20, 2016

Execution Plans, Parameter Sniffing, SQL Server

development, performance tuning

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

March 30, 2016

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

March 10, 2016

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

January 5, 2016

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

December 15, 2015

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

December 8, 2015

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

November 3, 2015

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

April 7, 2015