17. SQL Server Metadata

Catalogue views are in SYS schema.

INFORMATION_SCHEMA based on ISO standards.

DMV - Dynamic Management Views and functions

DB_ID(), OBJECT_ID(), DATALENGTH()

Server-Level and Instance-Level Metadata

sys.dm_server_registry
  registry_key
  value_name
  value_data

Can find find listener port from registry also startup parameters, SQLArg%.

sys.dm_server_sources
sys.dm_os_buffer_descriptors
sys.dm_db_file_space_usage
sys.dm_io_virtual_file_stats
sys.master_files
xp_fixeddrives

Use of xp_cmdshell is against security best practice.

Metadata for Troubleshooting and Performance Tuning

sys.dm_os_performance_counters

• Running
• Runnable signal wait
• Suspended
  • Resource wait
  • Queue wait

sys.dm_os_wait_stats

Database Metadata

• < 2019 DBCC PAGE
• 2019+ sys.dm_db_page_info

Metadata-Driven Automation

• Rolling DB snapshots
• Rebuild fragmented indexes