After running sp_blitzcache (over time) I have found that everyday my plan cache is compiling new execution plans. 100% of my plan cache is built within the last 24 hours (each day).
I think these index rebuilds/reorganizes are causing this. I have found this job running every single night (I will switch it to once a week).
My question is; with these variables being commented out and not being defined in this script, won't this run on every index without any parameters? I think the previous DBA's intentions were to run the rebuilds and reorganizes when fragmentation was over 5% (for reorganize) and over 30% (for rebuilds), and I think this is just running it without parameters, is that the case?
--sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d master -Q "EXECUTE [dbo].[IndexOptimize] @databases = 'USER_DATABASES', @FragmentationLow = NULL,
--@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE', @FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
--@FragmentationLevel1 = 5,@FragmentationLevel2 = 30,@LogToTable = 'Y'" -b
USE master
GO
EXECUTE dbo.IndexOptimize
@databases = 'USER_DATABASES', @FragmentationLow = NULL,
@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationLevel1 = 5,@FragmentationLevel2 = 30,@LogToTable = 'Y'
IndexOptimize
to see what they are, though.