Humor

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: Transact-SQL CREATE OR ALTER PROCEDURE [dbo].[DropIndexes] @SchemaName NVARCHAR(255) = 'dbo', @TableName NVARCHAR(255) = NULL, @WhatToDrop VARCHAR(10) =…

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 2018 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

Every now and then, we put on our bad idea jeans in the company chat room and come up with something really ill-advised. We should probably bury these in the back yard, but…what fun would that be? Bad ideas are the most fun when they’re shared. One day, after seeing enough horrific but well-meaning indexes created…

Bad Idea Jeans: Dynamically Generating Ugly Queries and Task Manager Graffiti

August 11, 2016

Say you’re at Dell DBA Days, and you want to build a really ugly query, fast. You want to generate a StackOverflow database query that will take a long time to compile, and maybe demand a huge amount of memory to run, but not actually take any time to execute. You might start by building…

Dell DBA Days Prep: Country Songs About Databases

August 2, 2016

I travel a lot, and I’ve seen a lot of things, but even I was surprised this week when I sat down in a Cracker Barrel Restaurant that had the oddest soundtrack. It turns out there’s an entire sub-genre of country music that specializes in database songs. While I was sitting there eating my Uncle…

#DellDBADays 2016: What Would You Do with Unlimited Hardware?

June 2, 2016