0

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'
3
  • With those quoted out, it will use the default values of the stored procedure (which are likely the same). You'd have to script out IndexOptimize to see what they are, though. Sep 3, 2021 at 17:56
  • Erik - Would this cause my execution plans to be wiped every day?
    – Steve
    Sep 3, 2021 at 20:05
  • There is a strong correlation between updating statistics, the statistics update that accompanies indexes being rebuilt, and execution plan eviction. There could also be memory pressure on the box during index maintenance that wipes the plan cache. Sep 4, 2021 at 15:18

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Browse other questions tagged or ask your own question.