Ygthkb

Is there any danger of my neighbor having my wife's signature?

How to play songs that contain one guitar when we have two or more guitarists?

Variance of sine and cosine of a random variable

Multiple null checks in Java 8

Have the UK Conservatives lost the working majority and if so, what does this mean?

What does @ mean in a hostname in DNS configuration?

Can a planet be tidally unlocked?

Why Third 'Reich'? Why is 'reich' not translated when 'third' is? What is the English synonym of reich?

What does an unprocessed RAW file look like?

Is Screenshot Time-tracking Common?

What did Putin say about a US deep state in his state-of-the-nation speech; what has he said in the past?

Why do we interpret the accelerated expansion of the universe as the proof for the existence of dark energy?

What if you do not believe in the project benefits?

Why do we divide Permutations to get to Combinations?

Why do single electrical receptacles exist?

Found a major flaw in paper from home university – to which I would like to return

What is an explicit bijection in combinatorics?

Is there a way to pause a running process on Linux systems and resume later?

How to know if I am a 'Real Developer'

Coworker asking me to not bring cakes due to self control issue. What should I do?

Isn't a semicolon (';') needed after a function declaration in C++?

How do I write a maintainable, fast, compile-time bit-mask in C++?

When distributing a Linux kernel driver as source code, what's the difference between Proprietary and GPL license?

Dot product with a constant

How to benchmark disk performance from within SQL Server?

Using Central Management Server to provide convincing performance statisticsMySQL on RDS, data transfer from one instance to another as a production jobMigrating development and production databases to AWSHow can I determine the date of a snapshot in SQL Server 2008r2?How to benchmark Microsoft SQL ServerImport a sample database into SQL Server on AWS RDS free tierSQL Server: Using Dual Databases for Performance?Debug query on big table that is sometimes slowLosing customization capability with managed servers (AWS or Azure)?MySQL- reducing memory footprint

2

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.

sql-server sql-server-2008-r2 performance amazon-rds benchmark

share|improve this question

edited 11 mins ago

jadarnel27

5,47811937

asked Feb 4 at 9:23

scetoauxscetoaux

1134

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  For what purpose is the benchmark used? Doing a relevant benchmark is quite hard.
  
  – vonPryz
  Feb 4 at 9:54
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @vonPryz Heavy analytical SQL queries with identical execution plans taking x2 (or more) the time in different servers. I'm trying to make sure that that's the best that instance type can do in terms of I/O.
  
  – scetoaux
  Feb 4 at 10:10
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  Check that IO statistics for the queries on both servers have more or less the same count of physical or read ahead reads, and the ratio is not changed very much for the second execution when the data has to be in the buffer pool.
  
  – Denis Rubashkin
  Feb 4 at 10:25
  
  
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  You may be able to get a rough idea about read speeds by running DBCC CHECKDB WITH PHYSICAL_ONLY;, or rebuilding indexes and timing the commands. At the very least, it would populate sys.dm_io_virtual_file_stats with similar data as large sequential scans.
  
  – Erik Darling
  Feb 4 at 13:31
  

  
  
  
  

add a comment | 

2

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.

sql-server sql-server-2008-r2 performance amazon-rds benchmark

share|improve this question

edited 11 mins ago

jadarnel27

5,47811937

asked Feb 4 at 9:23

scetoauxscetoaux

1134

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  For what purpose is the benchmark used? Doing a relevant benchmark is quite hard.
  
  – vonPryz
  Feb 4 at 9:54
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @vonPryz Heavy analytical SQL queries with identical execution plans taking x2 (or more) the time in different servers. I'm trying to make sure that that's the best that instance type can do in terms of I/O.
  
  – scetoaux
  Feb 4 at 10:10
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  Check that IO statistics for the queries on both servers have more or less the same count of physical or read ahead reads, and the ratio is not changed very much for the second execution when the data has to be in the buffer pool.
  
  – Denis Rubashkin
  Feb 4 at 10:25
  
  
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  You may be able to get a rough idea about read speeds by running DBCC CHECKDB WITH PHYSICAL_ONLY;, or rebuilding indexes and timing the commands. At the very least, it would populate sys.dm_io_virtual_file_stats with similar data as large sequential scans.
  
  – Erik Darling
  Feb 4 at 13:31
  

  
  
  
  

add a comment | 

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.

sql-server sql-server-2008-r2 performance amazon-rds benchmark

share|improve this question

edited 11 mins ago

jadarnel27

5,47811937

asked Feb 4 at 9:23

scetoauxscetoaux

1134

share|improve this question

edited 11 mins ago

jadarnel27

5,47811937

asked Feb 4 at 9:23

scetoauxscetoaux

1134

share|improve this question

edited 11 mins ago

jadarnel27

5,47811937

asked Feb 4 at 9:23

scetoauxscetoaux

1134

edited 11 mins ago

jadarnel27

5,47811937

edited 11 mins ago

jadarnel27

5,47811937

asked Feb 4 at 9:23

scetoauxscetoaux

1134

asked Feb 4 at 9:23

scetoauxscetoaux

1134

1 Answer
1

active

oldest

votes

1

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.

share|improve this answer

edited 13 mins ago

answered Feb 4 at 19:45

jadarnel27jadarnel27

5,47811937

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  RDS does not allow taking backups to disk, just a stored procedure that wraps the process for it to send the backup to S3. I will give DBCC CHECKDB a go, seems simple enough for a quick test. Cheers.
  
  – scetoaux
  Feb 5 at 4:54
  
  
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  DBCC CHECKDB does not seem to support parallelism in SQL Server 2008 R2 which is what I am forced to run. It's difficult to max out the I/O subsystem with a single serial request. Rebuilding large indexes in parallel did the trick though.
  
  – scetoaux
  Feb 6 at 3:10
  

  
  
  
  

add a comment | 

Your Answer

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});


}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f228819%2fhow-to-benchmark-disk-performance-from-within-sql-server%23new-answer', 'question_page');
}
);

Post as a guest

Name

Email

Required, but never shown

1

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.

share|improve this answer

edited 13 mins ago

answered Feb 4 at 19:45

jadarnel27jadarnel27

5,47811937

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  RDS does not allow taking backups to disk, just a stored procedure that wraps the process for it to send the backup to S3. I will give DBCC CHECKDB a go, seems simple enough for a quick test. Cheers.
  
  – scetoaux
  Feb 5 at 4:54
  
  
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  DBCC CHECKDB does not seem to support parallelism in SQL Server 2008 R2 which is what I am forced to run. It's difficult to max out the I/O subsystem with a single serial request. Rebuilding large indexes in parallel did the trick though.
  
  – scetoaux
  Feb 6 at 3:10
  

  
  
  
  

add a comment | 

1

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.

share|improve this answer

edited 13 mins ago

answered Feb 4 at 19:45

jadarnel27jadarnel27

5,47811937

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  RDS does not allow taking backups to disk, just a stored procedure that wraps the process for it to send the backup to S3. I will give DBCC CHECKDB a go, seems simple enough for a quick test. Cheers.
  
  – scetoaux
  Feb 5 at 4:54
  
  
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  DBCC CHECKDB does not seem to support parallelism in SQL Server 2008 R2 which is what I am forced to run. It's difficult to max out the I/O subsystem with a single serial request. Rebuilding large indexes in parallel did the trick though.
  
  – scetoaux
  Feb 6 at 3:10
  

  
  
  
  

add a comment | 

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.

share|improve this answer

edited 13 mins ago

answered Feb 4 at 19:45

jadarnel27jadarnel27

5,47811937

share|improve this answer

edited 13 mins ago

answered Feb 4 at 19:45

jadarnel27jadarnel27

5,47811937

answered Feb 4 at 19:45

jadarnel27jadarnel27

5,47811937

answered Feb 4 at 19:45

jadarnel27jadarnel27

5,47811937