Product has been added to your cart.

Indexing

What’s Faster: IN or OR? Columnstore Edition

Last Updated September 11, 2023

Columnstore Indexes, Execution Plans

Pinal Dave recently ignited a storm of controversy when he quizzed readers about which one of these would be faster on AdventureWorks2019: SELECT * FROM Sales.SalesOrderHeader WHERE CustomerID IN (1, 2, 3); SELECT * FROM Sales.SalesOrderHeader WHERE CustomerID = 1 OR CustomerID = 2 OR CustomerID = 3; 123456789 SELECT *FROM Sales.SalesOrderHeaderWHERE CustomerID IN (1,…

Fundamentals of Columnstore

Learn Columnstore Indexes for Free This Week.

Last Updated September 18, 2022

Columnstore Indexes

Every weekday during the month of October, one of the modules from my Fundamentals classes will be absolutely free to watch. But you gotta watch it that day – you snooze, you lose. For this week’s Fundamentals of Columnstore class, you don’t have hands-on labs, and you’re not meant to follow along with the demos. Columnstore…

October is Free Fundamentals Month – And The Training Starts Now.

Last Updated October 8, 2022

Every weekday during the month of October, one of the modules from my Fundamentals classes will be absolutely free to watch. But you gotta watch it that day – you snooze, you lose. This week is Fundamentals of Index Tuning. To follow along with the labs this week & next week, you’ll need SQL Server 2017…

[Video] Fragmentation Explained in 20 Minutes at SQLBits

Last Updated August 30, 2022

Index Maintenance, Videos

What does fragmentation mean? How does it happen? Can you fix it with fill factor? Should you rebuild your indexes to fix it? At the SQLBits conference, I tried a new way of explaining it using markers and pieces of whiteboard paper as database pages, and doing inserts live on the fly. What you see…

How to Add Invisible Indexes in SQL Server Enterprise Edition

Last Updated July 19, 2022

Every now and then, a client says, “We want to add indexes to this third party application, but if we do, we’ll lose support.” No problem – enter indexed views. Say our vendor app is the Stack Overflow database, and the app constantly queries Users by display name: USE StackOverflow; GO DropIndexes; SET STATISTICS IO…

Columnstore Indexes are Finally Sorted in SQL Server 2022.

Last Updated July 12, 2022

Columnstore Indexes

There’s a widespread misconception that SQL Server’s columnstore indexes are like an index on every column. I debunk that myth in the first 30 minutes of my Fundamentals of Columnstore class, where I explain that a better way to think of them is that your table is broken up into groups of rows (1M rows…

Meme Week: Setting Fill Factor to Fix Fragmentation

Last Updated February 19, 2022

Index Maintenance

I just shake my head when I see people setting fill factor in an attempt to “fix” fragmentation. The default fill factor is 100%. That means during an index rebuild, SQL Server packs 100% of your 8KB pages with sweet, juicy, golden brown and delicious data. But somehow, some people have come to believe that’s bad,…

When Should You Use DESC in Indexes?

Last Updated January 25, 2022

The short answer is that if your query orders columns by a mix of ascending and descending order, back to back, then the index usually needs to match that same alternating order. Now, for the long answer. When you create indexes, you can either create them in ascending order – which is the default: CREATE INDEX…

New Year’s Resolution: Lose Weight in Your Database

Last Updated January 5, 2022

Made a New Year’s resolution to lose weight this year? Forget taking it off your waistline – take it off your database instead with: sp_BlitzIndex @GetAllDatabases = 1, @Mode = 2, @SortOrder = ‘size’ Or: sp_BlitzIndex @GetAllDatabases = 1, @Mode = 2, @SortOrder = ‘rows’ This produces an inventory of all the indexes sorted by…

You Probably Shouldn’t Index Your Temp Tables.

Last Updated August 17, 2021

When you’ve got a process that uses temp tables, and you want to speed it up, it can be tempting to index the temp table to help work get done more quickly. However, in most cases – not all, but most – that’s a bad idea. To explain why, I’m going to take a large…

Mastering Index Tuning

Stop Procrastinating: Master Index Tuning in 1 Month for $195.

Last Updated July 10, 2021

You’ve conquered my Fundamentals of Index Tuning and Fundamentals of Columnstore classes, and you’re ready to take it to the next level. It’s time for you to join my Mastering Index Tuning class. When I teach it live – the next one is July 19-21 – it’s 3 days of lectures and hands-on labs where you learn: How to…

Fundamentals of Columnstore

Lock Escalation Sucks on Columnstore Indexes.

Last Updated June 29, 2021

Columnstore Indexes

If you’ve got a regular rowstore table and you need to modify thousands of rows, you can use the fast ordered delete technique to delete rows in batches without hitting the lock escalation threshold. That’s great for rowstore indexes, but…columnstore indexes are different. To demo this technique, I’m going to use the setup from my…

Fundamentals of Columnstore

Learn Fundamentals of Columnstore for $1.

Last Updated July 3, 2021

Columnstore Indexes

Last week, I gave you a 1-week, $1 deadline, and 777 of you took me up on it. That’s awesome! I love having fun with stuff like this. Let’s do it again! You’ve got 1 week to learn the Fundamentals of Columnstore for $1, and sales close Saturday. Here’s the abstract: Your report queries are…

Learn Fundamentals of Index Tuning for $1.

Last Updated June 26, 2021

You’re lazy and overconfident. You’ve been reading this blog for a while, and you’ve been telling yourself, “Yeah, I’m pretty good at this database thing. I’ve got years of experience under my belt. I know the fundamentals.” You’ve told yourself, “Yeah, someday I’ll get to Brent’s How to Think Like the Engine class,” but…you’ve never…

Fundamentals of Columnstore

Free Columnstore Training Class on Tuesday

Last Updated April 1, 2021

Columnstore Indexes

Your report queries are too slow. You’ve tried throwing some hardware at it: your production SQL Server has 12 CPU cores or more, 128GB RAM, and SQL Server 2016 or newer. It’s still not enough to handle your growing data. It’s already up over 250GB, and they’re not letting you purge old data. Will columnstore…

Partitioned Tables Cause Longer Plan Compilation Times.

Last Updated February 26, 2021

Folks sometimes ask me, “When a table has more indexes, and SQL Server has more decisions to make, does that slow down execution plan generation?” Well, maybe, but the table design choice that really screws you on compilation time is partitioning. If you choose to partition your tables, even tiny simple queries can cause dramatically higher…

And it looks bad even in shades.

Free Webcast: Help! My SQL Server Maintenance is Taking Too Long!

Last Updated January 22, 2021

Backup and Recovery, Company News, Index Maintenance, Production Database Administration

“Time for a smoke break.” You manage growing SQL Server databases with shrinking nightly maintenance windows. You just don’t have enough time left each night to do the necessary backups, corruption checking, index maintenance, and data jobs that your users and apps want to run. Cloud storage isn’t helping the problem, either. Stop playing Tetris…

Autoparameterized Trivial Queries May Not Get Partition Elimination

Last Updated November 26, 2020

How’s that for a niche topic? You’re probably never going to know this, but since I had to figure it out the hard way, I’m writing this down so I don’t forget it: queries that get trivial optimization may not get partition elimination. This post is going to be a little on the long side…

When Do I Need to Use DESC in Indexes?

Last Updated November 30, 2020

If I take the Users table from any Stack Overflow database, put an index on Reputation, and write a query to find the top 100 users sorted by reputation, descending: CREATE INDEX Reputation ON dbo.Users (Reputation); SELECT TOP 100 * FROM dbo.Users ORDER BY Reputation DESC; 12345 CREATE INDEX Reputation ON dbo.Users (Reputation); SELECT TOP 100…

Want to use columnstore indexes? Take the ColumnScore test.

Last Updated September 27, 2020

Columnstore Indexes

When columnstore indexes first came out in SQL Server 2012, they didn’t get a lot of adoption. Adding a columnstore index made your entire table read-only. I often talk about how indexing is a tradeoff between fast reads and slow writes, but not a lot of folks can make their writes quite that slow. Thankfully, Microsoft…