Safe Words
This a short post about a sort of quirky error message I got while trying to run the TPC-C load test against SQL Server’s in-memory tables.
Error in Virtual User1: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]The integer value XXXXXXXXXXXX is out of range.
A few of the tables would get created, but not all of them. It wasn’t obvious where that number was coming from, until I thought back to the setup.
In the build script, there’s a helpful looking line for you to adjust the hash bucket count that your indexes get created with.
I had set mine to a higher number, thinking it would help load speeds to have a bunch of buckets ready in there.
But the thing is, buried way down in the script, there’s already some math to pad the hash buckets for you.
If you’re in the mood to be totally underwhelmed, you should also run this test against Hekaton.
Thanks for reading!
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.
Finally got around to running HammerDB 4.1
Tried the flashy Hekaton switch and it failed to create the tables at your hash buckets value of 64 above
1 works just fine
I think ill call Hekaton “όχι εκατόn”
Or, in the immortal word of Riddick “Not Furya”
TPC-H
My Disk-Based Database score on a HP Z5 was [388,000]
Hekaton on the same hardware was [45,500 to about 90,000] – Jamie’s Cryin’
On an Azure SQL G5 8Core 32GB 200GB instance (the default): [33,402] 10x slower- Not Gonna Cloud much
Plus on Hekaton, I got lots of error 701 – not enough memory to run this query
Nice