Automating Index Fragmentation Reports
Summary: Guest blogger, Laerte Junior discusses how to use PowerShell to automate an index fragmentation report and generate an Excel file.
One of the day-to-day tasks of a DBA is to maintain your environment. This task includes checking the fragmentation of indexes. In this post, I will show how to automate this collection and generate an Excel file.
Environment considerations
For this particular script, I am assuming that like me, you use a central “collector” machine. For that, you have installed SQL Server 2012 or later, Excel, and Windows PowerShell 4.0 or later. Yes! I will use some coding features that only work in Windows PowerShell 4.0 and later. So you are good to go if your checklist looks exactly like this one:
- Windows PowerShell 4.0 or later
- SQL Server 2012 or later
- Coffee and chocolate donuts
- WooHoo ! Watched the new Star Wars trailer
Coding considerations
Here are some things to consider about running this script:
- $PSItem and PipelineVariable common parameter are being used. This is why you need PowerShell 4.0 or later. $Psitem is a new syntax to the $_ placeholder in Windows PowerShell 3.0, and PipelineVariablean awesome new common parameter supported in Windows PowerShell 4.0. For more information, see:
- Invoke-SQLCMD is used. It is a cmdlet supported by the SQLPS provider. In early versions of SQL Server, SQLPS is not a module. In this code, I am importing it as a module, so that is why you need a central repository machine with SQL Server 2012 or later to collect the data.
- This script is used to automate the collection of the data, so it will be scheduled and called by the Windows Task Scheduler. This means that I need to log everything to check if something bad happened. I am using a Start/Stop transcript for that operation.
- A list with the SQL Server instance names. The script is using a text file called instancenames.txt. It can be easily changed to query a table in the central repository with the all SQL Server instance names.
- If a SQL Server instance could not be connected, the script cannot stop. It needs to skip the instance, log, and flow the script to the next instance name. To perform this operation, I am using an error handle plan with Try–Catch–Finally and the ErrorVariable common parameter.
- I am not using SMO to retrieve the index fragmentation information. SMO has a known bug and I want to keep my code as simple as I can—not doing tons of conditions to check what version of SQL Server is to use SMO. For more information, see SMO and Index Fragmentation.
- I am using T-SQL directly with Invoke-SQLCMD to gather the index fragmentation and SMO to connect and run into the databases.
- KISS–L (keep it simple, stupid Laerte). We are members of a team, and as a team, I need to remember that other people will maintain my code. There are very good best practices documented for coding in shared and corporate environments, but essentially I like to say, “Use common sense.”
- Tons of verbose and coding comments. Yes. Everything needs to be logged deeply and people need to understand what I did.
- Do not reinvent the wheel. Check the Windows PowerShell community channels and you will find a lot of very good functions, scripts, and ideas already posted by the top of the PowerShell heroes. Believe me, I live (virtually) with them. They are the best. Literally, the Council Jedi.
Coding flow
The idea of this script is to automate the report of index fragmentation. The control flow is:
- Run into the SQL Server instance names
- Connect to the instance
- Run into the databases
- Retrieve the index fragmentation to each table
- Export to CSV files by database
- Generate an Excel file with the databases from the instance as worksheets. The Excel name will have the name of the instance.
To generate the Excel file from the CSV files, I am using the awesome function from my fellow MVP, Luc Dekens. Check it out: Beyond Export-Csv: Export-Xls.
Here’s the code
Note You can download this code from GitHub: LaerteJunior/ScriptingGuysNov2015.
Let’s get deep into the code…
Check and configure the location of the script and initial configurations
#getting the location of the script to create the log and instance directories.
#if error , need to stop the script
$scriptRoot = Split-Path -Parent $MyInvocation.MyCommand.Definition
$PSScriptRoot = $scriptRoot
Set-Location $PSScriptRoot
-
- Create the paths
try {
$Creator = @{
$True = { $null}
$False = {$null = New-Item -Path $Path -ItemType Directory }
}
$Path = “$($PSScriptRoot)\Log”
& $Creator[(Test-Path $Path)]
} catch {
throw “Could not create the LOG directory”
}
Test to start the script
#Trying to import SQLPS module. If could not, need to stop the script
try {
import-module SQLPS -DisableNameChecking
} catch {
throw “Could not import the SQLPS module”
}
#Check to SMO. If could not, need to stop the script
if ([Reflection.Assembly]::LoadWithPartialName(“Microsoft.SqlServer.SMO”) -eq $null -or ([System.Reflection.Assembly]::LoadWithPartialName(“Microsoft.SqlServer.ConnectionInfo”) -eq $null)) {
Throw “SMO not avaliable”
}
Define some variables
#TSQL index fragmentation
$SQLIndex = “
SELECT object_name(IPS.object_id) AS [TableName],
SI.name AS [IndexName],
IPS.Index_type_desc,
IPS.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(db_id(), NULL, NULL, NULL , ‘DETAILED’) IPS
JOIN sys.tables ST WITH (nolock) ON IPS.object_id = ST.object_id
JOIN sys.indexes SI WITH (nolock) ON IPS.object_id = SI.object_id AND IPS.index_id = SI.index_id
WHERE ST.is_ms_shipped = 0 “#and IPS.page_count > 1000”
#variable to use to store the instance CSV created. Will use to generate the Excel files.
$PathCSV = @()
#Variable to load the instance names
$InputInstances = “$($PSScriptRoot)\InstanceNames.txt”
#Verbose preference to show evertything
$VerbosePreference = ‘Continue’
Load the instance names and check the connection
Try {
#Loading the Instance Names and storing in the InstanceName Variable by the awesome
pipelinevariable common parameter
Get-Content $InputInstances -PipelineVariable InstanceName -ErrorAction Stop |
#foreach in the instances
ForEach-Object {
Write-Verbose “Connecting instance $($InstanceName)”
try {
try {
#clean the InstanceSkipped variable. It is stored in the write error to skip the instances
with error
$InstanceSkipped.Clear()
$Connection = new-object (“Microsoft.SqlServer.Management.Common.ServerConnection”) $InstanceName
$Connection.Connect()
} catch {
Write-Error “Could not connect on instance $($InstanceName) . Error : Error $($_.Exception.Message)” -ErrorVariable InstanceSkipped
} finally {
#if the instance was not skipped by any error
if (!($InstanceSkipped)) {
Define some variables and collect the data
#replacing the “\\” by _ to create the paths and csv files
$InstanceNamePath = $InstanceName -replace “\\”,”_”
#acumulating the paths to the $pathCSV to use to generate the Excel file and
# storing the current path to the $path variable
$PathCSV += $Path = “$($PSScriptRoot)\Instances\$($InstanceNamePath)”
#removing the path and all inside it if it exits. I am not handling error here or checking if exists do remove, I dont need it
#if not exists and try to remove will generate error that will be supressed and if exists will be removed
Remove-Item -Recurse -Path $Path -Force -ErrorAction SilentlyContinue
New-Item -Path $Path -ItemType Directory -Force
#connecting to the Instance. At this point I know that the instance is online
#and storing the database enumeration to the DatabaseName Variable using the awesome
#pipelinevariable common parameter
$SQLServer = new-object (“Microsoft.SqlServer.Management.Smo.Server”) $Connection
$SQLServer.Databases |
Where-Object {!($PSItem.IsSystemObject)} -PipelineVariable DatabaseName |
foreach-object {
#running the TSQL and saving to a CSV
invoke-sqlcmd -ServerInstance $InstanceName -Query $SQLIndex -Database $DatabaseName.name
-verbose:$false |
Select-Object @{N=’Table Name’;E={$_.TableName}},
@{N=’Index Name’;E={$_.IndexName}},
@{N=’Index Type’;E={$_.Index_type_desc}},
@{N=’Fragmentation’;E={$_.avg_fragmentation_in_percent}} |
Export-Csv “$($Path)\$($DatabaseName.name).csv” -NoClobber -NoTypeInformation
Generate the Excel file
try {
if ($PathCSV) {
$PathCSV |
ForEach-Object {
$PathToSave = “$($Psitem)\$(($Psitem -split ‘\\’)[-1]).xlsx”
dir “$($Psitem)\*.csv” |
ForEach-Object {
try {
$data = Import-Csv $Psitem.fullname
Export-Xls $data $PathToSave -WorksheetName $Psitem.name -AppendWorksheet
Write-Verbose “Excel created for $($PathToSave)”
} catch {
write-Error “Oh Boy..something bad happened. Error : Error $($_.Exception.Message)”
}
}
}
}
} catch {
write-Error “Oh Boy..something bad happened. Error : Error $($_.Exception.Message)”
}
Schedule and register the script
I am using scheduled jobs in Windows PowerShell 3.0, so it just creates the trigger:
$trigger = New-JobTrigger -Daily -At 1am
Register the job in the Windows Task Scheduler by pointing to the .ps1 file:
Register-ScheduledJob -Name EnergyAnalysisJob -Trigger $trigger -FilePath ‘C:\laerte\scripting guys\indexes.ps1’
For more information, see Scheduling Background Jobs in Windows PowerShell 3.0.
That is it. Thanks to my good friend, Ed Wilson, for allowing me to talk a little bit about my passion: automation!
~Laerte
Thanks, Laerte. Awesome post.
I invite you to follow me on Twitter and Facebook. If you have any questions, send email to me at scripter@microsoft.com, or post your questions on the Official Scripting Guys Forum. See you tomorrow. Until then, peace.
Ed Wilson, Microsoft Scripting Guy
Light
Dark
0 comments