Physical IO Cost vs Logical IO Cost

[Former Member]

As far as I know the ASE optimiser estimates a physical page read as 18 and a logical page read as 2.

This suggests the cost of a physical read is only 9 times greater than a logical read.

In the real world (or at least my world) a physical page is between 2ms and 18ms depending on the SAN tier, what time of day it is, etc

And a logical page is about 0.05ms. So at best a physical page read is about 50 times slower and at worse 300 times slower.

Shouldn't we adjust these values for the world as it is now ? ie logical read should be set to 2 and a physical read should be set between 100 and 300.

Is it possible to change these values to something reflecting our own environments ?

I don't think these timings are that unusual.

[kevin_sherlock]

In more recent versions, these ratios (and they are simply ratios, that is, number relative to each other as you point out) are configurable, and YES, you should configure them to the best of your knowledge of your environment:

1> sp_configure 'cost'

2> go

Parameter Name                 Default     Memory Used          Config Value                                                                                                                        Run Value                                                                                                                           Unit                 Type ------------------------------ ----------- -----------          ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------          ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------          -------------------- ---------- cost of a cpu unit                    1000           0                  1000                                                                                                                                1000                                                                                                                        number               dynamic cost of a logical io                     2           0                     2                                                                                                                                   2                                                                                                                        number               dynamic cost of a physical io                   25           0                    25                                                                                                                                  25                                                                                                                        number               dynamic

In older versions of ASE, you can use "dbcc tune(wlogical | wphysical , <n>)", but these settings weren't persistent across ASE reboots, so had to be done every time you start ASE.

[Former Member]

Thanks - I see the ratio is now 2 to 25.

As its dynamic I'll see what happens when I change it.

Are these parameters only used when it compiles a proc ?

How does it use these values ? Is it looking in the cache to see what's there ?

[kevin_sherlock]

These numbers are used by the optimizer when costing query plans.  That can be for proc compiles, SQL statement compiles, triggers, etc.  The optimizer uses a great number of factors and statistics to determine how many physical ios, and cache io's a query/statement will need.  Then uses these multipliers (along with other stats) to give each possible query plan a "cost".  The plan with the lowest cost is chosen as the ultimate query plan to cache and execute.

A very detailed explanation can be found in the product manuals, or some decent google searches.

Here are some starting points:

http://infocenter.sybase.com/help/topic/com.sybase.infocenter.dc00743.1570/html/queryprocessing/titl...