CALIBRATE DATABASE issue

[justin_willey]

The scenario is a customer testing the move of a database to a new server / SAN.

The new server has 2 x 24 core Intel Processors, 192 GB RAM, Windows 2012 Server. SAN is EMC all SSD system connected by fibre channel.

Database engine is 16.0.0.2076 - the 72GB database file is in 11.0.1 format still. 4k page size. 50GB allocated to DB cache.

If the database is copied straight over and started under v16 general performance is good. However given the change in hardware we thought it would be worth running CALIBRATE SERVER. After doing this the general responsiveness of the system was very much slower - seemingly across all queries and some heavy duty processes have gone from seconds to minutes. Running RESTORE DEFAULT CALIBRATION made things better but definitely not as good as before.

IOParallelism remains at 1 at all stages. Running sa_unload_cost_model() before the CALIBRATE SERVER produced an empty file. After the calibration it produced a file with content, and after the restoration of the default calibration it produced an empty file again.

My questions:

• Is this a problem because of using v16 to calibrate a v11 database?
• Is it something I need to worry about - ie is there any danger of a self-tuning mechanism producing the same effect?
• Is it odd that a system like this has IOParalellism of 1?

Content of the cost model file mentioned above was:

1,0,2,0x0700000001000000c800000020030000800c000004d04c00e89c9900b036330192000000d5000000de000000e10000006d0100006f0100006f01000092000000d5000000de000000e1000000db000000dc000000d9000000400d0300

[Former Member]

No real help here, but personally, I wouldn't do any calibration until the db was rebuilt using unload/reload. There may be some performance gains in tweaking the page size. Calculating the optimum page size is a science far enough advanced to me that it might as well be voodoo. I simply rebuilt mine using 4k, 8k, and 16k, and timed some queries commonly used in the system. Based on that, 8k was best for us.

[bjanker77]

Hi Justin.

Have you figured this out? Our biggest customer just moved their 22GB database from a 4 year old server to a brand new server with 2 x 8 core CPU, 64GB RAM and 2 x 200GB SSD drives in RAID 1. The database was rebuilt using unload/reload, but the performance is awful. Every query is using the double amount of time, and all feels really sluggish and slow.

Any good ideas from the top of your head?

Regards,

Bjarne Anker Maritech Systems AS Norway

[justin_willey]

No we haven't sorted it out yet. I'm getting together the material for a more detailed question on the disk performance aspect.

Have you tried the CALIBRATE SERVER process - we have had good results in other cases?

[Breck_Carter]

FWIW this Google search turns up many interesting conversations...

why is ssd slow