I didn’t expect anything for free in index maintenance. After all, it takes a lot of CPU and transaction log space to remove fragmentation.
Let’s talk about two things that SQL Server does for us: one statistic is created per index we create, and when we rebuild that index, we get fresh statistics. We can also update statistics with full scan on our own, without needing to rebuild the index.
For all the time we spend rebuilding our indexes, often the big performance gain we get is from the fresh statistics and those statistics are made with full scan.
Let’s get some proof
Using StackOverflow2010, here’s my index.
CREATE NONCLUSTERED INDEX ix_Location_CreationDate ON dbo.Users
(Location, CreationDate)
INCLUDE (Reputation);
GO
Now, let’s update the statistics with the automatic sample size.
UPDATE STATISTICS Users (ix_Location_CreationDate);
I say that index needs to be rebuilt, at least for the purposes of this demo.
ALTER INDEX ix_Location_CreationDate ON dbo.Users REBUILD;Now taking a second look at the statistics:
Moral of the post
Statistics are automatically updated with the default sample size when a certain amount of data changes. I won’t talk about what that certain amount is, because it changes in major versions of SQL Server. What that means to us is that data changes can occasionally change statistics, which can mean worse performance.
Then, when the index maintenance jobs run and rebuild the indexes, the statistics get a full sample of the data. This means that your performance might improve from fresh statistics alone.
Stay tuned!
First, thanks for the post. It’s always good to see folks thinking about index and stats maintenance.
You say the index needs to be rebuilt in your example but, as Brent Ozar says, rebuilding an index is a really expensive way to update statistics. Rebuilds should only be done if high logical fragmentation or low page density indicates that a Rebuild needs to be done.
It is possible to rebuild statistics (both index and column stats… Rebuilds don’t rebuild column stats) with a Full Scan without doing an index Rebuild as follows…
UPDATE STATISTICS SchemaName.TableOrIndexedViewObjectName (Index_or_Stats_Name) WITH FULLSCAN;
Hey Jeff, thanks for the kind words. You bring up some great points, and I agree that it’s easier to update statistics with fullscan than to spend the time to rebuild an index.
In my example about the index needing to be rebuilt, I was trying to say that I needed to rebuild the index to make my demo work. I can see how the phrasing could say otherwise, so I think I need to rewrite.
As for updating statistics in general, it is definitely cheaper and more effective to spend server resources on updating statistics rather than spending time rebuilding indexes. I’m sure entire posts could be written on the right strategy for maintenance. I wrote this post to create a demo showing that index rebuilds come with fresh statistics, but it’s definitely not the only way to get a set of statistics with full scan.
I had heard this before. Had to fight (here) to get index maintenance performed. With this ‘evidence’ I have added justification.
THANKS!