0

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.

7
  • what sort of disks does your server have ? – Stephen Morris - Mo64 Mar 23 at 14:29
  • its on VM but not extreme io – SQL_NoExpert Mar 23 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). – Stephen Morris - Mo64 Mar 23 at 14:34
  • The disks are on HQISILON...not ssd – SQL_NoExpert Mar 23 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? – Ali Razeghi Mar 23 at 18:48
0

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).

0

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

0

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:

https://docs.microsoft.com/en-us/sql/relational-databases/search/full-text-search?view=sql-server-ver15

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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