MSSQL Dynamic Management Views (DMVs) for indexes
SQLCAT Blog posts on SQL Server 2005 Dynamic Management Views (DMVs):
"Since indexes provide an alternative to a table scan, and because DMVs expose index usage counters, we can compare the cost vs. the benefit of indexes. This comparison will include the maintenance cost of keeping the index up to date, versus the benefit of reads e.g. when an index can be used in lieu of a table scan. Keep in mind that an update or delete operation involves both a read which is first required to determine whether a row qualifies for the update operation, and a write if a row is found to qualify. In an insert operation, only writes will be performed on all indexes. Consequently, in an insert-intensive workload, writes will exceed reads. In an update-intensive (updates and deletes) workload, read and write counts are generally close assuming there are not a lot of records not found. In read-intensive workloads, read counts will exceed write counts. Referential constraints such as foreign keys require other read activity (for inserts, updates, and deletes) to ensure referential integrity is maintained."
Enjoy
Friday, December 30, 2005 3:55 PM
