SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Navision Performance and sp_executesql


Navision Performance and sp_executesql

Author
Message
cunningham
cunningham
SSC-Addicted
SSC-Addicted (401 reputation)SSC-Addicted (401 reputation)SSC-Addicted (401 reputation)SSC-Addicted (401 reputation)SSC-Addicted (401 reputation)SSC-Addicted (401 reputation)SSC-Addicted (401 reputation)SSC-Addicted (401 reputation)

Group: General Forum Members
Points: 401 Visits: 854
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
Grant Fritchey
Grant Fritchey
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39297 Visits: 32621
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

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
cunningham
cunningham
SSC-Addicted
SSC-Addicted (401 reputation)SSC-Addicted (401 reputation)SSC-Addicted (401 reputation)SSC-Addicted (401 reputation)SSC-Addicted (401 reputation)SSC-Addicted (401 reputation)SSC-Addicted (401 reputation)SSC-Addicted (401 reputation)

Group: General Forum Members
Points: 401 Visits: 854
I made a slight mistake, the query is no sp_executesql, it is sys.sp_execute. Is this the same thing?
Grant Fritchey
Grant Fritchey
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39297 Visits: 32621
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

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
cunningham
cunningham
SSC-Addicted
SSC-Addicted (401 reputation)SSC-Addicted (401 reputation)SSC-Addicted (401 reputation)SSC-Addicted (401 reputation)SSC-Addicted (401 reputation)SSC-Addicted (401 reputation)SSC-Addicted (401 reputation)SSC-Addicted (401 reputation)

Group: General Forum Members
Points: 401 Visits: 854
Yes worth a go. Thanks for your response
cunningham
cunningham
SSC-Addicted
SSC-Addicted (401 reputation)SSC-Addicted (401 reputation)SSC-Addicted (401 reputation)SSC-Addicted (401 reputation)SSC-Addicted (401 reputation)SSC-Addicted (401 reputation)SSC-Addicted (401 reputation)SSC-Addicted (401 reputation)

Group: General Forum Members
Points: 401 Visits: 854
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.
Grant Fritchey
Grant Fritchey
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39297 Visits: 32621
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

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search