Invoke-SqlAssessment - IndexFragmentation rule doesn't take partitions into consideration

[jpomfret]

If I run the following to check my local pubs database for fragmentation (config.json lowers the threshold to 0) I get three rows per index as it is a partitioned table.

$inst = Connect-DbaInstance -SqlInstance mssql1 -SqlCredential $sqlCred
$inst.databases['pubs'] | Invoke-SqlAssessment -Check IndexFragmentation -Verbose -Configuration .\config.json

If you add sp.partition_number into the query that runs this check you'll be able to see which partitions are fragmented and then this could be appended to the message returned

This was originally posted on the SQLServerPSModule repo but suggested I post here
microsoft/SQLServerPSModule#68

[jpomfret]

Seems like something got updated in a newer ruleset - but it still doesn't work for partitioned tables.
Now it sums all the fragmentation numbers up for the partitions so I have over 100% fragmented

[jpomfret]

Ok, me and @ClaudioESSilva spent some time this afternoon looking into how this could be resolved and it looks like the following two changes would resolve this issue and allow the SQL Assessment to be more informative and correct when running against environments with partitioned tables.

Changing the probe query and the message for the IndexFragmentation check.

Message

sql-server-samples/samples/manage/sql-assessment-api/ruleset.json

Line 5259 in 265be93

"message": "Remove fragmentation of @{IndexFullName} index. Current fragmentation level is @{fragmentation:#0.##}%",

Change to include a spot for partitionNumber (this only contains text if the table is partitioned)

      "message": "Remove fragmentation of @{IndexFullName} index@{partitionNumber} Current fragmentation level is @{fragmentation:#0.##}%",

Probe Queries

There are two queries depending on the version of the SQL Instance

This is the query for "version": "[11.0,12.0)"

sql-server-samples/samples/manage/sql-assessment-api/ruleset.json

Line 19309 in 265be93

"query": "SELECT CASE i.[type] WHEN 0 THEN CONCAT(SCHEMA_NAME(t.schema_id), '.', t.[name], ' (HEAP)') ELSE CONCAT(SCHEMA_NAME(t.schema_id), '.', t.[name], '.', i.[name]) END AS IndexFullName, stat.fragmentation, stat.page_count, i.[type] AS IndexType FROM sys.indexes AS i WITH (NOLOCK) INNER JOIN sys.tables AS t WITH (NOLOCK) ON t.[object_id] = i.[object_id] AND t.is_ms_shipped = 0 INNER JOIN ( SELECT [object_id], index_id, SUM(ps.avg_fragmentation_in_percent) AS fragmentation, SUM(ps.page_count) AS page_count FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) ps WHERE ps.index_level = 0 AND ps.alloc_unit_type_desc = 'IN_ROW_DATA' GROUP BY [object_id], index_id ) stat ON stat.index_id = i.index_id AND stat.[object_id] = i.[object_id] WHERE i.[type] IN (0, 1, 2)"

Change to group by partition number and if it is a partitioned table, add the partition number information so we can use it in the message

SELECT CASE i.[type] WHEN 0 THEN CONCAT(SCHEMA_NAME(t.schema_id), '.', t.[name], ' (HEAP)') ELSE CONCAT(SCHEMA_NAME(t.schema_id), '.', t.[name], '.', i.[name]) END AS IndexFullName , stat.fragmentation, stat.page_count, i.[type] AS IndexType, CASE WHEN p.index_id IS NULL THEN '.' ELSE ' (Partition Number: ' + CAST(stat.partition_number AS varchar(5)) + ').' END AS partitionNumber FROM sys.indexes AS i WITH (NOLOCK)  INNER JOIN sys.tables AS t WITH (NOLOCK) ON t.[object_id] = i.[object_id]  AND t.is_ms_shipped = 0  INNER JOIN  (  SELECT [object_id], index_id, SUM(ps.avg_fragmentation_in_percent) AS fragmentation, SUM(ps.page_count) AS page_count , ps.partition_number FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) ps	 WHERE ps.index_level = 0  AND ps.alloc_unit_type_desc = 'IN_ROW_DATA'  GROUP BY [object_id], index_id , ps.partition_number ) stat  ON stat.index_id = i.index_id  AND stat.[object_id] = i.[object_id]  LEFT JOIN sys.partitions AS p WITH (NOLOCK) ON p.object_id = i.object_id AND p.index_id = i.index_id AND p.partition_number = 2 WHERE i.[type] IN (0, 1, 2)

This is the query for "version": "[12.0,)"

sql-server-samples/samples/manage/sql-assessment-api/ruleset.json

Line 19325 in 265be93

"query": "SELECT CASE i.[type] WHEN 0 THEN CONCAT(SCHEMA_NAME(t.schema_id), '.', t.[name], ' (HEAP)') ELSE CONCAT(SCHEMA_NAME(t.schema_id), '.', t.[name], '.', i.[name]) END AS IndexFullName, stat.fragmentation, stat.page_count, 0 AS fragmentation_CI, 0 AS [state], i.[type] AS IndexType FROM sys.indexes AS i WITH (NOLOCK) INNER JOIN sys.tables AS t WITH (NOLOCK) ON t.[object_id] = i.[object_id] AND t.is_ms_shipped = 0 INNER JOIN ( SELECT [object_id], index_id, SUM(ps.avg_fragmentation_in_percent) AS fragmentation, SUM(ps.page_count) AS page_count FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) ps WHERE ps.index_level = 0 AND ps.alloc_unit_type_desc = 'IN_ROW_DATA' GROUP BY [object_id], index_id ) stat ON stat.index_id = i.index_id AND stat.[object_id] = i.[object_id] WHERE i.[type] IN (0, 1, 2, 7) UNION SELECT CONCAT(SCHEMA_NAME(t.schema_id), '.', t.[name], '.', i.[name]) AS IndexFullName, 0, 0, SUM( (ISNULL(rg.deleted_rows, 1) * 100) /CASE WHEN rg.total_rows = 0 THEN 1 ELSE rg.total_rows END ), rg.[state], i.[type] FROM sys.column_store_row_groups AS rg WITH (NOLOCK) INNER JOIN sys.indexes AS i WITH (NOLOCK) ON i.index_id = rg.index_id and i.[object_id] = rg.[object_id] INNER JOIN sys.tables AS t WITH (NOLOCK) ON t.[object_id] = rg.[object_id] AND t.is_ms_shipped = 0 GROUP BY rg.[object_id], i.[name], t.[name], t.[schema_id], rg.[state], i.[type]"

This is the query including partition numbers, it's worth noting that if this is run in SSMS duplicates may occur but this is based on having multiple rows with different states, these will then be filtered out by the API as it only looks for state <> 3

SELECT  CASE i.[type]  WHEN 0 THEN CONCAT(SCHEMA_NAME(t.schema_id), '.', t.[name], ' (HEAP)')  ELSE CONCAT(SCHEMA_NAME(t.schema_id), '.', t.[name], '.', i.[name])  END AS IndexFullName , stat.fragmentation, stat.page_count, 0 AS fragmentation_CI, 0 AS [state], i.[type] AS IndexType  , CASE  WHEN p.index_id IS NULL THEN '.' ELSE ' (Partition Number: ' + CAST(stat.partition_number AS varchar(5)) + ').' END AS partitionNumber FROM sys.indexes AS i WITH (NOLOCK)  INNER JOIN sys.tables AS t WITH (NOLOCK)  ON t.[object_id] = i.[object_id]  AND t.is_ms_shipped = 0  INNER JOIN  (  SELECT [object_id], index_id, SUM(ps.avg_fragmentation_in_percent) AS fragmentation, SUM(ps.page_count) AS page_count  , ps.partition_number FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) ps  WHERE ps.index_level = 0 AND ps.alloc_unit_type_desc = 'IN_ROW_DATA'  GROUP BY [object_id], index_id, ps.partition_number  ) stat  ON stat.index_id = i.index_id  AND stat.[object_id] = i.[object_id]  LEFT JOIN sys.partitions AS p WITH (NOLOCK) ON p.object_id = i.object_id AND p.index_id = i.index_id AND p.partition_number = 2 WHERE i.[type] IN (0, 1, 2, 7)   UNION   SELECT CONCAT(SCHEMA_NAME(t.schema_id), '.', t.[name], '.', i.[name]) AS IndexFullName , 0, 0, SUM( (ISNULL(rg.deleted_rows, 1) * 100) /CASE WHEN rg.total_rows = 0 THEN 1 ELSE rg.total_rows END ), rg.[state], i.[type]  , CASE  WHEN p.index_id IS NULL THEN '.' ELSE ' (Partition Number: ' + CAST(rg.partition_number AS varchar(5)) + ').' END AS partitionNumber FROM sys.column_store_row_groups AS rg WITH (NOLOCK)  INNER JOIN sys.indexes AS i WITH (NOLOCK)  ON i.index_id = rg.index_id  and i.[object_id] = rg.[object_id]  INNER JOIN sys.tables AS t WITH (NOLOCK)  ON t.[object_id] = rg.[object_id]  AND t.is_ms_shipped = 0  LEFT JOIN sys.partitions AS p WITH (NOLOCK) ON p.object_id = i.object_id AND p.index_id = i.index_id GROUP BY rg.[object_id], i.[name], t.[name], t.[schema_id], rg.[state], i.[type], p.index_id, rg.partition_number 

Results

You can see in my test I get four results for that table, one for each partition that is fragmented above the threshold (0 in this example)

These are the same SQL queries just formatted for readability:

# older versions - no columnstore
SELECT CASE i.[type] WHEN 0 THEN CONCAT(SCHEMA_NAME(t.schema_id), '.', t.[name], ' (HEAP)') ELSE CONCAT(SCHEMA_NAME(t.schema_id), '.', t.[name], '.', i.[name]) END AS IndexFullName
    , stat.fragmentation, stat.page_count, i.[type] AS IndexType --, stat.partition_number
	, CASE WHEN p.index_id IS NULL THEN '.'
		ELSE ' (Partition Number: ' + CAST(stat.partition_number AS varchar(5)) + ').'
	END AS partitionNumber
FROM sys.indexes AS i WITH (NOLOCK) 
INNER JOIN sys.tables AS t WITH (NOLOCK) ON t.[object_id] = i.[object_id] 
AND t.is_ms_shipped = 0 
INNER JOIN 
( 
    SELECT [object_id], index_id, SUM(ps.avg_fragmentation_in_percent) AS fragmentation, SUM(ps.page_count) AS page_count
			, ps.partition_number
    FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) ps 		
    WHERE ps.index_level = 0 
	AND ps.alloc_unit_type_desc = 'IN_ROW_DATA' 
    GROUP BY [object_id], index_id , ps.partition_number
) stat 
ON stat.index_id = i.index_id 
AND stat.[object_id] = i.[object_id] 
LEFT JOIN sys.partitions AS p WITH (NOLOCK)
ON p.object_id = i.object_id
AND p.index_id = i.index_id
AND p.partition_number = 2
WHERE i.[type] IN (0, 1, 2)

# 2012+ inc columnstore
SELECT 
	CASE i.[type] 
		WHEN 0 THEN CONCAT(SCHEMA_NAME(t.schema_id), '.', t.[name], ' (HEAP)') 
		ELSE CONCAT(SCHEMA_NAME(t.schema_id), '.', t.[name], '.', i.[name]) 
	 END AS IndexFullName
	, stat.fragmentation, stat.page_count, 0 AS fragmentation_CI, 0 AS [state], i.[type] AS IndexType 
	, CASE 
		WHEN p.index_id IS NULL THEN '.'
		ELSE ' (Partition Number: ' + CAST(stat.partition_number AS varchar(5)) + ').'
	END AS partitionNumber
FROM sys.indexes AS i WITH (NOLOCK) 
	INNER JOIN sys.tables AS t WITH (NOLOCK) 
	ON t.[object_id] = i.[object_id] 
	AND t.is_ms_shipped = 0 
	INNER JOIN 
	( 
		SELECT [object_id], index_id, SUM(ps.avg_fragmentation_in_percent) AS fragmentation, SUM(ps.page_count) AS page_count 
				, ps.partition_number
		FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) ps 
		WHERE ps.index_level = 0 AND ps.alloc_unit_type_desc = 'IN_ROW_DATA' 
		GROUP BY [object_id], index_id, ps.partition_number 
	) stat 
	ON stat.index_id = i.index_id 
	AND stat.[object_id] = i.[object_id] 
	LEFT JOIN sys.partitions AS p WITH (NOLOCK)
	ON p.object_id = i.object_id
	AND p.index_id = i.index_id
	AND p.partition_number = 2
WHERE i.[type] IN (0, 1, 2, 7) 

UNION 

SELECT CONCAT(SCHEMA_NAME(t.schema_id), '.', t.[name], '.', i.[name]) AS IndexFullName
	, 0, 0, SUM( (ISNULL(rg.deleted_rows, 1) * 100) /CASE WHEN rg.total_rows = 0 THEN 1 ELSE rg.total_rows END ), rg.[state], i.[type] 
	, CASE 
		WHEN p.index_id IS NULL THEN '.'
		ELSE ' (Partition Number: ' + CAST(rg.partition_number AS varchar(5)) + ').'
	END AS partitionNumber
FROM sys.column_store_row_groups AS rg WITH (NOLOCK) 
	INNER JOIN sys.indexes AS i WITH (NOLOCK) 
	ON i.index_id = rg.index_id 
	and i.[object_id] = rg.[object_id] 
	INNER JOIN sys.tables AS t WITH (NOLOCK) 
	ON t.[object_id] = rg.[object_id] 
	AND t.is_ms_shipped = 0 
	LEFT JOIN sys.partitions AS p WITH (NOLOCK)
	ON p.object_id = i.object_id
	AND p.index_id = i.index_id
GROUP BY rg.[object_id], i.[name], t.[name], t.[schema_id], rg.[state], i.[type], p.index_id, rg.partition_number

[jpomfret]

Hey @WilliamDAssafMSFT I wondered if you could help me with this issue - It's also listed as an issue here microsoft/SQLServerPSModule#68 but neither have got much traction.

The code above solves the problem and has been tested locally.

[WilliamDAssafMSFT]

I checked, @Matteo-T and @chlafreniere are aware and this is queued. Having presented a solution is very handy, thanks for this and the followup @jpomfret.

[jpomfret]

Thanks @WilliamDAssafMSFT - appreciate it!