SQL Server – How to Benchmark Disk Performance

amazon-rdsbenchmarkperformancesql serversql-server-2008-r2

I know of tools such as SQLIO and diskspd to benchmark IO/disk performance, but need something that can be run from the SQL Server instance itself via T-SQL, as I'm working on a Cloud-hosted environment (AWS RDS) and do not have access to the server itself.

There is this article on using sys.dm_io_virtual_file_stats to monitor file IO but I need something that can be run to generate some IO load on demand on multiple servers and generate some kind of report I can use to compare.

Heavy analytical SQL queries with identical execution plans are taking x2 (or more) the time in different servers. I'd like to run this benchmark in order to try to to make sure that's the best this instance type can do in terms of I/O.

Best Answer

To get a general sense of I/O speed on the disks, you can perform any of these disk-intensive maintenance tasks. Note that these won't really help if you're trying to test for a specific workload, but if you're purely interested in disk I/O driven by T-SQL commands only, this is a good "easy" solution.

Create or Rebuild Indexes

This will give you both reads and writes, as tables will be scanned, and then written to new indexes in the specified index order. This is probably most helpful if you can do it on tables with lots of rows, or big data types.

DBCC CHECKDB

Running DBCC CHECKDB on a database will generate a large amount of reads, as it's checking for corruption throughout all of the indexes and tables in the database. Since you're on 2008 R2, which doesn't have parallel CHECKDB, the utility of this option might be limited.

Database backups

If you weren't on RDS (which restricts backups to be performed to S3 through a wrapper stored procedure), running a native SQL Server backup would be a good option to push read and write I/O.

You may still be able to test read I/O by backing up to the NUL device. See BACKUP (Transact-SQL), and especially:

The NUL device can be used to test the performance of backups, but should not be used in production environments.

The general approach with any of these solutions would be to get a before and after from sys.dm_io_virtual_file_stats. There is an example of how to do that in the Paul Randal blog post you linked to in the question.

Related Solutions

Sql-server – Migrating development and production databases to AWS

One of my customers is running their entire production site up on EC2. They've had pretty good luck with it, except for when there is some sort of Amazon caused outage (which there have been a couple of over the last couple of years).

The biggest thing that you need to worry about is planning for an EC2 site failure with your application. If EC2 has a major failure are you ok with the database being offline for a couple of days, or possibly loosing all the data in the database? If not then you'll need to handle that via traditional SQL Server DR planning by having another server running as a hot standby server in another Amazon data center. The same goes for the web servers and anything else that hits the database.

The slowest part of using EC2 is the storage. There are ways around this by getting lots of volumes from Amazon and striping across them within the VM.

As for the dev machine, that's pretty easy. Just toss it up there and it should be fine as long as you've got backups going somewhere.

Sql-server – Two servers, different execution plans. Several orders of magnitude difference in run time

Comparing details from the two execution plans provided:

Slow plan was compiled on build 11.0.2100.60 - SQL Server 2012 RTM
Fast plan was compiled on build 12.0.4100.1 - SQL Server 2014 SP1
Slow plan uses the version 70 cardinality estimator
Fast plan uses the version 120 cardinality estimator
Slow plan has estimated available DOP of 2 (half the visible CPUs)
Fast plan has estimated available DOP of 4 (half the visible CPUs)
Estimated memory grant available is also different between the two
Parameter values used during compilation are identical

With so many fundamental software and hardware differences, it is not really surprising the plans are different.

Ideally, development and test environments should match production as closely as possible. They should certainly be on the same version of SQL Server (and note the 2012 installation there is still at RTM, two full Service Packs and seven further Cumulative Updates behind current.

Without a statistics-only copy of the schema involved in the query, it is hard to know what to suggest to improve the 2012 query plan as it stands. If you are comfortable using hints, you might try adding:

OPTION (LOOP JOIN, HASH JOIN, CONCAT UNION, QUERYTRACEON 8649);

at the end of the query to encourage the optimizer in the general direction of the better plan. The last part of that hint collection (setting trace flag 8649 for the query) is undocumented and not supported for production.

Those hints may or may not be successful, since the underlying issue is the inaccurate cardinality estimation provided by the 70 level cardinality estimator. Query rewrites or indexing changes might be successful as well, but it's really not practical to guess on that from here.