Sql-server – Capturing sp_executesql statement in sql profiler

Just use a trace template. Click File, New Trace, and choose the Replay template. That'll get you everything that happens.

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