Feeds:
Posts
Comments

Archive for the ‘Dynamic Management Views’ Category

SQL SERVER – dm_db_log_space_usage – dynamic management view

Posted in Dynamic Management Views, SQL SERVER, tagged , , , , on January 1, 2014| 1 Comment »

SQL Server database Maintenance plays an important role to improve database performance. One of the important aspects of maintenance is SQL Server log, even if you configured it properly, you should keep an eye on it and time to time, you should do its maintenance. To do the maintenance, you need to know the size of SQL Server log, used size and its percentage as well. There are multiple ways to achieve it. One of the famous methods of DBCC command is DBCC SQLPERF to find these details. I also have been using this DBCC Command since SQL Server 2005. It works fine but the only problem with this DBCC command is that if you need to manipulate its result set further, you need to place its result set in the temporary table and manipulate.

A few days ago, I was working on database log size and looking for some alternate solution. After some research, I found one undocumented dynamic management view that is shipped with SQL Server 2012 namely dm_db_log_space_usage. It gives the log size of the database and other details in bytes, you can convert it in MBs and manipulate its result set further (no need for temporary table) as well. Given below is the script to demonstrate how it works.

USE AdventureWorks2012
GO
SELECT
DB_NAME(database_id) AS [Database Name],
ROUND(CONVERT(FLOAT,total_log_size_in_bytes/1024)/1024,2) AS [Log Size (MB)],
ROUND(CONVERT(FLOAT,used_log_space_in_bytes/1024)/1024,2) AS [Log Used size (MB)],
ROUND(used_log_space_in_percent,2) AS [Log Used % (MB)]
FROM
sys.dm_db_log_space_usage

--OUTPUT

SQL Server log size.1.1

Read Full Post »

SQL SERVER 2012 – Breaking Changes – sys.dm_os_memory_cache_counters

Posted in Dynamic Management Views, SQL SERVER, tagged , , , , on April 22, 2013| Leave a Comment »

sys.dm_os_memory_cache_counters is one of the dynmaic management views shipped with SQL Server 2005. The major benefits of this dynamic management view is to provide the list of summary stating how cache is allocated against each request.

Given below is a simple query that will give the details about cache.

--This query is compatibale with SQL Server 2005 and 2008 only.
Select [name],[type],SUM([single_pages_kb]) As [single_pages_kb]
,SUM([multi_pages_kb]) As [multi_pages_kb]
from  sys.dm_os_memory_cache_counters
Group By [name],[type]
Order By [name], [type]
--OUTPUT

dm_os_memory_cache_counters1.1

A few days ago I tried using the same query in SQL Server 2012 to check out the summary of cache but the above query did not work. Given below is the error detail.

Select [name],[type]
,SUM([single_pages_kb]) As [single_pages_kb]
,SUM([multi_pages_kb]) As [multi_pages_kb]
from  sys.dm_os_memory_cache_counters
Group By [name],[type]
Order By [name], [type]
--OUTPUT

Msg 207, Level 16, State 1, Line 2
Invalid column name ‘single_pages_kb’.
Msg 207, Level 16, State 1, Line 3
Invalid column name ‘multi_pages_kb’.
sys.dm_os_memory_cache_counter1.2

Oooops……………
It generated an error. The first impression I got was that these two fields have been eliminated from sys.dm_os_memory_cache_counters in SQL Server 2012 ?

But the answer is No, these field are not eliminated but renamed. Given below are the details.


S.No

Previous Column Name

New Column Name

1

single_pages_kb

pages_kb

2

multi_pages_kb

pages_in_use_kb

Lets rename the column in the above query and execute it again in SQL Server 2012.

--This query is compatible with SQL Server 2012 and above.
Select [name],[type]
,SUM([pages_kb]) As [pages_kb]
,SUM([pages_in_use_kb]) As [pages_in_use_kb]
from sys.dm_os_memory_cache_counters
Group By [name],[type]
Order By [name], [type]
--OUTPUT

sys.dm_os_memory_cache_counter1.3

Conclusion :
In SQL Server 2012, sys.dm_os_memory_cache_counters has renamed the two columns single_pages_kb, multi_pages_kb to pages_kb, pages_in_use_kb respectively. Remember to implement this renamed column change effects to avoid such errors.

Reference : MSDN

Read Full Post »