Storage area networks are getting larger and more sophisticated. One of the problems I face as SQL consultants is running sqlio on them.
One common stumbling block is the size of the cache on some storage area networks can be huge (128GB plus), especially with the use of solid state devices as a tier “zero” cache accelerator. If our test file is too small we simply get the “from cache” speed of the SAN and while this is interesting its not indicative of performance when we throw a couple of TB of SQL Server data onto it. The fact is that often read cache on a SAN is a waste of money for SQL Server anyway as the cache hit ratio on the SAN will be incredibly low – SQL Server already has its own cache in the buffer pool, so its not going to be reading data from the cache of the SAN anytime soon unless the cache on the SAN is larger than the size of the SQL Servers buffer pool (possible but unlikely).
So what do we do when the SAN has a 64GB cache ? Well guidelines we use is that the size of the test file should be about four times the size of the cache. So your going to need a 256GB test file.
What if you don’t want to wait for a 256GB test file to be created as you only have limited test time?
Here’s a neat trick that Joe Sack from the SQL MCM program showed me for using SQL Servers “Instant File Initialisation” feature to massively speed up creation of test files.
1. Ensure that SQL DB Engine is installed and that Instant File Initialisation is configured in the local Security Policy Editor.
2. Create a database MDF file of the correct size (say 256GB) and detach the database
3. Modify the parameter file that sqlio uses to specify that you have pre-sized the file (see below)
4. Away you go.
Modifying the sqlio parameter file:
Usually the parameter file for sqlio is called something like “param.txt” and looks like this:
You can simply change the filename to be the MDF file and then delete the last parameter so that the size is not specified.
Using this technique I recently had a 1TB test file generated in under three seconds. Nice 
