SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


PK Lookup Doing Table Scan?


PK Lookup Doing Table Scan?

Author
Message
kevin.kembel
kevin.kembel
SSC Veteran
SSC Veteran (274 reputation)SSC Veteran (274 reputation)SSC Veteran (274 reputation)SSC Veteran (274 reputation)SSC Veteran (274 reputation)SSC Veteran (274 reputation)SSC Veteran (274 reputation)SSC Veteran (274 reputation)

Group: General Forum Members
Points: 274 Visits: 206
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?
kevin.kembel
kevin.kembel
SSC Veteran
SSC Veteran (274 reputation)SSC Veteran (274 reputation)SSC Veteran (274 reputation)SSC Veteran (274 reputation)SSC Veteran (274 reputation)SSC Veteran (274 reputation)SSC Veteran (274 reputation)SSC Veteran (274 reputation)

Group: General Forum Members
Points: 274 Visits: 206
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search