Tuning Indexes - Professional SQL Server 2005 Performance Tuning - page 364

UPDATE STATISTICS
You can manually update a set of statistics or all the statistics for a specified index using this statement.
There are a couple of options that are worth commenting on:

❑ FULLSCAN: Calculates statistics based on all the data. This gives the most accurate results but

takes the most time to create. Without this option SQL Server does a quick scan on the object to
determine the minimum sample size required to get good results and uses that.

❑ RESAMPLE: Inherits the sample size that was used the last time the statistics were updated.

You can use sp_updatestats as a wrapper for UPDATE STATISTICS. This allows you to update all the
statistics in an entire database using this one statement.

ALTER INDEX REBUILD
When you rebuild an index, SQL Server takes advantage of the fact that you're ''touching'' every page
by using it as an opportunity to update the statistics with the equivalent of FULLSCAN as described in
the previous section. One customer had a nightly maintenance job that updated statistics with the default
sample size straight after they rebuilt their indexes! Not only was their maintenance period longer but
the result was less accurate statistics!

The following code will show you the creation date of the statistics for all the user-created indexes in the
current database:

SELECT object_name(o.id) AS 'Table Name',
i.name AS 'Index Name',
stats_date(i.id, i.indid) AS 'Statistics Date'
FROM sys.sysobjects o (NOLOCK),
sys.sysindexes i (NOLOCK)
WHERE o.id = i.id
AND o.type = 'u'
AND i.indid BETWEEN 1 AND 250
AND i.name NOT LIKE '_wa_sys%'
ORDER BY object_name(o.id), i.name
GO

Indexed Views
SQL Server allows you to create a clustered index on a view, which has the effect of persisting the view to
disk making it more like an actual table. You can even create non-clustered indexes on top of an indexed
view. The benefit of this is that aggregates in your view will be pre-calculated and you can pre-join tables
storing the results on disk.

The optimizer in the Developer and Enterprise Editions of SQL Server will even evaluate if an indexed
view would be useful without the view being referenced. In other editions you have to reference the view
manually and specify the NOEXPAND hint so SQL Server treats it like a normal table.

Indexed views are most useful in scenarios where there are lots of joins and aggregations. Fortunately,
you don't need to analyze that yourself; SQL Server comes with a tuning tool to help you decide.