Blog Post

Azure Database Support Blog
3 MIN READ

How to maintain Azure SQL Indexes and Statistics

Yochanan_Rachamim's avatar
Yochanan_Rachamim
Icon for Microsoft rankMicrosoft
Mar 14, 2019
First published on MSDN on Jul 03, 2016   [2020-11-09]Note: New version with major update is now available include resume functionality, resumable index rebuild, fix forwarded records in heaps....
Updated May 04, 2025
Version 5.0
Maintenance
reindex
update statistics
wozorio's avatar
wozorio
Copper Contributor
Jan 19, 2024

Hello Yochanan_Rachamim first of all, congrats for such a great work and for sharing it with us! :smile:

 

I have run the script on some Azure SQL databases a couple of times, statistics were updated and fragmented indexes matching the criteria (fragmentation level over 30% and more than 40 pages) were defragmented successfully.

However, now the defrag operation is failing on quite a few indexes, throwing the following error:

FAILED : 1934ALTER INDEX failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.

Googling it, a lot of people suggest turning `QUOTED_IDENTIFIER` on but I noticed that this is already done in the SQL script, in the beginning of the variables section: `set quoted_identifier on;`

 

Could you please share your thoughts on why this error is happening?

 

Best regards,

Wellington