Bad Idea Jeans

Bad Idea Jeans

When you build a monitoring tool that sends advice via email, you discover two things: Some people have really, really big execution plans Email servers have reasonable limits on file attachment sizes Add those two things together, and Richie found himself working on a new feature for SQL ConstantCare® that would automatically break up advice…

Stupid T-SQL Tricks

Last Updated May 30, 2018

Bad Idea Jeans, T-SQL

Presented without comment: Transact-SQL CREATE TABLE dbo.[FROM] ([SELECT] INT, [WHERE] INT, [LIKE] INT); GO SELECT [SELECT] FROM [FROM] WHERE [WHERE] LIKE [LIKE]; GO 1234 CREATE TABLE dbo.[FROM] ([SELECT] INT, [WHERE] INT, [LIKE] INT);GOSELECT [SELECT] FROM [FROM] WHERE [WHERE] LIKE [LIKE];GO Next up, can you break up a query with spaces? Yep: Transact-SQL SELECT * FROM…

How to Throttle Logins to SQL Server

Last Updated February 12, 2018

Bad Idea Jeans

So, uh, you can use WAITFOR in a logon trigger: Transact-SQL CREATE OR ALTER TRIGGER SorryNorm ON ALL SERVER FOR LOGON AS BEGIN IF ORIGINAL_LOGIN()= 'NormTheNewGuy' WAITFOR DELAY '00:00:15'; END; GO 123456 CREATE OR ALTER TRIGGER SorryNorm ON ALL SERVER FOR LOGON ASBEGINIF ORIGINAL_LOGIN()= 'NormTheNewGuy' WAITFOR DELAY '00:00:15';END;GO You probably don’t want it to be TOO…

Creating Insert Triggers to Silently Ignore Data You Don’t Want

Last Updated February 1, 2018

Bad Idea Jeans, T-SQL

Say you’ve got an application that insists on inserting data into the database, and…you don’t want the data. You want the application to THINK it inserted the data – you don’t want to roll it back or return an error to the end user. You just don’t want the data, and you don’t want the…

Can you prevent deletes and inserts without a WHERE clause from running?

Last Updated December 12, 2017

Bad Idea Jeans, Humor

File this under bad idea thong In the tradition of Klaus Aschenbrenner wanting to prevent people from writing SELECT * queries, I thought it might be nice to prevent people from running deletes or updates without a WHERE clause. In a vaguely scientific way. Now, I don’t really condone this. It just seemed funny at…

Bad Idea Jeans: Finding Undocumented Trace Flags

Last Updated October 4, 2017

Bad Idea Jeans

I need to start this blog post with a disclaimer: you should not be doing this. Hell, *I* should not be doing this. But when SQL Server 2017 came out Monday, I noticed a few unusual SNAPSHOT_MATERIALIZATION entries in sys.messages and a few related undocumented system objects that hinted about a new feature Microsoft hadn’t…

How to Drop All Your Indexes – Fast

Last Updated July 19, 2022

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

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

Last Updated May 26, 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

Last Updated May 23, 2017

Bad Idea Jeans, T-SQL

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…

Stupid Things I’ve Done With SQL

Last Updated February 13, 2017

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

Last Updated February 13, 2017

Bad Idea Jeans, 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

Last Updated February 13, 2017

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

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

Last Updated February 13, 2017

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

Last Updated February 13, 2017

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

Last Updated February 13, 2017

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

Last Updated February 13, 2017

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

Last Updated April 1, 2019

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…

Creating Tables and Stored Procedures in TempDB – Permanently

Last Updated November 4, 2020

Bad Idea Jeans, Humor, Parameter Sniffing, SQL Server, TempDB

No, not #tables – actual tables. Here’s how: Transact-SQL USE tempdb; GO /* This one is only available during my session: */ CREATE TABLE #myTempTable (ID INT IDENTITY(1,1), Stuffing VARCHAR(100)); GO /* This one is global, meaning it's available to other sessions: */ CREATE TABLE ##myTempTable (ID INT IDENTITY(1,1), Stuffing VARCHAR(100)); GO /* You can…

Trace Flag 2330: Who needs missing index requests?

Last Updated February 13, 2017