Posts by Erik Darling

Erik Darling

365 posts

Restoring tempdb since GETDATE(). Now blogging at ErikDarlingData.com.

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

A Gentle Reminder That Corruption Stinks

Last Updated February 13, 2017

Erik Darling

CHECKDB and Corruption, SQL Server

Seriously DBCC CHECKDB (YourDatabaseHere) WITH NO_INFOMSGS, ALL_ERRORMSGS; Oh. It’s broken. Brent says: normally, we don’t post client screenshots on the blog because it’s hard to sanitize the sensitive data. This time, not so much. Storage took care of this for us.

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…

Questions You Should Be Asking About Your Backups

Last Updated February 9, 2017

Erik Darling

Backup and Recovery, SQL Server

#basicbackup So you’re taking backups! That’s great. Lots of people aren’t, and that’s not so great. Lots of people think they are, but haven’t bothered to see if the jobs are succeeding, or if their backups are even valid. That’s not great, either; it’s about the same as not taking them to begin with. If…

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…

The Law Of The Minimum

Last Updated February 13, 2017

Erik Darling

Monitoring

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…

Update On Connect Item To Replace DBCC SHOW_STATISTICS

Last Updated October 5, 2016

Erik Darling

SQL Server

ALL CAPS EXCITEMENT In rather fun news, a Connect Item of mine about replacing DBCC SHOW_STATISTICS with a DMV or function got updated as ‘Resolved as Fixed’. It was only slightly odd, because I intentionally funneled votes to Greg Low’s item after realizing I had duplicated it. So, Greg, if you’re out there… Uh, hi!…

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…

DBA Days: Money for Nothing, Chips for Free

Last Updated August 31, 2016

Erik Darling

SQL Server

Throwing hardware at it We gotta move these E5-2670 v3s These were just sitting around Dell. On a table. Not doing anything. They might have been broken; I’m not sure. But that’s not the point. The most meager of server blades hanging out here had 128 GB of RAM in it. One-hundred-and-twenty-eight. Gigabytes. Let that sink…

Question From Office Hours: SQL Handle vs. Plan Handle

Last Updated February 13, 2017

Erik Darling

Execution Plans

Great question! We recently added some columns to sp_BlitzCache to help you remove undesirable plans from the cache. Doing this will force SQL to come up with a new plan, or you know, just re-create the old plan. Because that just happens sometimes. I answered it, but I wasn’t happy with my answer. So here’s…

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…

Cardinality Estimator Trace Flags and Links

Last Updated August 28, 2016

Erik Darling

SQL Server

This comes up during Office Hours once a week Which pretty much gives it a 100% hit rate. I’m writing this mostly to have something to reference when people ask. Trace flags If you restore a database on a SQL Server 2014 or greater instance, you have choices to make. You can either set the…

Where Do Missing Index Requests Come From?

Last Updated November 16, 2017

Erik Darling

Indexing, sp_BlitzIndex

Be honest, here You don’t care about most indexing problems. Duplicates, Borderline Duplicates, Unused, Wide, Non-aligned Partitions… All you’re here for are the Missing Indexes. Wham, bam, your query finishes in less than a second, ma’am. Take this quarter, go downtown and have a rat gnaw that thing off your face. And I get it.…