Check SQL Server average percent fragmentation using PowerShell

Introduction

This script can be used to check your SQL Server average percent fragmentation and list the fragmentation between 5 and 30 or more than 30 percent.

Scenarios

By default, we can use New-OSCSqlConnection to create a SqlConnection and then use Get-OSCFragmentationInfo to get fragmented tables. Also, you can use Windows Authentication or SQL Server Authentication to connect your SQL Server. If you want to use SQL Server Authentication, just need to uncomment some code in this script.

Script

This script contains two advanced functions New-OSCSqlConnection and Get-OSCFragmentationInfo. You can use them in following ways:

Method 1:

Download the script and copy it to your computer.
Open the script file by using Notepad or any other script editors.
Scroll down to the end of the script file, and then add the example command which you want to run.
Save the file, and then run the script in PowerShell console.

Method 2:

Rename scriptname.ps1 to scriptname.psm1 (PowerShell Module file)
Run Import-Module cmdlet to import this module file.
Import-Module filepath\scriptname.psm1

Here are some code snippets for your references. To get the complete script sample, please click the download button at the beginning of this page.

PowerShell

Edit|Remove

#Global object to store SqlConnection. Also,avoid duplication of user input. 
$scriptEnvVars = New-Object PSObject
$scriptEnvVars | Add-Member NoteProperty "SqlConnection" $null
 
##Windows Authentication
# Create SqlConnection object and define connection string
$oConnection = New-Object System.Data.SqlClient.SqlConnection
$oConnectionB = New-Object System.Data.SqlClient.SqlConnectionStringBuilder
$oConnectionB["Data Source"] = $ServerName
$oConnectionB["Database"] = "master"
$oConnectionB["Trusted_Connection"] = "SSPI"
$oConnection.ConnectionString = $oConnectionB.ConnectionString
 
#Sql query statement to get all fragmented databases
    $sqlQuery = "DECLARE @dbCount INT SELECT @dbCount = Count(*) FROM [master].[sys].[databases] DECLARE @index INT SET @index=1 DECLARE @dbName NVARCHAR(2000)DECLARE @dbStatus NVARCHAR(100) DECLARE @sql NVARCHAR(max) DECLARE @tempTable TABLE (Database_Name NVARCHAR(2000),Table_Name NVARCHAR(2000),Index_Name NVARCHAR(2000),Index_Id INT,Index_Level INT,Average_Percent_Fragmentation FLOAT ) WHILE(@index<=@dbCount) BEGIN SELECT @dbName='['+A.dbName+']',@dbStatus=A.dbStatus FROM(SELECT database_id AS ID, name AS dbName, state_desc AS dbStatus FROM [master].[sys].[databases] WHERE  is_distributor <> 1 AND name NOT IN ( 'master', 'model', 'msdb', 'tempdb' ))AS A WHERE A.ID=@index SET @sql='USE '+@dbName +' SELECT Db_name(B.database_id) AS ''Database Name'', Object_name(B.object_id)   AS ''Table Name'', name AS ''Index Name'', B.index_id                 AS ''Index ID'', index_level AS ''Index Level'', avg_fragmentation_in_percent AS ''Average Percent Fragmentation'' FROM sys.dm_db_index_physical_stats(Db_id(), NULL, NULL, NULL, ''DETAILED'') AS B JOIN sys.indexes AS C ON B.object_id = C.object_id AND B.index_id = C.index_id WHERE name IS NOT NULL' IF(UPPER(@dbStatus) = 'ONLINE') BEGIN INSERT INTO @tempTable EXEC(@sql) END SET @index=@index+1 END SELECT * FROM @tempTable"
    
#Create SqlDataAdapter object with command text and connection
$sqlDataTable = New-Object System.Data.DataTable
$oSQLAdapter = New-Object System.Data.SqlClient.SqlDataAdapter($sqlQuery,$scriptEnvVars.SqlConnection)
$oSQLAdapter.Fill($sqlDataTable) | Out-Null
    
#Close SqlConnection
$scriptEnvVars.SqlConnection.Close()
    
#List the objects whose average percent fragmentation is between 5 to 30 percent
if($Between5And30Percent)
{
    Write-Host $Message.Between5And30Percent
    $sqlDataTable = $sqlDataTable | Where-Object{
        $_.Average_Percent_Fragmentation -gt 5 -and $_.Average_Percent_Fragmentation -le 30
    }
        
    if($sqlDataTable -eq $null)
    {
        Write-Host $Message.NoFragmentedTables
        return $null
    }
}

#Global object to store SqlConnection. Also,avoid duplication of user input.  
$scriptEnvVars = New-Object PSObject 
$scriptEnvVars | Add-Member NoteProperty "SqlConnection" $null 
  
##Windows Authentication 
# Create SqlConnection object and define connection string 
$oConnection = New-Object System.Data.SqlClient.SqlConnection 
$oConnectionB = New-Object System.Data.SqlClient.SqlConnectionStringBuilder 
$oConnectionB["Data Source"] = $ServerName 
$oConnectionB["Database"] = "master" 
$oConnectionB["Trusted_Connection"] = "SSPI" 
$oConnection.ConnectionString = $oConnectionB.ConnectionString 
  
#Sql query statement to get all fragmented databases 
    $sqlQuery = "DECLARE @dbCount INT SELECT @dbCount = Count(*) FROM [master].[sys].[databases] DECLARE @index INT SET @index=1 DECLARE @dbName NVARCHAR(2000)DECLARE @dbStatus NVARCHAR(100) DECLARE @sql NVARCHAR(max) DECLARE @tempTable TABLE (Database_Name NVARCHAR(2000),Table_Name NVARCHAR(2000),Index_Name NVARCHAR(2000),Index_Id INT,Index_Level INT,Average_Percent_Fragmentation FLOAT ) WHILE(@index<=@dbCount) BEGIN SELECT @dbName='['+A.dbName+']',@dbStatus=A.dbStatus FROM(SELECT database_id AS ID, name AS dbName, state_desc AS dbStatus FROM [master].[sys].[databases] WHERE  is_distributor <> 1 AND name NOT IN ( 'master', 'model', 'msdb', 'tempdb' ))AS A WHERE A.ID=@index SET @sql='USE '+@dbName +' SELECT Db_name(B.database_id) AS ''Database Name'', Object_name(B.object_id)   AS ''Table Name'', name AS ''Index Name'', B.index_id                 AS ''Index ID'', index_level AS ''Index Level'', avg_fragmentation_in_percent AS ''Average Percent Fragmentation'' FROM sys.dm_db_index_physical_stats(Db_id(), NULL, NULL, NULL, ''DETAILED'') AS B JOIN sys.indexes AS C ON B.object_id = C.object_id AND B.index_id = C.index_id WHERE name IS NOT NULL' IF(UPPER(@dbStatus) = 'ONLINE') BEGIN INSERT INTO @tempTable EXEC(@sql) END SET @index=@index+1 END SELECT * FROM @tempTable" 
     
#Create SqlDataAdapter object with command text and connection 
$sqlDataTable = New-Object System.Data.DataTable 
$oSQLAdapter = New-Object System.Data.SqlClient.SqlDataAdapter($sqlQuery,$scriptEnvVars.SqlConnection) 
$oSQLAdapter.Fill($sqlDataTable) | Out-Null 
     
#Close SqlConnection 
$scriptEnvVars.SqlConnection.Close() 
     
#List the objects whose average percent fragmentation is between 5 to 30 percent 
if($Between5And30Percent) 
{ 
    Write-Host $Message.Between5And30Percent 
    $sqlDataTable = $sqlDataTable | Where-Object{ 
        $_.Average_Percent_Fragmentation -gt 5 -and $_.Average_Percent_Fragmentation -le 30}if($sqlDataTable -eq $null) 
    { 
        Write-Host $Message.NoFragmentedTables 
        return $null 
    }}

Examples

Example 1: Display help about New-OSCSqlConnection
Command: Get-Help New-OSCSqlConnection
Screenshot:

Example 2: Create SqlConnection
Command: New-OSCSqlConnection -ServerName "TestServerName\TestInstanceName"
Screenshot:

Example 3: Display help about Get-OSCFragmentationInfo
Command: Get-Help Get-OSCFragmentationInfo
Screenshot:

Example 4: List the objects whose average percent fragmentation is between 5 to 30 percent.
Command: Get-OSCFragmentationInfo -Between5And30Percent
Screenshot:

Example 5: List the objects whose average percent fragmentation is more than 30 percent.
Command: Get-OSCFragmentationInfo -MoreThan30Percent
Screenshot:

Prerequisite

Windows PowerShell 2.0

Additional Resources

Technical Resources:
http://support.microsoft.com/kb/2755960