SQL Server ReIndexing Jobs fail due to full transaction logs

We've encountered issues with a couple of our customers wherein, as that database grows, index maintenance becomes difficult to manage.

In some cases, because the indexes become quite large, the index rebuilds on badly fragmented indexes fill the transaction logs.

For one of our customers, we resolved it in part by altering the recovery model to be BULK LOGGED during the index maintenance.

Index Rebuild operations qualify as a minimally logged operation, and can therefore take advantage of reduced logging in BULK LOGGED recovery model.

I wanted to do some research or testing before I recommended this, because it’s a big deal to change recovery models, do index maintenance, and then switch back.

I did find a link to someone else who did some testing, though, and found that using bulk logged would, indeed, reduce log file growth during  re-indexing.

https://blogs.msdn.microsoft.com/sqlserverfaq/2011/01/07/using-bulk-logged-recovery-model-for-bulk-operations-will-reduce-the-size-of-transaction-log-backups-myths-and-truths/

(Thanks to Balmukund for running and publishing this test!!!)

For his test Balmukund used Adventure Works, of course 😉. Classic 😀

In FULL RECOVERY MODE, the DBCC Reindex used about 100 MB space. 

(by my math using rounding: 41-2 = 39% used. 258 *.39 = 100.62)

In BULK LOGGED, it used about 2.5 MB.  

(again, rounding: 4.77- 2.98 = 1.8% of 258 is 2.58.)

This is a *VERY* significant difference. 

While I understand that switching back and forth between FULL and BULK LOGGED recovery model is scary, this remains something  to keep in our hip pocket when index maintenance is filling the transaction log! 

TODO: Verify that partitioning the table/index (es) involved will not also help this situation. My guess is that it will not, because there is only one transaction log for a database in sql server, and logging is logging. Still, I'd like to test the theory.

Someday 😏

Creating Hash Values for Strings In MSSQL, DB2 UDB for LUW, and Oracle

It will often occur that we need to search a large character string in a relational database, something like an email address, postal address, etc.

For the most part, when we do such searches, we're searching for a sub-string within that large string of characters.

However, for instances where we know we need an equivalency search, we can make things more efficient by generating a hash of the string and then storing that in an indexed column.

This can be done in all 3 of the major RDBMS platforms.  Links below:

MSSQL uses the CHECKSUM() function.
DB2 LUW uses the DBMS_UTILITY.GET_HASH_VALUE() function.
Oracle uses the ORA_HASH function.

This article by Jeff Reinhard over at SQLServerCentral.com is what inspired me to look for the similar function in DB2 & Oracle, and it does a very nice job of explaining the use case for this using an e-mail address scenario.

If anyone knows how to do this in postgressql or MySQL, I'd love to have you add a few words or link in the comments. Thanks! :)

Nothing Ever Always

Sometimes I get really frustrated by the things I used to know, but somehow forgot.
For instance, I USED to know that, in a relational database, nothing is ever always true.
Huh?

Perhaps an example will be illuminating.

Indexes - we are told that they help reads and hurt writes.  I suppose that's true, on some level, because of the processing the RDBMS has to do in order to perform the actual operation of writing to the table, and updating the indexes.

However, I had a situation today wherein I found myself creating an index to DRAMATICALLY speed up a write operation.

Both for a delete where and an update where, adding the index cut the execution (in DB2, measured in timerons) by 60 - 80 %.  Wow. 

Honestly, I should have known this.  I've been doing this for a while. I get used to pat ideas and the looking at a problem in a very particular way, or on a small set of data.

Bottom line, in relational databases, nothing ever always.