I'm doing some research about the DCM (Differential Change Map) page in SQL Server 2016. This is what I discovered so far:

  • page type: 16
  • each DCM page covers a GAM interval
  • each data file has a DCM page starting with page_id 6

When I analyzed a database containing filestream data I discovered something strange. I used following query to retrieve the file_id of the file containing the filestream data:

select file_id from sys.database_files where type_desc='FILESTREAM'

Next I used DBCC Page to analyze the DCM page of this file

dbcc traceon(3604,1)
dbcc page(33,65537,6,3)
dbcc traceoff(3604,1)

In the 'Allocation Status' I saw that the DIFF pointed to (1:6).

Allocation Status

PFS (1:1) = 0x44 ALLOCATED 100_PCT_FULL

Does this mean that a file with FILESTREAM data always points to a DCM page in data file containing the filestream table?
I think this is because a file with FILESTREAM data doesn't contains data pages but serves as a container for the files. Is my conclusion correct?

  • I'm confused by the phrase " the file containing the filestream data". That "file" isn't a file, it is a folder. I.e., that folder contains files (one file for each instance of BLOB data, where one instance is a value, like a column value for a row). Jun 18, 2019 at 8:16
  • @tibor karaszi: I refer to this as a file because it has a file_id and it also appears in sys.database_files. That's actually my question, is this the reason why the DCM file points to the data file because it is actually a container/directory? Jun 18, 2019 at 8:23


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.