Home Uncategorized Thinking Big (Adventure)

    Thinking Big (Adventure)

    2023
    8

    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

    Previous articleA Computer Scientist Meets T-SQL
    Next articleCloudSeeder: CLR Stored Procedures For Creating CPU Pressure
    Adam Machanic helps companies get the most out of their SQL Server databases. He creates solid architectural foundations for high performance databases and is author of the award-winning SQL Server monitoring stored procedure, sp_WhoIsActive. Adam has contributed to numerous books on SQL Server development. A long-time Microsoft MVP for SQL Server, he speaks and trains at IT conferences across North America and Europe.

    8 COMMENTS

    1. 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!  ðŸ™‚

    2. 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.

    3. Thanks a ton, this script saved me a few hours at least!  I’ll be keeping this for demo’s myself 🙂

    4. There are a couple of interesting points with time in this posting but I do not know if these people center to heart. There’s some validity but I’m going to take hold opinion until I check into it further.

    5. There are a couple of interesting points with time in this posting but I do not know if these people center to heart. There’s some validity but I’m going to take hold opinion until I check into it further.

    Comments are closed.