Retrieve metadata information in SQL Server 2012

Introduction

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

Background

SQL server contains metadata information in compatibility views,catalog views and DMO. this article focused on exploring these sources. SQL server maintain backward compatibility with available compatibility views. although columns/schema of views might differ from previous version.

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.
• Only way to get data from system table is through DAC (dedicated administrator connection).
• System table are for only internal purpose. we dont use it in general purposes.

User can not read data of any system table. Error 208 is recevied 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 sysdatabases display only 12 columns.

All catalog views are designed in object oriented model. base columns in child views are derive from base views.
eq : sys.objects is base view contains 12 superset columns while sys.tables is derived view contains 12 same superset column 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 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 partiton,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 is using DMV sys.dm_tran_active_transactions to return details of all active transaction 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 is using 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 is using sys.dm_tran_locks DMV. it display available transaction locks in 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 is using 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 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 article explain SQL Server metadata information. it will provide internal details of all available SQL Server 2012 Metadata.

History

No updates available