Training for SQL Server, one of the questions in Learning material says:

Before migrating on-premise SQL Server deployment (single server) to Azure VM, you need to evaluate "number and types of read/write operations" of the existing deployment
Which tool should you use ?

Options (pick correct one)

  1. SQL Server Profiler trace using TSQL template
  2. SQLIOSim
  3. Database Engine Tuning Advisor
  4. SQL Disk Usage Standard Report

Best probably would have been "Performance Monitor", but its not in the list
I think correct one is 4) "SQL Disk Usage Standard Report", but Learning Material says that correct answer is 2) "SQLIOSim"

But, In my opinion, SQLIOSim is for testing the disk subsystem, it is not for measuring existing SQL Server instance metrics

Who is right - me, Learning material, or you (your option) ?
And what they mean by "types" of read/write operations ?

  • 2
    Can you add the link to the training material to your question? I interpret "number and types" to mean the number of sequential and random IO requests, in which case the correct answer would be none of the above.
    – Dan Guzman
    Sep 7 '20 at 12:11
  • 2
    You are correct. You should either measure the IO consumed by the SQL instance (perfmon), or the IO capabilities of the existing infrastructure. And SQLIOSim would not be the tool for that. It would be SQLIO or Diskspd. Sep 7 '20 at 15:48

Who is right - me, Learning material, or you (your option ) ?

None of the options is correct. The "Disk Usage Standard Report" is a database-level report that displays disk space utilization, not IO.

enter image description here

And what they mean by "types" of read/write operations ?

Judging that the "correct" answer was SQLIOsim, it probably meant sequential/random IO size that you can measure with SQLIO or Diskspd.

Here's what they're getting at:

When preparing for a SQL Server migration, a simple and effective migration plan is to provide equivalent-or-better resources in the new environment. Alternatively you can attempt to measure the SQL Server resource utilization and base size the new environment off of that.

Of the two approaches the first is simpler and safer, and for a migration to an Azure VM, where you get to choose the VM family, size, and storage configuration, and where subsequent right-sizing is simple, that's the approach I would take.

Use Diskspd or SQLIO to measure the IO system, and use the system specs or standard benchmarks to measure the CPU and Memory capabilities, and spec the new enviornment from that. Very simple, and can be done by a sysadmin, developer, or DBA.

The problem with this approach is that sometimes the existing deployment is unnecessarily fast, and results in an unnecessarily expensive new environment. Then I would dig in with DBA and admin tools and adjust the estimate based on the actual utilization.

This happens frequently for storage where either:

  1. There's a high-end shared SAN device providing storage for multiple servers. You might measure a very high peak throughput on such a device, and would be unlikely to want to provide an Azure Disk configuration that matches it for each server.

  2. SQL Server databases are stored on local NVMe flash storage and replicated with AlwaysON Availability Groups. Storing your databases on the local flash drive is not supported for SQL Server on Azure VMs (although that's how Azure SQL Database Premium/Business Critical runs).

For that you would use something like the Azure SQL Database DTU Calculator, which will collect performance counters and create an estimate of the cpu and IO throughput required.

For IO I would start with:

Logical Disk - Avg. Disk Bytes/Read
Logical Disk - Avg. Disk Bytes/Write
Logical Disk - Avg. Disk sec/Read
Logical Disk - Avg. Disk sec/Write
Logical Disk - Disk Reads/sec
Logical Disk - Disk Writes/sec
Logical Disk - Disk Read Bytes/sec
Logical Disk - Disk Write Bytes/sec

Focusing mainly on the log and tempdb volumes, unless you see consistently significant data file read IO. But you still have to imagine how the workload would behave if you reduced peak capacity in the new environment. Do you really care if backup takes a little longer? Sometimes yes, sometimes no. Is the CPU maxed only during an overnight batch operation, and do you really care if that takes a bit longer?

The additional data collection and the need to interpret it with the understanding of how SQL Server works and in the context of the business needs of the application create significant additional complexity.

  • thanks for the answer! I will study this week and mark as Answer then. Btw, what if under "SQL Disk Usage Standard Report" they meant the Disk Usage in Management Data Warehouse SQL Server feature ? Looks like it can show Latency, Queue, Bandwidth info for the SQL Server instance Sep 7 '20 at 17:30
  • 1
    Unlikely. The Standard Reports are a frequently-used, always-available tool. The Management Data Warehouse is more obscure, and requires setup. Sep 7 '20 at 17:59
  • one last question if I may: from your posts, measure the IO capabilities of existing system using the DiskSpd or SQLIO, and not the SQLIOSim. Why SQLIOSim is not good for it ? Sep 9 '20 at 13:52
  • SQLIOSim is for stress testing the IO subsystem for correctness, not performance. Sep 9 '20 at 13:55
  • 1
    Oh I see! So judging by 1. Profiler and 3. DTA does not seem to fit as a "correct" answer, and 4.SQL Disk Usage Standard Report is just a Space used report in SSMS (not MDW one), we don't have a choice and should really pick 2. SQLIOSim, although they probably were mistaken and should have put "SQLIO" or DiskSpd there... correct me if I am wrong. Thanks for great answer anyway! Sep 9 '20 at 14:03

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.