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)

About these ads

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.

SELECT *
FROM sys.dm_server_memory_dumps

Above DMV returns following result.

SELECT *
FROM sys.dm_server_services

Above DMV returns following result.

SELECT *
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.

SELECT *
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.

SELECT 1
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:

SELECT 1
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.

SELECT *
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)

SQL SERVER – Denali – DMV Enhancement – sys.dm_exec_query_stats – New Columns

SQL Server version next Denali has lots of enhancements. Some of the enhancements are just game changing and overcomes needs of more coding to do the same thing.

Similar function DMV is sys.dm_exec_query_stats. There are four new columns added to this DMV. I have often used this DMV to check recently ran query, their execution plan by joining more DMVs to it. However, there was also need of knowing how many rows my queries have returned.

This DMV is enhanced with four more queries.

total_rows – Total number of rows returned by query
last_rows – Number of the rows return by the last execution of the query
min_rows – Minimum numbers of the rows returned by the query since it is compiled
max_rows – Maximum numbers of the rows returned by the query since it is compiled

Here see the quick example of the columns:

SELECT qs.execution_count,
qs.total_rows, qs.last_rows, qs.min_rows, qs.max_rows,
SUBSTRING(qt.TEXT,qs.statement_start_offset/2 +1,
(
CASE WHEN qs.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.TEXT)) * 2
ELSE qs.statement_end_offset END -
qs.statement_start_offset
)/2
) AS query_text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
ORDER BY qs.execution_count DESC;

If you run above query it will give us different result based on your server’s workload.

You can see that the above result set – it displays how many time query has executed and how new columns (total_rows, last_rows, min_rows and max_rows) returns result based on the query.

Now here is the question back to you – if you have downloaded Denali and installed it, I would like to see you use this new column and come up with some more creative usage.

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

SQL SERVER – Author’s Book is Available in India and USA

I am feeling very good to write this short blog post. My book is now officially available on in India and USA.

In India you can get it from Flipkart - http://bit.ly/pinalbook

In USA you can get it from Amazon – http://amzn.to/pb49jq

This book is just like this blog and contains all the complex subject in very simple manner. I am confident that you will for sure like this book if you like this blog.

Here is quick video shot by my wife when I was reading my own book. See the original post to see the video.

Here is quick secret for you – there is big surprise in September for all those who own this book.

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