Multiple database ID for single DB in sys.dm_os_buffer_descriptors??

Question

Multiple database ID for single DB in sys.dm_os_buffer_descriptors??

Grant Roles

SSC Veteran

Points: 283
More actions
July 10, 2019 at 4:47 pm

#3661653

Hey guys. I use sys.dm_os_buffer_descriptors to get a sense of what percentage each database is consuming. This is SQL server 2016 (13.0.5149.0). I just noticed something funny in the results in that one of my databases has two different database IDs! The legit ID for this database is 15 and the "hidden" one is 29.
When I look in master.sys.databases the second database ID does not exist and is actually skipped over by another DB ID of 30. I thought this "extra" database ID might be related to NUMA nodes, but this is definitely not happening for any other databases on this server.
I'm left scratching my head on this one. Has anyone else seen this sort of behavior?
-Grant Roles

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

crow1969 Hall of Fame Points: 3001 More actions · Answer 1

If it has a different database_id, how are you certain it should be associated with database_id = 15?

I see entries for the System Resource Database, but that is ID 32767. Could it have been changed on your system? I thought the System Resource database was more or less hard-coded in.

Sue_H SSC Guru Points: 90860 More actions · Answer 2

In addition to checking the distinct database ids in sys.dm_os_buffer_descriptors, you may want to post the actual query you are using to see if anyone can reproduce the issue. Not real sure how you determined that one database has two different ids in the DMV so posting the query would help.

Sue

Grant Roles SSC Veteran Points: 283 More actions · Answer 3

So how I discovered the odd results were looking at the function DB_NAME(db_id) for ID 15 and 29 both resolve to the same database name. I see database_id = 29 in the sys.dm_os_buffer_descriptors dmv even though that ID doesn't exist in sys.databases.

Here's the memory percentage script I was using:

DECLARE @total_buffer INT;

SELECT @total_buffer = cntr_value
   FROM sys.dm_os_performance_counters 
   WHERE RTRIM([object_name]) LIKE '%Buffer Manager'
   AND counter_name = 'Database Pages';  -- Use  Total Pages for SQL 2008R2 and below

;WITH src AS
(
   SELECT 
       database_id, db_buffer_pages = COUNT_BIG(*)
       FROM sys.dm_os_buffer_descriptors
       --WHERE database_id BETWEEN 5 AND 32766
       GROUP BY database_id
)
SELECT
   [db_name] = CASE [database_id] WHEN 32767 
       THEN 'Resource DB' 
       ELSE DB_NAME([database_id]) END,
   db_buffer_pages,
   db_buffer_MB = db_buffer_pages / 128,
   db_buffer_percent = CONVERT(DECIMAL(6,3), 
       db_buffer_pages * 100.0 / @total_buffer)
FROM src
ORDER BY db_buffer_MB DESC;

-Grant Roles

Sue_H SSC Guru Points: 90860 More actions · Answer 4

Could be related to the DBCC CHECKDB hidden snapshot. The database_id for it won't show up in sys.databases but the database id for the snapshot (if you see it when checking processes, etc) will return the name of the original database.

Sue

Andrey Right there with Babe Points: 780 More actions · Answer 5

I saw the same weird thing too.

If i'm not mistaken in my case it was due to dbcc checkdb running in parallel which creates hidden snapshot of the database it checks now. That's why second db_id was also resolved into your database name.

https://www.sqlskills.com/blogs/paul/issues-around-dbcc-checkdb-and-the-use-of-hidden-database-snapshots/

PS Sue was faster with reply than me 😉

This reply was modified 4 years, 9 months ago by Andrey.

Grant Roles SSC Veteran Points: 283 More actions · Answer 6

Thank you both for your responses. It turns out this IS the case for me. This only happened when the DBCC checkDB was running. Since then it has completed and I no longer see this hidden database_id.

-Grant Roles