Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

PK Lookup Doing Table Scan? Expand / Collapse
Author
Message
Posted Tuesday, April 16, 2013 9:06 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, August 22, 2013 2:22 PM
Points: 39, Visits: 190
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?
Post #1442794
Posted Tuesday, April 16, 2013 9:48 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, August 22, 2013 2:22 PM
Points: 39, Visits: 190
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.
Post #1442823
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse