Part 4
SQL Server Metadata
Objectives
Metadata is data that describes other data.
Metadata is exposed through a series of
Catalog views :
• Information schema views
• Dynamic management views and functions
• System functions
• System stored procedures
Introducing Metadata Objects
Catalog views reside in the sys schema.
Information schema views reside in the INFORMATION_SCHEMA
schema, less detail than catalog views but are based on the
ISO standards.
Some views of INFORMATION_SCHEMA
INFORMATION_SCHEMA.TABLES returns one row for
each table or view in the current database.
INFORMATION_SCHEMA.TABLE_PREVILEGES returns one
row for each table privilege
INFORMATION_SCHEMA.COLUMNS returns one row for
each column.
INFORMATION_SCHEMA.COLUMN_PREVILEGES returns
one row for each column that has a privilege
Some views of sys schema
[Link] contains one row per database in
the instance of SQL Server.
sys.database_files contains a row per file of a
database as stored in the database itself.
[Link] contains a row for each user-defined,
schema-scoped object that is created within a
database.
[Link] returns a row for each user table in SQL
Server.
[Link] returns a row for each column of an
object that has columns, such as views or tables
Introducing Metadata Objects
Many dynamic management views (DMVs) are available in
SQL Server :
• provide information about the current state of the
instance
• use for troubleshooting and tuning performance
Server-Level and Instance-Level Metadata
Server-level metadata supplys configuration
information or troubleshoot an issue, do not have
access to the underlying operating system.
Example, dm_server category of DMVs offers
views that allow check the status of serveraudits
• view SQL Server’s Registry keys
• find the location of memory dump files
• find details of the instance’s services.
Exposing Registry Values
sys.dm_server_registry DMV exposes key registry entries
pertaining to instance.
Exposing Service Details
sys.dm_server_services exposes details of the services the
instance
Analyzing Buffer Cache Usage
• dm_os category of DMV exposes 41 objects that contain
information about the current status of SQLOS.
• Use sys.dm_os_buffer_descriptors DMV to determine the
percentage of the buffer cache each database is using on the
instance.
Metadata for Capacity Planning
SQL Server exposes metadata providing
information about the current size and usage of
database files to plan ahead and arrange
additional capacity
Exposing File Stats
• sys.dm_db_file_space_usage DMV returns details
of the space used within each data file of the
database.
• sys.dm_io_virtual_file_stats DMV returns IO statistics for
the database and log files of the database to determine the
amount of data being written to each file and warn you of
high IO stalls.
• sys.master_files catalog view returns a record for every file
within every database on the instance.
Use three above metadata objects to produce powerful
reports that can help you with capacity planning and
diagnosing performance issues.
Exposing File Stats
Exposing File Stats
Analyzing Drive Space with xp_fixeddrives
Metadata for Troubleshooting and
Performance Tuning
Retrieving Perfmon Counters
sys_dm_os_performance_counters DMV exposes the SQL Server
Perfmon counters within SQL Server.
SQL Server Memory Manager: “Memory Grants Pending”
Represents the current number of processes waiting for a
workspace memory grant.
Retrieving Perfmon Counters
Capturing the number of lock requests that are occurring per
second over the space of one minute.
"Lock requests/sec" is a general measure of SQL Server load.
Almost every request requires locks, so if the system is busy, you
will have a lot of locks
Retrieving Perfmon Counters
Capturing the plan cache hit ratio for the instance. It gives the
ratio of the data pages found and read from the SQL Server buffer
cache and all data page requests, recommended value for Buffer
Cache Hit Ratio is over 90
Retrieving Perfmon Counters
SQL Server latches are a special type of low-level system locks which are held
as long as the physical operation lasts on the memory page in order to protect
memory consistency.
Capture the Average Latch Wait Time (ms) counter.
Capture the value and its corresponding base counter twice.
Average Latch Wait Time (ms) =
(Second – First) “Average Latch Wait Time (ms)”
/ (Second – First) “Average Latch Wait Time
base”
Analyzing Waits
Waits are a natural aspect of any RDBMS, but they can also
indicate a performance bottleneck.
Resource waits occur when a thread requires access to an object,
but that object is already in use, and therefore, the thread has to
wait.
waiting to take a lock out on an object,
waiting for a disk resource to respond
Queue waits occur when a thread is idle and is waiting for a task
to be assigned.
External waits occur when a thread is waiting for an external
resource, such as a linked server.
Each query is likely to alternate between the three states as it
progresses.
Analyzing Waits
sys.dm_os_wait_stats returns details of the cumulative waits for
each wait type.
To find the highest waits over a defined period, you need to
sample the data twice and then deduct the first sample from the
second sample
Database Metadata
sys.dm_db_page_info (DatabaseId, FileId, PageId, Mode ) returns
one row that contains the header information from the page,
including the object_id, index_id, and partition_id.
sys.fn_PageResCracker (page_resource) returns the db_id, file_id,
and page_id.
sys.dm_exec_requests returns information about each request that is
executing in SQL Server.
[Link] contains information about processes that are
running on an instance of SQL Server
Database Metadata
Metadata-Driven Automation
sys.dm_db_page_info (DatabaseId, FileId, PageId, Mode ) returns
one row that contains the header information from the page,
including the object_id, index_id, and partition_id.
sys.fn_PageResCracker (page_resource) returns the db_id, file_id,
and page_id.
sys.dm_exec_requests returns information about each request that is
executing in SQL Server.
[Link] contains information about processes that are
running on an instance of SQL Server