Calibration best practices

[Former Member]

This is a general question. While the documentation provides us with a number of possible options for calibration, it is not quite clear to me when the calibration should be used and which calibration type should be chosen at what circumstances. The rationale for the calibration is quite clear and the concept of calibration is not unique to SQL Anywhere. The question is how to use it properly.

Just for example, would it be considered a good practice to calibrate every database? What are pros and cons? Also, is there any way to see the calibration statistics in a readable way, better than provided by sa_unload_cost_model? Is it possible to see how calibration affects optimizer's calculations? I'm looking for something better than just comparing query plans before and after the calibration.

Please share your experience with calibration in SQLA.

[MarkCulp]

For the reader, here is the link to the ALTER DATABASE CALIBRATE page in the documentation.

Generally if you are running on a typical computer with your database on a single spindle disk then you likely won't need to calibrate your database since the out-of-box settings are good enough for this case. However if you are using an SSD or a RAID configuration for your database and/or dbspace(s) then running calibrate on the database could help improve the query plans chosen by the optimizer.

You only need to run calibrate once per database/configuration since the calibration only determines the relative speed of your I/O subsystem where your database (and/or dbspace(s)) reside. If you move your database to a new computer or disk system then running calibration again would be a good idea.

Regarding tools to see the affect of the calibration, this would be very difficult to construct since the effect could be very subtle and will depend on your workload. Your suggested method of looking at the before and after plans for your workload queries is the best method.

I do not know of any tools for looking at the calibration statistics. You could look at the results in the system tables?

[Former Member]

Thanks for quick answer. You mentioned that it is possible to see calibration statistics in system tables. What tables should I look in?

By a "tool" for check the effect of the calibration I meant something like "set option show_lio_consting" in ASE 15 or trace flags 302 and 310 in previous ASE versions. Are there any plans to introduce such a functionality in SQLA?

[MarkCulp]

The table is sys.sysoptstat but I see that the calibration data is a long binary and therefore isn't much use to a human reader. 😞