richard.austin (7/4/2013)
Hi all,I have a query that takes 2 seconds to compile and run (first time round) when the server is restarted. When the server has been up and running for a day or two, I will try and run the query and all of a sudden it will take 10 minutes. Nearly all of this time is spent compiling the query, and the execution plan has changed all of a sudden and I don't understand why. This system is not yet live so is not subject to change in data, the databases are refreshed from live every morning at 7am. This is eventually going to be the live system and was supposed to be improved performance for the end users, but at the moment we are stuck on this long running query issue.
There are NO wait times while the query is running, no significant cost in the plan, it is all compile time. The server is a virtual server with 8 cores and 12gb of memory, sql capped at 10gb it has SAN disk. there is still available memory for the OS (300mb according to perfmon). Thought this might be external memory pressure and was hoping to see available mb at 0 at the time when the performance deteriorated but this was not the case. I am out of ideas and the deadline is approaching, any thoughts will be appreciated.
Find the fast and slow query plans attached.
It's a right old conundrum
Thanks
Rich
There are 2 things that I can think of off the top of my head that might be causing this. Parameter sniffing (which a recompile of the proc will usually fix) or data being added to indexes which only an index maintenance plan would fix (along with a rebuild of stats if only doing a reorg instead of a rebuild).
--Jeff Moden
Change is inevitable... Change for the better is not.