Recently I decided to test one of the new database scoped default settings in SQL Server 2019. So, I tested the one which sets index rebuilds to be online by default.
The idea is that you set this at the database level so that index rebuilds are online by default, without having to specify it in the index rebuild command. When you select this scoped configuration you have three options; which are off, when_supported or fail_supported.
This was first introduced in Azure SQL database and is now in SQL Server 2019. You can read about it more in detail here.
However, whilst testing this I discovered some interesting things relating to how it works with various compatibility levels and index types. Which I will discuss.
To make things more interesting I tested this on the SQL Server on Linux instance I installed previously. Which I installed using a local repository. You can read more about that in detail here.
For the purpose of this test I used databases with various compatibility levels. Which were SQL Server 2012, 2016,2017 and 2019.
I used the AdventureWorks2012 database for the database that used the SQL Server 2012 compatibility level. For all the other databases I used the WideWorldImports database and changed the compatibility level.
In addition, I created an extended event that uses the sqlserver.progress_report_online_index_operation event. Which logs the progress of online index operations and you can find out about in detail here.
Afterwards, I began testing by issue index rebuild commands on all of the databases without using the online option. Which is when I discovered interesting things.
Traditional rowstore index
For a start, it looks like if you set database default to use either the when_supported or fail_supported options it works for rowstore indexes on all databases. In fact, this appears to be regardless of the compatibility level.
I say this because when I issued a rebuild index command with no online option they all logged entries into the extended event.
Now, this is where things get a bit more interesting. Suprisingly, it appears that non-clustered columnstore indexes get rebuilt online in all databases when using either the when_supported or fail_supported default options.
However, the results for clustered columnstore indexes are a different story. Of course, clustered columnstore indexes do not work in SQL Server 2012 so that does not apply here.
When using the SQL Server 2016 and SQL Server 2017 compatibility levels and using the when_supported option, SQL Server does not attempt to do an online rebuild.
However, when I used the fail_supported option for databases using the SQL Server 2016 and SQL Server 2017 compatibility levels SQL Server appears to force them to do online rebuilds.
I say this due to the fact that it gets loggged in the extended event. In addition, the extended event appears to show that it also does online rebuilds for any rowstore indexes the table uses.
After the rebuild has finished it displays the below message.
“Warning: ALTER INDEX REBUILD statement is being forced to run WITH (ONLINE = ON) because the ELEVATE_ONLINE database scoped configuration is set to FAIL_UNSUPPORTED. The statement may fail. See the SQL Server error log for more information.”
When using the SQL Server 2019 compatibility level (150), using either of these options causes SQL Server to do online rebuilds for both types of indexes. Which is expected.
In summary, if the results of the extended event are correct then it looks like you might want to look to take advantage of the fail_supported option. Which, according to these test results force online rebuilds to occur.
However, do bare in mind that I did use sample databases and for larger databases the results may vary.
After hearing some horror stories in the past about people rebuilding indexes without using the online option I’m sure this will be a welcome addition in SQL Server 2019.
In addition, if these findings are anything to go by it could prove to be more flexible than originally expected. Of course, you can test these findings for yourself. If you do test them then feel free to add your results as a comment.