slow query (Network Steve Forum)

slow query

my version is:

Microsoft SQL Server 2008 (SP3) - 10.0.5538.0 (X64) Apr 3 2015 14:50:02 Copyright (c) 1988-2008 Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (VM)

I have a query which is taking a long time. analyzing the QEP I see a large gap between the estimated number of rows and the actual number of rows. I simplified the query to just that section and the result is the same. estimated 1 row actual 6000+ rows. (the section is the basis for a join to other large tables which in stead of 1 row must join 6000+)

I have already updated statistics and recompiled the query but to no avail

how can I get sql server to create a better estimate and hence a better QEP?

the shortened query:

select * FROM dbo.items
WHERE items.net_code=1
and dbo.items.item_num between 1 and 99000 and items.year_num=1971

the QEP (in XML)

<?xml version="1.0" encoding="utf-16"?>
<ShowPlanXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Version="1.1.1" Build="10.0.5538.0" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
<BatchSequence>
<Batch>
<Statements>
<StmtSimple StatementCompId="1" StatementEstRows="1" StatementId="1" StatementOptmLevel="TRIVIAL" StatementSubTreeCost="0.0032833" StatementText="SELECT * FROM [dbo].[items] WHERE [items].[net_code]=@1 AND ([dbo].[items].[item_num]>=@2 AND [dbo].[items].[item_num]<=@3) AND [items].[year_num]=@4" StatementType="SELECT" QueryHash="0x4C4EDD0E4C55883C" QueryPlanHash="0x3E389B754A284D59">
<StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" />
<QueryPlan DegreeOfParallelism="1" CachedPlanSize="40" CompileTime="17" CompileCPU="17" CompileMemory="336">
<RelOp AvgRowSize="8340" EstimateCPU="1E-07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Compute Scalar" NodeId="0" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="0.0032833">
<OutputList>
<ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="net_code" />
<ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="year_num" />
<ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="item_num" />
<ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="broadcast_code" />
<ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="sort_order" />
<ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="item_title" />
<ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="item_desc" />
<ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="duration" />
<ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="update_date" />
<ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="rowguid" />
<ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="orig_program_code" />
<ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="item_archive_num" />
<ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="broadcast_archive_num" />
<ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="TC" />
<ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="lang_code" />
<ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="updated_by" />
<ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="orig_broadcast_date" />
<ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="duration_time" />
<ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="orig_TC" />
<ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="created_by" />
<ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="create_date" />
<ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="item_search" ComputedColumn="true" />
</OutputList>
<ComputeScalar>
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="item_search" ComputedColumn="true" />
<ScalarOperator ScalarString="[Expr1005]+isnull([recordings].[dbo].[items].[item_desc],CONVERT_IMPLICIT(nvarchar(max),'',0))">
<Arithmetic Operation="ADD">
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1005" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Intrinsic FunctionName="isnull">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="item_desc" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Column="ConstExpr1003">
<ScalarOperator>
<Convert DataType="nvarchar(max)" Length="2147483647" Style="0" Implicit="true">
<ScalarOperator>
<Const ConstValue="''" />
</ScalarOperator>
</Convert>
</ScalarOperator>
</ColumnReference>
</Identifier>
</ScalarOperator>
</Intrinsic>
</ScalarOperator>
</Arithmetic>
</ScalarOperator>
</DefinedValue>
</DefinedValues>
<RelOp AvgRowSize="4517" EstimateCPU="1E-07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Compute Scalar" NodeId="1" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="0.0032832">
<OutputList>
<ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="net_code" />
<ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="year_num" />
<ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="item_num" />
<ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="broadcast_code" />
<ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="sort_order" />
<ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="item_title" />
<ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="item_desc" />
<ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="duration" />
<ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="update_date" />
<ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="rowguid" />
<ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="orig_program_code" />
<ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="item_archive_num" />
<ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="broadcast_archive_num" />
<ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="TC" />
<ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="lang_code" />
<ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="updated_by" />
<ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="orig_broadcast_date" />
<ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="duration_time" />
<ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="orig_TC" />
<ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="created_by" />
<ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="create_date" />
<ColumnReference Column="Expr1005" />
</OutputList>
<ComputeScalar>
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Expr1005" />
<ScalarOperator ScalarString="ltrim(isnull([recordings].[dbo].[items].[item_title],N'')+N' ')">
<Intrinsic FunctionName="ltrim">
<ScalarOperator>
<Arithmetic Operation="ADD">
<ScalarOperator>
<Intrinsic FunctionName="isnull">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="item_title" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="N''" />
</ScalarOperator>
</Intrinsic>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="N' '" />
</ScalarOperator>
</Arithmetic>
</ScalarOperator>
</Intrinsic>
</ScalarOperator>
</DefinedValue>
</DefinedValues>
<RelOp AvgRowSize="4314" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Clustered Index Seek" NodeId="2" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="1274590">
<OutputList>
<ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="net_code" />
<ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="year_num" />
<ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="item_num" />
<ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="broadcast_code" />
<ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="sort_order" />
<ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="item_title" />
<ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="item_desc" />
<ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="duration" />
<ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="update_date" />
<ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="rowguid" />
<ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="orig_program_code" />
<ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="item_archive_num" />
<ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="broadcast_archive_num" />
<ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="TC" />
<ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="lang_code" />
<ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="updated_by" />
<ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="orig_broadcast_date" />
<ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="duration_time" />
<ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="orig_TC" />
<ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="created_by" />
<ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="create_date" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="6258" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="net_code" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="year_num" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="item_num" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="broadcast_code" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="sort_order" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="item_title" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="item_desc" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="duration" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="update_date" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="rowguid" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="orig_program_code" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="item_archive_num" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="broadcast_archive_num" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="TC" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="lang_code" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="updated_by" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="orig_broadcast_date" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="duration_time" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="orig_TC" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="created_by" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="create_date" />
</DefinedValue>
</DefinedValues>
<Object Database="[recordings]" Schema="[dbo]" Table="[items]" Index="[PK_items]" IndexKind="Clustered" />
<SeekPredicates>
<SeekPredicateNew>
<SeekKeys>
<Prefix ScanType="EQ">
<RangeColumns>
<ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="net_code" />
<ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="year_num" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="[@1]">
<Identifier>
<ColumnReference Column="@1" />
</Identifier>
</ScalarOperator>
<ScalarOperator ScalarString="[@4]">
<Identifier>
<ColumnReference Column="@4" />
</Identifier>
</ScalarOperator>
</RangeExpressions>
</Prefix>
<StartRange ScanType="GE">
<RangeColumns>
<ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="item_num" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="CONVERT_IMPLICIT(int,[@2],0)">
<Identifier>
<ColumnReference Column="ConstExpr1004">
<ScalarOperator>
<Convert DataType="int" Style="0" Implicit="true">
<ScalarOperator>
<Identifier>
<ColumnReference Column="@2" />
</Identifier>
</ScalarOperator>
</Convert>
</ScalarOperator>
</ColumnReference>
</Identifier>
</ScalarOperator>
</RangeExpressions>
</StartRange>
<EndRange ScanType="LE">
<RangeColumns>
<ColumnReference Database="[recordings]" Schema="[dbo]" Table="[items]" Column="item_num" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="[@3]">
<Identifier>
<ColumnReference Column="@3" />
</Identifier>
</ScalarOperator>
</RangeExpressions>
</EndRange>
</SeekKeys>
</SeekPredicateNew>
</SeekPredicates>
</IndexScan>
</RelOp>
</ComputeScalar>
</RelOp>
</ComputeScalar>
</RelOp>
<ParameterList>
<ColumnReference Column="@4" ParameterCompiledValue="(1971)" ParameterRuntimeValue="(1971)" />
<ColumnReference Column="@3" ParameterCompiledValue="(99000)" ParameterRuntimeValue="(99000)" />
<ColumnReference Column="@2" ParameterCompiledValue="(1)" ParameterRuntimeValue="(1)" />
<ColumnReference Column="@1" ParameterCompiledValue="(1)" ParameterRuntimeValue="(1)" />
</ParameterList>
</QueryPlan>
</StmtSimple>
</Statements>
</Batch>
</BatchSequence>
</ShowPlanXML>

August 20th, 2015 4:55am

my version is:

I have already updated statistics and recompiled the query but to no avail

how can I get sql server to create a better estimate and hence a better QEP?

the shortened query:

select * FROM dbo.items
WHERE items.net_code=1
and dbo.items.item_num between 1 and 99000 and items.year_num=1971

the QEP (in XML)

Free Windows Admin Tool Kit Click here and download it now

August 20th, 2015 5:05am

It seems that query has been auto-parameterised. Do you have forced parameterisation in use in this database?

Then, the plan is compiled for the values in your post, so the parameterisation is not likely to cause the poor estimate.

Rather, I would guess that it is a matter of correlation. You have predicates on three columns. SQL Server have histogram for each of them, and for each column it can make a decently accurate estimate how many rows the condition on the single column matches. But it has no information of correlation or lack thereof. As I recall, it assumes no correlation at all, but the correlation may be negative. For instaance, most rows from 1971 may prove to have net_code = 8.

August 20th, 2015 5:49pm

the parameterization is simple.

the correlation would make sense if the estimate was too high, but the estimate is 1 instead of 6000+.

looking at the data 99% of the data matches all the condition 1971. the other 2 conditions removes a few rows each.

Free Windows Admin Tool Kit Click here and download it now

August 21st, 2015 2:12am

the correlation would make sense if the estimate was too high, but the estimate is 1 instead of 6000+.

Sorry, got lost in the details.

Then again, it strikes both ways. That is, if there is a high correlation between the search conditions, the estimate will be too low. Although, the exact estimate of 1 seems spooky. If you try one column at a time, what estimates do you see?

August 21st, 2015 3:24am

for only net=1 estimate 589000 actual 633000 seems ok

for item between 0 and 99000 estimate 821000 actual 797000

for year=1971 estimate 1.0038 actual 6276

with all 3 conditions estimate 1 actual 6258

Free Windows Admin Tool Kit Click here and download it now

August 21st, 2015 3:44am

for year=1971 estimate 1.0038 actual 6276

So that is the creepy one.

This query should give information about the year column:

SELECT s.*, stats_date(o.object_id, s.stats_id)
FROM   sys.stats s
JOIN   sys.objects o ON s.object_id = o.object_id
WHERE o.name = 'accountstats'
AND EXISTS (SELECT *
   FROM   sys.stats_columns sc
   JOIN   sys.columns c ON c.object_id = s.object_id
   AND c.column_id = sc.column_id
   WHERE sc.object_id = s.object_id
   AND sc.stats_id = s.stats_id
   AND c.name = 'accountdate'
   AND sc.stats_column_id = 1)
FROM   sys.stats

Particularly, check stats_date and no_recompute. If they look normal, use DBCC SHOW_STATISTICS to check the histogram.

August 21st, 2015 10:34am

I am not sure what to make of the results:

your query:

object_id name stats_id auto_created user_created no_recompute has_filter filter_definition stats_date
1281306612 _WA_Sys_00000002_4C5F2FF4 8 1 0 0 0 NULL 2015-08-20 11:27:20.447

dbcc show_statistics

RANGE_HI_KEY RANGE_ROWS EQ_ROWS DISTINCT_RANGE_ROWS AVG_RANGE_ROWS
0 0 511399.7 0 1
1900 0 858.7517 0 1
1942 0 1.00388 0 1
1952 0 442.3872 0 1
1953 0 6089.33 0 1
1955 0 442.3872 0 1
1956 0 102.5213 0 1
1957 0 41.44932 0 1
1958 0 442.3872 0 1
1959 0 468.41 0 1
1960 0 1.00388 0 1
1961 0 102.5213 0 1
1962 0 754.6606 0 1
1964 0 1.00388 0 1
1968 0 1145.002 0 1
1969 0 2055.8 0 1
1970 0 2602.278 0 1
1971 0 1.00388 0 1
1972 0 338.2961 0 1
1973 0 8041.039 0 1
1974 0 5829.103 0 1
1975 0 6375.581 0 1
1976 0 8249.221 0 1
1977 0 7130.241 0 1
1978 0 6349.558 0 1
1979 0 6193.421 0 1
1980 0 8561.494 0 1
1981 0 10357.07 0 1
1982 0 12673.09 0 1
1983 0 15665.71 0 1
1984 0 11762.3 0 1
1985 0 12673.09 0 1
1986 0 12933.32 0 1
1987 0 12855.25 0 1
1988 0 17175.03 0 1
1989 0 18189.92 0 1
1990 0 18996.63 0 1
1991 0 17643.44 0 1
1992 0 15145.26 0 1
1993 0 17070.94 0 1
1994 0 44498.95 0 1
1995 0 31461.54 0 1
1996 0 36015.53 0 1
1997 0 38799.96 0 1
1998 0 17669.47 0 1
1999 0 27636.19 0 1
2000 0 15119.23 0 1
2001 0 15275.37 0 1
2002 0 30082.33 0 1
2003 0 15509.58 0 1
2004 0 29093.47 0 1
2005 0 13141.5 0 1
2006 0 15119.23 0 1
2007 0 14937.08 0 1
2008 0 14494.69 0 1
2009 0 1092.957 0 1
2010 0 4267.736 0 1
2011 0 14963.1 0 1
2012 0 30264.49 0 1
2013 0 49104.98 0 1
2014 0 21989.25 0 1
2015 0 16888.78 0 1
2020 0 1.00388 0 1

Free Windows Admin Tool Kit Click here and download it now

August 23rd, 2015 2:54am

This topic is archived. No further replies will be accepted.