Let's say you had report queries that were going wildly parallel, and you wanted to put a stop to it, but you couldn't afford SQL Server Enterprise Edition.
You could do is create databases with different MAXDOP settings:
[crayon-6a72da343680f913613208/]
Yes, MAXDOP has been a database-scoped configuration since SQL Server 2016.
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
Presented without comment:
[crayon-6a72da34434f6199740131/]
Next up, can you break up a query with spaces? Yep:
[crayon-6a72da34434fe174451172/]
Well if you can do that - can you break up a query across lines? Sure you can:
[crayon-6a72da3443501231583620/]
And now, brace yourself: this one is so weird that I can't even embed it in the blog. I'm just going to show you a picture of it first:
So, uh, you can use WAITFOR in a logon trigger:
[crayon-6a72da3443d52515648937/]
You probably don't want it to be TOO long, lest their app report it as a connection timeout. You want it just slow enough to make them angry.
I mean, theoretically. I would certainly never do anything like this. And I'm certainly not as diabolical as Dave Dustin:
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 hassle of deleting it later.
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.
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…
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 [crayon-6a72da3446583534089836/] DropIndexes for SQL Server 2008-2014 This one doesn't include…
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
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 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 done. What follows is a non-comprehensive (and embarrassing code free) list of things I've done with SQL that I really should have used something else to do.
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 one for Express Edition, I suppose.
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.
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 - I don't want them to rely on tables if they don't have to - so we'll start a CTE like this:
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 I figured, why not build one in SQL Server?
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…
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 a numbers table with Method #7 from this StackOverflow answer, and then:
[crayon-6a72da344a6f0876103151/]
Which gives you a 10,000 join query, but I'm only showing the first 10 lines here:
No, not #tables - actual tables. Here's how: [crayon-6a72da344ab5f063228051/] The first one disappears when my session is over, but the latter two persist until the SQL Server is restarted. Why would you ever do the latter two? Say you need to share data between sessions, or between different applications, or staging tables for a data…
Hey, remember 2005?
What a great year for... not SQL Server. Mirroring was still a Service Pack away, and there was an issue with spinlock contention on OPT_IDX_STATS or SPL_OPT_IDX_STATS. The KB for it is over here, and it's pretty explicit that the issue was fixed in 2008, and didn't carry over to any later versions. For people still on 2005, you had a Trace Flag: 2330.
Developers hate his bad idea jeans:
[crayon-6a72da344baae416904809/]
Presto, the schemabinding option means no one can change the underlying tables. (At least, until they figure out about your view.)
Obligatory disclaimer: seriously, this is a really bad idea. But it's hilarious.
