sql server

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…

Tracking tempdb growth using Extended Events

Last Updated February 13, 2017

Erik Darling

SQL Server, TempDB

Everyone knows tempdb is a wierd [sic] place Brent refers to it as a public toilet. I agree with that sentiment. It will let anyone in to do whatever they want. Recently I was trying to track down what was causing tempdb’s log to grow. I was thinking about using a Trace to do it,…

Trace Flag 2330: Who needs missing index requests?

Last Updated February 13, 2017

Erik Darling

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

Erik Darling

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!”…

SELECT INTO and non-nullable columns

Last Updated April 9, 2015

Erik Darling

SQL Server

SELECT…INTO is one of my favorite SQL Server features. It’s great for creating table skeletons with false WHERE clauses (1=2), moving a real table to a staged/temp location for testing, etc. In SQL Server 2014 It acquired the ability to go parallel, which is pretty neat, but that’s not what we’re talking about here. It…

SQL Server Table Partitioning Tutorial: Videos and Scripts

Last Updated December 29, 2021

Kendra Little

Partitioning, SQL Server, Videos

There’s a secret to learning about SQL Server’s table partitioning feature: you need to get your hands on some code and really play with it in a test environment. In this tutorial, Kendra will walk you through test scripts that set up a sample table partitioning environment. These scripts demonstrate gotchas to look out for…

Long Term Backup Storage With Amazon Glacier

Last Updated February 9, 2017

Jeremiah Peschka

Amazon Web Services (AWS)

A while back, Jes asked who’s taking your backups. Making sure you have good backups is important. How much thought are you giving to handling historical backups? Right now, there’s a really good chance that you’re using a solution based on tape. While tape backups work, there’s a better way. How Are You Archiving Backups Right…

The Trouble with Keys

Last Updated September 25, 2012

Jeremiah Peschka

Architecture

Scaling up is hard: big hardware gets expensive fast. Scaling out is equally difficult; interesting design problems creep in to scale out solutions. One of the more troublesome issues architects face when scaling out is the issue of distributing identity. It’s often advantageous for object identity to be unique and portable across all database instances…