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

Forum for users of SQL Compare schema synchronization utility

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

Postby Niall » Wed Jun 12, 2013 9:45 am

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.
Posts: 23
Joined: Wed Jul 21, 2010 8:15 am

Postby eddie davis » Fri Jun 14, 2013 3:11 pm

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 Davis
Technical Support Engineer
Red Gate Software Ltd
E-mail: support@red-gate.com
eddie davis
Posts: 1038
Joined: Wed Jun 14, 2006 2:47 pm
Location: Red Gate Software

Postby Niall » Thu Sep 26, 2013 8:36 am

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.
Posts: 23
Joined: Wed Jul 21, 2010 8:15 am

Postby Michelle Taylor » Thu Sep 26, 2013 12:30 pm

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...)
Michelle Taylor
Posts: 536
Joined: Mon Oct 30, 2006 11:45 am
Location: Red Gate Software

Postby Niall » Thu Sep 26, 2013 12:33 pm

Auto update stats is on and I have have also for all the system tables ran update statistics with resample.
Posts: 23
Joined: Wed Jul 21, 2010 8:15 am

Postby Niall » Fri Sep 27, 2013 3:42 pm

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).
Posts: 23
Joined: Wed Jul 21, 2010 8:15 am

Return to SQL Compare Previous Versions

Who is online

Users browsing this forum: No registered users and 0 guests