Blog - Brent Ozar

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:

CREATE TABLE dbo.Employees
(
    ID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
    EmployeeName VARCHAR(50) NULL
);
GO
INSERT INTO dbo.Employees(EmployeeName) VALUES (NULL);
GO
CREATE INDEX IX_EmployeeName ON dbo.Employees (EmployeeName);
GO
/* Then get the execution plan for this: */
SELECT *
  FROM dbo.Employees
  WHERE EmployeeName IS NULL;
GO

CREATE TABLE dbo.Employees

(

ID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,

EmployeeName VARCHAR(50) NULL

);

INSERT INTO dbo.Employees(EmployeeName) VALUES (NULL);

CREATE INDEX IX_EmployeeName ON dbo.Employees (EmployeeName);

/* Then get the execution plan for this: */

SELECT *

FROM dbo.Employees

WHERE EmployeeName IS NULL;

Here’s the plan:

Yep, SQL Server stores the nulls in the index. That makes sense, really, because sooner or later, you’re going to want to find the rows with nulls, and when you’ve only got a few nulls in a big table, the index can help a lot there.

To learn more about this stuff in action, watch my free 90-minute course, How to Think Like the SQL Server Engine.

What sessions do you want to see at GroupBy next month?

March 6, 2019

Brent Ozar

GroupBy Conference

GroupBy.org is a free online conference run by the community, for the community. You get to pick the sessions by voting.

Voting is open now through March 15 for the next event, happening April 11. Here are some of the sessions you can choose from:

Just Enough Database Theory for Power BI
Linux OS Fundamentals for the SQL Admin
Marketing for SQL Consultants and Business Owners
PowerShell for the SQL DBA
SQL Server Memory Internals & Troubleshooting
Think Like a Certification Exam

Go vote now. It’s totally free, no registration required.

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 have Read Committed Snapshot (RCSI), Snapshot Isolation (SI), or Availability Groups (AGs) enabled
RCSI or SI was enabled, or the database was added into an Availability Group
During the deletions, a 14-byte timestamp was added to each deleted row to support RCSI/SI/AG reads

To demonstrate the growth, I took the Stack Overflow database (which doesn’t have RCSI enabled) and created a bunch of indexes on the Posts table. I checked index sizes with sp_BlitzIndex @Mode = 2 (copy/pasted into a spreadsheet, and cleaned up a little to maximize info density):

sp_BlitzIndex before

I then deleted about half of the rows:

BEGIN TRAN;
DELETE dbo.Posts WHERE Id % 2 = 0;
GO

BEGIN TRAN;

DELETE dbo.Posts WHERE Id % 2 = 0;

Amusingly, while the deletes were happening, the data file was growing to accommodate the timestamps, too! The SSMS Disk Usage Report shows the growth events – here’s just the top to illustrate:

SSMS Disk Usage Report

(Gotta love a demo where deletes make the database grow.) While the delete was running, I ran sp_BlitzIndex again. Note that the clustered index has less rows, but its size has already grown by about 1.5GB. The nonclustered indexes on AcceptedAnswerId have grown dramatically – they’re indexes on a small value that’s mostly null, so their index sizes have nearly doubled!

I don’t have to wait for the deletion to finish to prove that out, so I’ll stop the demo there. Point being: when you do big deletions on a table that was implemented before RCSI, SI, or AGs were enabled, the indexes (including the clustered) can actually grow to accommodate the addition of the version store timestamp.

Things you might take away from this:

RCSI, SI, and AGs have cost overheads you might not initially consider
After enabling RCSI, SI, or an AG, rebuild your indexes – not that it’s going to make your life better, it’s just that you can control when the page splits, object growth, fragmentation, and logged activity happens instead of waiting around for it to hit when you’re in a rush to do something
Offline index rebuilds seems to have a related effect too – Swart noted in a comment that when he tested them, they were rebuilt without the 14-byte version stamp, meaning that subsequent updates and deletes incurred the page splits and object growth
I have really strange hobbies

[Video] Demoing SQL Server 2019’s new Accelerated Database Recovery

March 4, 2019

Brent Ozar

SQL Server 2019

Wouldn’t it be nice if rollbacks just finished instantly? Wouldn’t you love for startup times to be near-zero even when SQL Server crashed just as someone in the middle of a transaction? How much would you pay for all this? (Well, I’m a little afraid to ask that, since we don’t know yet whether this is an Enterprise-only feature, but dang, I sure hope not.)

Here’s how Accelerated Database Recovery works in SQL Server 2019:

In the video, I’m using the Stack Overflow 2013 (50GB) database with this script:

/* Grow data & log files out to give us some room to work */
USE [master]
GO
ALTER DATABASE [StackOverflow2013] SET COMPATIBILITY_LEVEL = 150
GO
ALTER DATABASE [StackOverflow2013] 
  MODIFY FILE ( NAME = N'StackOverflow2013_1', SIZE = 15360000KB )
GO
ALTER DATABASE [StackOverflow2013] 
  MODIFY FILE ( NAME = N'StackOverflow2013_2', SIZE = 15360000KB )
GO
ALTER DATABASE [StackOverflow2013] 
  MODIFY FILE ( NAME = N'StackOverflow2013_3', SIZE = 15360000KB )
GO
ALTER DATABASE [StackOverflow2013] 
  MODIFY FILE ( NAME = N'StackOverflow2013_4', SIZE = 15360000KB )
GO
ALTER DATABASE [StackOverflow2013] 
  MODIFY FILE ( NAME = N'StackOverflow2013_log', SIZE = 10240000KB )
GO
/* Enable RCSI - not technically required, but more on that in another post. */
ALTER DATABASE [StackOverflow2013] SET READ_COMMITTED_SNAPSHOT ON WITH NO_WAIT
GO
USE StackOverflow2013;
GO
/* Rebuild the table to get the RCSI version time stamps: */
ALTER INDEX ALL ON dbo.Users REBUILD;

/* Set up an index to make our transaction slower: */
CREATE INDEX IX_Reputation_LastAccessDate 
  ON dbo.Users(Reputation, LastAccessDate);

SELECT * FROM sys.databases;



/* Run a transaction that does a lot of work: */
BEGIN TRAN
UPDATE dbo.Users
  SET Reputation = 1000000,
      LastAccessDate = GETDATE();
GO
ROLLBACK

ALTER DATABASE StackOverflow2013 SET ACCELERATED_DATABASE_RECOVERY = ON;
SELECT * FROM sys.databases;

/* Run a transaction that does a lot of work: */
BEGIN TRAN
UPDATE dbo.Users
  SET Reputation = 1000000,
      LastAccessDate = GETDATE();
GO

/* How much space is being used? */
SELECT TOP 100 * FROM sys.dm_tran_persistent_version_store_stats
SELECT TOP 100 * FROM sys.dm_tran_top_version_generators
SELECT TOP 100 * FROM sys.dm_tran_version_store
SELECT TOP 100 * FROM sys.dm_tran_version_store_space_usage

/* Clustered index */
SELECT * FROM sys.dm_db_index_physical_stats(5, 149575571, 1, 0, 'DETAILED')

/* Nonclustered index */
SELECT * FROM sys.dm_db_index_physical_stats(5, 149575571, 2, 0, 'DETAILED')

EXEC sp_persistent_version_store;

ROLLBACK;
GO

EXEC sp_persistent_version_cleanup;

/* Grow data & log files out to give us some room to work */

USE [master]

ALTER DATABASE [StackOverflow2013] SET COMPATIBILITY_LEVEL = 150