Friday, March 30, 2007 4:07 AM
royashbrook
more cool usage information for sql2005
yet another
incredible article from chad boyd's blogthis article is about "determining what
objects/structures/files/etc. are consuming the largest amount of space
at a given time (or over time) within the Sql Server buffer pool" in sql 2005.
i'm going to post something similar to the little one here. I had a little trouble with it running out of space in int for bytes, so I just switched to KB since that's plenty precise for me. I also switched the formatting to something more consistent with what I typically use. code below, enjoy!
set transaction isolation level read uncommitted
select
case grouping(dbName)
when 1 then '--- TOTAL ---'
else dbName
end dbName
, case grouping(fileId)
when 1 then '--- TOTAL ---'
else fileId
end fileId
, case grouping(pageType)
when 1 then '--- TOTAL ---'
else pageType
end pageType
, count(*) countPages
, sum(row_count) sumRowCount
, avg(row_count) avgRowCount
, sum(freeSpaceKBytes) sumFreeSpaceKBytes
, avg(freeSpaceKBytes) avgFreeSpaceKBytes
from
(
select
case database_id
when 32767 then 'resourceDb'
else cast(db_name(database_id) as varchar(25))
end dbName
, cast(file_id as varchar(10)) fileId
, cast(page_type as varchar(25)) pageType
, row_count row_count
, free_space_in_bytes / 1024 freeSpaceKBytes
from
sys.dm_os_buffer_descriptors bufferDescriptor
) tmp
group by
dbName
, fileId
, pageType
with rollup
order by
case grouping(dbName)
when 1 then 'zzzzzzzzzzzzzzzzzzzzzzzzzzz'
else dbName
end
, case grouping(fileId)
when 1 then 'zzzzzzzzzzzzzzzzzzzzzzzzzzz'
else fileId
end
, case grouping(pageType)
when 1 then 'zzzzzzzzzzzzzzzzzzzzzzzzzzz'
else pageType
end
Filed under: sql