SQLOptimizer is a PowerShell script that uses standard .Net framework classes to connect to a specified SQL instance and perform optimization routines on indexes or heaps.  It also attempts to perform optimization on certain objects such as internal tables that are only accessible via the dedicated administrator console in SQL Server.

There are two main functions of interest.  Get-SqlFragmentation and Invoke-SqlOptimization; parameters are displayed below.  Full help is available in the script.  Both functions offer a -PageCountMinimum filter which is set to '1000' by default.  The page count is a measure of the size of a specific object and due to how SQL Server interally arranges its data, sometimes there is no way to reduce fragmentation in objects that are very small.  Luckily they are so small that fragmentation does not have any substantial impact on database operation.

PowerShell
Edit|Remove
Get-SqlFragmentation [-Server] <String>  
   [[-FragmentationMinimum] <Int32>] [[-PageCountMinimum] <Int32>] [-All]  
     
 
S V:\> Get-SqlFragmentation -Server 'MyServer' -PageCountMinimum 0 
 
Name                                              Fragmentation PageCount 
----                                              ------------- --------- 
[CM_XYZ].[dbo].[AlertClass].[AlertClass_PK]               33.33         3 
[CM_XYZ].[dbo].[ArticleData].[ArticleData_AK]             60.00         5 
[CM_XYZ].[dbo].[CI_Models].[CI_Models_PK]                 50.00         4 
[CM_XYZ].[dbo].[CI_Rules].[CI_Rules_AK]                   50.00         2 
[CM_XYZ].[dbo].[CI_SettingMap].[CI_SettingMap_PK]         66.67         3 
[CM_XYZ].[dbo].[ClientKeyData].[ClientKeyData_PK]         66.67         3 
[CM_XYZ].[dbo].[ContentDPMap].[ContentDPMap_PK]           66.67         3 
[CM_XYZ].[dbo].[DataItem].[DataItem_AK]                   50.00         2 
[CM_XYZ].[dbo].[DataItem].[DataItem_PK]                   66.67         3 
[CM_XYZ].[dbo].[Feature_EC].[Feature_EC_PK]               66.67         3 
[CM_XYZ].[dbo].[GroupMap].[GroupMap_AK]                   50.00         2
PowerShell
Edit|Remove
Invoke-SqlOptimization [-Server] <String> [[-Database] <String>]  
   [[-FragmentationMinimum] <Int32>] [[-PageCountMinimum] <Int32>]  
   [-IncludeInternalIndexes] [-IncludeSystemDatabases]  
   [-DoOfflineOperations] [-UseEnterpriseEditionFeatures] [-All] [-WhatIf] 
 
PS V:\> Invoke-SqlOptimization -Server 'MyServer' -PageCountMinimum 0 
Optimizing: [CM_XYZ].[dbo].[Add_Remove_Programs_DATA].[Add_Remove_Programs_DATA_AK] 
Optimizing: [CM_XYZ].[dbo].[Add_Remove_Programs_HIST].[Add_Remove_Programs_HIST_PK] 
Optimizing: [CM_XYZ].[dbo].[ADDiscoveryStats].[ADDiscoveryStats_PK] 
Optimizing: [CM_XYZ].[dbo].[AdminUIContent].[AdminUIContent_PK] 
Optimizing: [CM_XYZ].[dbo].[AlertClass].[AlertClass_PK] 
Optimizing: [CM_XYZ].[dbo].[AlertVariableClass].[AlertVariableClass_PK] 
Optimizing: [CM_XYZ].[dbo].[ArticleData].[ArticleData_AK] 
Optimizing: [CM_XYZ].[dbo].[BGB_ServerHistory].[BGB_ServerHistory_PK] 
Optimizing: [CM_XYZ].[dbo].[CH_ClientSummaryHistory].[CH_ClientSummaryHistory_PK]
Unlike similiar scripts, all data enumeration and textual processing is performed in PowerShell for those who are not familiar with T-SQL and wish to modify the script.  Using PowerShell also make easy to run from a scheduled task for those not familiar with SQL maintenance plans:
PowerShell
Edit|Remove
PowerShell -File 'SQLOptimizer.ps1' -Server MyServer [Other Options]
By default, all operations are performed using 'online' functionally such as REORGANIZE or REBUILD WITH (ONLINE = ON).  This means that table is still accessible when performing the optimization.  However, this type of optimization cannot optimize some objects or optimize objects fully due their technical configuration or active usage.  SQL Optimizer can do offline operations using -DoOfflineOperation which uses REBUILD operations.  This does not take the database offline, but may impact access to the specific table when a specific index in that table is being optimized.  

Feedback

Any feedback about this module, ideas for improvement, or bug reports should be provided on my GitHub issue tracker (link).  The GitHub repository will also always have the latest version of the module with minor version changes; major version changes will be updated on the Script Center site.