January 5, 2012 at 3:18 am
Can anyone help with the following.
I have 2 servers. Server A and B running SQL 2005 standard with merge replication set up between them. I have a table (table_A) that I cannot bulk delete from on server B. Deleting from the same table on server A works. Normally the delete task runs on B and those deletes are replicated to A.
I also noticed the following: After performing a count on server A and B Table_A then running the bulk delete on A the deletes didn’t appear to replicate to B – Its as if this table on server B has a problem.
There is around 170,000 records in the table and I’m running the following syntax
delete from table_A where datediff(hh,time_occurred,getdate()) > 24
A few days ago this t-sql completed within a second. Now it doesn’t complete. I can however delete a single row at a time. Nothing has changed on the server to my knowledge.
I have set up a profiler trace before, during and after executing this t-sql and run the trace through database engine tuning advisor and no recommendations were made.
I’m thinking I should truncate the table however want to avoid as I will need to stop replication and take a new snapshort after the articles has been removed from the publisher.
Does anyone else have any other suggestions?
January 5, 2012 at 3:32 am
A little more information.
If I run the following the query completes instantly;
select top 10 * from Table_A
however If I run
Select top 10 * from Table_A
order by time_occurred
The query runs for 10 seconds, then I ended....this should not take this long.
looks like the sorting of this table is messed up. Note there are no indexes only the one replication added. however as mentioned there were no recommendations from the tuning adviser for any indexes.
January 5, 2012 at 10:04 am
Why no index on time_occurred ?
Is the table getting bigger, so that originally a table scan was quick, but now takes much longer ?
January 5, 2012 at 10:39 am
TOP 10 can grab one page (or a few) and return 10 rows. Any 10.
The order by means you must load the entire table to ensure you have the top 10 from that order.
Add an index.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply