Feeds:
Posts
Comments

Archive for the ‘Dynamic Management Views and Functions’ Category

SQL Server database Maintenance plays an important role to improve database performance. One of the important aspects of maintenance is SQL Server log, even if you configured it properly, you should keep an eye on it and time to time, you should do its maintenance. To do the maintenance, you need to know the size of SQL Server log, used size and its percentage as well. There are multiple ways to achieve it. One of the famous methods of DBCC command is DBCC SQLPERF to find these details. I also have been using this DBCC Command since SQL Server 2005. It works fine but the only problem with this DBCC command is that if you need to manipulate its result set further, you need to place its result set in the temporary table and manipulate.

A few days ago, I was working on database log size and looking for some alternate solution. After some research, I found one undocumented dynamic management view that is shipped with SQL Server 2012 namely dm_db_log_space_usage. It gives the log size of the database and other details in bytes, you can convert it in MBs and manipulate its result set further (no need for temporary table) as well. Given below is the script to demonstrate how it works.

USE AdventureWorks2012
GO
SELECT
 DB_NAME(database_id) AS [Database Name],
 ROUND(CONVERT(FLOAT,total_log_size_in_bytes/1024)/1024,2) AS [Log Size (MB)],
 ROUND(CONVERT(FLOAT,used_log_space_in_bytes/1024)/1024,2) AS [Log Used size (MB)],
 ROUND(used_log_space_in_percent,2) AS [Log Used % (MB)]
FROM
sys.dm_db_log_space_usage

--OUTPUT

SQL Server log size.1.1

Advertisements

Read Full Post »

sys.dm_os_sys_info is one of the handy dynamic management views when it comes to query about the miscellaneous information of computer, resources & consumption by SQL Server. sys.dm_os_sys_info is with SQL Server family since SQL Server 2005, but whenever there is a new version of SQL Server, it always brings modification / additional helpful information in this view. But this modification / addition may  break your code if you don’t consider these changes with respect to versions. To avoid any such errors, I have prepared a comparison sheet of different versions of sys.dm_os_sys_info along with the changes to make life far easier.

Given below is the screen image of the comparison sheet. (Also attached Excel file for version 2007 & above):

sys.dm_os_sys_info.1.1

Reference : MSDN

Read Full Post »

sys.dm_db_database_page_allocations is one of the dynamic management functions shipped with SQL Server 2012. As its name implies, it does the same; it gives the information as to how objects keep data in different pages and its allocation in the databases.

This function is somehow same as undocumented DBCC IND command but it provides much more information than DBCC IND that we will discuss later in this article.

Syntax :

sys.dm_db_database_page_allocations
(@DatabaseId , @TableId , @IndexId , @PartionID , @Mode)

Parameters :
@DatabaseId :You need to pass the required database ID. This parameter is mandatory and data type of this argument is small integer.
@TableId:You need to pass the required table ID. This parameter is optional and data type of this argument is integer.
@IndexId:You need to pass the required Index ID. This parameter is optional and data type of this argument is integer.
@PartionID:You need to pass the required Partion ID. This parameter is optional and data type of this argument is integer.
@Mode:You need to pass the required Mode. This parameter is mandatory and data type of this argument is nvarchar(64). In this argument we must pass only ‘DETAILED’ OR ‘LIMITED’.

Purpose :

As we know that SQL Server stores data in the pages and whenever we need to view this information in earlier versions (2005, 2008) of SQL Server we need to invoke an undocumented command DBCC IND to get the info. The problem with this command is that if you want to further manipulate its result set you need to insert its record set in a temporary table and it also has a limited level of information.
In SQL SERVER 2012, sys.dm_db_database_page_allocations came as a replacement for the DBCC IND command and it gives more information about the internal storage of the data.

Given below are the major differences between sys.dm_db_database_page_allocations and DBCC IND.

S.No sys.dm_db_database_page_allocations DBCC IND

1

It is an in line function, so you can easily manipulate its result set without using any temporary table. You need temporary table to manipulate its result set.
2 It gives you the information about unallocated pages of the object as well. It gives you the information about allocated pages of the object only.
3 It gives you the complete details of internal storage including allocated unit details. It does not give you the complete details of internal storage including allocated unit details.
4 You can query all the database objects allocation at a time. You can query only one object at a time.

Given are column comparisons between sys.dm_db_database_page_allocations and DBCC IND command.  Also highlighted are the new fields introduced in this function.

dm_db_database_page_allocation

Let me explain few examples to explain this function :

Example 1 :
In this example, this function will give you the page allocation of “Sales” table only.

Select * from sys.dm_db_database_page_allocations(DB_ID(), object_id('Sales'),NULL,NULL,'DETAILED')

Example 2 :
In this example, this function will give you the page allocation for all objects including system objects.

Select * from sys.dm_db_database_page_allocations(DB_ID(), NULL ,NULL,NULL,'DETAILED')

Reference : CONNECT

Read Full Post »

sys.dm_exec_describe_first_result_set_for_object is one of the dynamic management functions introduced in SQL server 2012. This function also displays the first result set metadata information like “dm_exec_describe_first_result_set”  dynamic management function, but the major difference is“dm_exec_describe_first_result_set” use for SQL queries and “sys.dm_exec_describe_first_result_set_for_object” use for objects like stored procedure, triggers.

Syntax :

sys.dm_exec_describe_first_result_set_for_object
    ( @object_id , @include_browse_information )

Parameters :
@object_id : Object ID of stored procedure and trigger only. Data type is integer.
@include_browse_information : It can be from 0 to 2. using this parameter, function returns the additional information. Data type is bit.

Purpose :

The purpose of sys.dm_exec_describe_first_result_set_for_object  is to view the metadata information of first result set of any stored procedure or trigger. Lets say, if any procedure or trigger having more than one result set then the meta data information will be displayed for the first result set only.

Note : sys.dm_exec_describe_first_result_set_for_object can not display the meta data of other than “stored procedure or trigger”.

Let me explain it with simple examples :

Example 1 : (View metadata information of first result set of the Stored Procedure) when @include_browse_information=0

Use AdventureWorks2012
GO
Select * from
sys.dm_exec_describe_first_result_set_for_object
(object_id('[dbo].[UDP_Employee]'),0)
--OUTPUT
--Given below are the few columns from the result set. Other than that, there are other important information also available to give you complete details of metadata.
--But if the @include_browse_information=0 then this function will not give you the source data (Source Database, Source Schema, Source Table, Source Column) information.

Example 2 : (View metadata information of first result set of the Stored Procedure) when @include_browse_information=1

Use AdventureWorks2012
GO
Select * from
sys.dm_exec_describe_first_result_set_for_object
(object_id('[dbo].[UDP_Employee]'),1)
--OUTPUT
--If the @include_browse_information=1 then this function will give you the source data (Source database, Source Schema, Source table, Source column) information including the information available in @include_browse_information=0

Example 3 : (View metadata information of first result set of the Stored Procedure having two result set) when @include_browse_information=0
Lets create a stored procedure having two result set.

Create Procedure SP_test
As
Select top 2 [DepartmentID],[Name] from [HumanResources].[Department] order by [DepartmentID]

Select top 2 [BusinessEntityID],[JobTitle] from [HumanResources].[Employee] order by [BusinessEntityID]
GO
EXEC SP_test

Lets view the meta data information of the first result set of “SP_test” stored procedure using “sys.dm_exec_describe_first_result_set_for_object”

Use AdventureWorks2012
GO
Select * from
sys.dm_exec_describe_first_result_set_for_object
(object_id('[dbo].[SP_test]'),0)
--OUTPUT
--Given below are the few columns from the first result set Only.

Example 4 : View metadata information of View
In this example, “sys.dm_exec_describe_first_result_set_for_object” will generate an error because nothing except stored procedure or triggers is allowed.

Use AdventureWorks2012
GO
Select * from
sys.dm_exec_describe_first_result_set_for_object
(object_id('[HumanResources].[vEmployee]'),1)
--OUTPUT
--In this case all values will be NULL expect error information columns in the end of the result set.
--Given below is the screen images of the last columns.

Summary :
sys.dm_exec_describe_first_result_set_for_object gives the meta data information of the first result set of any stored procedure and trigger, but it differs from different @include_browse_information parameter.

  1. This function can filter the meta data column as per the requirement.
  2. This function returns the data in a tabular form, so you can utilize it in any other function or procedure.

Reference : MSDN

Read Full Post »

In this article we will discuss the new dynamic management function namely “sys.dm_exec_describe_first_result_set”. The functionality of this function is almost similar  to “sp_describe_first_result_set” system stored procedure, but the implementation (Syntax) and usage is different.

Syntax :

sys.dm_exec_describe_first_result(@tsql, @params, @include_browse_information)

“sp_describe_first_result_set” provides the detailed meta data on the basis of different@include_browse_information parameters. But lets say if we need only two columns from this meta data info and you would like to query it like a table, both requirements are not fulfilled by “sp_describe_first_result_set” stored procedure, because it does not return the data as a tabular form and also you cannot query only few columns. In this case you can use “sys.dm_exec_describe_first_result_set” function to fulfill both your requirements in addition to the “sp_describe_first_result_set” functionality.

Let me explain the difference with the help of simple example :


---sp_describe_first_result_set
USE AdventureWorks2012;
GO
EXEC sp_describe_first_result_set @tsql =N'SELECT * FROM HumanResources.Department'

---sys.dm_exec_describe_first_result_set
USE AdventureWorks2012;
GO
SELECT [column_ordinal],[name],[system_type_id]   FROM sys.dm_exec_describe_first_result_set
(N'SELECT * FROM HumanResources.Department', null, 0) ;

--Output

Summary :
This function has the same functionality as “sp_describe_first_result_set” but the two main differences are given below :

  1. This function can filter the meta data column as per the requirement.
  2. This function returns the data in a tabular form, so you can utilize it in any other function or procedure.

Reference : MSDN

Read Full Post »