598
Part V: Enterprise Data Management
To view operational index statistics, query the sys.dm_db_index_operational_stats dynamic
management. You can also check usage statistics by querying sys.dm_db_index_usage_stats and physical
statistics by querying sys.dm_db_index_physical_stats. The sys.dm_db_index_operational_
stats and sys.dm_db_index_physical_stats require that you provide input arguments or NULL values
for each of the inputs, such as database ID, object ID, index ID, and partition number.
To view the T-SQL code that sp_updatestats system stored procedure executes, run the sp_helptext sp_
updatestats command.Index Density
Index density refers to what percentage of the index pages contains data. If the index
density is low, SQL Server must read more pages from the disk to retrieve the index data.
The index’s fill factor refers to what percentage of the index page contains data when the
index is created, but the index density slowly alters during inserts, updates, and deletes.The ALTER INDEX REBUILD command completely rebuilds the index. Using this command
is essentially the equivalent of dropping and creating the index with the added benefi t to
allow the user to set the fi ll factor as the index is re-created. In contrast, the
ALTER INDEX REORGANIZE command repairs fragmentation to the index’s fi ll factor but
does not adjust the target fi ll factor.The following code re-creates all the indexes on the Frag table and sets the fi ll factor to 98
percent:USE tempdb;
ALTER INDEX ALL ON Frag REBUILD WITH (FILLFACTOR = 98);At this point, you have a couple of objects in the tempdb database that you need to clean
up. Use the following code to perform the task:DROP TABLE Frag;
DROP PROCEDURE Add100K;By default, ALTER INDEX REBUILD operation is an OFFLINE operation. In contrast,
ALTER INDEX REORGANIZE is always performed ONLINE. During the ALTER INDEX REBUILD operation,
the underlying tables and indexes are not available for queries and data modifi cation. But if you have SQL Server
Enterprise or Developer Editions, then SQL Server enables you to perform ONLINE index operations by using the
ONLINE=ON option. When you use ONLINE=ON option, exclusive table locks are held only for a short amount of
time, and then the tables and indexes are available for queries and data modifi cation.c22.indd 598c22.indd 598 7/31/2012 9:24:31 AM7/31/2012 9:24:31 AM
http://www.it-ebooks.info