Indexing

In our last cliffhanger episode, I said that if we ran this query: Transact-SQL UPDATE dbo.Users SET Age = Age + 1 WHERE Id = 643; 123 UPDATE dbo.Users SET Age = Age + 1 WHERE Id = 643; And we had this index: Transact-SQL CREATE INDEX IX_LastAccessDate_Id_DisplayName_Age ON dbo.Users(LastAccessDate, Id, DisplayName, Age); 12 CREATE INDEX IX_LastAccessDate_Id_DisplayName_Age ON…

How to Think Like the SQL Server Engine: Should Columns Go In the Key or the Includes?

November 7, 2019

In our last episode, in between crab rangoons, I had you create either one of these two indexes: Transact-SQL CREATE INDEX IX_LastAccessDate_Id_Includes ON dbo.Users(LastAccessDate, Id) INCLUDE (DisplayName, Age); GO CREATE INDEX IX_LastAccessDate_Id_DisplayName_Age ON dbo.Users(LastAccessDate, Id, DisplayName, Age); GO 1234567 CREATE INDEX IX_LastAccessDate_Id_Includes ON dbo.Users(LastAccessDate, Id) INCLUDE (DisplayName, Age);GOCREATE INDEX IX_LastAccessDate_Id_DisplayName_Age ON dbo.Users(LastAccessDate, Id, DisplayName, Age);GO And I said…

How to Think Like the SQL Server Engine: Building Wider Indexes to Deal with Bad T-SQL

November 6, 2019

In our last episode, we were running into problems with these two queries: Transact-SQL SELECT LastAccessDate, Id, DisplayName, Age FROM dbo.Users WHERE LastAccessDate BETWEEN '2018-08-31' AND '2018-09-01' ORDER BY LastAccessDate; SELECT LastAccessDate, Id, DisplayName, Age FROM dbo.Users WHERE CAST(LastAccessDate AS DATE) = '2018-08-31' ORDER BY LastAccessDate; 123456789 SELECT LastAccessDate, Id, DisplayName, Age FROM dbo.Users WHERE LastAccessDate BETWEEN…

How to Think Like the SQL Server Engine: What’s a Key Lookup?

October 22, 2019

In our last couple of queries, we’ve been using a simple query to find the Ids of everyone who accessed the system since mid-2014: 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; But Ids alone aren’t all that useful – so let’s…

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

October 17, 2019

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

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

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

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

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

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

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

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

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

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

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

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

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

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