July 30, 2014 at 10:36 am
Hi all,
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.
Any ideas?
Thanks
July 30, 2014 at 10:46 am
The choice for an ad hoc query, a parameterized query, stored procedure, or sp_executesql are not made internally by SQL Server. They're all made by the application. There's nothing in SQL Server that would take a prepared statement and turn it into sp_executesql on the fly. I'm not an expert with Solarwinds Ignite, but I wonder if there is a setting or aggregation or something that is taking the invidual statements being executed through sp_executesql and turning them all into a single aggregate under the sp_executesql heading? That's where I'd look.
Oh, and update the instance of SQL Server. There are tons and tons of bug fixes in the SP and CUs. Running RTM of 2008R2 is almost criminally negligent at this point in time.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 30, 2014 at 12:40 pm
I made a slight mistake, the query is no sp_executesql, it is sys.sp_execute. Is this the same thing?
July 31, 2014 at 3:57 am
cunningham (7/30/2014)
I made a slight mistake, the query is no sp_executesql, it is sys.sp_execute. Is this the same thing?
That's for executing prepared statements. Different than sp_executesql because the prepared statements are prepared in the app code then passed to SQL Server where they're treated similarly to stored procedures.
I don't know Solarwinds Ignite well enough to tell you how it works, but I think it should be able to differentiate between different prepared statements. I suspect you have some type of aggregate set. You might post this question on their support forums.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 31, 2014 at 6:19 am
Yes worth a go. Thanks for your response
August 8, 2014 at 4:28 am
To let you know after running dbccfreeproccache to clear out stale plans Navision performance for the processes mentioned improved dramatically, and sp_execute makes up for a tiny percentage of queries according to Confio. I had heard parameter sniffing was a possible issue in Navision, and this is what let me to clear out the procedure cache.
Whether it is coincidence or not is not confirmed, last week was period end and very busy compared to this week, however from the stats from 5 weeks ago I can still see a marked improvement.
August 8, 2014 at 4:52 am
Interesting stuff. Thanks for posting the follow up. Clearing the cache as a mechanism for dealing with bad parameter sniffing is somewhat problematic. You might want to look to plan guides instead (since you won't be able to directly affect the queries themselves).
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply