Use this script to get a quick view of the worst index fragmentation per SharePoint 2010 database. I create this script when I noticed that the SPHealthAnalyzer DBREINDEX job didn't seem to fix my fragmentation as previously imagined. Performance may suffer on databases where fragmentation levels exceed 40%. Refer to SQL Server and SharePoint guidance on how to fix index fragmentation. 

PowerShell
Edit|Remove
#############################################################################################
# GetDatabaseFragmentationLevel
#   	
# Gets the precentage value of fragmentation (%) for the most fragmented index in each
#  SharePoint database in the SharePoint 2010 farm.
# User must have rights to query master database
#############################################################################################

## Add the SharePoint SnapIn just in case this script wasnt run in the SharePoint mgmt console
Add-PSSnapin Microsoft.SharePoint.PowerShell;

## Enumerate each service instance
get-spserviceinstance -all | foreach-object {

	## Locate the database service instance(s)
	if ($_.typeName -eq "Microsoft SharePoint Foundation Database") {
	
		Write-Host "Databases on"$_.Server.Name
		## Create a connection to the master DB using the sql instance name and default integrated security
		$connString = "Provider=sqloledb;Data Source=" + $_.Server.Name + ";Initial Catalog=Master;Integrated Security=SSPI;"
		$conn = New-Object System.Data.OleDb.OleDbConnection $connString
		
		## Enumerate each DB on this instance and query sys.databases for important info
		foreach ($Database in $_.Databases){
		
		Write-Host "Database:" $Database.Name
		## Create a DataSet for storing Database Properties which we'll need later
		$dataset = New-Object System.Data.DataSet
		
			#Create a string to hold our variable for our DB_ID
		
			$DeclareIDSQLString = "DECLARE @DBId int;"
			
			#Create a string to hold our query that assigns the @DBId variable

			$SelectIDSQLString = "SELECT @DBId = database_id		
								FROM sys.databases
								WHERE name='" + $Database.Name + "';"
								
			#Create a string to create a query to return the max fragmentation level of each databases
			#This query returns the worst index fragmentation level for a particular database
			#This query passes null values to most parameters and returns values based on default parameters
			#This query only gathers external fragmentation levels using the limited view option
			#Pass the detailed parameter of sys.dm_db_index_physical_stats to get internal fragmentation
			
			$SelectFragLevelSQLString = "SELECT d.name, MAX(avg_fragmentation_in_percent) as Fragmentation_Level
								FROM sys.dm_db_index_physical_stats(@DBId, 0, null, 0, null), 
									sys.databases d
								WHERE @DBId = d.database_id
								GROUP BY d.name;"
								
			$FullSQLString = $DeclareIDSQLString + $SelectIDSQLString + $SelectFragLevelSQLString;	
			
			## Create an OleDb command object to execute our query
			$cmd = New-Object System.Data.OleDb.OleDbCommand $FullSQLString, $conn
			$conn.Open()
			
			## Create a data adapter to handle our query data 
			$adapter = New-Object System.Data.OleDb.OleDbDataAdapter $cmd
			
			## Load our data into our DataSet
			[void] $adapter.Fill($dataSet, "Database")
			
			## Close our connection to SQL to keep things tidy
			$conn.Close()			
			
			## Get all the data we pulled back from our query 
			$dataSet.Tables | Select-Object -Expand Rows
		}
		
	}
}