While developing a new query at work I wrote it and profiled it in SQL Query Analyzer. The query was performing really good without any table scans but when I encapsulated it within a stored procedure the performance was horrible. When I looked at the execution plan I could see that SQL Server picked a different plan that used a table scan instead of an index seek on TableB (I've been forced to obfuscate the table and column names a bit but none of the query logic has changed).
Here's the query
SELECT
DATEADD(dd, 0, DATEDIFF(dd, 0, TableA.Created)) AS Day,
DATEPART(hh, TableA.Created) AS [Hour],
SUM(TableB.Quantity) AS Quantity,
SUM(TableB.Amount) AS Amount
FROM
TableA
INNER JOIN TableB ON TableA.BID = TableB.ID
WHERE
(TableA.ShopId = @ShopId)
GROUP BY
DATEADD(dd, 0, DATEDIFF(dd, 0, TableA.Created)),
DATEPART(hh, TableA.Created)
ORDER BY
DATEPART(hh, TableA.Created)
When I run the query "raw" I get the following trace stats
Event Class Duration CPU Reads Writes SQL:StmtCompleted 75 41 7 0
And when I run the query as a stored proc using the following command
DECLARE @ShopId int
SELECT @ShopId = 1
EXEC spStats_GetSalesStatsByHour @ShopId
I get the following trace stats
Event Class Duration CPU Reads Writes SQL:StmtCompleted 222 10 48 0
I also get the same result if I store the query in an nvarchar and execute it using sp_executesql like this (it performs like the sproc)
DECLARE @SQL nvarchar(2000)
SET @SQL = 'SELECT DATEADD(dd, ...'
exec sp_executesql @SQL
The stored procedure does not contain anything except for the select statement above. What would cause sql server to pick an inferior execution plan just because the statement is executed as a stored procedure?
We're currently running on SQL Server 2000
SQL Server Management Studio has three options to display execution plans: The Estimated Execution Plan is the compiled plan, as produced by the Query Optimizer based on estimations. This is the query plan that is stored in the plan cache. The Actual Execution Plan is the compiled plan plus its execution context.
Execution plans are stored in memory called plan cache, hence can be reused. Each plan is stored once unless optimizer decides parallelism for the execution of the query. There are three different formats of execution plans available in SQL Server - Graphical plans, Text plans, and XML plans.
Stored procedures are a sequence of SQL statements that access the relational database management system. Prepared statements are queries that contain the placeholders instead of actual values. It can be stored in the database server.
PLAN_TABLE is the default sample output table into which the EXPLAIN PLAN statement inserts rows describing execution plans.
This generally has something to do with parameter sniffing. It can be very frustrating to deal with. Sometimes it can be solved by recompiling the stored procedure, and sometimes you can even use a duplicate variable inside the stored procedure like this:
alter procedure p_myproc (@p1 int) as
declare @p1_copy int;
set @p1_copy = @p1;
And then use @p1_copy in the query. Seems ridiculous but it works.
Check my recent question on the same topic:
Why does the SqlServer optimizer get so confused with parameters?
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With