Skip to main content
Database Administrators

Return to Answer

Fixes table layout
Source Link
Ian Boyd
Ian Boyd
  • 1k
  • 11
  • 21

SQL Server I/O and CPU Cost is an estimate of 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 the SQL Server optimizer.

The others are:

|╔════════════════════════════╤════════════════╗
║ Item                       |│ Cost (seconds) |║
|----------------------------|----------------|╟────────────────────────────┼────────────────╢
|║ I/O cost (per page)        |│ 0.0031250 s    |║
|║ CPU cost (first row)       |│ 0.0001581 s    |║
|║ CPU cost (additional rows) |│ 0.0000110 s    |║
╚════════════════════════════╧════════════════╝

So if you had a query with:

  • I/O Cost: 2.82387 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.

SQL Server I/O and CPU Cost is an estimate of 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 the SQL Server optimizer.

The others are:

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

So if you had a query with:

  • I/O Cost: 2.82387 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.

SQL Server I/O and CPU Cost is an estimate of 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 the SQL Server optimizer.

The others are:

╔════════════════════════════╤════════════════╗
║ Item                       │ Cost (seconds) ║
╟────────────────────────────┼────────────────╢
║ I/O cost (per page)        │ 0.0031250 s    ║
║ CPU cost (first row)       │ 0.0001581 s    ║
║ CPU cost (additional rows) │ 0.0000110 s    ║
╚════════════════════════════╧════════════════╝

So if you had a query with:

  • I/O Cost: 2.82387 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.

Source Link
Ian Boyd
Ian Boyd
  • 1k
  • 11
  • 21

SQL Server I/O and CPU Cost is an estimate of 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 the SQL Server optimizer.

The others are:

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

So if you had a query with:

  • I/O Cost: 2.82387 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.