Humor

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…

What If Week: What Would You Look At First?

August 16, 2017

Humor

For this week’s What If series, we’re exploring what would happen if you had access to SQL Server’s source code – like if you got a job at Microsoft, signed a partner NDA, if the code leaked, or if it went open source. Today’s question is, “What would you look at first?” Brent says: I’d…

What If Week: SQL Server Code Review

August 15, 2017

Top Secret For the sake of any lawyers out there reading, I want to start by saying we do not now, nor have we ever possessed SQL Server’s source code. I wouldn’t even know what to do with it if I did — I doubt it’s written in crayon. Heck, I’m not even competent enough…

How to Drop All Your Indexes – Fast

August 11, 2017

Bad Idea Jeans, Indexing

Sometimes I need to reset stuff during performance training classes. I know some of you teach classes, too, and some of you just like doing crazy stuff. So here you go, a stored procedure to lose weight fast: DropIndexes for SQL Server 2016 & Newer Transact-SQL CREATE OR ALTER PROCEDURE [dbo].[DropIndexes] @SchemaName NVARCHAR(255) = 'dbo',…

Live Blogging: Erik vs. PowerShell

July 29, 2017

Humor

Oh, hey, happy Saturday First, I’d like to apologize to Conor Cunningham for the blatant theft of a blog title. That’s what you get for not posting in three years (like the desert miss the rain~). Why am I here? Why am I writing PowerShell, my sworn enemy? I do it for you. I’m trying…

There’s Something Funny About Variable Assignment

June 28, 2017

dba, development, sql server

Guess who I’m going to give you three queries, and you have to guess what the output will be before you run them. Here they are: Transact-SQL DECLARE @DatabaseName NVARCHAR(256) = N'' SELECT @DatabaseName = d.name FROM sys.databases d WHERE d.name = 'master' AND compatibility_level = 130 ORDER BY d.database_id PRINT @DatabaseName SELECT @DatabaseName =…

Why Your Biggest Query Plans Don’t Show Up in Some DMVs

June 2, 2017

Bad Idea Jeans, Execution Plans

SQL Server has three ways to get execution plans from the plan cache: sys.dm_exec_query_plan – around since SQL Server 2005 sys.dm_exec_text_query_plan – added in 2005 SP2 sys.query_store_plan – new in SQL Server 2016 And there’s an important problem with the first one. To show it, let’s take one of my many bad ideas, building queries that take 12+…

Bad Idea Jeans: Multiple Index Hints

May 26, 2017

Bad Idea Jeans, T-SQL

dba, development, performance tuning, sql server

I hate that you can do this Here’s the thing: I’m mostly writing this because I didn’t know you could do it. But it’s cool, because it’ll reinforce some other concepts, and I’ll show you why you shouldn’t do it. I’m talking, of course, about index hints. To be more specific, hinting multiple indexes on…

CTEs, Views, and NOLOCK

March 29, 2017

dba, development, performance tuning, sql server

This is a post because it surprised me It might also save you some time, if you’re the kind of person who uses NOLOCK everywhere. If you are, you’re welcome. If you’re not, thank you. Funny how that works! I was looking at some code recently, and saw a CTE. No big deal. The syntax…

First Responder Kit Release: We hired Brent back because he promised to fix his code

March 21, 2017

March comes in like a 40 of Steel Reserve and leaves like your memory of the night after drinking a 40 of Steel Reserve. Get it here! sp_Blitz Improvements — #758 and #756 We made it less scary to run on Express Edition. Why? Because running Express Edition is scary enough. Coded by @Totty1973 and…

I’m just saying it’s valid T-SQL syntax, that’s all.

February 24, 2017

Humor

Bad idea #1 Bad idea #2 Bad idea #3

Stupid Things I’ve Done With SQL

November 15, 2016

development, 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…

DBA Days: Killing TempDB softly

September 8, 2016

Bad Idea Jeans, SQL Server

badidea, dba, development, 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

September 7, 2016

badidea, dba, development, 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,…

Upcoming SQL Server 2019 Features

August 31, 2016

Humor, SQL Server 2019

If you think that’s big, you should see the size of the drink I happened to be in Seattle this week after finishing up an Alaska cruise, and I had the chance to sit down with some of the PMs responsible for upcoming SQL Server features. It turns out they’re already deep in development with…

Bad Idea Jeans Week: Dynamically Generating 999 Indexes on a Table

August 25, 2016

Bad Idea Jeans, Humor, Indexing, SQL Server

Let’s say, just theoretically, you wanted to demo a query that takes SQL Server a long time to compile. And in order to make SQL Server’s job tougher, you wanted to create the maximum number of indexes possible on a table. You might do something like this: Transact-SQL SELECT TOP 999 'CREATE INDEX [IX_' +…

Bad Idea Jeans Week: Dynamically Generating Long Queries

August 24, 2016

As part of an experiment, I needed to build a really long query. (Don’t ask.) From another recent experiment, I know that SQL Server won’t let a query return more than 65,535 columns. I set about writing a one-line query that would return 65,535. I’m a big fan of writing the simplest reproduction scripts possible –…

Bad Idea Jeans Week: Building a Fork Bomb in SQL Server

August 23, 2016

Somewhat different than a sex bomb, a fork bomb is a denial-of-service attack that just starts a process that replicates itself, thereby starting more and more processes until the service goes down. Wikipedia’s fork bomb page lists examples on most operating systems (including Windows). I’ve always found fork bombs funny because of their elegant simplicity, so…

Bad Idea Jeans Week: Prevent the Use of the Database Engine Tuning Advisor

August 22, 2016