I love answering questions about SQL My forum of choice is dba.stackexchange.com because I find the voting and point system with badges for being a decent internet person addictive. If you like more traditional forums, SQLServerCentral.com has a lot of really smart people on it as well. When I post questions or answers, I try…
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…
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…
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…
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: CREATE PROCEDURE [dbo].[DropIndexes] AS SET NOCOUNT ON CREATE TABLE #commands (ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, Command…
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…
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: 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 = d.name…
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+…
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…
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…
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 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…
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…
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,…
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 some truly revolutionary capabilities. Now, I probably shouldn’t be sharing these with you,…
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: SELECT TOP 999 ‘CREATE INDEX [IX_’ + c1.COLUMN_NAME…
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 –…
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…
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…
