Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Get Object Name from database_id, file_id, Page_id Expand / Collapse
Author
Message
Posted Wednesday, March 5, 2014 9:45 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, September 12, 2014 3:03 AM
Points: 2,879, Visits: 3,226
I have Buffer Pool Extension enabled and want to get the object name for all pages shown in sys.dm_os_buffer_descriptors.

When a page is in the BPE and no longer in the Bufferpool, the normal way of working this out via allocation_unit_id cannot be used as that column is null as shown below.



Does anyone know a SQL query that can get to the object_id using just the database_id, file_id and page_id WITHOUT using DBCC PAGE. I need to get a result set that includes the object_id for every row in sys.dm_os_buffer_descriptors.



Original author: SQL Server FineBuild 1-click install and best practice configuration of SQL Server 2014, 2012, 2008 R2, 2008 and 2005. 28 July 2014: now over 30,000 downloads.
Disclaimer: All information provided is a personal opinion that may not match reality.
Concept: "Pizza Apartheid" - the discrimination that separates those who earn enough in one day to buy a pizza if they want one, from those who can not.
Post #1547898
Posted Wednesday, March 5, 2014 11:10 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, September 11, 2014 2:28 PM
Points: 1,194, Visits: 2,219
EdVassie (3/5/2014)
I have Buffer Pool Extension enabled and want to get the object name for all pages shown in sys.dm_os_buffer_descriptors.

When a page is in the BPE and no longer in the Bufferpool, the normal way of working this out via allocation_unit_id cannot be used as that column is null as shown below.



Does anyone know a SQL query that can get to the object_id using just the database_id, file_id and page_id WITHOUT using DBCC PAGE. I need to get a result set that includes the object_id for every row in sys.dm_os_buffer_descriptors.



Check this one ..

SELECT *, OBJ.name AS Object_Name
FROM sys.dm_os_buffer_descriptors AS bd
INNER JOIN
(
SELECT object_name(object_id) AS name
,index_id ,allocation_unit_id
FROM sys.allocation_units AS au
INNER JOIN sys.partitions AS p
ON au.container_id = p.hobt_id
AND (au.type = 1 OR au.type = 3)
UNION ALL
SELECT object_name(object_id) AS name
,index_id, allocation_unit_id
FROM sys.allocation_units AS au
INNER JOIN sys.partitions AS p
ON au.container_id = p.partition_id
AND au.type = 2
) AS obj
ON bd.allocation_unit_id = obj.allocation_unit_id
--WHERE database_id = db_id()

--
SQLBuddy
Post #1547946
Posted Wednesday, March 5, 2014 11:39 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:50 AM
Points: 42,994, Visits: 36,149
Potentially. I haven't tried this because I don't have a 2012 instance available. It's undocumented.

SELECT * 
FROM sys.dm_db_database_page_allocations(DB_ID('<Database Name>'), NULL, NULL, NULL, 'DETAILED') dpa
WHERE allocated_page_file_id = @FileID AND allocated_page_page_id = @PageID




Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1547958
Posted Wednesday, March 5, 2014 11:48 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:50 AM
Points: 42,994, Visits: 36,149
sqlbuddy123 (3/5/2014)
Check this one ..

SELECT *, OBJ.name AS Object_Name
FROM sys.dm_os_buffer_descriptors AS bd
INNER JOIN
(
SELECT object_name(object_id) AS name
,index_id ,allocation_unit_id
FROM sys.allocation_units AS au
INNER JOIN sys.partitions AS p
ON au.container_id = p.hobt_id
AND (au.type = 1 OR au.type = 3)
UNION ALL
SELECT object_name(object_id) AS name
,index_id, allocation_unit_id
FROM sys.allocation_units AS au
INNER JOIN sys.partitions AS p
ON au.container_id = p.partition_id
AND au.type = 2
) AS obj
ON bd.allocation_unit_id = obj.allocation_unit_id
--WHERE database_id = db_id()



How would that work when the allocation_unit_id is null, as shown in the image and as Ed explicitly stated?



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1547962
Posted Wednesday, March 5, 2014 11:58 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 4:51 PM
Points: 21,733, Visits: 15,424
GilaMonster (3/5/2014)
Potentially. I haven't tried this because I don't have a 2012 instance available. It's undocumented.

SELECT * 
FROM sys.dm_db_database_page_allocations(DB_ID('<Database Name>'), NULL, NULL, NULL, 'DETAILED') dpa
WHERE allocated_page_file_id = @FileID AND allocated_page_page_id = @PageID



Should work. I might add database_id to the predicate.




Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1547965
Posted Wednesday, March 5, 2014 12:10 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:50 AM
Points: 42,994, Visits: 36,149
SQLRNNR (3/5/2014)
GilaMonster (3/5/2014)
Potentially. I haven't tried this because I don't have a 2012 instance available. It's undocumented.

SELECT * 
FROM sys.dm_db_database_page_allocations(DB_ID('<Database Name>'), NULL, NULL, NULL, 'DETAILED') dpa
WHERE allocated_page_file_id = @FileID AND allocated_page_page_id = @PageID



Should work. I might add database_id to the predicate.


No need, because the first parameter to the function limits the returned resultset to just the DB of interest. It's like index_physical_stats.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1547970
Posted Wednesday, March 5, 2014 12:16 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 4:51 PM
Points: 21,733, Visits: 15,424
GilaMonster (3/5/2014)
SQLRNNR (3/5/2014)
GilaMonster (3/5/2014)
Potentially. I haven't tried this because I don't have a 2012 instance available. It's undocumented.

SELECT * 
FROM sys.dm_db_database_page_allocations(DB_ID('<Database Name>'), NULL, NULL, NULL, 'DETAILED') dpa
WHERE allocated_page_file_id = @FileID AND allocated_page_page_id = @PageID



Should work. I might add database_id to the predicate.


No need, because the first parameter to the function limits the returned resultset to just the DB of interest. It's like index_physical_stats.


Fair point. I ran it and passed the DBName to the parameter. So, dunno what I was thinking.




Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1547974
Posted Thursday, March 6, 2014 5:37 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, September 12, 2014 3:03 AM
Points: 2,879, Visits: 3,226
Gail's code looks like it might work

I am out of office until Monday 10 and will check it out then. Thanks for the suggestion!

BTW, Buffer Pool Extension is a really good addition to SQL Server. We put a 400GB BPE file on to our 60GB BI server and when I last checked we were getting about 70% hit rate on the BPE, and a lot more throughput on user reporting. IMHO anyone considering SQL2014 backed by spinning disks should aim to get SSD space and exploit the BPE.


Original author: SQL Server FineBuild 1-click install and best practice configuration of SQL Server 2014, 2012, 2008 R2, 2008 and 2005. 28 July 2014: now over 30,000 downloads.
Disclaimer: All information provided is a personal opinion that may not match reality.
Concept: "Pizza Apartheid" - the discrimination that separates those who earn enough in one day to buy a pizza if they want one, from those who can not.
Post #1548215
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse