October 13, 2017 at 6:09 am
Dear all,
I was requested to create a maintance plan keep our data warehouses working properly.
This means that I will create a job to rebuild indexes (weekend basis), another to update statistics (daily basis) . One for DBCC checkDB (daily)
Can I have some advices from your side on what to consider and in which subjects should I touch in order to create this jobs?
Thank you,
Pedro
October 13, 2017 at 6:34 am
Theres a number of ways, create a normal SQL maintenance plan adding in the relevant tasks. Use Ola Hallengren's scripts, look at MinionWare, or write your own routines.
Personally I would use Ola or Minion, SQL Maintenance plans are to clunky for my liking.
But you also need to see how the ETL load processes are run. If they are dropping and recreating the indexes on each load, why spend the time maintaining them, if they are trickle feed, slowly changing and the indexes don't get dropped recreated on the loads then yes.
October 13, 2017 at 7:06 am
I use Ola's solution, but try to adjust some of the default settings based on your environment, I specify below parameters:
sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d Maint_DB -Q "EXECUTE [dbo].[IndexOptimize] @databases = 'USER_DATABASES', @FragmentationLevel1 = 30,@FragmentationLevel2 = 60,@PageCountLevel = 1000,@UpdateStatistics ='ALL',@OnlyModifiedStatistics = 'Y',@MaxDOP = 0,@LogToTable = 'N'" -b
October 25, 2017 at 3:12 am
Hello,
What is the link to find the OLA scripts?
October 25, 2017 at 3:21 am
river1 - Wednesday, October 25, 2017 3:12 AMHello,What is the link to find the OLA scripts?
They are all here:
https://ola.hallengren.com/
Thanks
October 25, 2017 at 3:25 am
Thank you
October 25, 2017 at 3:45 am
Thank you very much. It seems very good.
I was readying and I don't understand one thing.
Normally, it I reorganize indexes I always do an update staticts. But in this scripts it seems that I have to do it in two separate steps, like:
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
Where I say that I want on low frag level to do notning (if index frag is equal or below 5%), reorganize, in case medium (below 30% and bigger then 5%) and rebuild in case of more then 30 %. Sounds great. But then, I would like to say that in case of index reorg, I would do an update statistics . In case of rebuild no need.
How are you doing this?
If I do the below, I will satisfy all my conditions, correct?
Rebuild or reorganize all indexes with fragmentation and update modified statistics on all user databases
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,
@UpdateStatistics = 'ALL',
@OnlyModifiedStatistics = 'Y'
October 25, 2017 at 6:06 am
My understanding is that when you rebuild an index, only index stats gets updated but it does not update column stats. What you have is correct, you are saying do update stats only when it is modified. I also recommend you to read https://www.brentozar.com/archive/2014/12/tweaking-defaults-ola-hallengrens-maintenance-scripts/ where he suggests using different fragmentation levels.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply