Timeline for Performance of an In-Memory Table is worse than a disk-based table
Current License: CC BY-SA 3.0
18 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Jun 15, 2020 at 9:05 | history | edited | CommunityBot |
Commonmark migration
|
|
| S Mar 30, 2016 at 12:10 | history | bounty ended | Paul White♦ | ||
| S Mar 30, 2016 at 12:10 | history | notice removed | Paul White♦ | ||
| Mar 26, 2016 at 7:16 | answer | added | Sean Gallardy | timeline score: 7 | |
| Mar 24, 2016 at 5:57 | comment | added | Cristiano Ghersi | @SeanGallardy, that's exactly the problem: the number of records in that table is extremely volatile, from 0 to 100M. the pair id1-id2 anyway is always unique.(it is the pk). So, what is the right number for bucket_count given these hypotheses? | |
| Mar 23, 2016 at 19:00 | comment | added | Sean Gallardy | Cristiano, you went from having not enough buckets to WAY TOO MANY 131 thousand to 134 million. How many rows are in the table, total? How unique is id1, id2? If id1, id2 is not very unique it may not be a good hash index candidate. | |
| S Mar 23, 2016 at 9:15 | history | bounty started | Paul White♦ | ||
| S Mar 23, 2016 at 9:15 | history | notice added | Paul White♦ | Draw attention | |
| Mar 23, 2016 at 9:12 | history | edited | Paul White♦ | CC BY-SA 3.0 |
added 415 characters in body; edited tags; edited title
|
| Mar 20, 2016 at 7:19 | history | tweeted | twitter.com/StackDBAs/status/711452136769495040 | ||
| Mar 20, 2016 at 7:15 | comment | added | Daniel Hutmacher | @AaronBertrand true, I realize that my comment might sound a bit categorical. To clarify, native procedures are precompiled and don't touch disk-based data, which is how they truly come into their own right from a pure performance aspect. | |
| Mar 20, 2016 at 1:24 | comment | added | Aaron Bertrand | @DanielHutmacher FWIW I've seen counter-examples where all of the benefit was from removing the latching and adding natively compiled procedures gave zero or negligible improvement. I don't think there is any room for a blanket statement (though you may be right in this case, I have not even looked at the details). | |
| Mar 19, 2016 at 21:55 | comment | added | Daniel Hutmacher | I've found that the best performance improvements with in-memory technology can only be achieved using natively compiled stored procedures. | |
| Mar 19, 2016 at 16:39 | comment | added | Mikael Eriksson | The hash index is only useful for predicates on both included columns. Have you tried without a hash index on the table? | |
| Mar 19, 2016 at 14:18 | comment | added | Sean Gallardy |
My guess is you're running into row chain issues. Can you give us the output of select * from sys.dm_db_xtp_hash_index_stats ? Also, this link should answer most/all of your questions: msdn.microsoft.com/en-us/library/…
|
|
| Mar 19, 2016 at 13:26 | comment | added | TT. |
Are you sure you aren't running into parameter sniffing? Have you tried running the queries with OPTION(OPTIMIZE FOR UNKNOWN) (see Table Hints)?
|
|
| Mar 19, 2016 at 11:44 | history | migrated | from stackoverflow.com (revisions) | ||
| Mar 19, 2016 at 11:22 | history | asked | Cristiano Ghersi | CC BY-SA 3.0 |