Metadata Collection - SQL Server Integration Services Design Patterns - page 4

SQL Server exposes a wealth of information through catalog views, system func-
tions, dynamic management views (DMVs), and dynamic management functions
(DMFs). Let's briefly examine some of the metadata you will be using in this chapter.
Tip SQL Server Books Online is a great resource for learning more about the types
of metadata available in SQL Server. Try searching for “metadata functions,” “catalog
views,” and “DMVs” for more information.
sys.dm_os_performance_counters
The sys.dm_os_performance_counters DMV returns server performance
counters on areas including memory, wait stats, and transactions. This DMV is useful
for reporting file sizes, page life expectancy, page reads and writes per second, and
transactions per second, to name but a few.
sys.dm_db_index_usage_stats
The sys.dm_db_index_usage_stats DMV contains information on index util-
ization. Specifically, a counter is incremented every time a seek, scan, lookup, or up-
date is performed on an index. These counters are reinitialized whenever the SQL
Server service is started. If you do not see a row in this DMV for a particular index, it
means that a seek, scan, lookup, or update has not yet been performed on that index
since the last server reboot.
sys.dm_os_sys_info
The sys.dm_os_sys_info DMV contains information about server resources.
Perhaps the most frequently used piece of information in this DMV is the sqlserv-
er_start_time column, which tells you the last time the SQL Server service was
started.
sys.tables