usp_AdaptiveIndexDefrag / Not working on very large tables

Open
#311 0 comments 0 reactions 0 assignees View on GitHub

Nobody has claimed this yet.

Assessment

Difficulty
4/5
Estimated time
3-5 days
Newbie friendliness
35/100
Issue type
Bug
Clarity
Needs clarification
Activity status
Stale
Tech stack
azure, sql
Domain
databases, performance

Research direction

Start with dbo.usp_AdaptiveIndexDefrag and reproduce the provided execution against Azure SQL tables with more than 1 billion records. Use the enabled debug and command printing to examine the long-running reorganization and investigate the reported deadlock conditions. Done means the large-table behavior and deadlock risk are addressed without requiring application downtime.

Written by the indexing model from the issue text.

Description

It was suggested to me that the solution does not require applications downtime.

However it does not seem to be working well in Azure SQL and databases with tables having more than 1 billion of small length records (often more than 2 billion)

The process takes too long to complete the reorganization of indexes, more than a few days.
By the time this finishes the tables are fragmented again.

EXEC dbo.usp_AdaptiveIndexDefrag
@Exec_Print=1 ,
@printCmds=1 ,
@rebuildThreshold=100 ,
@rebuildThreshold_cs=100 ,
@updateStats=1 ,
@scanMode = 'LIMITED' ,
@onlineRebuild=1 ,
@minFragmentation=5 ,
@debugMode = 1 ,
@timeLimit=2880

There is also a suspicion that the tool is responsible for deadlock conditions during its run.

Dominant language
Jupyter Notebook
Stars
1.6k
Forks
749
No merged PRs in 30d

Contributor guide

No contributing guide indexed for this repository

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

More from microsoft/tigertoolbox

All issues in microsoft/tigertoolbox

Similar issues

More Databases issues

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.