If the title of this post doesn’t have you scratching your head, you may have been paying very rapt attention last time you saw me speak.
I love the portability of AdventureWorks and the fact that anyone can download it. Since it was released I’ve used it almost exclusively for demos in talks I’ve written. However, In recent months I’ve been moving away from the core tables in the database. Fact is, they’re just a bit too small to show performance artifacts of parallelism, spilling to tempdb, and the like — the topics that I’m currently enamored with.
Instead I’ve started using a couple of tables modeled after Production.Product and Production.TransactionHistory. These tables are called dbo.bigProduct and dbo.bigTransactionHistory, and I refer to them collectively as bigAdventure.
The bigAdventure tables are several times larger than their AdventureWorks brethren, and allow me to easily create queries that overwhelm the 8 cores on my laptop. Which is exactly what I need to emulate the large data warehouse queries we see in the real world. So far I’ve been able to do most of what I need with only the two tables, but I hope to add more to the mix soon (for example, I’ve been working on a bigger version of Sales.CurrencyRate to help illustrate some SQLCLR techniques).
The current bigAdventure script is attached to this post. I meant to include it in the demo download for my PASS session, also posted today, but forgot to put it into the ZIP file. I thought that someone out there who didn’t attend my session might want to use it, so here you are.
Enjoy!
File Attachment: make_big_adventure.zip
I noticed while running this script that the default grow for the AdventureWorks database is 16MB. You might want to advise people to bump that up by at least adding a zero to the end (160 MB).
Great script! Wish I had it 2 weeks ago! 🙂
The right thing for MS to do is to make AdventureWorks scalable with a scaling parameter and have the actual data generated per this scaling parameter. So if you want a 100GB AdventureWorks, adjust the parameter and generate a 100GB database. This is how most benchmarks scale their databases, and it has worked well.
Linchi: I couldn’t agree more. Connect item? 🙂
Delicious way to trick out some larger data sets 🙂
Finally got around to downloading this…thanks Adam!
Thanks a ton, this script saved me a few hours at least! I’ll be keeping this for demo’s myself 🙂