Dire compare speed in moderate sized DBs - Workaround(?) inc

Niall

I have noted that the very very long run time for scanning indexes on medium sized Data Warehouses is still an issue with 10.4 and the index read step can take 3-4 hours for a 17k object (as Red-Gate deem objects) DB.

I had noted that this was supposedly instance specific for us in that for two out of seven instances the scna time was 3-4 hours but for the other five it was in minutes (could be faster - but I wont be happy until it is instant). I had thought this was some setting on the instance but have almost managed to rule that out as I have removed as many differences in settings as I can.

However this morning I tested to see if the content of the database mattered as both the slow instances have no rows in their tables and it seems it does in deed matter. Just sticking a few hundred thousand record in all tables has 'cured' one of the slow running isntances.

Now I do appreciate that this may well be a SQL Server issue but given the above information maybe your development team could use the above pointer, do some mroe testing and see if they can change the index scan query to run better on an empty database with a moderate number of tables.

Machine spec in our case should not be an issue as the DB server is a medium sized IBM box with 48 cored, 0.5 TB ram and banked SSD, SAS and SATA disk, with one of the slow instancs being fully SSD runing Windows 2012 with SQL 2012 SP1 CU4. The desktop PC used is 8 core Win 8 64 bit, 32 GB ram with SSD and a few TB of good enough SATA.

Eddie D

Hi Niall,
Thank you for your forum post and sorry to hear that you are experiencing performance problems.

A support call has been created for you, the call reference is F0073984.

Would it be possible for you to create a SQL Compare Snapshot file of one of the data sources in question for us to test against?

Many Thanks
Eddie

Niall

This is still an ongoing issue, if anything it is getting worse as the long reading indexes step now happens on smaller databases than before.

Michelle T

The last time I remember this happening to a customer, it was tracked down to their statistics not being set to auto-update, making one of our index-related queries run slower and slower.

I don't know if this is the case here, but it might be worth checking that the database is set to auto-create and auto-update statistics, and updating statistics on slow-running instances? (Especially as inserting data seems to have fixed one of them, which is an operation which can cause statistics to update...)

Niall

Auto update stats is on and I have have also for all the system tables ran update statistics with resample.

Niall

I think the reason the performance has recently got worse is due to our increasing the number of partitions we store tables and indexes on. We have recently switched from fixed quarterly to fixed monthly partitions.

It may also be the case that using the command line version works MUCH faster than the Windows GUI one. Not sure on this yet though but a command line I kicked off earler today did finish before I bothered to check it several hours later, whereas the Windows GUI has been stuck on the reading indexes step for 3 hours since. I will run a few tests next week to see if this is the case. That the command line works is not a fix as far as I am concerned as we do need to change the project config after the schema read has happened and/or only upgrade a few tables/procs now and again (all not possible using the command line).