Sql-server – For every insert statement I see sp_executesql in SQL Server Profiler

performanceprofilersql-server-2008

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
  • 2×4 Xeon

Best Answer

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...

Related Question