search

microsoft / SQLServerPSModule

This repo is the home of SQL Server PowerShell Module development.
MIT License
45 stars 1 forks source link

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

Open jpomfret opened 7 months ago

jpomfret commented 7 months ago

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

image

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 image

Matteo-T commented 4 days ago

Hey @chlafreniere - is this something your Team own these days? If so, could you take a look into it? If not, please assign back to me... and/or we can chat when you have a minute.

jpomfret commented 1 day ago

Hey @Matteo-T \ @chlafreniere,

Thanks for picking this up - it's causing me a bit of a blocker so would be ace to get some movement.

I worked with one of my colleagues to come up with a potential solution - it's documented here as I wasn't sure where the right place was: https://github.com/microsoft/sql-server-samples/issues/1260

Let me know if you need further help testing

Thanks!