Page Count Question -please advise

Last Post 29 Mar 2011 08:05 AM by gunneyk. 3 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
SQL_Jr
New Member
New Member

--
18 Mar 2011 01:01 PM
So, there is a script to find out the page count usage per database using the sys.dm_os_buffer_descriptors DMV.
If Each page in SQL Server is 8KB in size, is the simple expression (page count * 8)/1024 get me the total usage size of the db pages in MB?  Is there a way to yield a usage percentage via script.  Here is a script that gets the following info:



SELECT (CASE WHEN ([is_modified] = 1) THEN 'Dirty' ELSE 'Clean' END) AS 'Page State', (CASE WHEN ([database_id] = 32767) THEN 'Resource Database' ELSE DB_NAME (database_id) END) AS 'Database Name', COUNT (*) AS 'Page Count', (COUNT(*)*8)/1024, Cast (SUM(free_space_in_bytes/1024) as BIGINT)AS 'Free Space In MB' FROM sys.dm_os_buffer_descriptors GROUP BY [database_id], [is_modified] ORDER BY [database_id], [is_modified]

Thanks!
gunneyk
New Member
New Member

--
18 Mar 2011 02:30 PM
Do you want the usage in terms of how much memory each is using or how much actual pages on disk they consume? If its memory then that is one way to get it. If you want actual disk usage you need to look at the allocation units dmv. There are 3 types of pages that can hold table data. Check BooksOnLine for the details.


SELECT SCHEMA_NAME(CAST(OBJECTPROPERTYEX(sp.object_id, N'SchemaId') AS INT)) AS [Schema]
, OBJECT_NAME (sp.object_id) AS [Object Name]
, sp.index_id AS [Index ID]
, sa.type_desc AS [Alloc Unit Type]
, [filegroup_id] AS [FileGroup ID]
, [total_pages] AS [Total Pages]
, [used_pages] AS [Used Pages]
, [data_pages] AS [Data Pages]
, [rows] AS [Rows]
FROM sys.system_internals_allocation_units AS sa INNER JOIN sys.partitions AS sp
ON sa.container_id = sp.partition_id
WHERE sp.[object_id] > 10000 AND sa.container_id = sp.partition_id
AND OBJECT_NAME (sp.object_id) NOT LIKE 'queue_me%'
AND sp.index_id IN (1,0)
ORDER BY [Schema], [Object Name]
SQL_Jr
New Member
New Member

--
28 Mar 2011 10:08 AM
hi, gunneyk - this is very informative! To answer your Q, I was looking to get the "memory usage" for each. The bold "(COUNT(*)*8)/1024, Cast (SUM(free_space_in_bytes/1024) as BIGINT)AS 'Free Space In MB' " is my modification, and wanted to confirm this is correct
gunneyk
New Member
New Member

--
29 Mar 2011 08:05 AM
I think you need to use the buffered_page_count column. Try this.

-- Database level Buffer Cache Contents
SELECT DB_NAME(database_id) ,
count(*)AS Buffered_Page_Count ,
count(*) * 8192 / (1024 * 1024) as Buffer_MB
FROM sys.dm_os_buffer_descriptors AS bd
--WHERE database_id = db_id()
GROUP BY database_id
ORDER BY [Buffer_MB] DESC
You are not authorized to post a reply.

Acceptable Use Policy