November 29, 2012 at 1:31 pm
Setup:
TableA is replicated from serverA using transactional replication to serverB
Application calls the SQLDependency method to create a query notification on serverB.somedb.dbo.tableA
Archive on serverA starts deleting rows from tableA in batches of 3000
Replication latency increases dramatically
Querying counts on ServerB.somedb.dbo.tableA show deletes are occurring, but extremely slow (5-10/sec). Query plan is using internal table query_notification_<objectid>
So, we have tried to shutdown the application before doing this archive. Query notification remains after shutdown of application. KILL QUERY NOTIFICATION SUBSCRIPTION ALL is issued and subscription is gone. However, the internal table is still there!!! This is killing us. Once the internal table goes away (not sure what makes it go away) the delete picks up speed tremendously (500000 rows/sec).
Does anyone have experience with this and can you help me to get rid of this internal table quickly?
Jared
CE - Microsoft
November 29, 2012 at 1:53 pm
Well... I have now narrowed down that it is not a replication issue or anything tied directly to service broker or the subscription for the query notification. It seems directly ties to the internal table that is created. Strangely enough, although this internal table is created when the query notification subscription is created... it is not dropped when the subscription is killed. In fact, it looks like it just hangs around for some period of time and then the engine drops it. I can't for the life of me figure out why this internal table is not going away when the query notification subscription is killed, and what determines when that table is dropped.
Jared
CE - Microsoft
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply