1

As I understand from other StackExchange posts, SQL Servers estimates are unitless meaning an Estimated I/O cost of 2.82387 doesn't refer to the time taken or disk I/O's, but a relative measure of how it compares to other query criteria such as the CPU cost.

enter image description here

Is it possible to get a real estimate of disk I/O's for a query, how many pages it believes it will read in etc.? I'm also interested in the actual costs in these units.

As part of a course I'd like to use methods for calculating estimates (Disk I/O's and result sizes) manually and comparing these to SQL Server results for the same queries.

Is it possible real information on execution plans in SQL server and if so how? Or is it only possible to get this unitless information, which I can't really use for comparison.

3

SQL Server I/O and CPU Cost is an estimate in seconds from the year 2000.

SQL Server estimates that each I/O will take 3.125 ms (i.e. 1/320 s, because of the assumption that the disk can perform 320 I/O operations per second. 1/320 = 0.003125). Each I/O is fetching an 8 KB page from the disk.

This is one of the magic numbers inside SQL Server. Others are:

| Item                       | Cost (seconds) |
|----------------------------|----------------|
| I/O cost (per page         | 0.003125 s     |
| CPU cost (first row)       | 0.0001581 s    |
| CPU cost (additional rows) | 0.000011 s     |

So for your query:

  • Your I/O Cost: 2.82387 s
  • Time per I/O: 0.003125 s

This means it estimated: 2.82387 s / 0.003125 s⁄IO = 903.6384 I/O pages

Note: Just because they were seconds doesn't mean they are seconds. The cost isn't implying that I/O will take 2.82 s. Today it's just a unitless magic number; but that magic number does have origins.

| improve this answer | |
2

The best numbers for IO you can get is to turn on statistics.

SET STATISTICS IO ON

and then run your query via management studio. The set option will work for your current connection, and the information will appear in the messages tab.

Then you'll get information on how many pages will be read based on your query.

Also for ease of parsing complex/large query statistics, I use this handy little webpage: http://www.statisticsparser.com/

| improve this answer | |
  • This is definitely helpful. Is it possible to get similar information for the estimated plan? – Duane Nov 26 '15 at 12:04
  • Not that I know off - estimates will be based off the estimated execution plan. However I don't know if you know - but you can hover the mouse over the Arrow as well in the plan and get the estimated data size. – Allan S. Hansen Nov 26 '15 at 12:13
  • Why would you like to have detailed info on an estimated plan? The actual plan is what you need. – RayofCommand Nov 26 '15 at 19:25
1

I guess you are looking for a T-SQL command called "set statistics io on"

USE YOUR_DB_NAME;
GO       
SET STATISTICS IO ON;
GO

if put that before your query you will see the read pages the message tab. Don't forgot to set the statistics off afterwards.

Find the documentation here: msdn IO stats

| improve this answer | |

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.