Posts by Erik Darling

Erik Darling

364 posts

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

sp_BlitzQueryStore: A Gentle Introduction

Last Updated May 26, 2020

Execution Plans, First Responder Kit, Parameter Sniffing, Query Store (QDS), sp_BlitzQueryStore, SQL Server 2016, SQL Server 2017

Odds and ends During the precon, we asked how many people were on 2016, how many people were aware of query store, how many people were using it, and how many people were using sp_BlitzQueryStore. About 1/3 of the hands went up at first, then for each successive question, fewer and fewer hands stayed up.…

First Responder Kit Release: Treating Object References Like Object Instances, Man

Last Updated May 9, 2018

Erik Darling

First Responder Kit, First Responder Kit Updates

Object reference not set to an instance of an object. You can download the updated FirstResponderKit.zip here. sp_Blitz Improvements #1281 We’ve lowered the priority of Query Store being disabled. It’s just not as important as the backups you’re not taking and the corruption checks you’re not running. sp_BlitzCache Improvements #1253 Due to an unfortunate copy and paste incident,…

Partition Level Locking: Explanations From Outer Space

Last Updated November 22, 2017

Erik Darling

Development, Locking, Blocking, and Isolation Levels, Partitioning

It’s not that I don’t like partitioning It’s just that most of my time talking about it is convincing people not to use it. They always wanna use it for the wrong reasons, and I can sort of understand why. Microsoft says you can partition for performance. Partitioning large tables or indexes can have the…

Good Reasons to Rebuild or Reorganize Indexes

Last Updated November 29, 2017

Erik Darling

Index Maintenance

No, not that It’s not the F word. Not like this embarrassing page that was somehow updated in 2017, which still tells you to reorg at 5% and rebuild at 30% fragmentation, like your data is still on a drive that looks like a record player and might still start making a clicking noise when…

First Responder Kit Release: The Ides Of November

Last Updated May 9, 2018

Erik Darling

First Responder Kit, First Responder Kit Updates

IB Rewop This is a cleanup release to get some of the pull requests in that didn’t make it in before the precon. There’s also a secret unlockable character that Brent is blogging about next week! Please clap. You can download the updated FirstResponderKit.zip here. sp_Blitz Improvements #1199 We’ve updated the unsupported builds list! Now you can…

Implied Predicate and Partition Elimination

Last Updated December 4, 2017

Erik Darling

Execution Plans

>implying Way back when, I posted about turning the Votes table in the Stack Overflow database into a Partitioned View. While working on related demos recently, I came across something kind of cool. It works for both partitioned tables and views, assuming you’ve done some things right. In this example, both versions of the table…

Partitioned Views, Aggregates, and Cool Query Plans

Last Updated December 4, 2017

Erik Darling

Execution Plans

The Max for the Minimum Paul White (obviously of course as always) has a Great Post, Brent® about Aggregates on partitioned tables Well, I’m not that smart or good looking, so I’ll have to settle for a So-So Post about this. There are actually quite a few similarities between the way a partitioned table and…

First Responder Kit Release: Precon Precogs

Last Updated May 9, 2018

Erik Darling

First Responder Kit, First Responder Kit Updates

This release comes to you from a hotel room in Chicago: The land of fiscal insolvency and one shooting per hour. It’s pretty nice, otherwise. This release is to get the pre-precon important stuff in. As much as I’d like to push all the recent contributions through, between travel, speaking, and uh… what do the…

SQL Server Workarounds

Last Updated October 12, 2017

Erik Darling

Development, SQL Server, T-SQL

360 Questions We’ve been asking you folks a lot about what you’d add to SQL Server, and we’ve gotten some great answers. There are even some that have been answered in the last couple versions of SQL Server. For instance, 2016 brought us STRING_SPLIT! SQL Server 2017 has a whole bunch of neat stuff: CONCAT_WS…

Do Variables Exist?

Last Updated October 12, 2017

Erik Darling

Humor, SQL Server

I know, I know I swore I’d never talk about variables again. Then I ran into some funny business. Maybe it was quite as funny as the Temp Table Ghosts, but hey. What’s as funny as temp tables? It’s in your heaaaaaaaad If I run this, what do you think will come back? Transact-SQL IF…

First Responder Kit Release: Still Totally Lacking A Servicing Model

Last Updated September 29, 2017

Erik Darling

First Responder Kit

A lot of people would think we’re crazy for releasing a FRK update a few days before a new version of SQL Server drops, but we’ve been adding 2017 compatible features for months Also none of you are on 2017 Most of you won’t live to be on 2017 If you’re lucky, your kids3000 will…

Adaptive Blog Posts

Last Updated October 2, 2017

Erik Darling

Adaptive Joins, Execution Plans

It turns out I can be dumb In a previous blog post about Adaptive Joins, I thought that EXISTS wasn’t supported by the new feature. It turns out that EXISTS is totally 100% supported, as long as your indexes support EXISTS. To show this, I need to show you a query that gets an Adaptive…

SQL Server 2017 Showplan Schema Is Available

Last Updated September 28, 2017

Erik Darling

Execution Plans, SQL Server, SQL Server 2017

File this under… Things probably only I care about. But the Showplan Schema for SQL Server 2017 popped up… well, I caught it last night. I probably could have written this sooner, huh? So what’s new? Well, if I compare the 2016 SP1 schema using KDiff, a few things stand out. Some I’ve blogged about,…

Live Query Plans and Blocking

Last Updated September 21, 2017

Erik Darling

Execution Plans, Indexing, Locking, Blocking, and Isolation Levels, SQL Server

You’d think this would be cool After all, you’ve got a query plan being executed LIVE ON NATIONAL TELEVISION! But it’s not. It’s actually one of those weird reasons why you still need other ways of looking at DMVs for issues. Demo Library Let’s take a closer look! First, we need an index. Indexes are…

Why Columnstore Indexes May Still Do Key Lookups

Last Updated June 25, 2021

Erik Darling

Columnstore Indexes, Execution Plans, Indexing, SQL Server

I was a bit surprised that key lookups were a possibility with ColumnStore indexes, since “keys” aren’t really their strong point, but since we’re now able to have both clustered ColumnStore indexes alongside row store nonclustered indexes AND nonclustered ColumnStore indexes on tables with row store clustered indexes, this kind of stuff should get a…

Answering Questions For Fun And No Profit

Last Updated September 20, 2017

Erik Darling

Humor, SQL Server

I love answering questions about SQL My forum of choice is dba.stackexchange.com because I find the voting and point system with badges for being a decent internet person addictive. If you like more traditional forums, SQLServerCentral.com has a lot of really smart people on it as well. When I post questions or answers, I try…

The Ghosts of Temp Tables Past

Last Updated September 6, 2017

Erik Darling

Humor, SQL Server

True story You may find it hard to believe, but I recently had to fix a small bug in sp_BlitzCache and sp_BlitzQueryStore. Since both stored procedures have similar functions, they also share some temp table names (mainly the ones for parsing down XML nodes to more manageable chunks). In the window where I was making…

Optional Parameters and Missing Index Requests

Last Updated September 15, 2017

Erik Darling

Execution Plans, Indexing, SQL Server

That’s when it all gets blown away At one point or another in everyone’s SQL-querying career, they end up writing a query that goes something like this: Transact-SQL SELECT something FROM stuff WHERE (@thing1 is NULL or whatever = @thing1) AND ... 1234 SELECT somethingFROM stuffWHERE (@thing1 is NULL or whatever = @thing1)AND ... These…

Indexing for Windowing Functions: WHERE vs. OVER

Last Updated March 12, 2018

Erik Darling

Execution Plans, Indexing, SQL Server

Life Is Messy Demo queries have this nasty habit of being clean. Even using a pit of despair like Adventure Works or World Wide Importers, it’s easy to craft demo queries that fit the scenario you need to make yourself look like a genius. Stack Overflow, in all its simplicity, makes this even easier (lucky…

How Much Can One Column Change A Query Plan? Part 2

Last Updated September 8, 2017

Erik Darling

Execution Plans, Indexing, SQL Server

What happened in Part 1? Join Elimination, naturally. Until the end. My copy of the Stack Overflow database doesn’t have a single foreign key in it, anywhere. If we go down the rabbit hole a couple steps, we end up at a very quotable place, with Rob Farley. 2. Duplicated rows Not necessarily duplicated completely,…