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

Have you ever faced a 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 was 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. Let us read more about sys.dm_os_sys_info in this blog post.

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

One of the my DBA friends, who always used my performance tuning scripts, 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 NameNew Column Name
physical_memory_in_bytesphysical_memory_kb
bpool_commit_targetcommitted_target_kb
bpool_visiblevisible_target_kb
virtual_memory_in_bytesvirtual_memory_kb
bpool_commitedcommitted_kb

If you read it carefully, then you will notice that new columns now display fewer 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 the 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 the 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 (https://blog.sqlauthority.com)

,
Previous Post
SQL SERVER – Solution to Puzzle – Simulate LEAD() and LAG() without Using SQL Server 2012 Analytic Function
Next Post
SQL SERVER – Get Directory Structure using Extended Stored Procedure xp_dirtree

Related Posts

5 Comments. Leave new

  • Dave, genious…..

    Reply
  • Chirag Satasiya
    November 25, 2011 1:44 pm

    Hi pinal sir,
    Useful information.

    Thank$
    Chirag Satasiya

    Reply
  • thankyou sir!!!
    pankaj pandey

    Reply
  • Kamran Shahid
    April 12, 2013 1:35 pm

    Nice one Pinal

    Reply
  • But, I think It is not just field name has changed where as they have changed complete column. Earlier *****_bytes was showing bytes only now ***_kb columns shows values in kb
    EarlierColumn/1024=NewColumn. I suggest here to alter calculation as well in SQL Statements.

    This column change may be due to increasing usage of data, and the datatype used here may not be suitable to hold it.

    Reply

Leave a Reply

Menu