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]
[DepartmentID] id
,[Name] n
,[GroupName] gn
FROM [HumanResources].[Department]

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.

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

Option2: Pass second parameter @include_browse_information as a 1.

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

Here is the result of Option1 and Option2.

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.

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
-- Identify Virtual Processors in for SQL Server 2000
([Index] VARCHAR(2000),
[Name] VARCHAR(2000),
[Internal_Value] VARCHAR(2000),
[Character_Value] VARCHAR(2000)) ;
EXEC xp_msver;
SELECT Internal_Value AS VirtualCPUCount
FROM #TempTable
WHERE Name = 'ProcessorCount';

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.

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)

SQL SERVER – Denali – Three DMVs – sys.dm_server_memory_dumps – sys.dm_server_services – sys.dm_server_registry

In this blog post we will see three new DMVs which are introduced in Denali. The DMVs are very simple and there is not much to describe them. So here is the simple game. I will be asking a question back to you after seeing the result of the each of the DMV and you help me to complete this blog post.

FROM sys.dm_server_memory_dumps

Above DMV returns following result.

FROM sys.dm_server_services

Above DMV returns following result.

FROM sys.dm_server_registry

Above DMV returns following result.

Now here is the question for you – how will you use this DMV in your application. One thing for sure is that this makes it easier to find out various information. We can easily know which services are running and what was the start time etc but also where exactly you will use this in production server?

Response to Question:
SQLConcept – Feodor Georgiev has given excellent summary on this subject. A Must Read

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

SQL SERVER – Detecting Database Case Sensitive Property using fn_helpcollations()

In my recent Office Hours, I received a question on how to determine the case sensitivity of the database.

The quick answer to this is to identify the collation of the database and check the properties of the collation. I have previously written how one can identify database collation. Once you have figured out the collation of the database, you can put that in the WHERE condition of the following T-SQL and then check the case sensitivity from the description.

FROM fn_helpcollations()

The method shown above is the most recommended method and I suggest using the same.

When I was a young DBA, I did not have the patience to follow the above method. I used to do something very simple.

WHERE 'SQL' = 'sql'

If the above query returns me the result, it means that the database is case-insensitive. Please note that by no means do I suggest using this method; I really recommend using the method fn_helpcollations().

Another interesting suggestion was from Dave Dustin who is SQL Server MVP from New Zealand. He has provided the following script:

FROM sys.Databases
WHERE name='<databasename>'
AND (collation_name LIKE '%CS%' OR collation_name LIKE '%BIN%')

Insert your database name in the WHERE clause. If the query returns any result, it means the database is case-sensitive.

It’s interesting to see that one simple question can result to three interesting ways to know the answer. Do you know any other method to know the database case sensitivity? Please share it here and I will post it with due credit.

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

SQL SERVER – Denali – DMV – sys.dm_os_windows_info – Information about Operating System

One more quick introduction to DMV for Denali. Following DMV provides information about Windows Operating System. Here is the quick example of the same.

This DMV returns information about the operating system volume (directory) on which the specified databases and files are stored. Here is the quick example I have created for the same.

FROM sys.dm_os_windows_info;

Here is the screenshot of the same:

Here is my question back to you – where would you use this stored procedure in your application? What is your preferred method to know details about Windows? One last question – what is 1033 in the last column of the table result and what is 1033 represent?

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

SQL SERVER – Denali – DMV – sys.dm_os_volume_stats – Information about operating system volume

SQL Server Denali has many new interesting feature – one of the interesting feature is New DMVs.

This DMV returns information about the operating system volume (directory) on which the specified databases and files are stored. Here is the quick example I have created for the same.

SELECT DB_NAME(f.database_id) DatabaseName,
f.FILE_ID, size DBSize, file_system_type,
volume_mount_point, total_bytes, available_bytes
FROM sys.master_files AS f
CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.FILE_ID);

Here is the screenshot of the same:

In the result set we can see the file system and volume database is mounted on as well database size.

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