关于SQL Server页面压缩三步执行顺序及DBCC结果的技术咨询

Great question—let’s break this down step by step to clear up your confusion.

First, let’s confirm the execution order of page compression’s three stages: you’re absolutely right about the sequence:

• Step 1: Row Compression runs first
  This stage optimizes individual rows by stripping unnecessary padding (like trailing spaces in fixed-length columns) and using variable-length storage for data types that would otherwise take up fixed space (e.g., converting CHAR(10) to a variable-length equivalent if the actual data is shorter). It’s a row-level tweak that shrinks each row before moving to page-level compression.

• Step 2: Prefix Compression
  After row compression, SQL Server scans each column across all rows in the page to spot repeated prefixes. These shared prefixes get stored in a compression information (CI) area at the end of the page, and each row replaces the prefix with a pointer to this area. This eliminates redundant prefix data across the page.

• Step 3: Dictionary Compression
  Finally, the engine hunts for repeated values across all columns in the page (not just per-column prefixes). These duplicates are stored in the same CI area as a dictionary, and rows reference the dictionary entry instead of storing the full value. This works especially well when multiple columns share identical repeated values.

Now, regarding your confusion with DBCC PAGE not showing the expected compression details, here are common reasons and fixes to help you see the data:

• You’re using incorrect DBCC PAGE parameters
  To view detailed page content including compression metadata, you need to enable trace flag 3604 first (to redirect output to your query window) and use the 3 option for full page details. The command should look like this:

  DBCC TRACEON(3604);
  DBCC PAGE(YourDatabaseNameOrID, FileID, PageID, 3);
  

  Replace YourDatabaseNameOrID, FileID, and PageID with your target database and page identifiers.

• The page might not be using page compression
  Double-check that your table/index is configured for page compression (not just row compression). Verify this with:

  SELECT name, type_desc, data_compression_desc 
  FROM sys.indexes 
  WHERE object_id = OBJECT_ID('YourTableName');
  

  If data_compression_desc shows ROW instead of PAGE, only row compression is active—so you won’t see prefix/dictionary compression artifacts.

• Low data repetition means compression didn’t generate visible metadata
  SQL Server only applies prefix/dictionary compression when it’s cost-effective. If the page has very little repeated data (e.g., all rows have unique values in every column), the engine might skip these stages entirely. Test with a table that has highly repetitive data (like a column with many identical strings or numbers) to see the compression metadata clearly.

• You’re looking in the wrong section of the DBCC PAGE output
  Compression metadata (prefix lists, dictionary entries) lives in the "Compression Information" section near the bottom of the page output. Look for sections labeled Prefix Compression Info or Dictionary Compression Info—these only appear if those compression stages were applied.

内容的提问来源于stack exchange，提问作者Hello Everyone