SQL Server 2019 database scoped settings for indexes discovery

Reading Time: 3 minutes

Recently I made an interesting discovery about SQL Server 2019 database scoped settings. Because I tested the one that sets index rebuilds to be online by default.

Setting this at the database level allows index rebuilds to be 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.

SQL Server 2019 Test environment

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.

Database scoped settings for traditional rowstore indexes in SQL Server 2019

For a start, it looks like if you set database default to use either the when_supported or fail_unsupported 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.

Database scoped settings for Columnstore indexes in SQL Server 2019

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_unsupported 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_unsupported 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.

Conclusion about database scoped settings for indexes

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_unsupported 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.

Final word about database scoped settings for indexes in SQL Server 2019

I hope people find this post about SQL Server 2019 database scoped settings for indexes discovery useful.

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.