Comments (2)
jpomfret
commented on May 20, 2024
1
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
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)"
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,)"
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_numberfrom sql-server-samples.
jpomfret
commented on May 20, 2024
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
from sql-server-samples.
Related Issues (20)
- Sql sample files in blob storage unavailable
- Unable to connect to Azure SQL Server from custom edge module.
- instnwnd.sql is not working exactly HOT 1
- Probe IndexStatistics failed to obtain data: Execution Timeout
- Calling Http endpoints in T-SQL using CURL extension - Content Type. HOT 1
- Sql
- Sebas
- Please update Security Permissions Posters and Post to Newer Blog Site
- create database SQL HOT 2
- .
- Incomplete samples/databases/wide-world-importers/wwi-ssdt/wwi-ssdt/PostDeploymentScripts/pds150-ins-app-cities-h.sql script
- Create Database
- install SQL server connect Database engine HOT 1
- Sql
- SqlEngineConfigAction_install_confignonrc_Cpu64 on Windows 11 HOT 1
- Just to mention ....
- Unpicked sales in WorldWideImporters database causes purchases to increase indefinitely?
- Importing AdventureWorks script does not work on fresh install of SSMS 19 HOT 1
- Data generation fails due to invalid records in ColdRoomTemperatures
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from sql-server-samples.