sqlnaive (11/20/2012)
"Why it was running perfectly without any issues before and after that one bad day ?"Secondly, when once again I removed the clustered index and ran the query, it started working fine even without clustered index. So other doubt is:
"What got changed after I created the clustered index on temporary table for once (even after trying dropping clustered index and/or dropping and recreating that temporary table) ?"
i dont think the restored back could make the issue reproducible , restoration often help to trace or catch the issue with the data or functionality (only available with that back up) it nothing to do with resource contention (like IO CPU etc ) which could be player/culprit in this issue .
Test , are you getting same delaey .slow response from same backed up data EVERYTIME ????????
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)