Indexing

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

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

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

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

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

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

Execution Plans, GroupBy Conference, Indexing, Videos

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

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…

When Does SARGability Matter Most?

December 27, 2018

!erehT iH I know what you’re thinking. Another post about how you should just never do this one thing and all your queries will magically end up faster. Just watch the video. For more reading, check out these posts: Sargability: Why %string% Is Slow Optional Parameters and Missing Index Requests Computed Columns: Reversing Data…

Do Functions Stop You From Using Indexes?

December 20, 2018

Indexing, T-SQL

Say I’ve got a function in my WHERE clause: Transact-SQL SELECT DisplayName FROM dbo.Users WHERE LTRIM(RTRIM(DisplayName)) = 'Brent Ozar'; 123 SELECT DisplayName FROM dbo.Users WHERE LTRIM(RTRIM(DisplayName)) = 'Brent Ozar'; If I have an index on DisplayName, will SQL Server use it? Sure: Function in the WHERE clause Even though SQL Server can’t seek to “Brent Ozar,” it will…

Indexed View Matching With GROUP BY And DISTINCT

December 5, 2018

Bit Of A Kick I’ve been playing with indexed views a little bit lately for some demos in my Bits Precon. There are a whole bunch of limitations in creating indexed views. One of them is that you can’t base the query on DISTINCT. Fair enough, but you can do GROUP BY. And what’s pretty…

Indexed View Creation And Underlying Indexes

December 4, 2018