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.
how much worse is a full index built on binary(8) than the equivalent bigint value?
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.
2 answers
Sort by: Newest
-
-
Seeya Xi-MSFT 16,451 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.