|
|
Posted by Erland Sommarskog on 09/09/06 22:43
(billmiami2@netscape.net) writes:
> When it's working, the query runs very quickly, even when 1000 rows are
> returned. This is true for both query analyzer and the web page. Of
> course when it breaks down, the web page stops but query analyzer still
> runs it in a split second.
>
> I certainly know that SQL Server creates an execution plan on the first
> run, but I never thought that there would be two execution plans and
> certainly not a different one for a large resultset vs. a small
> resultset.
When I said that there could be different plans for large and small result
sets that was a simplification. Consider this simple procedure:
CREATE PROCEDURE get_count @val int, @count OUTPUT AS
SELECT @count = count(DISTINCT col1) FROM tbl WHERE col2 = @val
Assume that here is a non-clustered index on col2 and that this index
does not include col1, nor is col1 in the clustred index. Assume further
that the distribution of col2 is uneven. 30% of the rows have 0 in this
column, the remaining rows have scattered value.
If the first invocation is for @val = 10, the optimizer will use the
index to compute the query. But if the first invocation is for @val = 0,
the optimizer will scan the table, because that is faster in this case.
Now, exactly what is going in your application I don't know. But it
sounds as if the procedure is recompiled at some point, and the input
values at that point are very atypical, leading to a poor execution plan
for regular values. That poor plan could affect smaller selection, but
you could be lucky that the cost is less noticeable in this case.
But why would the procedure be recompiled? There are several reasons
for this. One is change in statistics. By default SQL Server maintains
statistics on the tables, and when they change for a table, referring
procedures will be recompiled. It could also be that the plan falls out
of cache if there is memory pressure, and the procedure has not been
used for a while.
One thing you could consider is to add WITH RECOMPILE to the procedure
definition. In this case the procedure is recompiled each time it is
invoked, and nothing is put into cache. The recompile has a cost, but
at least you prevent a bad plan from sticking.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|