Posts by Erik Darling

Erik Darling

365 posts

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

Forwarded Fetches and Bookmark Lookups

Last Updated September 2, 2018

Erik Darling

Execution Plans, Indexing

Base Table When you choose to forgo putting a clustered index on your table, you may find your queries utilizing forwarded fetches — SQL Server’s little change of address form for rows that don’t fit on the page anymore. This typically isn’t a good thing, though. All that jumping around means extra reads and CPU…

Locks Taken During Indexed View Modifications

Last Updated September 2, 2018

Erik Darling

Locking, Blocking, and Isolation Levels, SQL Server

Frankenblog This post has been nagging at me for a while, because I had seen it hinted about in several other places, but never written about beyond passing comments. A long while back, Conor Cunningham wrote: This same condition applies to indexed view maintenance, but I’ll save that for another day :). AFAIK he hasn’t…

First Responder Kit Release: What Does A Fish Know About Friday?

Last Updated August 31, 2018

Erik Darling

First Responder Kit Updates

I know, it seems like just yesterday I was doing one of these releases. But no, it was three weeks ago. You’ve just been drunk for a really long time. You can download the updated FirstResponderKit.zip here. sp_Blitz Improvements #1698 – Now warns you about any SQL Modules that have ANSI_NULLS or QUOTED_IDENTIFIER off. Thanks @MisterZeus! #1719 – @TheUsernameSelectionSucks pointed out…

What Kind Of Statistics Updates Invalidate Plans?

Last Updated May 26, 2020

Erik Darling

Parameter Sniffing, Statistics

Basics If you update statistics when the underlying objects haven’t been modified, plans won’t be invalidated. That makes total sense if your statistics update doesn’t also change the statistics. But what about when they do? That seemed like a no-brainer to me. What if you used a higher sampling percentage and got a more accurate…

Do You Have Tables In Your Tables?

Last Updated August 24, 2018

Erik Darling

Development

This Isn’t A Trick Question Hopefully it’ll get you thinking about your tables, and how they’re designed. One of the most consistent problems I see with clients is around wide tables. I don’t mean data types, I mean the number of columns. Going back to Michael Swart’s 10% Rule, if your tables have > 100…

Tall Tales From Table Variables

Last Updated August 13, 2018

Erik Darling

Execution Plans, Indexing

Secret Squirrel When you modify a table with multiple indexes, SQL Server may choose either a narrow plan, if it doesn’t think all that many rows are going to change, or a wide plan if it thinks many will. In narrow plans, the work SQL Server has to do to modify many indexes is hidden…

Foundational Material: Microsoft SQL Server Book and Blogs From The Past

Last Updated August 13, 2018

Erik Darling

SQL Server

What Did Dinosaurs Watch On TV? These are some of my favorite books and blogs from Microsoft from the way-back machine. I can’t say every bit of information is still 100% true and should be followed to the letter, but hey, that’s what happens. This is stuff I consider foundational material, though. I’ve learned a…

Wait Stats When VSS Snaps Are Slow

Last Updated August 8, 2018

Erik Darling

SQL Server

Deus Redux A while back I wrote about the Perils of VSS Snaps. After working with several more clients having similar issues, I decided it was time to look at things again. This time, I wanted blood. I wanted to simulate a slow VSS Snap and see what kind of waits stats I’d have to…

A Common Query Error

Last Updated August 14, 2018

Erik Darling

SQL Server

So Many Choices When you only need stuff from a table where there’s matching (or not!) data in another table, the humble query writer has many choices. Joins In/Not In Exists/Not Exists No, this isn’t about how NOT IN breaks down in the presence of NULLs, nor is it a performance comparison of the possibilities.…

Setting Up SQL Server: People Still Need Help

Last Updated August 8, 2018

Erik Darling

SQL Server

I Like What’s Happening I wanna start off by saying that I like what Microsoft has been doing with the setup process — it made a lot of sense to add the tempdb configuration screen, and having a checkbox to turn on Instant File Initialization was amazingly helpful. Even in the cloud, people still need…

First Responder Kit Release: A Year From Now These Will All Stop Working On 2008 And 2008R2

Last Updated August 10, 2018

Erik Darling

First Responder Kit Updates

You think I’m kidding. Time bomb. Boom. Get your upgrade underwear on. You can download the updated FirstResponderKit.zip here. sp_Blitz Improvements #1664 – We’re officially smart enough to not warn people that we’re recompiling our own stored procedures. #1669 – Reworded the stacked instances details. Servers may be installed and not running. #1687 – @josh-simar has servers linked with AD accounts,…

Why Does My Select Query Have An Assert?

Last Updated August 8, 2018

Erik Darling

Execution Plans

You And Ert This is a quick post because it came up with a client. I like having stuff to point people to — that’s sort of like automation, right? Anyway! Lots of plans have Assert operators in them. But they’re usually performing modifications. Assert operators are used to check Foreign Key and Check Constraint…

Wait Stats Should Be Easy By Now

Last Updated July 20, 2018

Erik Darling

Wait Stats

Why Is My Query… We’ve all started a question with a close approximation of those words. No matter how you finish that sentence, there’s some basic information that you need to collect to figure it out, like: Query plan Wait stats Other server activity Those are a good place to start. It’s easy enough to…

A Query That Should Be Contradicted

Last Updated July 20, 2018

Erik Darling

Execution Plans

Innocent Enough I was writing another query, and became enamored with the fact that HAVING will accept IS NULL or IS NOT NULL as a predicate. What I ended up writing as an example was this query: Transact-SQL SELECT v.PostId, SUM(v.UserId) AS whatever FROM dbo.Votes AS v WHERE v.UserId IS NULL GROUP BY v.PostId HAVING…

Do I Have A Query Problem Or An Index Problem?

Last Updated July 20, 2018

Erik Darling

SQL Server

Party Up When someone says “this query is slow”, and you can rule out contextual stuff like blocking, odd server load, or just an underpowered server, what’s the first thing you look at? There’s a lot of potential culprits, and they could be hiding in lots of different places. Pick a card, any card. After…

Is the CXCONSUMER Wait Type Harmless? Not So Fast, Tiger.

Last Updated August 9, 2020

Erik Darling

Execution Plans, SQL Server

Let’s say you’ve got a query, and the point of that query is to take your largest customer/user/whatever and compare their activity to smaller whatevers. If SQL Server doesn’t balance that work evenly across multiple threads, you can experience the CXCONSUMER and/or CXPACKET wait types. To show how SQL Server ends up waiting, let’s write…

A Quirk In Parallel Hash Join Plans

Last Updated July 18, 2018

Erik Darling

Execution Plans

Bailing, Takes Me Away Both of these queries will return zero rows, in the 2010 version of the Stack Overflow database. Transact-SQL SELECT COUNT(*) AS records FROM dbo.Votes AS v JOIN dbo.Posts AS p ON v.UserId = p.OwnerUserId WHERE v.CreationDate >= '2011-01-01'; SELECT COUNT(*) AS records FROM dbo.Votes AS v JOIN dbo.Posts AS p ON…

A New tempdb Trace Flag Appears!

Last Updated July 2, 2018

Erik Darling

SQL Server

Coming Around Microsoft has been doing a good job of taking stuff that used to be Wizard Hat and making it part of out of the box functionality. Starting with SQL Server 2016, you didn’t need to keep these in your setup checklist: Trace flag 1118 to reduce SGAM contention Trace flag 1117 so when…

#TSQL2sday: +

Last Updated July 10, 2018

Erik Darling

SQL Server

For this month’s T-SQL Tuesday, He of Perfect Hair, Bert Wagner asked, “What code have you written that you would hate to live without?” There are a lot of pieces of code I frequently need to reuse when working on the First Responder Kit, like string splitting, XML PATH-ing, and converting milliseconds to some human…

Building SQL ConstantCare®: Analyzing Query Plans

Last Updated June 24, 2018

Erik Darling

SQL ConstantCare

XML Delenda Est Last week, I talked about how we’re going to be collecting query plans from users who opt to have us do so (and only users who opt in! No surprises, here) by leveraging code we already have written in sp_BlitzCache, and how we’re going to do the XML analysis on our end using…

Posts by Erik Darling

Save your cart?