Indexing

How to Think Like the SQL Server Engine: Adding a Nonclustered Index

October 17, 2019

How to Think Like the SQL Server Engine, Indexing

When we left off in the last post, our users kept running this query, and they want it to be really fast: Transact-SQL SELECT Id FROM dbo.Users WHERE LastAccessDate > '2014/07/01' ORDER BY LastAccessDate; 1234 SELECT Id FROM dbo.Users WHERE LastAccessDate > '2014/07/01' ORDER BY LastAccessDate; Let’s pre-bake the data by creating a copy of…

DBA Training Plan 17: Should You Partition Your Tables?

September 3, 2019

Brent Ozar

DBA Training Plan, Partitioning

In the last episode, I talked about knowing when it’s time to scale out: identifying when our data is getting to be so large that we have to split it across multiple servers, and I explained why that is so challenging. But what about table partitioning – SQL Server’s ability to break up a single…

Research Paper Week: Automatic Indexing in Azure SQL DB

August 21, 2019

Brent Ozar

Architecture, Azure SQL DB, Indexing

Before I give you the link to the next research paper in this week’s series, I wanna give you a few questions to think about: If you had to build something to automatically add indexes to SQL Server databases, where would you start? What data would you use to make your decisions? How would you…

Research Paper Week: Query Execution in Column-Oriented Database Systems

August 20, 2019

Brent Ozar

Columnstore Indexes

This week, I’m sharing some of my favorite papers that I’ve read. Sometimes they’re about future technologies that haven’t shipped yet – and may never ship! Sometimes, like this one, they’re not from Microsoft at all, but from someone else in the industry who’s explaining a problem that we face in SQL Server. SQL Server…

DBA Training Plan 10: Managing Index Fragmentation

August 13, 2019

Brent Ozar

DBA Training Plan, Index Maintenance

In the last episode, we looked at your index designs with the output of sp_BlitzIndex. You might have gotten a little overwhelmed what with all the different warnings (and all the learning resources!) You might have thought, “Is there an easier way?” “Is there a way I can just run a job, and have something…

DBA Training Plan 9: My 3 Index Guidelines

August 12, 2019

Brent Ozar

DBA Training Plan, Indexing

In our last episode, we used sp_BlitzIndex @Mode = 2 to get to know the contents of our database, sorted our indexes from biggest to smallest, and started asking questions about leftover backup tables that we probably didn’t need to keep around anymore. Now that you’ve taken out the trash, let’s have a closer look…

DBA Training Plan 8: What’s Inside Your Own Databases

August 6, 2019

Brent Ozar

DBA Training Plan, Indexing

In the last episode of the DBA Training Plan, we talked about what’s inside the MDF file: a series of pages that store a database’s tables. Now, it’s time to check out the contents of YOUR database to understand what’s using the most space, what’s getting used the most often, and what you might be…

DBA Training Plan 7: How SQL Server Stores Stuff in MDF Files

July 31, 2019

Brent Ozar

DBA Training Plan, Indexing

So far in the DBA Training Plan, we’ve been working hard to make sure the contents of our MDF files are backed up, corruption-free, and secured. Now, uh…what’s actually inside them? Inside each of your database data files (MDFs, although technically you can use any extension you want, even PDF or MP3), SQL Server stores…

Yet Another Way Missing Index Requests are Misleading

July 2, 2019

Brent Ozar

Indexing

Graduates of my Mastering Index Tuning class will already be familiar with the handful of ways the missing index DMVs and plan suggestions are just utterly insane. Let’s add another oddity to the mix: the usage counts aren’t necessarily correct, either. To prove it, Let’s take MattDM’s Stack Exchange query, “Who brings in the crowds?” He’s…

Does the Rowmodctr Update for Non-Updating Updates?

May 20, 2019

Brent Ozar

Index Maintenance

Get Notebook – FAQ Update May 20 – make sure to read to the end for an update. Okay, look, it’s a mouthful of a blog post title, and there are only gonna be maybe six of us in the world who get excited enough to check this kind of thing, but if you’re in…

Index scans aren’t always bad, and index seeks aren’t always great.

April 16, 2019

Brent Ozar

Execution Plans, Indexing

Somewhere along the way in your career, you were told that: Index seeks are quick, lightweight operations Table scans are ugly, slow operations And ever since, you’ve kept an eye on your execution plans looking for those performance-intensive clustered index scans. When you see ’em, you go root ’em out, believing you’ve got a performance…

What happens when you cancel or kill a resumable index creation?

March 25, 2019

Brent Ozar

Indexing

SQL Server 2019 adds resumable online index creation, and it’s pretty spiffy: Transact-SQL CREATE INDEX IX_DisplayName ON dbo.Users(DisplayName) WITH (ONLINE = ON, RESUMABLE = ON, MAX_DURATION = 1); 12 CREATE INDEX IX_DisplayName ON dbo.Users(DisplayName) WITH (ONLINE = ON, RESUMABLE = ON, MAX_DURATION = 1); Those parameters mean: ONLINE = ON means you’ve got the money for…

Are nulls stored in a nonclustered index?

March 7, 2019

Brent Ozar

Indexing

When you index a nullable field, are the rows with nulls stored in the index? It’s easy enough to find out by creating a table with a nullable field, and then creating an index on it: Transact-SQL CREATE TABLE dbo.Employees ( ID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED, EmployeeName VARCHAR(50) NULL ); GO INSERT INTO…

Can deleting rows make a table…bigger?

March 5, 2019

Brent Ozar

Indexing

Michael J. Swart posted an interesting question: he had a large table with 7.5 billion rows and 5 indexes. When he deleted 10 million rows, he noticed that the indexes were getting larger, not smaller. Here’s one way that deletes can cause a table to grow: The rows were originally written when the database didn’t…

What does Azure SQL DB Automatic Index Tuning actually do, and when?

February 19, 2019

Brent Ozar

Azure SQL DB, Indexing

Azure SQL DB’s Automatic Tuning will create and drop indexes based on your workloads. It’s easy to enable – just go into your database in the Azure portal, Automatic Tuning, and then turn “on” for create and drop index: ENGAGE Let’s track what it does, and when. I set up Kendra Little‘s DDL trigger to log index changes, which…

How to Think Like the Engine – 2019 Edition

January 25, 2019

Brent Ozar

Indexing, SQL Server, Videos

You’re a developer or a DBA, and you’re comfortable writing queries to get the data you need. You’re much less comfortable trying to design the right indexes for your database server. In this series of videos, you’ll learn how the SQL Server engine looks at your indexes and builds your query results. In 90 minutes…

New Fundamentals of Index Tuning Course

January 23, 2019

Brent Ozar

Indexing

No Comments

You’re a developer who needs to make your application go faster, and you don’t want to (or can’t!) change the queries. You’ve never been to an official training class, and your manager isn’t about to spring for travel. In my new Fundamentals of Index Tuning course, you’ll learn: How to design indexes for a query…

sp_BlitzIndex's new statistics histograms at the bottom

How Should We Show Statistics Histograms in sp_BlitzIndex?

January 10, 2019

Brent Ozar

Indexing, sp_BlitzIndex

If you’re a graduate of my free How to Think Like the SQL Server Engine course – and you’d better be, dear reader – then you’re vaguely familiar with DBCC SHOW_STATISTICS. It’s a command that shows you the contents of a statistics histogram. When I’m doing the first two parts of the D.E.A.T.H. Method – (D)eduplicating identical…

The Pleasure Of Presenting With Joe Obbish

January 3, 2019

Erik Darling

Execution Plans, GroupBy Conference, Indexing, Videos

No Comments

Grouple On At the December 2018 GroupBy I had the honor of co-presenting with one of the smartest guys around, Joe Obbish. Here’s the video of our session, “The Problems With Optional Operators”, and a link to the resources. Enjoy! Resources: Slides and Demos

Why Do Some Indexes Create Faster Than Others?

January 3, 2019

Erik Darling

Execution Plans, Indexing

No Comments

To warn you ahead of time, this post is an exploration without an answer (yet). There’s some interesting stuff in here, but no conclusions. If that’s not your kind of post, feel free to skip it. If it is, well, here goes nothin’… Wild, Wild Life Creating indexes is kind of a funny thing. By…