Thanks a ton Usman, able to solve a lot of issues after your explanation. Thanks, you rock! 😎

Hmmm... so do you mean that if the other 2 columns (Message, DateTimeStamp) were a part of another index on the table... and were not a part of the index...

Sorry to spam, just to add, the other 2 columns, on which its not doing a key look up (Message, Datetimestamp) are not part of the clustered index. Here's the...

Wow, now thats a toughie... Some thoughts:

1] Is this sproc used by multiple applications or by multiple pages on the same application, do you see any pattern as to when...

I was expecting a more technical explanation Jeff!!! 😛

http://blogs.msdn.com/b/craigfr/archive/2007/10/25/recursive-ctes.aspx

Craig says clearly here that the anchor part is executed only once, then why is my change making such a...

Sorry wrong place for this post , please ignore.

Hi Shubham,

Can u check your exec plan? Are you seeing any scans, can you share it here?

Regards - Yasub

Hi,

Can you check your exec plan, even if there are indexes it may be possible that it is doing an index scan. In such a case the lock is escalated...

Thats the big problem Grant, 🙁 , the filtering condition is on the joining table and not this one.... 🙁

Syed, While creating the new index, kept the fillfactor as its...

Hi John,

Is the ANSI_WARNINGS option OFF at the DB where you are not getting any error.

Can you try using Danny's Method at both the servers, to check if it is...

Thanks Grant, i see the point. Any suggestions as to how i can solve this?

Hey Jayanth, this problem is on the pre production environment, cant run these DBCC commands there right...

Apologies for posting info in bits and pieces, here are the stats:

Before Index:

Cpu 156, Reads 4356, Writes 10, Duration 155

After Index:

Cpu 265, Reads 3688, Writes 9, Duration 414

The above exec plan, iafter adding index, here's the one before:

The index is for the first half of the plan.

Here you go...