Unformatted text preview: _20gb.sh We waited until the processes finished before we moved onto the next step.
Creating the database
We modified the database creation SQL Server scripts in the DVD Store distribution package to build the
database schema, which includes the file structure, tables, indices, stored procedures, triggers, and so on. We
built a master copy of the 20GB database version for SQL Server 2008, and then used that master copy to
restore our test database to each test server VM between each test run. We stored the backup file on the VM C:
drive for quick access.
We followed these steps to create the database:
1. We created the database and file structure using database creation scripts provided with DS2. We made
size modifications specific to our 20GB database and the appropriate changes to drive letters.
2. We created database tables, stored procedures, and objects.
3. We set the database recovery model to bulk-logged to prevent excess logging.
4. We loaded the data that the database generated. For data loading, we used the import wizard in SQL
Server Management Studio. Where necessary, we retained options from the original scripts, such as
Enable Identity Insert.
5. We created indices, full-text catalogs, primary keys, and foreign keys using the database-creation scripts.
6. We updated statistics on each table according to database-creation scripts, which sample 18 percent of
the table data.
7. We created ds2user SQL Server login and user for testing using the following Transact SQL (TSQL)
CREATE LOGIN [ds2user] WITH PASSWORD=N’’,
8. We set the database recovery model back to full.
We made the following changes in the build scripts:
• Because we varied the size of the datasets, we sized the files in our scripts to reflect the database size
and the number of files per filegroup. We allowed for approximately 40 percent free space in our
database files to ensure that filegrowth activity did not occur during the testing.
Principled Technologies, Inc.: Virtualized OLTP workload performance comparison of end-to-end solutions:
Dell PowerEdge R710 with Dell EqualLogic storage vs. HP ProLiant DL385 G5 with HP StorageWorks EVA
4400 storage 15 •
• We followed Microsoft’s recommendation of having 0.25 to 1 file per filegroup per core. We used two files
per filegroup on our 2 virtual processor VMs.
We did not use the DBCC PINTABLE command for the CATEGORIES and PRODUCTS tables, both
because Microsoft recommends against this practice and because the commands do nothing in SQL
We added the FORCESEEK hint in the BROWSE_BY_ACTOR stored procedure because in initial testing
we found that SQL Server 2008 was choosing an index scan instead of the preferred index seek, adding
We created a SQL Server login called ds2user and mapped a database user to this login. We made each
such user a member of the db_owner fixed database role.
Using the DVD Store scripts as a referen...
View Full Document
This document was uploaded on 01/20/2014.
- Fall '11