Index rebuild/reorg

May 27, 2011 at 11:15 AM

I am thinking of using the Invoke-DBmaint for some maintenance, but have a question.

I would like to update statistics on the indexes that are reorganized (not rebuilt). Is there a way I can accomplish this without having to (re)write some code?

Coordinator
May 28, 2011 at 6:03 PM

It looks like this would be a one-line change to the SQLMaint.ps1:

 

# If frag > 10 and < 30 and pages > 1000 , or choose reorg
                                                if (($_.AverageFragmentation -ge  10 -and $_.AverageFragmentation -le 30  -and $_.Pages -ge 1000) -or $action -eq "IDX_REORG")

$InitialTime = Get-Date -Format "yyyy/MM/dd hh:mm:ss"
                                                       Invoke-SqlIndexDefrag $index
                                                      $endtime = Get-Date -Format "yyyy/MM/dd hh:mm:ss"
 Write-ScriptLog -msg $("Initial Time : $($initialtime) End Time : $($endtime) .Server $($index.Server) Database $($index.$DbName) Table $($index.table) Index $($index.name) Avg Fragmentation $($_.AverageFragmentation) | Reorganize Completed." ) -date $($date) -reporton $($ReportOn)

#Add Call to updatestatistics method on index:

$index.updatestatistics()

#or use full scan or specify sample size etc.

#$index.updatestatistics("FullSCAN")

May 28, 2011 at 6:50 PM

Thanks for your response, Chad.

I really appreciate your work on sqlpsx and your contribution to the powershell community.

 

I see a couple of possibilities here:

 * I implement your suggested change in the dbmaint-module (which will be overwritten with a new version of sqlpsx)

 * I write my own dbmaint-layer on top of your functions in the SQLServer module

 * The dbmaint-module is changed as suggested, maybe with a parameter to  (idx_all / idx_all_stats and idx_reorg / idx_reorg_stats)

 

What would you recommend?

 

Regards,

Coordinator
May 28, 2011 at 10:58 PM

Adding param provides the most flexibility. If you do make the change upload a patch in the source code area and I'll include it in the next release.