I recently implemented the Maintenance Solution as a nightly job in my company's database, via Windows Scheduler. It works as intended, except for one issue - the procedures are being created thousands of times despite the nightly task only executing them.
This is the script:
rem Run maintenance
sqlcmd -S censoredDatabaseName -E -Q "EXEC sp_Maintenance" > "censoredLogFolder"
rem Delete old files
PushD "censoredBackupLocation" && (forfiles -d -30 -c "cmd /c del /q @path") & PopD
And here is sp_Maintenance:
-- maintenance scripts stolen from Ola Hallengren
-- backup database to network drive
EXEC dbo.DatabaseBackup
@BackupType = 'FULL',
@Databases = 'USER_DATABASES',
@Directory = 'censoredBackupLocation'
-- optimise indexes
EXECUTE dbo.IndexOptimize
@Databases = 'USER_DATABASES',
-- Don't touch low fragmentation indexes
@FragmentationLow = NULL,
-- moderate/highly fragmented indexes are reorganised or rebuilt
@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
-- definition of fragmentation levels
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30,
-- don't bother for small indexes
@MinNumberOfPages = 100
-- refresh statistics
EXECUTE dbo.IndexOptimize
@Databases = 'USER_DATABASES',
-- skip index optimisation, it's done above
@FragmentationLow = NULL,
@FragmentationMedium = NULL,
@FragmentationHigh = NULL,
-- activate statistics commands
@UpdateStatistics = 'ALL'
-- check integrity of database
EXEC dbo.DatabaseIntegrityCheck
@Databases = 'ALL_DATABASES'
For reference, here is the top 5 queries from sp_BlitzCache. The task is run at 2am, the same time as listed for each of the CREATE PROCEDURE queries.
| Cost | Query Text | Warnings | # Executions | Total CPU (ms) | Last Execution |
|---|---|---|---|---|---|
| 0 | CREATE PROCEDURE sp_Maintenance AS … | Parameter Sniffing, Low Cost High CPU | 10 | 82396 | 22/12/2022 2:00 |
| 0.032869 | a SELECT query | Forced Serialization | 658864 | 61861 | 22/12/2022 8:02 |
| 0 | CREATE PROCEDURE dbo.IndexOptimize … | We couldn't find a plan for this query. More info on possible reasons: https://www.brentozar.com/go/noplans | 20 | 48377 | 22/12/2022 2:00 |
| 0 | CREATE PROCEDURE dbo.CommandExecute … | We couldn't find a plan for this query. More info on possible reasons: https://www.brentozar.com/go/noplans | 5422 | 47340 | 22/12/2022 2:00 |
| 0 | CREATE PROCEDURE dbo.CommandExecute … | We couldn't find a plan for this query. More info on possible reasons: https://www.brentozar.com/go/noplans | 58 | 30267 | 22/12/2022 2:00 |
It doesn't look like there is any CREATE PROCEDURE statements anywhere in my scripts, nor in any of the Maintenance Solution scripts. Is this something I need to be worried about? The company isn't international, so it's guaranteed that no one will be on the server when the maintenance job is running.
