Posts by Erik Darling

Pocket Square

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

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…
Read More

Do You Have Tables In Your Tables?

Development
11 Comments
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…
Read More

Wait Stats When VSS Snaps Are Slow

SQL Server
4 Comments
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…
Read More

A Common Query Error

SQL Server
18 Comments
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.…
Read More

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

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,…
Read More

Why Does My Select Query Have An Assert?

Execution Plans
4 Comments
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…
Read More

Wait Stats Should Be Easy By Now

Wait Stats
1 Comment
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…
Read More

A Query That Should Be Contradicted

Execution Plans
21 Comments
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…
Read More

A Quirk In Parallel Hash Join Plans

Execution Plans
0
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…
Read More

A New tempdb Trace Flag Appears!

SQL Server
19 Comments
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…
Read More

#TSQL2sday: +

SQL Server
5 Comments
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…
Read More