How to detect which indexes have checksums and which ones don't?

Question

Is there a query that will show which tables / indexes need to be rebuilt so that they have checksums available for data verification? The DB was switched recently from torn page to checksum verification so only some newer indexes / tables have the required checksums and those which index maint has rebuilt. Need to see what the remaining tables / indexes are that need rebuilding to have checksum enabled.

Answer 1

The checksum isn't at the object or index level (aka allocation unit in this context). It is at the page level.

I.e., an allocation unit can have some pages without checksum (those that hasn't been modified since you turned on checksum) and other pages do have checksum (those that has been modified since you turned on checksum).

So you have to look at the page level to see whether each page has checksum or not. Below can serve as a starting point for you, you probably want to customize to your needs, though.

SELECT
 OBJECT_SCHEMA_NAME(a.object_id) AS schema_
,OBJECT_NAME(a.object_id) AS tbl
,a.index_id
--,*
FROM sys.dm_db_database_page_allocations(DB_ID('Adventureworks'), NULL, null, null, 'DETAILED') AS a
CROSS APPLY sys.dm_db_page_info ( DB_ID(), a.allocated_page_file_id, a.allocated_page_page_id, 'LIMITED' ) AS p
WHERE p.has_checksum = 0
AND p.page_type IN(1, 2)

Note that as written it read every page in the database. So consider whether you want to use the parameters to sys.dm_db_database_page_allocations and run it only for those tables/indexes you are interested in.

You can of course also aggregate and count number of pages with/without checksum, etc. Here's another version:

SELECT
 OBJECT_SCHEMA_NAME(a.object_id) AS schema_
,OBJECT_NAME(a.object_id) AS tbl
,a.index_id
,SUM(CAST(p.has_checksum AS int)) AS pages_with_checksum
,SUM(CASE p.has_checksum WHEN 1 THEN 0 ELSE 1 END) AS pages_without_checksum
--,*
FROM sys.dm_db_database_page_allocations(DB_ID('Adventureworks'), NULL, null, null, 'DETAILED') AS a
CROSS APPLY sys.dm_db_page_info ( DB_ID(), a.allocated_page_file_id, a.allocated_page_page_id, 'LIMITED' ) AS p
WHERE p.page_type IN(1, 2)
AND OBJECTPROPERTY(a.object_id, 'IsMsShipped') = 0 --Filter out system tables
GROUP BY 
  OBJECT_SCHEMA_NAME(a.object_id)
 ,OBJECT_NAME(a.object_id)
 ,a.index_id
 ORDER BY pages_without_checksum DESC, pages_with_checksum DESC

Note sys.dm_db_page_info was added in SQL Server 2019.