Red Gate forums :: View topic - Dire compare speed in moderate sized DBs - Workaround(?) inc
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Compare 10
SQL Compare 10 forum

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

Search in SQL Compare 10 forum
Post new topic   Reply to topic
Jump to:  
Author Message
Niall



Joined: 21 Jul 2010
Posts: 20

PostPosted: Wed Jun 12, 2013 10:45 am    Post subject: Dire compare speed in moderate sized DBs - Workaround(?) inc Reply with quote

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.
Back to top
View user's profile Send private message
eddie davis



Joined: 14 Jun 2006
Posts: 922
Location: Red Gate Software

PostPosted: Fri Jun 14, 2013 4:11 pm    Post subject: Reply with quote

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
_________________
Eddie Davis
Technical Support Engineer
Red Gate Software Ltd
E-mail: support@red-gate.com
Back to top
View user's profile Send private message Send e-mail
Niall



Joined: 21 Jul 2010
Posts: 20

PostPosted: Thu Sep 26, 2013 9:36 am    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
Michelle Taylor



Joined: 30 Oct 2006
Posts: 528
Location: Red Gate Software

PostPosted: Thu Sep 26, 2013 1:30 pm    Post subject: Reply with quote

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...)
Back to top
View user's profile Send private message
Niall



Joined: 21 Jul 2010
Posts: 20

PostPosted: Thu Sep 26, 2013 1:33 pm    Post subject: Reply with quote

Auto update stats is on and I have have also for all the system tables ran update statistics with resample.
Back to top
View user's profile Send private message
Niall



Joined: 21 Jul 2010
Posts: 20

PostPosted: Fri Sep 27, 2013 4:42 pm    Post subject: Reply with quote

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).
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group