Databases Reference
In-Depth Information
This query returns the name and several other properties for every clustered and nonclustered index
in your database, sorted by the date on which statistics on that index were last updated. This can
help you track down performance problems caused by out of date statistics that could be causing the
SQL Server Query Optimizer to choose a poorly performing execution plan. I like to use this query
to discover whether I have old statistics on my more volatile and important tables in the database.
Unless you have a compelling reason not to, it is usually a very good idea to have SQL Server
automatically create statistics and automatically update them as the data changes in your tables.
Especially for OLTP workloads, I usually like to enable the Auto Update Statistics Asynchronously
database setting, which allows the Query Optimizer to use existing statistics while new ones are
being generated (instead of waiting for the new ones to be created). This can give you more predict-
able query performance instead of taking a big performance hit during a statistics update operation.
NOTE
It is also a good practice to manually update statistics on a periodic
basis as part of your regular database maintenance. Even under Auto Update
Statistics, statistics are not updated the moment data changes. To keep the
update frequency from conl icting with normal query workloads, the auto
update is only triggered when a certain threshold of data change has occurred.
Performing periodic manual statistics updates ensures you always have up to
date statistics.
Next, using the query shown in Listing 15-51, you will i nd out which indexes in the current data-
base have the most fragmentation.
LISTING 15-51:
Fragmentation information for all indexes
-- Get fragmentation info for all indexes
-- above a certain size in the current database
-- Note: This could take some time on a very large database
SELECT DB_NAME(database_id) AS [Database Name],
OBJECT_NAME(ps.OBJECT_ID) AS [Object Name],
i.name AS [Index Name], ps.index_id, index_type_desc,
avg_fragmentation_in_percent, fragment_count, page_count
FROM sys.dm_db_index_physical_stats(DB_ID(),NULL, NULL, NULL ,'LIMITED') AS ps
INNER JOIN sys.indexes AS i WITH (NOLOCK)
ON ps.[object_id] = i.[object_id]
AND ps.index_id = i.index_id
WHERE database_id = DB_ID()
AND page_count > 500
ORDER BY avg_fragmentation_in_percent DESC OPTION (RECOMPILE);
-- Helps determine whether you have fragmentation in your relational indexes
-- and how effective your index maintenance strategy is
Search WWH ::
Custom Search