2

I have a database which has almost 130 tables.

I am using the Ola Hallengren maintenance solution and IndexOptimize scripts.

I have two tables in particular that in my opinion needs to be better handled:

  • Table1: PK INT, almost 1.5 millions of rows
  • Table2: PK INT, foreign Key to Table 1, almost 6.5 millions of rows

On table2 I have created an index some time ago, following an azure recommendation, like this:

CREATE NONCLUSTERED INDEX [IX_Table2_ForeignKeyField] 
ON[dbo].[Table2] (
    [ForeignKeyField]
) 
INCLUDE([AnotherField]) 
WITH( ONLINE = ON )

When the IndexOptimize runs I can see that to rebuild the IX_Table2_ForeignKeyField index the stored procedure uses the following command

ALTER INDEX [IX_Table2_ForeignKeyField] 
ON [mydb].[dbo].[Table2] 
REORGANIZE 
WITH (LOB_COMPACTION = ON) 

The results are that it takes more than two hours to reorganize this index (previous reorganization was done 3 days ago) and at the end it says that

<ExtendedInfo>
  <PageCount>644096</PageCount>
  <Fragmentation>0.752683</Fragmentation>
</ExtendedInfo>

Is there any procedure that I can use to make this command run faster?

EDIT: One more info: Table1 has from 500 to 2500 insert per day. On table2 I have almost 10k insert per day growing.

migrated from stackoverflow.com Dec 30 '17 at 16:14

This question came from our site for professional and enthusiast programmers.

  • 1
    Required viewing – Erik Darling Dec 30 '17 at 17:50
  • Are there any deleted and updates to once inserted rows? Or it only, basically, aggregate new records? If they are mainly for inserting, BRAIN indexes are as effective as normal ones, while weighting only 5% of b-tree etc – kakaz Dec 30 '17 at 18:04
  • Not always. Rows are never deleted. less than 5% are updated once within 15 minutes from the insert. What are BRAIN indexes? – Lorenzo Dec 30 '17 at 18:29
3

Index maintenance is an I/O intensive workload. Under these circumstances please consider to scale to premium tiers prior to run index maintenance tasks, and when the workload finishes scale down to previous tier. You can automate that using T-SQL. That should reduce maintenance time.

Additionally, compacting large objects data (image, text, ntext, varchar(max), nvarchar(max), varbinary(max), and xml) can save disk space but it can increase the time it takes to maintain indexes. If you have a restricted time window for reindexing you can set LOB_COMPACTION to OFF.

Hope this helps.

  • Thanks for your answer. Do you have any suggestion on how to automate the tier scale up/down? – Lorenzo Dec 30 '17 at 17:52
  • ALTER DATABASE [db1] MODIFY (EDITION = 'Premium', MAXSIZE = 1024 GB, SERVICE_OBJECTIVE = 'P15'); – Alberto Morillo Dec 31 '17 at 1:39

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.