Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Working on tuning our database and queries for one of our products, I decided to pop open Profiler in the QA environment and see what it shows.

I saw for every insert I called, a call was made to sp_executesql with the text of the insert statement.

This seems to be a big hit, is there a way to turn this off?

Environment:

  • SQL Server 2008 R2 Standard
  • Windows Server 2008 R2 Enterprise
  • 32 GB RAM
  • 2x4 Xeon
share|improve this question

3 Answers 3

I wouldn't worry too much.

The idea is to parameterise statements for re-use. That is, avoid compiling

Consider you have 3 different clients run 3 separate statements:

SELECT col1, col2 FROM SomeTable WHERE col3 = 1

SELECT col1, col2 FROM SomeTable WHERE col3 = 15

SELECT col1, col2 FROM SomeTable WHERE col3 = 42

Each of these has different text therefore will be compiled to a different plan. That is, 3 compiles and 3 plans in cached.

Now, these 3

EXEC sp_executesql 'SELECT col1, col2 FROM SomeTable WHERE col3 = @p0', '@p0 int', '1'

EXEC sp_executesql 'SELECT col1, col2 FROM SomeTable WHERE col3 = @p0', '@p0 int', '15'

EXEC sp_executesql 'SELECT col1, col2 FROM SomeTable WHERE col3 = @p0', '@p0 int', '42'

The re-usable plan here is for the statement SELECT col1, col2 FROM SomeTable WHERE col3 = @p0 so you have one plan is cache.

In busy systems, this compile time matters. And most queries are more complex then this of course: you can often see this for 1st run vs subsequent run times (see Testing Query Speed)

Also in complex and busy systems then plan cache size may be limited or takes RAM away from data cache (buffer pool) which can hinder things too. The extreme could be a 10000 plans or one for a busy web app. (The effect depends on 32 vs 64 bit subtleties)

Some ORMs and drivers allow you to switch this off, usually "prepared statements" or some option: but be warned it can slow things down. You said "this seems to be a big hit" which implies you are concerned about load...

share|improve this answer
2  
Great explanation of why some systems do this. It lets them parametrize their SQL without using stored procedures. –  Nick Chammas Sep 15 '11 at 19:06

If you are using most ORMs (Link2SQL, EF, etc.) they cause this lovely little method to be used. If you are writing a parametrized insert statement it'll come through in this method as well.

share|improve this answer
    
So I can't get around this using parameterized inserts? Like... at all? –  Jeremy Boyd Sep 15 '11 at 13:05
    
Nope. Your only option would be to use your own stored procedure. –  mrdenny Sep 15 '11 at 17:19

I'm not sure I understand, you want to stop using sp_executesql? If so, then you simply need to change your code to not use this method. I believe that the SQLCommand object in .NET will utilize sp_executsql, so if you are using .NET then you should expect to see this call.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.