Have you ever encountered a situation where something suddenly stops working? It can be frustrating, but sometimes it leads to valuable insights and a deeper understanding of the system. In this blog post, we will explore the sys.dm_os_sys_info DMV in SQL Server and discuss a recent experience that highlights the importance of staying up-to-date with changes in the system.
One of my DBA friends, who often relies on my performance tuning scripts, recently reached out to me with a question. He had upgraded his SQL Server edition from 2008 R2 to SQL Server 2012 RC0 and noticed that his queries using the sys.dm_os_sys_info DMV were no longer functioning. While this was not a critical issue since it was not affecting a production server, he wanted to resolve the error before it became a problem.
Upon examining the DMV on both SQL Server 2008 R2 and SQL Server 2012 RC0, I discovered that there were a few columns that had been renamed in the newer version. Typically, encountering breaking changes can be frustrating, but in this case, I found the changes to be meaningful and practical. The new column names provided clearer information, and the conversion to kilobytes (KB) made the results more intuitive and useful.
Here are the previous and new column 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 |
By examining these changes, I realized that the error my friend was facing was due to using the old column names in his application. Once I shared the details of the new columns with him, he quickly identified the issue and resolved it.
While encountering unexpected errors can be frustrating, it is important to embrace them as opportunities for learning and growth. In this case, the breaking changes in the sys.dm_os_sys_info DMV led to a deeper understanding of the system and a more efficient way of retrieving OS-related information.
As a general recommendation, it is always advisable to stay updated with the latest changes and features in SQL Server. This ensures that your applications and queries continue to function smoothly and take advantage of any improvements or optimizations introduced in newer versions.
In conclusion, the experience with the sys.dm_os_sys_info DMV serves as a reminder of the importance of staying informed about changes in SQL Server. By embracing these changes and adapting our practices accordingly, we can enhance our understanding of the system and improve the performance and reliability of our applications.