The AdventureWorks set of sample databases are commonly used for presentation demos and showing how to use SQL Server, but one of the biggest challenges in using these databases is their small size which is geared towards ease of distribution, more than demonstrating all of the functionality of SQL Server. The concept of creating a larger version of AdventureWorks is not new, Adam Machanic (Blog|Twitter) blogged last year about enlarging specific tables for demonstrations, and at the same time I found that I also needed to create tables that were larger datasets for my own purposes. The tables that I chose to enlarge for demonstration purposes were the Sales.SalesOrderHeader and Sales.SalesOrderDetail tables which can be used to demonstrate a number of different query plan and performance issues in SQL Server. Below is a script that can enlarge these tables into tables named Sales.SalesOrderHeaderEnlarged and Sales.SalesOrderDetailEnlarged in the specific AdventureWorks database being targeted. The attached script has been tested against the AdventureWorks2008R2 and AdventureWorks2012 databases specifically, but as long as the table schema is the same it may be applied to any version of AdventureWorks.
These larger tables can be used to produce parallel execution plans, plans that have large execution memory grant requirements, plans that perform sort and hash spill operations through tempdb, and many other uses in SQL Server. The size of the tables can be scaled by running the INSERT code block multiple times to increase the size of the Sales.SalesOrderHeader table by roughly 225MB, and to increase the size of the Sales.SalesOrderDetailEnlarged table by roughly 400MB per execution after the index rebuilds are run.
10 thoughts on “Enlarging the AdventureWorks Sample Databases”
Ran this script today, it worked great. I would recommend the following steps when you run this in your own test environment:
(a) change the USE command appropriately if you are using AdventureWorks2012
(b) change the data file autogrowth from 16 MB to 5 GB (mine expanded 76 times and ended up at 5.6 GB
(c) change the log file autogrowth from 10 percent to 2 GB (mine expanded 48 times and ended up at 1.6 GB)
Hi
I’m trying to use your script to create a large database on a SQL 2008 server. The main goal is to evaluate IO on a SSD configuration. I run it one time without any issue. Now i need to run again. Could you give me detailled instructions to increase tables size ? which lines to extract from the actual script ?
Another questions: Can I use new Enlarge tables instead of old one with all samples ?
Do I just need to rename it ? and delete old one ?
Thank you for your script and Best regards
Thank you SO MUCH for sharing !!!
Best
Alberto
Very helpful, I take away the idea!
It would appear that the script for this article is no longer available. Is there a copy somewhere?
A security rule on our WAF was blocking the .sql extension from being served. It has been corrected now.
Could you please reupload the script? It links to 404 now. Thank you!
A security rule on our WAF was blocking the .sql extension from being served. It has been corrected now.
The script is not available for downloading anymore. Could you please fix it? Thanks!
A security rule on our WAF was blocking the .sql extension from being served. It has been corrected now.