Performance

New-sqmOlaMaintenanceJobs

Creates three Ola Hallengren maintenance jobs — IndexOptimize (USER_DATABASES), IntegrityCheck (USER_DATABASES), and IntegrityCheck (SYSTEM_DATABASES) — with extensive fragmentation and CHECKDB tuning defaults.

Module: sqmSQLTool
Requires: dbatools
ShouldProcess: Yes
Output: PSCustomObject

Execution Flow

START dbatools available? throw error Test-sqmOlaInstallation -RequiredSet Index Ola objects installed? Status=Failed, return Yes internal _CreateJob helper, called 3× New-DbaAgentJob + step + schedule (Sunday 23:00) Job 1: IndexOptimize @Databases='USER_DATABASES' Job 2: IntegrityCheck @Databases='USER_DATABASES' Job 3: IntegrityCheck @Databases='SYSTEM_DATABASES' Return PSCustomObject (OverallStatus per-job) DONE
Requires Ola Hallengren's maintenance solution objects to already be installed (checked via Test-sqmOlaInstallation -RequiredSet Index). Job names are resolved from Get-sqmConfig (OlaJobNameIndexOpt, OlaJobNameIntUserDb, OlaJobNameIntSysDb). OverallStatus can be PartialSuccess if one of the three jobs fails while others succeed.

Parameters

ParameterTypeRequiredDescription
-SqlInstanceStringOptionalTarget SQL instance. Default: $env:COMPUTERNAME.
-SqlCredentialPSCredentialOptionalSQL authentication credential.
-FragmentationLevel1IntOptionalReorganize threshold (%). Default: 5.
-FragmentationLevel2IntOptionalRebuild threshold (%). Default: 30.
-MinNumberOfPagesIntOptionalMinimum index size considered. Default: 1000.
-FillFactorIntOptionalRebuild fill factor. Default: 90.
-MaxDOPIntOptionalMax degree of parallelism. Default: 0 (server default).
-SortInTempdbStringOptionalY/N. Default: Y.
-UpdateStatisticsStringOptionalALL / COLUMNS / INDEX. Default: ALL.
-CheckCommandsStringOptionalOla CheckCommands. Default: CHECKDB.
-PhysicalOnlyStringOptionalY/N. Default: N.
-NoIndexStringOptionalY/N. Default: N.
-ScheduleTimeStringOptionalDefault: 23:00.
-ScheduleDayIntOptionalSQL Agent weekday bitmask. Default: 1 (Sunday).
-EnableExceptionSwitchOptionalThrows terminating errors instead of returning an error status object.
-WhatIf / -ConfirmSwitchOptionalStandard ShouldProcess support.

Return Value

Returns a PSCustomObject with: SqlInstance, JobNameIndexOpt, JobNameIntUserDb, JobNameIntSysDb, IndexOptStatus, IntUserDbStatus, IntSysDbStatus, OverallStatus (PartialSuccess / WhatIf / Success / Failed), Message.

Examples

Example 1 — Create all three jobs with defaults

New-sqmOlaMaintenanceJobs -SqlInstance "SQL01"

Example 2 — Custom fragmentation thresholds

New-sqmOlaMaintenanceJobs -SqlInstance "SQL01" -FragmentationLevel1 10 -FragmentationLevel2 40 -MaxDOP 4