Rebuild a 300 GB index on an Azure SQL, tips please

Question

Dear Experts,

I have an Azure SQL on S4 and there is an index around 300 GB in size.

While rebuilding another index around 10 GB , it took 2 hours.

My plan is to increase the compute tier to P15 and then perform the rebuild.
Once done, downgrade to lower compute tier again..

Are there any other suggestions you may provide please?

Thanks

Answer 1

Good day @mo boy ,

It is very simple to give you a short answer saying that you should increase the pricing tier to P15 in order to improve the task, but in fact, it is much more complicate and more information is needed. Moreover, Moving to higher tier and back might consumes longer time in such action, so even if the action itself will be faster, the total time might be longer.

Moreover, you should focus on what led to this situation and how to avoid it in the future.

Missing information

I cannot directly advice you what to do, since I am not fully familiar with your system. I can only provide some golden rule and general points. Providing details of your related Table Schema and Index Structures will enable us to provide you with more specific guidance.

First missing information which we should get includes: What type of index are you talking about? Is this is the Clustered Index or a non-Clustered Index (the clustered index basically stores the table itself), What is the size of the data in the table? what is the fill factor of the index? what is the size of the Data in the Index, why the index raise to this size and what is the expected size after the rebuild? Is this an XML index or Full Text Index for example (These can be very big)?

Clarification

As at seems in first glance, something in your design might be problematic. You claim that you use S4 which is limited to one TB and only 200 DTU, and you have one index of 300GB size, so what is the size of the table? Is this single index size equal to 1/2 of the database size?

More points to think about about

@ If the index is larger than the table then fragmentation might consuming most of the index space. Again, think about what led to this situation (can be configuration of fill factor, multiple deleted, updates which required splitting pages...

@ How often to you need to do this task?

@ Did you thought about using Columnstore indexed instead?

@ Did you checked the option of using filtered indexes

@ Check the type of data which is used for the primary keys and for the index. Big fat string or large binary can lead to big indexes as it get duplicated in every index for that table.

@ Check if you really need all the included columns

What next?

We need more information

In general, moving to higher tier usually improve the execution time of the task after the upgrade, but did you thought about what impact the upgrade have?

• upgrade create a new compute instance for the database, which involves copying data and creating the database!
• Existing connections to the database in the original compute instance are dropped.

Thee actions might take longer that rebuild the index in some cases. It is all depend on the bottleneck of the action in your specific case.

For more information, please provide the missing information