SQL Server 2012 Metadata

• Download source - 2.8 KB

Introduction

Learning SQL Server internals begins with collecting metadata information of system. SQL server stores this information in different system objects. This article is based on exploring all available sources for metadata. These system objects require administrative privileges to access information. This information helps the user to understand how the database engine maintains all system details.

Background

SQL server contains metadata information in compatibility views, catalog views and DMO. This article focuses on exploring these sources. SQL Server maintains backward compatibility with available compatibility views although columns/schema of views might differ from previous versions.

Using the Code

Get the system objects details. SysAdmin has visibility of most of the system objects.

Select * from Sys.objects where type_desc='SYSTEM_TABLE'

• Result showing 74 system tables are available in master DB.
• The only way to get data from system table is through DAC (dedicated administrator connection).
• System tables are for internal purposes only. We don't use it in general purposes.

User cannot read data of any system table. Error 208 is received while reading of any system table.

Select * from sys.syssqlguides

Display Database Metadata in SQL Server 2012

There are 3 different ways available in SQL Server 2012 to read system metadata.

1. Compatiblity views (support for backward compatibility version 2000) like sysdatabases, sysobjects, sysprocess.
2. Catalog views (available in sys schema) like objects, databases, servers, users, etc.
3. Database management objects.

Display Metadata Using Compatibility Views

Select * from sys.sysdatabases

Display Metadata Using Catalog Views

select * from sys.databases

Output of compatibility views and catalog views are always different. Catalog views for sys.databases display 68 columns while compatibility views for sysdatabases display only 12 columns.

All catalog views are designed in object oriented model. Base columns in child views are derived from base views.
Example: sys.objects is base view that contains 12 superset columns while sys.tables is derived view that contains 12 same superset columns of base view with 16 subset columns specific to tables.

Select * from sys.objects

Select * from sys.tables

Display Metadata using Dynamic Management Objects

• DMO starts with name sys.dm_
• It contains objects and function.
• It is also known as DMV (dynamic management views).
• It allows to get internal behavior of SQL Server.

Available DMV Categories in SQL Server

• dm_exec_*: It provides information related to execution of code and associated connection.
• dm_os_*: It provides information related to low level system such as memory and scheduling.
• dm_tran_*: It provides details about current transaction.
• dm_logpool*: It provides details about logpool used to manage SQL Server 2012 log cache. It is a new feature added to make log records more easily retrievable when needed by features such as AlwaysOn.
• dm_io_*: It provides I/O details of data and log file of given database id.
• dm_db_*: It provides DB internal details like physical used space, available partition, missing indexes, index usage, etc.

dm_exec_* DMV Category

Select session_id,host_name,program_name,login_name,nt_domain,nt_user_name,
login_time,last_request_start_time,last_request_end_time,datediff
	(minute,login_time,last_request_start_time) ElapsedMinute,
reads,writes,logical_reads
from sys.dm_exec_sessions where original_login_name=ORIGINAL_LOGIN() and status='running'

This query returns details of current user session which includes host name, user name, user login time, total elapsed time in session, total read/write operations, etc. I have applied filter for displaying details of all running sessions only.

dm_os_* DMV Category

Select memory_object_address,parent_address,pages_in_bytes,creation_options,bytes_used,type,name,
memory_node_id,creation_time,page_size_in_bytes,max_pages_in_bytes,page_allocator_address
from sys.dm_os_memory_objects

This query returns details of internal memory objects. It provides details of all memory object address, total available pages in memory objects, page size, memory objects type, page allocator address, etc.

dm_tran_* DMV Category

Select transaction_id,name,transaction_begin_time,transaction_type,transaction_uow,transaction_state,
transaction_status,transaction_status2,dtc_state,dtc_status,dtc_isolation_level,
	filestream_transaction_id 
from sys.dm_tran_active_transactions

This query uses DMV sys.dm_tran_active_transactions to return details of all active transactions in current database server. It returns transaction begin time, transaction status, etc.

Select transaction_id,transaction_sequence_num,transaction_is_snapshot,first_snapshot_sequence_num,
last_transaction_sequence_num,first_useful_sequence_num 
from sys.dm_tran_current_transaction

This query uses sys.dm_tran_current_transaction DMV. It returns details about current transaction only.

Select resource_type,resource_subtype,resource_database_id,resource_description,
	resource_associated_entity_id,
resource_lock_partition,request_mode,request_type,request_status,
	request_reference_count,request_lifetime,
request_session_id,request_exec_context_id,request_request_id,request_owner_type,request_owner_id,
request_owner_guid,request_owner_lockspace_id,lock_owner_address 
from sys.dm_tran_locks

This query uses sys.dm_tran_locks DMV. It displays available transaction locks in the current database.

dm_logpool* DMV Category

Select hash_hit_total_search_length,hash_miss_total_search_length,hash_hits,
	hash_misses,hash_bucket_count,
mem_status_stamp,mem_status,logpoolmgr_count,total_pages,private_pages 
from sys.dm_logpool_stats

This query uses sys.dm_logpool_stats DMV. It returns current logpool status including total hash hits, total hash count, total pages available in logpool, etc.

Select bucket_no,database_id,recovery_unit_id,log_block_id,cache_buffer 
from sys.dm_logpool_hashentries

This query returns available logpool hash entries used to save data for Always on feature.

dm_io_* DMV Category

Select DB_NAME(database_id)DBName,file_id,sample_ms,num_of_reads,num_of_bytes_read,io_stall_read_ms,
num_of_writes,num_of_bytes_written,io_stall_write_ms,io_stall,size_on_disk_bytes,file_handle 
from sys.dm_io_virtual_file_stats(DB_ID('SQLGD'),1)	
Union
Select DB_NAME(database_id)DBName,file_id,sample_ms,num_of_reads,num_of_bytes_read,io_stall_read_ms,
num_of_writes,num_of_bytes_written,io_stall_write_ms,io_stall,size_on_disk_bytes,file_handle 
from sys.dm_io_virtual_file_stats(DB_ID('SQLGD'),2)

This query returns I/O details of given database mdf and ldf file. It display information like number of reads, total bytes read, number of writes, total bytes write, file size on disk, etc.

dm_db_* DMV Category

Select db_name(database_id)DBName,object_name(object_id)TableName,index_id,partition_id,rowset_id,
allocation_unit_id,allocation_unit_type,
allocation_unit_type_desc,clone_state_desc,extent_file_id,extent_page_id,
allocated_page_iam_file_id,allocated_page_iam_page_id,allocated_page_file_id,allocated_page_page_id,
is_allocated,is_iam_page,is_mixed_page_allocation,page_free_space_percent,page_type,page_type_desc,
page_level,next_page_file_id,next_page_page_id,previous_page_file_id,
	previous_page_page_id,is_page_compressed,
has_ghost_records 
from sys.dm_db_database_page_allocations(DB_ID('SQLGD'),OBJECT_ID('SQLGD.dbo.Product'),null,null,null)

This query returns the given database and table details like total indexes in table, available partitions, allocated unit type, allocated page file, page free space, etc.

Points of Interest

This tip explains SQL Server metadata information. It will provide internal details of all available SQL Server 2012 metadata.