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.