SQL SERVER – Table Space Allocation Details using DMV

help blue SQL SERVER   Table Space Allocation Details using DMVUnderstanding SQL Server from inside-out is always something I have loved to learn from time to time. Recently, I was asking my good friend Balmukund on how are pages allocated inside SQL Server and what are the various ways to find the same. He was quick to bounce and say have you ever checked DBCC IND and EXTENTINFO commands. These undocumented commands have been there for a while and I have seen usage of these commands at a number of sessions.

In the evening, I was pleasantly surprised to see Balmukund call me. This doesn’t happen quite often and I was eager to know what the context was. He had called me to talk about a DMV (dm_db_database_page_allocations) and if I had seen. Many a times I have seen as techies, it is difficult to sleep if there is a problem at hand. And my friend was no different, he has given me a learning path to look at. I started to dig into this DMV and was pleasantly surprised to see various details.

dm_db_database_page_allocations

SQL Server 2012 introduces a new dynamic management function that replaces the old and undocumented DBCC IND and DBCC EXTENTINFO commands. DBCC IND and DBCC EXTENTINFO were widely used by the Microsoft Support team when working on customer issues on database storage or space issue. Examples are database shrink, excessive space usage by a table or index, know the linkages of pages and so on.

DBCC IND and DBCC EXTENTINFO had several limitations. Since it was a DBCC command, in order to perform filtering and advanced grouping, you had to first import the entire output into a temporary table and then perform processing. The use of the new dynamic management function solves all those limitations posed. To recollect, the name of the new dynamic management function is: Sys.dm_db_database_page_allocations()

There are a number of arguments that need to be passed as part of this function. These include:

  1. databaseId – database id [not null]
  2. tableId – object id or NULL
  3. indexId – index id or NULL
  4. partitionId – partition id or NULL
  5. Mode – LIMITED or DETAILED

The output of this dynamic management function includes all the pages and extents allocated for table, index or partition. If we supply NULL for the indexId and partitionId arguments, information about all pages and extents allocated to the table is returned.

The last argument “mode” determines if extended information is returned in the output. The output of this function includes information from the page header. Obtaining that information involves extra processing and consumption of resources. In order to keep the execution time within reasonable limits, the default for this argument is LIMITED. If you specifically need information for any of those columns, you need to use the DETAILED option for this argument.

The sample code to use this new dynamic management function is provided, as follows:

SELECT *
FROM sys.dm_db_database_page_allocations(
DB_ID('AdventureWorks2012'),
OBJECT_ID('HumanResources.Department'),
NULL , NULL ,
'LIMITED')
GO

Sample part output from this command looks like below. Here since we have made the Index as NULL, we get records for both the indexes defined on this table.

space 01 SQL SERVER   Table Space Allocation Details using DMV

SELECT *
FROM sys.dm_db_database_page_allocations(
DB_ID('AdventureWorks2012'),
OBJECT_ID('HumanResources.Department'),
NULL , NULL ,
'DETAILED')
GO

space 02 SQL SERVER   Table Space Allocation Details using DMV

Here is a detailed dump and partial additional fields like the Page_Type and page_type_desc which we can see. The sys.dm_db_database_page_allocations DMV also shows unallocated pages, system pages like PFS, GAM, IAM, and SGAM pages, as well as pages allocated to a table or index. DBCC IND only shows pages allocated to the table, so the results will not be one-to-one many-a-times if we were to compare. Also, this DMV returns information about the extent and can include pages still not allocated but the extent is allocated to this object. The best way is to check if the additional entries are there for is_allocated = 0 column.

Let me know if you have ever used this DMV before.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – How to See Active SQL Server Connections For Database

Another question received via email –

“How do I I know which user is connected to my database with how many connection?”

Here is the script which will give us answer to the question.

SELECT DB_NAME(dbid) AS DBName,
COUNT(dbid) AS NumberOfConnections,
loginame
FROM    sys.sysprocesses
GROUP BY dbid, loginame
ORDER BY DB_NAME(dbid)

Here is the resultset:

connectiondb SQL SERVER   How to See Active SQL Server Connections For Database

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Finding Last Backup Time for All Database – Last Full, Differential and Log Backup – Optimized

Four years ago, I wrote a blog post SQL SERVER – Finding Last Backup Time for All Database. It has been a very popular script. SQL Expert Sravani has posted a fantastic script which also displays last full, differential and log backup for the database – I have blogged about it here SQL SERVER – Finding Last Backup Time for All Database – Last Full, Differential and Log Backup.

Well, in technology, there is always space for improvement and we should always be ready to learn and progress. SQL Expert TheSQLGuru came up with further optimized script which also lists all the details which earlier blog post listed, and the script is very quick to execute. Here is the script.

SELECT d.name AS 'DATABASE_Name',
MAX(CASE WHEN bu.TYPE = 'D' THEN bu.LastBackupDate END) AS 'Full DB Backup Status',
MAX(CASE WHEN bu.TYPE = 'I' THEN bu.LastBackupDate END) AS 'Differential DB Backup Status',
MAX(CASE WHEN bu.TYPE = 'L' THEN bu.LastBackupDate END) AS 'Transaction DB Backup Status',
CASE d.recovery_model WHEN 1 THEN 'Full' WHEN 2 THEN 'Bulk Logged' WHEN 3 THEN 'Simple' END RecoveryModel
FROM MASTER.sys.databases d
LEFT OUTER JOIN (SELECT database_name, TYPE, MAX(backup_start_date) AS LastBackupDate
FROM msdb.dbo.backupset
GROUP BY database_name, TYPE) AS bu ON d.name = bu.database_name
GROUP BY d.Name, d.recovery_model

Thanks TheSQLGuru for excellent script, you win USD 20 Amazon Gift Card or INR 1000 Flipkart Gift Voucher. I have already sent you emails with details.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – sys.dm_xe_map_values – Reasons for Statement Recompilation

Sometime I feel I know a lot about SQL Server and very next moment, I realize that honestly I do not know much about this product. Earlier today, I had similar moments. I was playing with few DMVs and suddenly I ended up on the DMV sys.dm_xe_map_values. There are only four columns and one of the columns is a GUID.

The reason I ended up on this DMV was because I was asked a question what are the different reasons any statement can be recompiled. I knew few of the reasons why would any statement recompile but I was not aware of all the reasons for any statement recompilation. After doing some search on the internet and my older archived I figured out the reasons for Statement Recompilation using sails. dm_xe_map_values.

If you have collected trace using either SQL Server Profiler or an Extended Events session, you can capture SP:Recompile event in your trace. When you look at the event it displays the reason for recompiling the statement. However, when you run following statement in SQL Server Management Studio, you can find all the possible reasons for statement recompilation.

SELECT dxmv.name,
dxmv.map_key,
dxmv.map_value
FROM sys.dm_xe_map_values AS dxmv
WHERE dxmv.name = N'statement_recompile_cause'
ORDER BY dxmv.map_key;

When I ran this query, I got following 14 different reasons for statement recompilation. I personally was not aware of all the 14 and it was interesting learning for me.

map_key map_value
1 Schema changed
2 Statistics changed
3 Deferred compile
4 Set option change
5 Temp table changed
6 Remote rowset changed
7 For browse permissions changed
8 Query notification environment changed
9 PartitionView changed
10 Cursor options changed
11 Option (recompile) requested
12 Parameterized plan flushed
13 Test plan linearization
14 Plan affecting database version changed

It will be interesting to build a test case for each of the compilation reason, as it will give a great learning experience.

Click to Download Scripts

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Identify Last User Access of Table using T-SQL Script

During the TechEd India 2013 presentations I received a question how to identify when any table is accessed by any of the user. It seems people would like to know if the table was used in any part of query by any user. The best possible solution is to create database audit task and watch the database table access. However, sometime we all want shortcut even thought it is not accurate. Here is how you can use DMV to do so. However, please note that this DMV will get reset when database services or servers are restart. Let me know if you think I should modify this DMV and have some better alternatives.

SELECT DB_NAME(ius.[database_id]) AS [Database],
OBJECT_NAME(ius.[object_id]) AS [TableName],
MAX(ius.[last_user_lookup]) AS [last_user_lookup],
MAX(ius.[last_user_scan]) AS [last_user_scan],
MAX(ius.[last_user_seek]) AS [last_user_seek]
FROM sys.dm_db_index_usage_stats AS ius
WHERE ius.[database_id] = DB_ID()
AND
ius.[object_id] = OBJECT_ID('YourTableName')
GROUP BY ius.[database_id], ius.[object_id];

Remember to change your database context to your current database as well make sure that you insert your table name in the object_id condition.

DMV sys.dm_db_index_usage_stats has columns related to last user lookup, last user scan and last user seek. Any table which is accessed will either go for seek or scan. We can watch these columns and figure out when the table was used last. Which ever value among the last_user_lookup, last_user_scan and last_user_seek is latest is the last user access of the table.

Click to Download Scripts

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Find Referenced or Referencing Object in SQL Server using sys.sql_expression_dependencies

A very common question which I often receive are:

How do I find all the tables used in a particular stored procedure?

How do I know which stored procedures are using a particular table?

Both are valid question but before we see the answer of this question – let us understand two small concepts – Referenced and Referencing.

Here is the sample stored procedure.

CREATE PROCEDURE mySP
AS
SELECT
*
FROM Sales.Customer
GO

Reference: The table Sales.Customer is the reference object as it is being referenced in the stored procedure mySP.

Referencing: The stored procedure mySP is the referencing object as it is referencing Sales.Customer table.

http://www.pinaldave.com/bimg/references.jpg

Now we know what is referencing and referenced object. Let us run following queries. I am using AdventureWorks2012 as a sample database. If you do not have SQL Server 2012 here is the way to get SQL Server 2012 AdventureWorks database.

Find Referecing Objects of a particular object

Here we are finding all the objects which are using table Customer in their object definitions (regardless of the schema).

USE AdventureWorks
GO
SELECT
referencing_schema_name = SCHEMA_NAME(o.SCHEMA_ID),
referencing_object_name = o.name,
referencing_object_type_desc = o.type_desc,
referenced_schema_name,
referenced_object_name = referenced_entity_name,
referenced_object_type_desc = o1.type_desc,
referenced_server_name, referenced_database_name
--,sed.* -- Uncomment for all the columns
FROM
sys.sql_expression_dependencies sed
INNER JOIN
sys.objects o ON sed.referencing_id = o.[object_id]
LEFT OUTER JOIN
sys.objects o1 ON sed.referenced_id = o1.[object_id]
WHERE
referenced_entity_name = 'Customer'

http://www.pinaldave.com/bimg/references1.jpg

The above query will return all the objects which are referencing the table Customer.

Find Referenced Objects of a particular object

Here we are finding all the objects which are used in the view table vIndividualCustomer.

USE AdventureWorks
GO
SELECT
referencing_schema_name = SCHEMA_NAME(o.SCHEMA_ID),
referencing_object_name = o.name,
referencing_object_type_desc = o.type_desc,
referenced_schema_name,
referenced_object_name = referenced_entity_name,
referenced_object_type_desc = o1.type_desc,
referenced_server_name, referenced_database_name
--,sed.* -- Uncomment for all the columns
FROM
sys.sql_expression_dependencies sed
INNER JOIN
sys.objects o ON sed.referencing_id = o.[object_id]
LEFT OUTER JOIN
sys.objects o1 ON sed.referenced_id = o1.[object_id]
WHERE
o.name = 'vIndividualCustomer'

http://www.pinaldave.com/bimg/references2.jpg

The above query will return all the objects which are referencing the table Customer.

I am just glad to write above query. There are more to write to this subject. In future blog post I will write more in depth about other DMV which also aids in finding referenced data.

Click to Download Scripts

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – 2012 – List All The Column With Specific Data Types in Database

5 years ago I wrote script SQL SERVER – 2005 – List All The Column With Specific Data Types, when I read it again, it is very much relevant and I liked it. This is one of the script which every developer would like to keep it handy. I have upgraded the script bit more. I have included few additional information which I believe I should have added from the beginning. It is difficult to visualize the final script when we are writing it first time. I use every script which I write on this blog, the matter of the fact, I write only those scripts here which I was using at that time. It is quite possible that as time passes by my needs are changing and I change my script. Here is the updated script of this subject. If there are any user data types, it will list the same as well.

SELECT s.name AS 'schema', ts.name AS TableName,
c.name AS column_name, c.column_id,
SCHEMA_NAME(t.schema_id) AS DatatypeSchema,
t.name AS Datatypename
,t.is_user_defined, t.is_assembly_type
,c.is_nullable, c.max_length, c.PRECISION,
c.scale
FROM sys.columns AS c
INNER JOIN sys.types AS t ON c.user_type_id=t.user_type_id
INNER JOIN sys.tables ts ON ts.OBJECT_ID = c.OBJECT_ID
INNER JOIN sys.schemas s ON s.schema_id = ts.schema_id
ORDER BY s.name, ts.name, c.column_id

I would be very interested to see your script which lists all the columns of the database with data types. If I am missing something in my script, I will modify it based on your comment. This way this page will be a good bookmark for the future for all of us.

Click to Download Scripts

Reference : Pinal Dave (http://blog.SQLAuthority.com)

 

SQL SERVER – DMV sys.dm_exec_describe_first_result_set_for_object – Describes the First Result Metadata for the Module

Here is another interesting follow up blog post of SQL SERVER – sp_describe_first_result_set New System Stored Procedure in SQL Server 2012. While I was writing earlier blog post I had come across DMV sys.dm_exec_describe_first_result_set_for_object as well. I found that SQL Server 2012 is providing all this quick and new features which quite often we miss  to learn it and when in future someone demonstrates the same to us, we express our surprise on the subject.

DMV sys.dm_exec_describe_first_result_set_for_object returns result set which describes the columns used in the stored procedure. Here is the quick example. Let us first create stored procedure.

USE [AdventureWorks]
GO
ALTER PROCEDURE [dbo].[CompSP]
AS
SELECT
[DepartmentID] id
,[Name] n
,[GroupName] gn
FROM [HumanResources].[Department]
GO

Now let us run following two DMV which gives us meta data description of the stored procedure passed as a parameter.

Option1: Pass second parameter @include_browse_information as a 0.

SELECT *
FROM sys.dm_exec_describe_first_result_set_for_object (
OBJECT_ID('[dbo].[CompSP]'),0) AS Table1
GO

Option2: Pass second parameter @include_browse_information as a 1.

SELECT *
FROM sys.dm_exec_describe_first_result_set_for_object (
OBJECT_ID('[dbo].[CompSP]'),1) AS Table1
GO

Here is the result of Option1 and Option2.

describe1 SQL SERVER   DMV sys.dm exec describe first result set for object   Describes the First Result Metadata for the Module

If you see the result, there is absolutely no difference between the results. Both of the resultset are returning column names which are aliased in the stored procedure. Let us scroll on the right side and you will notice that there is clear difference in some columns.

describe2 SQL SERVER   DMV sys.dm exec describe first result set for object   Describes the First Result Metadata for the Module

You will see in second resultset source_database, Source_schema as well few other columns are reporting original table instead of NULL values. When @include_browse_information result is set to 1 it will provide the columns details of the underlying table. I have just discovered this DMV, I have yet to use it in production code and find out where exactly I will use this DMV. Do you have any idea? Does any thing comes up to your mind where this DMV can be helpful.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Finding Count of Logical CPU using T-SQL Script – Identify Virtual Processors

I recently received email from one of my very close friend from California. His question was very interesting. He wanted to know how many virtual processors are there available for SQL Server. He already had script for SQL Server 2008 but was mainly looking for SQL Server 2000. He made me go to my past. I found following script from my old emails (I have no reference listed along with it, so not sure the original source).

-- Identify Virtual Processors in for SQL Server 2005, 2008, 2008R2, 2012
SELECT cpu_count
FROM sys.dm_os_sys_info
GO
-- Identify Virtual Processors in for SQL Server 2000
CREATE TABLE #TempTable
([Index] VARCHAR(2000),
[Name] VARCHAR(2000),
[Internal_Value] VARCHAR(2000),
[Character_Value] VARCHAR(2000)) ;
INSERT INTO #TempTable
EXEC xp_msver;
SELECT Internal_Value AS VirtualCPUCount
FROM #TempTable
WHERE Name = 'ProcessorCount';
DROP TABLE #TempTable
GO

Yesterday I shared on facebook page about I am writing this blog post, SQL Server Expert Simran Jindal shared following script which is applicable to SQL Server 2005 and later versions. I just got update from her that this query is of my dear friend and SQL Server MVP Glenn Berry. Thanks Glenn.

SELECT cpu_count AS [Logical CPU Count], hyperthread_ratio AS Hyperthread_Ratio,
cpu_count/hyperthread_ratio AS Physical_CPU_Count,
physical_memory_in_bytes/1048576 AS Physical_Memory_in_MB,
sqlserver_start_time, affinity_type_desc -- (affinity_type_desc is only in 2008 R2)
FROM sys.dm_os_sys_info

If know any other reliable method to get the count of logical CPU, please share that in comment and I will update this blog post with due credit.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – DVM sys.dm_os_sys_info Column Name Changed in SQL Server 2012

Have you ever faced situation where something does not work? When you try to fix it ‑ you enjoy fixing it and started to appreciate the breaking changes. Well, this is exactly I felt yesterday. Before I begin my story, I want to candidly state that I do not encourage anybody to use * in the SELECT statement.

deleteerase SQL SERVER   DVM sys.dm os sys info Column Name Changed in SQL Server 2012

One of the my DBA friends, who always used my performance tuning script, sent me an email yesterday with the following question –

“Every time I want to retrieve OS related information in SQL Server, I use DMV sys.dm_os_sys_info. I just upgraded my SQL Server edition from 2008 R2 to SQL Server 2012 RC0, and it suddenly stopped working. Well, this is not the production server; so the issue is not big yet – but, eventually I need to resolve this error. Any suggestion?”

The funny thing about this was that the original email was very long, but it did not talk about what the exact error is besides that the query is not working. I think this is the disadvantage of being too friendly on email sometimes. Well, nevertheless, I quickly looked at the DMV on my SQL Server 2008 R2 and SQL Server 2012 RC0 version.

To my surprise, I found out that there were few columns that are renamed in SQL Server 2012 RC0. Usually, when people see breaking changes, they do not like it; but when I see these changes, I was happy as new names were meaningful, and additionally, their new conversion is much more practical and useful.

Here are the columns’ previous names:

 

Previous Column Name New Column Name
physical_memory_in_bytes physical_memory_kb
bpool_commit_target committed_target_kb
bpool_visible visible_target_kb
virtual_memory_in_bytes virtual_memory_kb
bpool_commited committed_kb

If you read it carefully, then you will notice that new columns now display few results in the kb, whereas earlier results were in bytes. When I see the results in bytes, I always get confused as I cannot guess what exactly it will convert into. I like to see results in kb, and I am glad that new columns are now displaying the results in kb.

I sent the details of the new columns to my friend and ask him to check the columns used in application. From my comment, he immediately realized why he was facing such an error and fixed it.

Overall, all is well at the end, and I learned something new.

Reference: Pinal Dave (http://blog.SQLAuthority.com)