# encoding: ascii
# api: powershell
# title: Write-IndexFragmentation
# description: Collects Information about index fragmentation and writes the results to a csv file
# version: 2016.03.02
# type: script
# author: Brandon Warner
# license: CC0
# function: Write-IndexFragmentationReport
# x-poshcode-id: 6424
# x-archived: 2016-07-01T07:55:43
# x-published: 2016-06-28T15:52:00
#
#
<#
.SYNOPSIS
Analyzes SQL Server Index Fragmentation and creates a csv report

.DESCRIPTION
Collects Information about index fragmentation and writes the results to a csv file 

.PARAMETER ServerInstance
The Server\Instance which you want to analyze the index fragmentation on.

.PARAMETER DestFolderPath
The destination folder path

.EXAMPLE
Write-IndexFragmentationReport -ServerInstance 'MyServerInstance' -DestFolderPath 'C:\TEMP' -Verbose

.NOTES
+---------------------------------------------------------------------------------------------+
| REVISION HISTORY:                                                                           |
+---------------------------------------------------------------------------------------------+
|   DATE       AUTHOR          CHANGE DESCRIPTION                                             |
|   ---------- --------------- -------------------------------------------------------------- |
    2016.03.02 Brandon Warner  Initial Draft      
+---------------------------------------------------------------------------------------------+
| UNIT TESTING SCRIPTS:                                                                       |
   
+---------------------------------------------------------------------------------------------+
#>

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.Smo') | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SmoExtended') | Out-Null

function Write-IndexFragmentationReport
  {

    [CmdletBinding()]
    Param
      (
          [Parameter(Mandatory = $true, Position = 1)]
          [string]$ServerInstance

        , [Parameter(Mandatory = $true, Position = 2)]
          [string]$DestFolderPath
      )

    $s = New-Object Microsoft.SqlServer.Management.Smo.Server $ServerInstance
    $s.ConnectionContext.Disconnect() | Out-Null
    $s.ConnectionContext.ApplicationName     = 'PowerShell Script'
    $s.ConnectionContext.LoginSecure         = $true
    $s.ConnectionContext.ConnectTimeout = (60*10)
    $s.ConnectionContext.Connect()
  
    $results = @()

    $s.Databases | where {$_.IsSystemObject -ne $true} | %{
    
      $DatabaseID = $_.ID
      $DatabaseName = $_.Name
      $_.Tables | where {$_.IsSystemObject -ne $true} | 
        %{ 
          $tableName = $_.Name
          $ObjectID = $_.ID
          "Getting Index Data for $DatabaseName.$tableName" | oh
          $_.Indexes | 
            %{
                $IndexID = $_.ID
                "Analyzing index $($_.Name)" | oh
                $thisIndex = $_
                $frag_query = @"
SELECT
    avg_fragmentation_in_percent
  , page_count
  , avg_page_space_used_in_percent
FROM
  sys.dm_db_index_physical_stats($($DatabaseID), $($ObjectID), $($IndexID), NULL, 'LIMITED')
"@
        $frag_query | Write-Verbose
        $query_results                    = $s.Databases[$DatabaseName].ExecuteWithResults($frag_query)
        $page_count                       = $query_results.Tables[0].Rows[0].page_count
        $avg_fragmentation_in_percent     = $query_results.Tables[0].Rows[0].avg_fragmentation_in_percent
        $avg_page_space_used_in_percent   = $query_results.Tables[0].Rows[0].avg_page_space_used_in_percent
        
        $results +=  New-Object -TypeName PSObject -Property @{
            DatabaseName              = $DatabaseName
            TableName                 = $tableName
            IndexName                 = $thisIndex.Name
            IndexType                 = $_.IndexType                  
            AvgFragmentationInPercent = $avg_fragmentation_in_percent    
            AvgPageSpaceUsedPercent   = $avg_page_space_used_in_percent
            SizeMB                    = [Math]::Truncate(($page_count*8)/1024)
            FillFactor                = $thisIndex.FillFactor
          }
        
        }
      }
    }
    if($DestFolderPath.Substring($DestFolderPath.Length - 1, 1) -eq '\')
      { $DestFolderPath = $DestFolderPath.Substring(0,$DestFolderPath.Length-1) }
    $results | select DatabaseName,TableName,IndexName,IndexType,SizeMB,AvgFragmentationInPercent,AvgPageSpaceUsedPercent | 
      Export-Csv "$DestFolderPath\$($ServerInstance -replace '\\','_')_FragmentationReport_$((Get-Date).ToString("yyyy.MM.dd.HH.mm.ss")).csv" -Force -NoTypeInformation
  }
