sqlnaive (11/19/2012)

---

We have a procedure which performs _NO_ update or delete operation but only read. This procedure runs daily without any issue and gets completed within 1-2 minutes but one bad day, it ran continously for over 10 hours and we had to kill it. We tried rebuilding indexes and updating statistics but of no use. We finally gave up for the day. The very next day it ran again and strangely got completed within 1-2 minutes.

I took the backup from production environment for the bad day and restored it in test environment. Again I ran that procedure and somehow found the query which was causing the issue. The query contains one CTE, one physical table and one temporary table.

While running the query, the spid was showing CXPACKET as waitresourcetype which suggests high degree of parallelism and the query worked fine with MAXDOP=0. However I didn't want to do that change as the procedure ran wonderully for all other days.

The activity montior was showing following:

exchangeEvent id=Pipe 17758cf50 WaitType=e_waitPipeGetRow nodeId=4

I removed back that MAXDOP option and rather created clustered index on the temporary table (I know it should have been there in the very first place). Now it worked perfect. But my quesiton is why the issue arouse for that one day only ? There is no 'WITH RECOMPILE' option for that procedure either. It worked perfectly fine before that day and after that day. There was no drastic change in any of the involved table's volume.