3

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.

3

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.