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,
    @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.

  • what sort of disks does your server have ? Mar 23, 2021 at 14:29
  • its on VM but not extreme io Mar 23, 2021 at 14:31
  • 1
    well that doesn't answer my question at all - if you have solid state disks then index maintenance is largely unnecessary because there is no cost penalty to doing random access vis a vis sequential access (no spinning disk). Mar 23, 2021 at 14:34
  • The disks are on HQISILON...not ssd Mar 23, 2021 at 14:37
  • I was able to move away from a scenario like this by using partitioning and archiving old data with a union all, without downtime. Would those be an option or does old data get changed too? Mar 23, 2021 at 18:48

4 Answers 4


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 > 30%. Reorg is more online-ish than rebuild (in offline mode).


By the time Ola Hallengren script is completed the index will be fragmented again.

  1. Scale up your server (increase memory and CPU)
  2. Substitute HDD with SDD
  3. Buy a license from SentryOne, SQL Monitor, SQL Diagnostic Manager, Database Performance Monitoring, etc... and fix those blocking and deadlocks

The index fragmentation is not a problem, is a symptom of something else: fix that, not the fragmentation


so the real problem is this:

Since wildcard searches are being made indexes are also of no use as it is doing a full table scan and thus creates locking.

Not the fragmentation itself.

But without removing wildcards search the only this i see that can help you is adding full-text searching to those columns, and use the full-text query capabilities of SQL Server.

Here you can find how they work:


  • The query uses leading wildcard searches like '%abcd%' and If I am not wrong full text does not work with leading wildcard entries. Mar 24, 2021 at 17:07
  • I assumed you had a margin to make changes.
    – MBuschi
    Mar 24, 2021 at 18:08
  • wildcard searches like '%abcd%' is a business requirement... Mar 24, 2021 at 18:16

Spend some time evaluating Missing Indexes and Unused Indexes, as they can also load down system resources on tables that receive frequent Inserts, Deletes or Updates.

  • Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center.
    – Community Bot
    Sep 24, 2022 at 21:22

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.