development

Monitoring

Moving parts SQL Server keeps on growing. With every new edition, you get more features, feature enhancements, and uh, “feature enhancements”. As I’m writing this, SQL Server 2005 is less than a week away from support ending, and SQL Server 2016 is up to RC2. Brent’s retrospective post got me thinking a bit. We went…

One weird trick for managing a bunch of servers

Last Updated April 28, 2016

Let’s face it, most people don’t have just one SQL Server How many they tell Microsoft they have is another matter, but let the record show that I don’t condone licensing dishonesty. But going one step further, most places… Well, they’re ‘lucky’ if they have one DBA, never mind a team. Everyone else: Give me…

Stats Week: Messin’ With Statistics

Last Updated March 23, 2017

SQL Server, Statistics

If there’s one thing living in Texas has taught me It’s that people are very paranoid that you may Mess With it. Even in Austin, where the citizenry demand weirdness, they are vehemently opposed to any form of Messing, unless it results in mayonnaise-based dipping sauce. Me? I like Messing With stuff. Today we’re going…

Stats Week: Do Query Predicates Affect Histogram Step Creation?

Last Updated May 26, 2020

Execution Plans, Parameter Sniffing, SQL Server

Auto Create Statistics is your friend It’s not perfect, but 99% of the time I’d rather have imperfect statistics than no statistics. This question struck me as interesting, because the optimizer will totally sniff parameters to compile an initial plan. If you don’t have index statistics, or system statistics already on a column in a…

Indexes Helping Indexes

Last Updated February 13, 2017

This post isn’t going to solve any problems for you It was just something I stumbled on that struck me as funny, while working on a demo for something else. Brent didn’t believe it at first, so I thought I’d share with the class. Blog. You, Your Name Here. Hiya! So there I was, creating…

Psst… Hey buddy, you want some E-Discovery?

Last Updated February 9, 2017

kCura Relativity, SQL Server

One year ago today! Well, sort of one year ago. Who knows when this thing will get published? Only God and Brent. And part of the calendar year fell during a leap year, which was just plain cruel, like a two part Buffy episode where the second part is the first episode of the next…

Unique Indexes and Row Modifications: Weird

Last Updated March 12, 2018

Confession time This started off with me reading a blurb in the release notes about SQL Server 2016 CTP 3.3. The blurb in question is about statistics. They’re so cool! Do they get fragmented? NO! Stop trying to defragment them, you little monkey. Autostats improvements in CTP 3.3 Previously, statistics were automatically recalculated when the…

Still Serial After All These Years

Last Updated March 29, 2018

With each new version of SQL comes a slew of new stuff While some changes are cosmetic, others bewildering, and the rest falling somewhere between “who cares about JSON?” and “OH MY GOD TAKE MY MONEY!”, but not really my money, because I only buy developer edition. Aaron Bertrand has done a better job finding,…

Improved diagnostics for query execution plans that involve residual predicate pushdown

Last Updated February 13, 2017

Execution Plans, SQL Server

I love stuff like this! Even though it’s not on my list of dream features, it’s pretty neat. Getting new views into what SQL is doing when queries execute is pretty cool. You can read the short and gory details at the KB here: Improved diagnostics for query execution plans that involve residual predicate pushdown…

Filtered Indexes: Just Add Includes

Last Updated May 9, 2018

I found a quirky thing recently While playing with filtered indexes, I noticed something odd. By ‘playing with’ I mean ‘calling them horrible names’ and ‘admiring the way other platforms implemented them‘. I sort of wrote about a similar topic in discussing indexing for windowing functions. It turns out that a recent annoyance could also…

Trace Flag 2330: Who needs missing index requests?

Last Updated February 13, 2017

Bad Idea Jeans, Indexing, SQL Server

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…

The case for Query Store in tempdb

Last Updated December 4, 2020

Execution Plans, Query Store (QDS), TempDB

Query Store is so cool Billed as a flight data recorder for SQL Server, the Query Store is a repository of execution plan information, like the plan cache, except a bit more actionable. And it has a GUI. “Try looking into that place where you dare not look! You’ll find me there, staring out at you!”…

Window Functions and Cruel Defaults

Last Updated March 12, 2018

SQL Server, T-SQL

My First Post Here… Well, my first technical post, was about how the default index creation method is OFFLINE. If you want that sweet, sweet Enterpri$e Edition ONLINE goodness, you need to specify it. It’s been a while since that one; almost six months to the day. So here’s another one! But Window Functions Are…

Performance Benefits of Unique Indexes

Last Updated June 7, 2020

SQL server loves unique indexes Why? Because it’s lazy. Just like you. If you had to spend all day flipping pages around, you’d probably be even lazier. Thank Codd someone figured out how to make a computer do it. There’s some code below, along with some screen shots, but… TL;DR SQL is generally pretty happy…

Clustered Index key columns in Nonclustered Indexes

Last Updated March 12, 2018

Columnstore Indexes, Indexing, SQL Server

Clustered indexes are fundamental And I’m not just saying that because Kendra is my spiritual adviser! They are not ~a copy~ of the table, they are the table, ordered by the column(s) you choose as the key. It could be one. It could be a few. It could be a GUID! But that’s for another…

Logical Query Processing

Last Updated March 12, 2018

Execution Plans, SQL Server

You can’t do that on management studio Recently, while working with a client, I did something in a query that they were mystified by. I didn’t think much of it, but I thought it might be useful to you, dear readers, as well. Along with an explanation. Here’s a sample query that takes advantage of…

Indexing for GROUP BY

Last Updated March 12, 2018

It’s not glamorous And on your list of things that aren’t going fast enough, it’s probably pretty low. But you can get some pretty dramatic gains from indexes that cover columns you’re performing aggregations on. We’ll take a quick walk down demo lane in a moment, using the Stack Overflow database. Query outta nowhere! Transact-SQL…

And Party and Alt Shift

Last Updated April 14, 2015

This is a cool SSMS trick I picked up a while back Though not nearly as far back as I wish I had. It’s so cool I made a .gif of it in action. When you’re done putting your socks back on, I’ll tell you how it works. This .gif was brought to you by…

SELECT INTO and non-nullable columns

Last Updated April 9, 2015