sp_executesql increases number of reads?

Yash

Hi,

I am using SQL 2000 SP4.

I have compared 2 scenarios:
Scenario 1:
insert into #bacs_report
SELECT .....
WHERE <conditions>

When monitored in the profiler, this statement shows 18895 reads for
the SQL:StmtCompleted event.

The block that executes this stmt takes 750ms.

Scenario 2:
insert into #bacs_report
EXEC sp_executesql <the same SELECT query with parameters>

In profiler, this stmt shows 2 rows:
A SELECT stmt that has 60K reads
An INSERT stmt that has 70K reads

The overal block still takes around 750 ms with results that match
those of the first scenario.

Can someone explain why is there such a surge in the number of reads
when using sp_executesql? Is the profiler showing wrong results as the
total duration still remains the same?

Thanks,
Yash

Sep 10 '08 #1

Erland Sommarskog

Yash (ya****@gmail.com) writes:

I am using SQL 2000 SP4.

I have compared 2 scenarios:
Scenario 1:
insert into #bacs_report
SELECT .....
WHERE <conditions>

When monitored in the profiler, this statement shows 18895 reads for
the SQL:StmtCompleted event.

The block that executes this stmt takes 750ms.

Scenario 2:
insert into #bacs_report
EXEC sp_executesql <the same SELECT query with parameters>

In profiler, this stmt shows 2 rows:
A SELECT stmt that has 60K reads
An INSERT stmt that has 70K reads

The overal block still takes around 750 ms with results that match
those of the first scenario.

Can someone explain why is there such a surge in the number of reads
when using sp_executesql? Is the profiler showing wrong results as the
total duration still remains the same?

First, note that the total number of reads in the second case is 70000
reads, not 130000. The INSERT statement includes the SELECT statement.

As for the increased number of reads, this is likely to be due to
differences in query plans. You indicate that the call to sp_executesql
is parameterised, and the plan for

SELECT ... FROM tbl WHERE col = <const>

and

SELECT ... FROM tbl WHERE col = @para

is necessarily not the same. If the first case, the optimizer only needs
to consider the one and single value for the constant. In the other,
the optimizer must also account for other values. Then again, the
first time you run the query the optimizer "sniffs" the parameter, so
some there is some likelyhood for the same plan. Unless the query
already was in the cache, and the plan had been sniffed from a different
input value.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

Sep 10 '08 #2

Yash

I don't think one query runs faster because the plan is cached. The
numbers I have given are what I see after multiple executions.
I have now tested with 2 scenarios, one with INSERT and the other
without. This time both use sp_executesql and have parameters. I
observe that the one with :
insert into #bacs_report
EXEC sp_executesql <query with parameters>
takes 60K reads
and the one with just
EXEC sp_executesql <query with parameterstakes 18K

Looks like the INSERT is causing the problem when used with
sp_executesql. Why would the plan differ in the 2 cases?

Another issuse is that SQL Query Analyzer does not show the query plan
when the INSERT stmt is present with sp_executesql.

Thanks,
Yash

Erland Sommarskog wrote:

Yash (ya****@gmail.com) writes:

I am using SQL 2000 SP4.

I have compared 2 scenarios:
Scenario 1:
insert into #bacs_report
SELECT .....
WHERE <conditions>

When monitored in the profiler, this statement shows 18895 reads for
the SQL:StmtCompleted event.

The block that executes this stmt takes 750ms.

Scenario 2:
insert into #bacs_report
EXEC sp_executesql <the same SELECT query with parameters>

In profiler, this stmt shows 2 rows:
A SELECT stmt that has 60K reads
An INSERT stmt that has 70K reads

The overal block still takes around 750 ms with results that match
those of the first scenario.

Can someone explain why is there such a surge in the number of reads
when using sp_executesql? Is the profiler showing wrong results as the
total duration still remains the same?

First, note that the total number of reads in the second case is 70000
reads, not 130000. The INSERT statement includes the SELECT statement.

As for the increased number of reads, this is likely to be due to
differences in query plans. You indicate that the call to sp_executesql
is parameterised, and the plan for

SELECT ... FROM tbl WHERE col = <const>

and

SELECT ... FROM tbl WHERE col = @para

is necessarily not the same. If the first case, the optimizer only needs
to consider the one and single value for the constant. In the other,
the optimizer must also account for other values. Then again, the
first time you run the query the optimizer "sniffs" the parameter, so
some there is some likelyhood for the same plan. Unless the query
already was in the cache, and the plan had been sniffed from a different
input value.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

Sep 11 '08 #3

Erland Sommarskog

Yash (ya****@gmail.com) writes:

I don't think one query runs faster because the plan is cached. The
numbers I have given are what I see after multiple executions.
I have now tested with 2 scenarios, one with INSERT and the other
without. This time both use sp_executesql and have parameters. I
observe that the one with :
insert into #bacs_report
EXEC sp_executesql <query with parameters>
takes 60K reads
and the one with just
EXEC sp_executesql <query with parameterstakes 18K

Looks like the INSERT is causing the problem when used with
sp_executesql. Why would the plan differ in the 2 cases?

I ran this simple test:

create table #ord(OrderID int)
go
exec sp_executesql
N'SELECT OrderID FROM Northwind..Orders WHERE CustomerID = @c',
N'@c nchar(5)', @c = N'ALFKI'
* go
insert #ord
exec sp_executesql
N'SELECT OrderID FROM Northwind..Orders WHERE CustomerID = @c',
N'@c nchar(5)', @c = N'ALFKI'
go

I get 16 reads for the plain SELECT, where as the SELECT as part of the
INSERT gets 24 reads, and the INSERT batch in total has 63.

I think part of the problem here is what is a read. More IO operations
are to be the expected with the INSERT, but that would be writes. But
I don't see any writes at all, so it seems that writes counts as reads
for some reason.

I'm guessing wildly here, but I think the extra reads for the SELECT
insert the INSERT comes from when result is written to some internal
rowset.

Another issuse is that SQL Query Analyzer does not show the query plan
when the INSERT stmt is present with sp_executesql.

You can catch the plans with the Performance:Show Statistics Profile
event. You need to include BinaryData in the result set.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

Sep 11 '08 #4

Yash

Now I have included the INSERT within the dynamic SQL stmt. It now
reads:
INSERT INTO #temp
SELECT ....

Even with sp_executesql, it gives 19k reads. This has solved my
problem.

Thanks,
Yash

Erland Sommarskog wrote:

Yash (ya****@gmail.com) writes:

I don't think one query runs faster because the plan is cached. The
numbers I have given are what I see after multiple executions.
I have now tested with 2 scenarios, one with INSERT and the other
without. This time both use sp_executesql and have parameters. I
observe that the one with :
insert into #bacs_report
EXEC sp_executesql <query with parameters>
takes 60K reads
and the one with just
EXEC sp_executesql <query with parameterstakes 18K

Looks like the INSERT is causing the problem when used with
sp_executesql. Why would the plan differ in the 2 cases?

I ran this simple test:

create table #ord(OrderID int)
go
exec sp_executesql
N'SELECT OrderID FROM Northwind..Orders WHERE CustomerID = @c',
N'@c nchar(5)', @c = N'ALFKI'
� go
insert #ord
exec sp_executesql
N'SELECT OrderID FROM Northwind..Orders WHERE CustomerID = @c',
N'@c nchar(5)', @c = N'ALFKI'
go

I get 16 reads for the plain SELECT, where as the SELECT as part of the
INSERT gets 24 reads, and the INSERT batch in total has 63.

I think part of the problem here is what is a read. More IO operations
are to be the expected with the INSERT, but that would be writes. But
I don't see any writes at all, so it seems that writes counts as reads
for some reason.

I'm guessing wildly here, but I think the extra reads for the SELECT
insert the INSERT comes from when result is written to some internal
rowset.

Another issuse is that SQL Query Analyzer does not show the query plan
when the INSERT stmt is present with sp_executesql.

You can catch the plans with the Performance:Show Statistics Profile
event. You need to include BinaryData in the result set.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

Sep 12 '08 #5

Erland Sommarskog

Yash (ya****@gmail.com) writes:

Now I have included the INSERT within the dynamic SQL stmt. It now
reads:
INSERT INTO #temp
SELECT ....

Even with sp_executesql, it gives 19k reads. This has solved my
problem.

Indeed, that is likely to be more effcient. Thanks for giving me a
reminder!

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

Sep 12 '08 #6