THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Adam Machanic

Adam Machanic, Boston-based SQL Server developer, shares his experiences with programming, monitoring, and performance tuning SQL Server. And the occasional battle with the query optimizer.

Thinking Big (Adventure)

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.


Published Monday, October 17, 2011 1:56 PM by Adam Machanic


Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS



SQLvariant said:

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!  :-)

October 17, 2011 2:20 PM

Linchi Shea said:

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.

October 17, 2011 5:01 PM

Adam Machanic said:

Linchi: I couldn't agree more. Connect item? :-)

October 18, 2011 11:20 AM

Julie Smith said:

Delicious way to trick out some larger data sets :)

October 18, 2011 9:30 PM

Jen McCown said:

Finally got around to downloading this...thanks Adam!

February 10, 2012 1:55 AM

SSIS Junkie said:

Last week I launched AdventureWorks on Azure, an initiative to make SQL Azure accessible to anyone, in

April 2, 2012 6:19 PM

Joshua Austill said:

Thanks a ton, this script saved me a few hours at least!  I'll be keeping this for demo's myself :)

March 21, 2015 10:36 AM

Leave a Comment


About Adam Machanic

Adam Machanic is a Boston-based SQL Server developer, writer, and speaker. He focuses on large-scale data warehouse performance and development, and is author of the award-winning SQL Server monitoring stored procedure, sp_WhoIsActive. Adam has written for numerous web sites and magazines, including SQLblog, Simple Talk, Search SQL Server, SQL Server Professional, CoDe, and VSJ. He has also contributed to several books on SQL Server, including "SQL Server 2008 Internals" (Microsoft Press, 2009) and "Expert SQL Server 2005 Development" (Apress, 2007). Adam regularly speaks at conferences and training events on a variety of SQL Server topics. He is a Microsoft Most Valuable Professional (MVP) for SQL Server, a Microsoft Certified IT Professional (MCITP), and an alumnus of the INETA North American Speakers Bureau.

This Blog


Privacy Statement