how much worse is a full index built on binary(8) than the equivalent bigint value?

db042190 1,516

Hi we are weighing the performance trade off of a full index on a binary(8) column vs one on a column containing the bigint equivalent of the binary(8) over 10's of millions of records.

i have some evidence that its much quicker on a bigint column. our searches focus on the highest 10k or so values in these 2 fields every time we extract. i'm going to do an explain plan before i conclude too much here.

db042190 1,516 Reputation points

2022-08-03T15:25:08.203+00:00

i ran an explain plan. i saw seeks at 0% on the binary index. and depending on number of joins the 100% remaining cost was in the key lookups. so i feel stumped. thought i was going to see scans.
Tom Phillips 17,716 Reputation points

2022-08-03T15:50:58.587+00:00

You cannot just look at the percentage of the item in the query. You need to look at the overall cost of the entire query in read/write/cpu/ram.
db042190 1,516 Reputation points

2022-08-03T16:17:55.96+00:00

thx, so while i'm running it, what tool(s) can capture that? do i need a trace turned on?
db042190 1,516 Reputation points

2022-08-03T16:34:31.533+00:00

maybe these?

SET STATISTICS TIME ON;

SET STATISTICS IO ON;
db042190 1,516 Reputation points

2022-08-03T17:09:06.823+00:00

on one of my queries (which was supposedly blocking) i see these results

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.

(49984 rows affected)
Table 'only table in this one'. Scan count 1, logical reads 203316, physical reads 75, page server reads 0, read-ahead reads 222, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

SQL Server Execution Times:
CPU time = 1125 ms, elapsed time = 1385 ms.

Completion time: 2022-08-03T13:02:07.0158698-04:00

can you conclude anything? not sure what those logical reads are.
db042190 1,516 Reputation points

2022-08-03T17:26:42.153+00:00

on the other supposed blocker, i got this

SQL Server parse and compile time:
CPU time = 55 ms, elapsed time = 55 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.

(49984 rows affected)
Table 'table 2'. Scan count 49984, logical reads 222358, physical reads 606, page server reads 0, read-ahead reads 2299, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'table 3'. Scan count 49984, logical reads 212774, physical reads 0, page server reads 0, read-ahead reads 176, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'table 1'. Scan count 1, logical reads 204566, physical reads 478, page server reads 0, read-ahead reads 517, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

SQL Server Execution Times:
CPU time = 3234 ms, elapsed time = 3750 ms.

Completion time: 2022-08-03T13:10:35.3127186-04:00

supposedly this and the prior query were blocking today. i'm going to look for proof they were culprits for all 15 minutes of duration on the pkg.
Erland Sommarskog 101.8K Reputation points MVP

2022-08-03T19:25:50.503+00:00

not sure what those logical reads are.

That is the number of pages accessed for the query. That could be data pages or index pages.
Seeya Xi-MSFT 16,446 Reputation points

2022-08-09T07:06:43.837+00:00

Hi @db042190 ,

Hope everything goes well. We have not received a response from you. Did the reply could help you? If the response helped, do "Accept Answer". If it doesn't work, please let us know the progress. By doing so, it will benefit all community members who are having this similar issue. Your contribution is highly appreciated.

Best regards,
Seeya
Seeya Xi-MSFT 16,446 Reputation points

2022-08-29T08:41:47.263+00:00

Hi @db042190 ,

In order to benefit all community members who are having this similar issue, please choose an answer to accept or vote for the answer you think is useful to you. Your contribution is highly appreciated.

Best regards,
Seeya

2 answers

Seeya Xi-MSFT 16,446 Reputation points

2022-08-05T06:21:02.67+00:00

Hi @db042190 ,

Welcome to Microsoft Q&A!
Please refer to these articles to know about how to create a proper index:
https://blog.quest.com/11-sql-server-index-best-practices-for-improved-performance-tuning/
https://www.sqlshack.com/top-five-considerations-for-sql-server-index-design/
Hope these would give you some help.

Best regards,
Seeya

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Please sign in to rate this answer.
db042190 1,516 Reputation points

2022-08-05T14:06:42.173+00:00

thx seeyaxi. those articles suggest using int because of its compact size but in my case the bigint and binary(8) data types use the same aboumt of storage so the original questions stands...is either going to perform better than the other?

Seeya Xi-MSFT 16,446 Reputation points

2022-08-08T05:47:27.44+00:00

Hi @db042190 ,

Hope everything goes well.
I noticed that you have done the relevant performance comparison. The lower the logical read count, the fewer pages of data are read and the better the performance.

Best regards,
Seeya

If the answer is the right solution, please click "Accept Answer" and kindly upvote it.
Sign in to comment
db042190 1,516 Reputation points

2022-08-05T11:59:03.257+00:00

thx all. I just dont see the silver bullet i was looking for. i went and reran the package twice after all this getting the sub 10 second run time i expect always in incremental processing. the blocking these 2 queries were doing came suspiciously after an environment refresh from production and on a server that was having problems. but also after a "one time" version of the pkg that initially seeds al the data irrespective of the last set of 14 high keys encountered on the 14 tables it queries and stages. we will keep an eye on this package.
Please sign in to rate this answer.
Erland Sommarskog 101.8K Reputation points MVP

2022-08-05T14:09:56.807+00:00

One like to think that they are the same, but rumour has it that there are optimisations for int (and bigint?).

In any case, only way to find out is to test. (Which you have seemed to have done.)
Sign in to comment