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.
- Compatiblity views (support for backward compatibility version 2000) like
sysdatabases, sysobjects, sysprocess. - Catalog views (available in
sys schema) like objects, databases, servers, users, etc. - 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.
