insert query timing out reasons

[jcaulder]

I have a fairly simple insert query that continually times out from our application. I've ran the query from SSMS and killed it after 8 minutes when it didn't complete. We are using SQL 2008 Standard with merge replication to about 36 subscribers and one way transactional replication to one subscriber(reporting server).

I have rebuilt all indexes on all related tables including on the merge three primary tables(genhistory, contents, tombstone). There was a trigger on the table used to write to a log table for historical auditing which I've temporarily disabled to remove it from the equation. This did not help any. I have looked for blocks while the query runs and there doesn't appear to be any.

When the query starts running, if I look at the locks for the SPID, it has a total of about 116 locks.

I know this is not much information to go on but can anyone suggest something else to look for as the cause of this. The insert was running fine until a couple of days ago. All other queries appear to be running without issue.

Just as a final thought, Friday at noon I upgraded the database server from SQL 2000 to 2008 but the query performed fine after that until about Tuesday. Merge replication was not taken down for this upgrade. Transactional replication did not gracefully keep running post-upgrade so I had to delete the publication and recreate it. It has been running fine since then.

I don't think the upgrade has anything to do with it but wanted to throw it out there in case it could be affecting it. We've actually seen improvement with most queries post-upgrade except this one.

TIA for any assistance!

J

 

[SQLBill]

Are you seeing any deadlocking? With that many threads, it could be possible that threads are waiting for a resource that another thread has locked and in turn is locking a resource that the other is waiting for. For example, thread A locks table 1, but needs to access table 2 to finish the work on table 1. However, thread B has table 2 locked and is waiting access to table 1.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[jcaulder]

Just to let anyone interested know what caused this issue...

I ultimately had to get Microsoft involved. They tried updating statistics and rebuilding all indexes on all tables including all merge tables. Neither of these helped any since I had already done this. We ran profiler to determine what statement(s) exactly were causing the delay and found it was definitely related to merge replication. Microsoft's lead technical consultants reviewed the profiler trace and found that the problem was related to some new optimization built into SQL 2008. Under the publication properties-->subscription options there is a boolean option for "Precompute Partitions" which in our case was set to true and disabled. According to MS, if any subscribers are SQL 2000, this cannot be set to true. In our case, all subscribers are MSDE(SQL 2000). So in our case, the upgrade process correctly determined that we shoulnd't be able to change this setting(i.e. it disabled it) however it incorrectly set it to true when it should have been false. MS had to give me a query to run to set this to the correct value of false. There were a few other steps but this was the issue that had to be resolved.

"Precompute partitions" per MS moves the determination of what partition(s) a row belongs to at the point of insert/update/delete rather than during the merge/sync startup process. This is supposed to be more scaleable and efficient than the previous model in 2000. So during our insert of the row, the partitioning process was firing leading to the issue. We let the query run to completion and it took 52 minutes with "Precompute Partitions" set to true. Once this was disabled, it took less than 1 second!!

I still have a number of questions about what was happening 'behind the scenes' but this setting was definitely the culprit. MS seems to think it was partly due to the number of filters/joins on this article even though there were only 4 and MS recommends no more than 5. But some of the joined tables were fairly large. We were having no issues on any other table except this one.

So if after upgrade to SQL 2008 you start having insert query timeouts or performance issues, you may want to look at "precompute partitions".

In summary a combination of: a buggy upgrade process to 2008, SQL 2000 subscribers, merge replication, article with many filters or joins especially to large tables, precompute partitions = true may lead to performance issues on a table.

So this is a perfect storm scenario I guess but perhaps this post will help someone else down the road in the same situation.

J