Hopefully you can help me out here. We are running Navision 2009r2 with a sql 2008r2 (RTM) backend. Every week frontend users run a number of jobs, one in particular has started taking twice as long. I monitor my server using Ignite monitoring software. I can see from this software that last week, the queries ran by this weekly process show up in ignite as individual queries, all parameterised. However this week, there is just one big sp_executesql bar, instead of several distinct queries.
Why the change? Is there anything that would make the optimiser use sp_executesql instead of running the queries another way? Anything I can do to make it not use sp_executesql.
I have updated statistics for the whole db, i even cleared the procedure cache to force the queries to be recompiled. Yes I'm aware of the implications of doing this, but felt I had no choice. The indexes are rebuilt every night (those that are over 10% fragmented) using olla hallengren's script.