August 25, 2011 at 9:17 am
We have two instances of SQL 2008 R2 on a Windows 2008 64-bit server. One is the replication publisher, the other is the subscriber.
RAM 24 GB. Each SQL instance is set with minimum 6GB and maximum 12GB.
SELECT statements are reasonable fast to the naked eye, but a simple update statement on a 500 row table such as UPDATE TABLE SET COLUMN = 'AAA' WHERE COLUMN = 'BBB' can take 6-7 minutes. If I restart the SQL service, the update may take 10-20 seconds (still way too long), but after a few hours it's several minutes again.
What should I be looking at?
August 25, 2011 at 10:05 am
Is the column in the where clause indexed? How many rows are in the table? Are you updating a primary key? Have you looked at the query execution plan?
I'm sure someone will be along with a query tuning guide for you shortly. 🙂
August 25, 2011 at 11:33 am
Yes, the column is indexed, it is not the primary key. The table has 513 rows and only one is updated by the query. The estimated query execution plan shows the index being used and Estimated subtree cost is .256533. My experience tells me this should take less than the time it takes to lift your finger from the bottom of a mouse click.
August 25, 2011 at 11:36 am
dan-572483 (8/25/2011)
Yes, the column is indexed, it is not the primary key. The table has 513 rows and only one is updated by the query. The estimated query execution plan shows the index being used and Estimated subtree cost is .256533. My experience tells me this should take less than the time it takes to lift your finger from the bottom of a mouse click.
Please post the actual execution plan.
August 25, 2011 at 12:06 pm
Interesting, while the Estimated Execution Plan looks simple enough, when I run the query with "Include Actual Execution Plan" selected, the Execution Plan tab shows 238 queries. There are several triggers on the table, so apprently quite a lot happens when a "simple" update or insert occurs.
August 25, 2011 at 2:24 pm
Have a look at the replications' retention cleanup. Can it be your server still holds all changes since "the beginning of times"?
August 26, 2011 at 2:00 pm
dan-572483 (8/25/2011)
Interesting, while the Estimated Execution Plan looks simple enough, when I run the query with "Include Actual Execution Plan" selected, the Execution Plan tab shows 238 queries. There are several triggers on the table, so apprently quite a lot happens when a "simple" update or insert occurs.
Trigger on the table?
http://www.simple-talk.com/sql/t-sql-programming/rbar--row-by-agonizing-row
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply