SQL Server 2005 integrates a new schema named sys. This new schema includes
system views that are split into two categories:
- Schema views (e.g., tables, databases, indexes), which describe the metadata
of the database and its objects
- Dynamic management views (prefixed with dm_), which describe the current
status of the instance
By combining the schema views of
sys.tables (which describe the tables in the
current database) and sys.dm_db_index_
usage_stats (which describe the usage of
indexes), you can easily identify the most
accessed tables in your database. Any table
has at least one associated entry in sys.dm_
db_index_usage_stats, even if no index or
primary key has been created for it. If no
indexes are available, a heap will be present.
For each index or heap, the sys.dm_db_ index_usage_stats view provides the
fol- lowing fields:
- user_seeks (specifies the number of seeks)
- user_scans (specifies the number of scans)
- user_lookups (specifies the number of lookups)
Computing the sum of these three statistics for each table’s index or
heap is a good way to identify the most accessed (in read mode) tables in a
database.The SQL query in Listing 2, for
example, uses makes this computation, then displays tables in order from least
to most accessed.
Dynamic information isn’t limited to system views.You can use many new
system functions (e.g., sys.dm_db_index_physical_ stats, which replaces the
DBCC SHOW-CONTIG statement) to get dynamic information on some objects. I recommend
that you explore this new source information because it can help you monitor
and fine-tune your server.
—Arnaud Aubert
http://www.magesi.com
End of Article
