Product has been added to your cart.

sql server

Analyzing Temporary Stored Procedures with sp_BlitzCache

Last Updated November 16, 2017

First Responder Kit, sp_BlitzCache, TempDB

All the cool kids Know that when you’re trying to figure out why a stored procedure is slow sometimes, you should use a temporary stored procedure instead of pulling the code out and running it ad hoc. We frequently point people to Erland Sommarskog’s “Slow in the Application, Fast in SSMS“, because it’s an excellent…

Indexes For Kids

Last Updated February 13, 2017

Indexing, SQL Server

Kid Tested When you work with something a lot, you tend to extrapolate concepts from it out to everything you see. That one person collecting tickets is a serial zone. That food truck line is a HEAP. The empty subway car that smells like a toilet is Replication. Stuff like that. If you have one…

Do DDL Triggers in TempDB Survive Restarts?

Last Updated February 13, 2017

SQL Server, TempDB

Of Course Not Nothing survives in tempdb. But this kind of stinks for those of us who don’t want certain things created in there. In my case, when I’m working on Blitz scripts, I have a terrible habit of accidentally re-creating the procs in user databases. Usually whichever I’m building test data in, like Stack…

Filtered Indexes and Variables: Less Doom and Gloom

Last Updated March 12, 2018

Indexing, SQL Server

It Is Known That when you use filtered indexes, they get ignored when your queries are parameterized. This is a Plan Caching Thing©, of course. The simplest example is a bit column with a filtered index. If your index is on WHERE Bit = 1, it doesn’t have data for WHERE Bit = 0. That…

Build Me A Build: What Would You Do?

Last Updated February 14, 2017

We already know what I’d do In my last post, I talked about the desktop I’m building with my hardware budget for this year (and, uh, some of next year’s, too…). Based on awesome feedback from you all, I made a few changes to the specs. And holy cow, ordering made things even more different.…

Filtered Statistics Follow-up

Last Updated May 26, 2020

Parameter Sniffing, SQL Server, Statistics

During our pre-con in Seattle A really sharp lady brought up using filtered statistics, and for a good reason. She has some big tables, and with just 200 histogram steps, you can miss out on a lot of information about data distribution when you have millions or billions of rows in a table. There’s simply…

Is NOLOCK Ever The Right Choice?

Last Updated February 13, 2017

Locking, Blocking, and Isolation Levels, SQL Server

I’ve poked a lot of fun at NOLOCK I mean, it sucks. You can get incorrect data, and it can throw errors if your data is particularly volatile. Generally, outside of testing a query or grabbing some sample data, I’m going to avoid it. Crappy in every language But what if… What if it’s about…

Hidden in SQL Server 2017 CTP v1.1: sys.dm_db_stats_histogram

Last Updated May 12, 2017

SQL Server 2017, Statistics

It’s Friday night, so I’m waiting for new CTP Releases As soon as I got the email, I started reading the release notes. Some interesting stuff, of course. Batch mode queries now support “memory grant feedback loops,” which learn from memory used during query execution and adjusts on subsequent query executions; this can allow more…

Indexing Temp Tables

Last Updated February 13, 2017

Execution Plans, Indexing, SQL Server, TempDB

People often don’t give this thought Which is a shame, because I see people sticking fairly large amount of data into temp tables. On the rare occurrence that I do see them indexed, it’s a nonclustered index on a column or two. The optimzer promptly ignores this index while you select 10 columns and join…

Things I Would Love to Put In sp_BlitzCache

Last Updated April 21, 2017

Execution Plans, First Responder Kit, SQL Server

This is my favorite script to work on Getting stuff to work sp_BlitzCache is so rewarding because XML querying is so ridiculous. I wish Mikael Eriksson would write a book already; it’d make my life a whole lot easier. Along the way, I see a lot of things that I would love to warn people…

Selective XML Indexes: Not Bad At All

Last Updated February 14, 2017

Indexing, SQL Server

Promises, Promises I said I’d follow up on this, and here it is. I’m by no means the world’s foremost authority on these, I just played with a simple example until I figured out what worked the best for my query. So you can all play along at home, I’m using XML we all have…

Date Math In The WHERE Clause

Last Updated February 13, 2017

Execution Plans, SQL Server

Oh, THAT SARGability I realize that not everyone has a Full Metal Pocket Protector, and that we can’t all spend our days plunging the depths of query and index tuning to eek out every CPU cycle and I/O operation from our servers. I mean, I don’t even do that. Most of the time I’m just…

Spills SQL Server Doesn’t Warn You About

Last Updated February 13, 2017

Execution Plans, SQL Server

Don’t make me spill Table variables get a lot of bad press, and they deserve it. They are to query performance what the TSA is to air travel. No one’s sure what they’re doing, but they’ve been taking forever to do it. One particular thing that irks me about them (table variables, now) is that…

The Brighter Side of SQL Server’s future

Last Updated November 22, 2016

Believe it or not, I sometimes like things SP1 for 2016 has a bunch of cool new tricks: I’m pretty psyched on the Wait Stats stuff in actual execution plans. I hope it eventually comes in some form to cached plans, or at least Query Store plans, but this is a good start! Even if…

Stupid Things I’ve Done With SQL

Last Updated February 13, 2017

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

Top 5 Overlooked Index Features

Last Updated February 13, 2017

Indexing, SQL Server

I see a lot of indexes Most of them are pretty vanilla. Some are pretty memorable, like one that I saw with 93 INCLUDES. A lot of things that people could do with indexes get overlooked, either because they don’t know about them, or they’re not sure when they’ll help. So, here goes my attempt…

Can Indexes My Query Doesn’t Use Help My Query?

Last Updated February 13, 2017

Indexing, SQL Server

This came up during Office Hours And I love questions like this, because they reveal some interesting underpinnings of how the cardinality estimator works. It was something along the lines of “I have a slow query, and when I add an index the query goes faster even though it doesn’t use the index I created.”…

A Gentle Reminder That Corruption Stinks

Last Updated February 13, 2017

CHECKDB and Corruption, SQL Server

Seriously DBCC CHECKDB (YourDatabaseHere) WITH NO_INFOMSGS, ALL_ERRORMSGS; Oh. It’s broken. Brent says: normally, we don’t post client screenshots on the blog because it’s hard to sanitize the sensitive data. This time, not so much. Storage took care of this for us.

Why You Should Test Your Queries Against Bigger Data

Last Updated February 14, 2017

Load Testing, SQL Server

I Like Testing Things Especially Brent’s patience. And I like watching people test things. SQL Server is sort of perfect for that. You make some changes, you hit f5, and you wait for something to finish. If it’s faster, you ship it. Right? I mean, we all know you just added NOLOCK hints anyway. A…

MAX Data Types Do WHAT?

Last Updated March 12, 2018

Users are horrible, I know You can just never rely on them to pass in reasonable search strings. When you write stored procedures to retrieve information for them, it’s really tempting to define all your string variables as MAX length. As long as the actual data type, N/VARCHAR matches, you’re cool, right? Avoid that pesky…