| Downloads |
|---|
| 53878.zip |
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
