Sample Header Ad - 728x90

Index Maintenance on busy OLTP database

0 votes
4 answers
301 views
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.
Asked by SQL_NoExpert (1117 rep)
Mar 23, 2021, 02:28 PM
Last activity: May 22, 2025, 02:03 PM