Index Maintenance on busy OLTP database I have a database where constant read writes happening all the time. Searches are being done with wild card entries. The server is on SQL 2016 standard edition.
There is no budget for enterprise nor any intention to not using the wild card searches, and there is no maintenance window.
Since wildcard searches are being made indexes are also of no use as it is doing a full table scan and thus creates locking.
I am using Ola Hallengren script for indexing but the indexes maintenance is taking 10-12 hours to complete for a 300gb database as it busy 24/7.Below is the script
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'
This has become a frustrating issue and I am about to quit. Please advise what are my options here.
Don't do index defrag is one option. Did you measure the improvements from defrag in the first place?
HQISILON Doesn't say anything to me, your disk subsystem is one aspect. But also the query load, the execution plans etc. Best is to have something load to measure what you actually gain.
A parallel work should be to investigate whether you have index that don't help you. Sys.dm_db_index_usage_stats can be useful here.
If blocking is your issue then consider doing reorg instead of rebuild. I.e., change the parameters to the proc since you now do rebuild if &gt; 30%. Reorg is more online-ish than rebuild (in offline mode).