Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Navision Performance and sp_executesql Expand / Collapse
Author
Message
Posted Wednesday, July 30, 2014 10:36 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: 2 days ago @ 7:20 AM
Points: 64, Visits: 480
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
Post #1597856
Posted Wednesday, July 30, 2014 10:46 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 6:38 AM
Points: 13,755, Visits: 28,147
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 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1597859
Posted Wednesday, July 30, 2014 12:40 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: 2 days ago @ 7:20 AM
Points: 64, Visits: 480
I made a slight mistake, the query is no sp_executesql, it is sys.sp_execute. Is this the same thing?
Post #1597909
Posted Thursday, July 31, 2014 3:57 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 6:38 AM
Points: 13,755, Visits: 28,147
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 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1598160
Posted Thursday, July 31, 2014 6:19 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: 2 days ago @ 7:20 AM
Points: 64, Visits: 480
Yes worth a go. Thanks for your response
Post #1598212
Posted Friday, August 8, 2014 4:28 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: 2 days ago @ 7:20 AM
Points: 64, Visits: 480
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.
Post #1601113
Posted Friday, August 8, 2014 4:52 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 6:38 AM
Points: 13,755, Visits: 28,147
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 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1601119
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse