April 16, 2013 at 9:06 am
I had an issue with replication that I posted in the replication forum, but I don't think it is specific to replication as much as SQL2012 and indexing issues. Here is the other post, not sure if anyone in this forum has experienced this before?
http://www.sqlservercentral.com/Forums/Topic1442423-291-1.aspx
Basically, the merge process that enumerated changes in batches (sp_MSenumchanges_belongtopartition) executed a query to join the rowguid of the changes to the PK/rowguid in my user table. This should have been a super fast query to seek the clustered index of my user table, but instead looking up a 100 row batch was taking around 10 minutes (sometimes significantly longer) and pinning the CPU. If I ran the same query manually, outside of sp_MSenumchanges_belongtopartition, it would return in a matter of milliseconds.
Index usage stats looked like sp_MSenumchanges_belongtopartition was executing a scan on the table, instead of doing a clustered index seek like it would if I executed the query manually. I have no idea why the SP would be executed such a bad query plan, and not sure how to confirm that was the issue or how to more-permanently resolve it?
For now, I created a second nonclustered index on the PK, effectively duplicating the clustered index. Now replication is fine and sp_MSenumchanges_belongtopartition executes in <1s. When I look at the query plan, it shows sp_MSenumchanges_belongtopartition using my new index to locate the PK of the row. Then it joins that result to a clustered index seek to pull the whole row of data. So it's not as efficient as just a clustered index seek, but is working.
Any ideas? Index corruption a possibility? What should I look at next?
April 16, 2013 at 9:48 am
Just to reiterate some of the points from the other thread. Indexes are rebuilt nightly, and fragmentation doesn't seem to be a problem. Statistics are updated automatically, and are also updated manually every night, and don't seem to be out of date. Restarting the service/system didn't seem to have any effect, and I ran a DBCC CHECKTABLE that didn't come up with any errors.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply