2

We have a SQL 2016 database that has a 1.9bn row table which has a varbinary(255) column which we use to store the HashBytes of an nvarchar(2000) field in the same table.

We have a Non-Clustered index on the varbinary field and our index maintenance scripts perform a REORGANIZE on this every 2-3 days. But this takes 10+hours to complete.

Any way to improve the speed of index maintenance for varbinary fields?

  • What is the data that's stored and how is it used? Any idea why varbinary was chosen for the hash? – Mark Storey-Smith Nov 11 '16 at 11:29
  • In all honesty I've inherited this database and wasn't around when the schema was designed. But the varbinary is used to check for the existence of a record as part of the ETL process rather than joining on the nvarchar field and hence the NCI. – Adrian Sugden Nov 11 '16 at 11:58
4

If you have a table with 1.9 billion rows, I'm guessing it has a fairly low change rate as a percentage. Check how many rows you're actually inserting/updating on a daily basis - it's likely less than 1%.

In that case, it doesn't make sense to reorganize the whole table every 2-3 days (especially given that it takes 10+ hours.) I'd start by only doing index maintenance weekly, if not MONTHLY on a table of that size.

Start by taking a step back and asking, "What's the problem I'm trying to solve by doing index reorganizations?" If the answer is slow select statements, then index maintenance on a 1.9bn row table isn't going to be the answer.

  • We're inserting about 2m records per day and using Ola's index maintenance scripts with your suggested defaults brentozar.com/archive/2014/12/… and the index is still being selected for reorg every 2-3 days. The index is used to support the ETL process. The varbinary is used to check for the existence of a record rather than joining on the nvarchar field. – Adrian Sugden Nov 11 '16 at 13:08
  • Right, so 2mm records per day is less than 0.1% of the table's size. Don't reorganize the entire table just because you changed 0.3% of the contents. I'd argue you may not even want to reorg until you've changed at least a few percent (2-4%, meaning once a month.) – Brent Ozar Nov 11 '16 at 14:20
3

As per comment, my question as to usage was because I expected this index existed to check for existing records i.e. singleton lookups, no scan behaviour. With that in mind I echo @BrentOzar that reorganising that frequently is unlikely to yield any tangible benefit. Switch it off and see if ETL performance deteriorates, I suspect not.

If there is scope to modify the ETL process then I'd look at dumping the VARBINARY hash and replacing with BIGINT. I use a similar arrangement in a datawarehouse ETL process:

CAST(HASHBYTES('SHA1', longstring) AS BIGINT)

In this use case collisions as a result of truncating aren't a problem. The hash is used to check if a row already exist via an index comprised of 8 byte keys instead of an index of 255 bytes, or scanning a table containing the 4000 byte column. If the check yields no row, you insert. If there's a match on the hash, do the comparison on the raw text to determine if you have an existing row or need to insert.

  • Mark, regarding the updated explanation: fair enough. If you are already going to do the full comparison on collisions, then this just increases the number of times that happens as opposed to producing erroneous results. And if the frequency of collisions is small, that could outweigh the performance degradation of wider columns on a table with 2 billion rows, but might not on a table with less than 100 million rows. I updated that part of my answer to more so raise awareness of the behavior so that this conversion to BIGINT doesn't get misused elsewhere. – Solomon Rutzky Nov 11 '16 at 16:42
1

What is the FILLFACTOR on the NonClustered index? What hash algorithm are you using? Does that index have PAD_INDEX set to ON? What is the definition of the Clustered Index (including column datatypes)?

All of those will give us a clearer picture of the physical make-up of the index.

What other operations are occurring? Meaning, do you update the NVARCHAR(2000) column? Do you delete lots of rows? The only things that should be increasing fragmentation are: regular inserts (since the hash is effectively "randomly" ordered), updates to the NVARCHAR field as that will alter the value (but not size) of the hash, and LOTS of deletes.

The answers to these questions will give us a clearer understanding of how / why the fragmentation is increasing.

Also, outside of the fragmentation level reaching the default limit for Ola's script to recommend defragging, have you seen any degradation in ETL performance when you don't defrag?

Also, I would also be careful about converting the hashed value to BIGINT given that BIGINT is only 8 bytes yet all hash algorithms -- even MD5 -- are greater than 8 bytes (MD5 = 16 bytes, SHA1 = 20, SHA2_256 = 32, and SHA2_512 = 64). And converting binary values larger than 8 bytes to BIGINT silently truncates the values, hence you lose accuracy and increase occurrences of false positives. The following query shows this behavior:

SELECT CONVERT(BIGINT, 0xFFFFFFFFFFFFFF),      --  7 bytes = 72057594037927935
       CONVERT(BIGINT, 0xFFFFFFFFFFFFFFFF),    --  8 bytes = -1
       CONVERT(BIGINT, 0xFFFFFFFFFFFFFFFFFF),  --  9 bytes = -1
       CONVERT(BIGINT, 0xFFFFFFFFFFFFFFFFFFFF) -- 10 bytes = -1

Of course, as per @Marks explanation of the usage, it is possible that this truncation merely increases the frequency of doing the full comparison of the NVARCHAR field. Still, one should be aware of the behavior since it is a silent (i.e. non-obvious) truncation.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.