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

asked 04 Mar '15, 11:09

Justin%20Willey's gravatar image

Justin Willey
6.8k110142212
accept rate: 20%

edited 04 Mar '15, 11:14

1

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.

(05 Mar '15, 07:58) Bud Durland

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

(16 Mar '15, 08:54) Bjarne Anker
Replies hidden

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?

(16 Mar '15, 09:12) Justin Willey

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

why is ssd slow

(18 Mar '15, 07:54) Breck Carter
Be the first one to answer this question!
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×242
×203
×5

question asked: 04 Mar '15, 11:09

question was seen: 4,488 times

last updated: 18 Mar '15, 07:54