development

Things I Would Love to Put In sp_BlitzCache

Last Updated April 21, 2017

Execution Plans, First Responder Kit, SQL Server

This is my favorite script to work on Getting stuff to work sp_BlitzCache is so rewarding because XML querying is so ridiculous. I wish Mikael Eriksson would write a book already; it’d make my life a whole lot easier. Along the way, I see a lot of things that I would love to warn people…

Selective XML Indexes: Not Bad At All

Last Updated February 14, 2017

Erik Darling

Indexing, SQL Server

Promises, Promises I said I’d follow up on this, and here it is. I’m by no means the world’s foremost authority on these, I just played with a simple example until I figured out what worked the best for my query. So you can all play along at home, I’m using XML we all have…

Date Math In The WHERE Clause

Last Updated February 13, 2017

Erik Darling

Execution Plans, SQL Server

Oh, THAT SARGability I realize that not everyone has a Full Metal Pocket Protector, and that we can’t all spend our days plunging the depths of query and index tuning to eek out every CPU cycle and I/O operation from our servers. I mean, I don’t even do that. Most of the time I’m just…

Spills SQL Server Doesn’t Warn You About

Last Updated February 13, 2017

Erik Darling

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…

The Brighter Side of SQL Server’s future

Last Updated November 22, 2016

Erik Darling

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…

Stupid Things I’ve Done With SQL

Last Updated February 13, 2017

Erik Darling

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…

Top 5 Overlooked Index Features

Last Updated February 13, 2017

Erik Darling

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

Erik Darling

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.”…

Why You Should Test Your Queries Against Bigger Data

Last Updated February 14, 2017

Erik Darling

Load Testing, SQL Server

I Like Testing Things Especially Brent’s patience. And I like watching people test things. SQL Server is sort of perfect for that. You make some changes, you hit f5, and you wait for something to finish. If it’s faster, you ship it. Right? I mean, we all know you just added NOLOCK hints anyway. A…

MAX Data Types Do WHAT?

Last Updated March 12, 2018

Erik Darling

SQL Server

Users are horrible, I know You can just never rely on them to pass in reasonable search strings. When you write stored procedures to retrieve information for them, it’s really tempting to define all your string variables as MAX length. As long as the actual data type, N/VARCHAR matches, you’re cool, right? Avoid that pesky…

Set Statistics… Profile?

Last Updated February 13, 2017

Erik Darling

Development

Forgotten, But Not Gone Two of the best built-in tools to figure out if your query tuning efforts are headed in the right direction are SET STATISTICS TIME ON and SET STATISTICS IO ON. If you’re really fancy, you can use SET STATISTICS TIME, IO ON. By fancy I mean lazy. By lazy I mean…

Divide By Zero Hero

Last Updated September 22, 2016

Erik Darling

SQL Server

Alright, so at least they’re not NULLs But zero is actually literally much more problemaaaaatic per se (add lots of annoying vocal fry to this when you read it) when it comes to math. Or maths, depending on which message board you read. Zero has a weird history. It wasn’t always a number, and it…

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: Pre-maintenance Window Rituals

Last Updated February 12, 2017

Erik Darling

Processes and Practices

Like a DBA batter’s box While we were hanging out in between webcasts at Dell, we got to talking about stuff we used to do before maintenance windows. Things that brought us good luck, warded off blue screens and failed patches, and just generally made us feel better about working well into the early AM…

DBA Days: Killing TempDB softly

Last Updated February 13, 2017

Erik Darling

Bad Idea Jeans, SQL Server

Don’t make me regret publishing this This really will take a server down immediately. You can restart it, as we learned during DBA Days, but… If you also go ahead and make this an Agent job that runs at startup, there are additional challenges. Like every time Agent starts up, your server goes down. Score…

DBA Days: Chaos Sloth

Last Updated February 13, 2017

Erik Darling

Bad Idea Jeans, Humor, SQL Server

I love a bad idea I mean, just look at me. I’m covered in them! So when Brent wanted to randomly make his server act crappy, I wrote a script for it. Usual caveats here. Don’t run this in production. I’m not responsible for anything you do after you hit copy and paste. In fact,…

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…

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…