Flag Product

How to Set Up a SQL Server Stress Test Environment in 8 Steps: Lessons From the Field

Add to my list

Send to friend

Compare prices

Shop for it: (added from 1 site)

Buy itsql-server-performan...

See more products from this brand »

Added by

msi32 added to Favourites...

Rate this:

0 Ratings

Added to 1 list

1 comment about it Add Comment

Description:

As long as your application is in the preproduction phase, the hardware reserved for production can be used as a stress test environment. You must use this time as efficiently as possible. One thing you can do in an early phase is... See more » As long as your application is in the preproduction phase, the hardware reserved for production can be used as a stress test environment. You must use this time as efficiently as possible. One thing you can do in an early phase is testing your disk subsystem. The disk subsystem is in many ways the most important subsystem you should concentrate on during performance tests. * Adjustable: CPU's and memory can be adjusted once your server is up and running. Changing your disk configuration is much harder to do. * Experience: From my experience disk IO are the most common performance bottlenecks. * Timing: A thorough investigation of CPU or memory usage is often very application related, which means you can only start them after you have a working version of your application available. This is not required to test your disk subsystem; there are some very valuable tools available for benchmarking your disk subsystem even before SQL Server is installed. SQLIO.exe is just such an IO throughput benchmarking tool and spending time experimenting with it can be very useful. Some qualities of SQLIO: * It is a low-level tool that will help you to determine the capacity of your I/O subsystem. * It tests your disk subsystem end-to-end (HBA/switches/disks). * It will help you to define how to setup your partitions; database files in file groups for maximum parallel IO usage. * It is a free download. SQLIO is easy to use and it doesn't use the SQL Server storage engine (because SQL Server does not need to be installed already) but a minimal knowledge of the storage engine will help you to define the correct parameters so your results will simulate SQL Server behavior. What you should know before you can setup useful I/O patterns for SQL Server using SQLIO: * The majority (90 percent) of all data page requests are read requests; log records activity is for 99 percent write activity. * Data is accessed per page (8 KB) or per extend (64 KB) where log records are written at <1 KB; index pages are mostly accessed per extend (64 KB). * Data and index pages are accessed randomly; log records are sequentially written. * The majority of data page writes are performed when checkpoints occur. These checkpoints can flush excessive volumes of 8 KB pages. The most important drive you should investigate is the one where you want to store your log file because of its synchronous behavior. A couple of things you must keep in mind when using SQLIO: * Make sure the total size of the test files used in each test is significantly larger than the amount of cache on the storage area network (SAN) so not all the data is transferred from RAM to disk cache (> 2 GB > 64 GB for large SAN's). * Make sure you understand your specific hardware configuration and the theoretical limits for the given hardware. Your SAN vendor must assist you in this process. * Store all of the benchmark data in order to compare with the SQL Server I/O throughput numbers. You will find other important concepts in the accompanied documentation when you download the tool. To interpret SQLIO results, you need to know how many IO's your application will be using, this knowledge depends on having reliable business requirements. Another tool you can use to test your disk IO subsystem before SQL Server installation is the SQLIOStress utility, freely downloadable at http://support.microsoft.com/?id=231619. "SQLIOStress creates separate data and log files to simulate the I/O patterns that SQL Server will generate to its data file (.mdf) and its log file (.ldf). SQLIOStress does not use the SQL Server engine to perform the stress activity so it can be used to exercise a computer before you install SQL Server." (From SQLIOStress Readme.doc) See less »

Tags:

sql io stress test

Flag Comments

Comments (1)

From the list : Favourites Links/Tools by msi32

Utility to stress ur SQL DB

Add a Comment:

Not a Kaboodler? Join Now, It's Free!

Email:	We will never share your email with anyone, ever.
Screen Name:	For public display. Between 3-25 characters.
Password:	6 or more chatacters.
Birthday:	Why do I need to provide this?
	I have read and agree to the Terms of Service
	Remember me on this computer.

Kaboodle will send you a newsletter and updates from your friends. You can unsubscribe at any time. Kaboodle does not sell or share your email address or personal information with anyone.

Cancel Already a Kaboodler?