John Baima (john@nospam.com) writes:
[quoted text, click to view] > I need to understand what could cause two almost identical queries to
> run in a vastly different amount of time. The basic query is the same,
> it is just selecting different employees when calculating schedules.
> The number of selected employees in the slow query is 39. The number
> in the quick is 60. If I look at the execution plan for the two
> queries, at the base level I get:
>
> Physical operation: SELECT
> Logical operation: SELECT
> Row count: 39
> Number of executes: 1
> Subtree cost: 7.99
> Estimated Row count: 11
>
>
> Physical operation: SELECT
> Logical operation: SELECT
> Row count: 60
> Number of executes: 1
> Subtree cost: 8.59
> Estimated Row count: 12
>
> The top query consistently runs in 40 seconds and the one at the
> bottom runs in 4 seconds.
>
> I cannot post these queries (they are about 8k in length). Both
> queries access all tables with "with (readuncommitted)" and I never
> observe any deadlocks so I cannot account for the repeatable
> difference in the queries. Any suggestions?
Without knowing the queries and tables, it's diffiicult to give but
a generic reply. (Then again, if the queries are that large, I'm
quite relieved to be spared!)
SQL Server builds it's query plans from statistics about the data, and
the statistics are built on samples of the data. From these statistics
and the indexes, constraints etc, SQL Server makes estimates, and when
a plan that seems good enough is found, SQL Server goes for that plan.
To this end also comes "parameter sniffing", so if query is parameterised,
the second instance of the same query will reuse the plan of the
first, although it's based on the input to the first query. With
skewew distribution this can be a source of error.
As can the statistics. It may not be sampled well enough, or be out of
date. And even if the statistics is perfect, the optimizer may simply
go lost.
What you can do is to run UPDATE STATISTICS WITH FULLSCAN on all
involved tables, to make sure that you don't have state statistics.
Of course, your problem may be something else, like a missing index,
but as I said, I can only give a generic reply.
--
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
