SQL SERVER – DVM sys.dm_os_sys_info Column Name Changed in SQL Server 2012
Posted
by pinaldave
on SQL Authority
See other posts from SQL Authority
or by pinaldave
Published on Fri, 25 Nov 2011 01:30:02 +0000
Indexed on
2011/11/25
2:05 UTC
Read the original article
Hit count: 532
Pinal Dave
|PostADay
|sql
|SQL Authority
|SQL DMV
|SQL Query
|SQL Scripts
|SQL Server
|SQL Tips and Tricks
|T SQL
|Technology
Have you ever faced situation where something does not work and when you try to go and fix it – you like fixing it and started to appreciate the breaking changes. Well, this is exactly I felt yesterday. Before I begin my story of yesterday I want to state it candidly that I do not encourage anybody to use * in the SELECT statement.
One of the my DBA friend who always used my performance tuning script yesterday sent me email asking following question -
“Every time I want to retrieve OS related information in SQL Server – I used 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 was original email was very long but it did not talk about what is the exact error beside the query is not working. I think this is the disadvantage of being too friendly on email sometime. Well, never the less, 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 which 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 you will notice that new columns now display few results in the KB whereas earlier result was in bytes. When I see the results in bytes I always get confused as I could not guess what exactly it will convert to. I like to see results in kb and I am glad that new columns are now displaying the results in the 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 quickly realized why he was facing error and fixed it immediately.
Overall – all was well at the end and I learned something new.
Reference: Pinal Dave (http://blog.SQLAuthority.com)
Filed under: Pinal Dave, PostADay, SQL, SQL Authority, SQL DMV, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, T SQL, Technology
© SQL Authority or respective owner
