Benjamin Nevarez : Indexes

Rebuilding Indexes vs. Updating Statistics

Ben Nevarez — Tue, 06 Oct 2009 07:04:00 GMT

One of the questions I was asked recently while speaking at user groups, was regarding the order that jobs like rebuilding indexes or updating statistics should be performed as part of the database maintenance activities. Then I started writing this post about this topic on the weekend but was interrupted several times, including one of them to watch the premiere on VH1 of the movie Anvil: The Story of Anvil.

In general, the order should not matter, at least if you carefully consider these important points:

1) By default, the UPDATE STATISTICS statement uses only a sample of records of the table. Using UPDATE STATISTICS WITH FULLSCAN will scan the entire table.

2) By default, the UPDATE STATISTICS statement updates both index and column statistics. Using the COLUMNS option will update column statistics only. Using the INDEX option will update index statistics only.

3) Rebuilding an index, for example by using ALTER INDEX … REBUILD, will also update index statistics with the equivalent of using WITH FULLSCAN. Rebuilding indexes does not update column statistics.

4) Reorganizing an index, for example using ALTER INDEX … REORGANIZE, does not update any statistics.

So depending on your maintenance jobs and scripts several scenarios can exist.

The simplest scenario is if you want to rebuild all the indexes and update all the statistics. As mentioned before, if you rebuild all your indexes then all the index statistics will also be updated by scanning all the rows on the table. Then you just need to update your column statistics by running UPDATE STATISTICS WITH FULLSCAN, COLUMNS. Since the first job only updates index statistics and the second one only updates column statistics, it does not matter which one you execute first.

Some other more complicated scenarios include when you have a job which rebuilds your indexes depending on their fragmentation level. In these cases perhaps you want to update only those index statistics that were not touched by the index rebuild job, plus all the column statistics.

Of course, the worst case scenario would be if you first rebuild your indexes, which also updates the index statistics by scanning the entire table, and later you run UPDATE STATISTICS using the default values, which again updates the index statistics but this time with a default sample. Not only are you updating your index statistics twice but you are overwriting the better of the two choices.

Let me show you how these commands work with some examples using the AdventureWorks database. Create a new table dbo.SalesOrderDetail

select * into dbo.SalesOrderDetail

from sales.SalesOrderDetail

The next query uses the sys.stats catalog view and shows that there are no statistics objects for the new table.

select name, auto_created, stats_date(object_id, stats_id) as update_date from sys.stats

where object_id = object_id('dbo.SalesOrderDetail')

Use this query again to inspect the status of the statistics after each of the following commands. Now run the following query

select * from dbo.SalesOrderDetail

where SalesOrderID = 43670 and OrderQty = 1

Use the previous sys.stats query to verify that two statistics objects were created, one for the SalesOrderID column and another one for the OrderQty column (they both have names starting with _WA_Sys as shown in the next figure). Now create the following index and again run the query to verify that a new statistics object for the ProductID column has been created. Notice the value of the auto_created column which tells if the statistics were created by the query optimizer.

create index ix_ProductID on dbo.SalesOrderDetail(ProductID)

Run the next command to update the column statistics only. You can validate that only the column statistics were updated by looking at the update_date column which uses the STATS_DATE function to display the last date the statistics were updated.

update statistics dbo.SalesOrderDetail with fullscan, columns

This command will do the same for the index statistics

update statistics dbo.SalesOrderDetail with fullscan, index

These commands will update both index and column statistics

update statistics dbo.SalesOrderDetail with fullscan

update statistics dbo.SalesOrderDetail with fullscan, all

See how an index rebuild only updates index statistics

alter index ix_ProductID on dbo.SalesOrderDetail rebuild

Here you can verify that reorganizing an index does not update statistics

alter index ix_ProductID on dbo.SalesOrderDetail reorganize

Finally, remove the table you have just created

drop table dbo.SalesOrderDetail

The Missing Indexes Feature

Ben Nevarez — Fri, 25 Sep 2009 06:19:00 GMT

Since I will be speaking about the Query Optimizer at the coming PASS Summit, I have been preparing my presentation and at the same time blogging about it. This time I will describe the Missing Indexes feature, seen from the point of view of the Query Optimizer.

We know that it is the job of the Query Optimizer to find an efficient execution plan for a query. But we rarely see the Query Optimizer directly giving us indications about what it needs to produce a better execution plan. One of these cases is the Missing Indexes feature, which was introduced with SQL Server 2005.

The Query Optimizer defines what the best indexes for a query are, and if these indexes do not exist, it will make this information available in the XML plan and the sys.dm_db_missing_index DMVs. And of course, by showing this information the Query Optimizer is also warning you that it might not be selecting an efficient plan. This information shows which indexes may be helpful to improve the performance of your query. You can even use SQL Server 2008 Management Studio to display the CREATE INDEX commands needed to create these indexes, as shown later.

However, although this information about missing indexes is very helpful, this feature should not be used as a tuning tool and should not replace your own index analysis. Database administrators and developers should be aware of its limitations, as described on the Books Online entry ‘Limitations of the Missing Indexes Feature’.

So let us take a quick look to see how this feature works. Create a dbo.SalesOrderDetail table on the AdventureWorks database with the following command

select *

into dbo.SalesOrderDetail

from sales.SalesOrderDetail

Run this query and ask for a graphical or XML execution plan

select *

from dbo.SalesOrderDetail

where SalesOrderID = 43670

This query can benefit from an index on the SalesOrderID column but no missing indexes information is shown this time. One limitation of the Missing Indexes feature is that it does not work on a trivial plan optimization, like in this case. You can verify that this is a trivial plan by looking at the graphical plan properties (Optimization Level shows as TRIVIAL) or by looking at the XML plan (StatementOptmLevel="TRIVIAL).

You can avoid the trivial plan optimization by using more complex features. In our case we are just going to create a non related index

create index ix_ProductID on dbo.SalesOrderDetail(ProductID)

Note that the index created will not be used by our previous query but the query will no longer qualify for a trivial plan. Run the query again. This time the XML plan will contain something like this

</ColumnGroup>

</MissingIndex>

</MissingIndexGroup>

</MissingIndexes>

And if you look at the graphical plan (only SQL Server 2008 Management Studio) you will see a Missing Index warning and a CREATE INDEX command

You can right-click on the graphical plan and select Missing Index Details to see the CREATE INDEX command that can be used to create this index

Missing Index Details from SQLQuery1.sql

The Query Processor estimates that implementing the following index could improve the query cost by 99.703%.

USE [AdventureWorks]

CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]

ON [dbo].[SalesOrderDetail] ([SalesOrderID])

Create the recommended index after you provide a name to it. This time if you run the same query again and look at the execution plan you will see that an Index Seek operator is using the index you have just created and both the Missing Index warning and the MissingIndex element of the XML plan are gone.

Finally, remove the dbo.SalesOrderDetail table you have just created.

drop table dbo.SalesOrderDetail