SQL Server Query Plans, Elapsed Time is Lower, but Subtree Cost is Higher -
why elapsed execution time of first of 2 queries below lower, while estimated subtree cost higher?
estimated subtree cost guideline gauging query plan performance in conjunction other query performance indicators, response time, i'm surprised existance of inverse relationship between cost , time when comparing 2 similar queries side side.
i've created example database illustrate problem , included resulting xml query plans both queries. realize existence of stateid foreign key in both county , town tables bad form, easiest way me illustrate phenomenon.
i'm not asking "which more important, subtree cost or response time." want understand how inverse relationship can exist between query plan subtree cost , response time 2 similar queries, can make more informed decisions going forward.
please note 2 queries below produce exact same result set.
thanks!
create table dbo.county (stateid int, countyid int) create table dbo.town (stateid int, countyid int, townid int) declare @state table (stateid int, primary key clustered (stateid)) declare @county table (countyid int, primary key clustered (countyid)) insert @state values (27) insert @county select distinct c.countyid dbo.county c join @state s on c.stateid = s.stateid -- low time, high cost query select distinct t.countyid, t.townid dbo.town t join @state s on t.stateid = s.stateid -- low cost, high time query select distinct t.countyid, t.townid dbo.town t join @state s on t.stateid = s.stateid join @county c on t.countyid = c.countyid the following query plan query had estimated subtree cost of 6.06 , elapsed time of 1114 milliseconds.
<stmtsimple statementcompid="6" statementestrows="273495" statementid="3" statementoptmlevel="full" statementsubtreecost="6.06304" statementtext="select distinct
	t.countyid,
	t.townid
from dbo.town t
join @state s
	on t.stateid = s.stateid

-- low cost, high time query
" statementtype="select" queryhash="0x9347c19165c31dbf" queryplanhash="0x98ee57c66d8b347a"> <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="2" memorygrant="28144" cachedplansize="32" compiletime="8" compilecpu="8" compilememory="312"> <relop avgrowsize="15" estimatecpu="0.388488" estimateio="0" estimaterebinds="0" estimaterewinds="0" estimaterows="273495" logicalop="gather streams" nodeid="0" parallel="true" physicalop="parallelism" estimatedtotalsubtreecost="6.06304"> <outputlist> <columnreference database="[locality]" schema="[dbo]" table="[town]" alias="[t]" column="countyid" /> <columnreference database="[locality]" schema="[dbo]" table="[town]" alias="[t]" column="townid" /> </outputlist> <runtimeinformation> <runtimecountersperthread thread="0" actualrows="13027" actualendofscans="1" actualexecutions="1" /> </runtimeinformation> <parallelism> <relop avgrowsize="15" estimatecpu="3.79725" estimateio="0" estimaterebinds="0" estimaterewinds="0" estimaterows="273495" logicalop="aggregate" nodeid="1" parallel="true" physicalop="hash match" estimatedtotalsubtreecost="5.67455"> <outputlist> <columnreference database="[locality]" schema="[dbo]" table="[town]" alias="[t]" column="countyid" /> <columnreference database="[locality]" schema="[dbo]" table="[town]" alias="[t]" column="townid" /> </outputlist> <memoryfractions input="0.998723" output="1" /> <runtimeinformation> <runtimecountersperthread thread="2" actualrows="6515" actualendofscans="1" actualexecutions="1" /> <runtimecountersperthread thread="1" actualrows="6512" actualendofscans="1" actualexecutions="1" /> <runtimecountersperthread thread="0" actualrows="0" actualendofscans="0" actualexecutions="0" /> </runtimeinformation> <hash> <definedtowns /> <hashkeysbuild> <columnreference database="[locality]" schema="[dbo]" table="[town]" alias="[t]" column="countyid" /> <columnreference database="[locality]" schema="[dbo]" table="[town]" alias="[t]" column="townid" /> </hashkeysbuild> <buildresidual> <scalaroperator scalarstring="[locality].[dbo].[town].[countyid] [t].[countyid] = [locality].[dbo].[town].[countyid] [t].[countyid] , [locality].[dbo].[town].[townid] [t].[townid] = [locality].[dbo].[town].[townid] [t].[townid]"> <logical operation="and"> <scalaroperator> <compare compareop="is"> <scalaroperator> <identifier> <columnreference database="[locality]" schema="[dbo]" table="[town]" alias="[t]" column="countyid" /> </identifier> </scalaroperator> <scalaroperator> <identifier> <columnreference database="[locality]" schema="[dbo]" table="[town]" alias="[t]" column="countyid" /> </identifier> </scalaroperator> </compare> </scalaroperator> <scalaroperator> <compare compareop="is"> <scalaroperator> <identifier> <columnreference database="[locality]" schema="[dbo]" table="[town]" alias="[t]" column="townid" /> </identifier> </scalaroperator> <scalaroperator> <identifier> <columnreference database="[locality]" schema="[dbo]" table="[town]" alias="[t]" column="townid" /> </identifier> </scalaroperator> </compare> </scalaroperator> </logical> </scalaroperator> </buildresidual> <relop avgrowsize="15" estimatecpu="0.494228" estimateio="0" estimaterebinds="0" estimaterewinds="0" estimaterows="273495" logicalop="repartition streams" nodeid="2" parallel="true" physicalop="parallelism" estimatedtotalsubtreecost="1.8773"> <outputlist> <columnreference database="[locality]" schema="[dbo]" table="[town]" alias="[t]" column="countyid" /> <columnreference database="[locality]" schema="[dbo]" table="[town]" alias="[t]" column="townid" /> </outputlist> <runtimeinformation> <runtimecountersperthread thread="2" actualrows="1017328" actualendofscans="1" actualexecutions="1" /> <runtimecountersperthread thread="1" actualrows="1093191" actualendofscans="1" actualexecutions="1" /> <runtimecountersperthread thread="0" actualrows="0" actualendofscans="0" actualexecutions="0" /> </runtimeinformation> <parallelism partitioningtype="hash"> <partitioncolumns> <columnreference database="[locality]" schema="[dbo]" table="[town]" alias="[t]" column="countyid" /> <columnreference database="[locality]" schema="[dbo]" table="[town]" alias="[t]" column="townid" /> </partitioncolumns> <relop avgrowsize="15" estimatecpu="0.571604" estimateio="0" estimaterebinds="0" estimaterewinds="0" estimaterows="273495" logicalop="inner join" nodeid="3" parallel="true" physicalop="nested loops" estimatedtotalsubtreecost="1.38307"> <outputlist> <columnreference database="[locality]" schema="[dbo]" table="[town]" alias="[t]" column="countyid" /> <columnreference database="[locality]" schema="[dbo]" table="[town]" alias="[t]" column="townid" /> </outputlist> <runtimeinformation> <runtimecountersperthread thread="2" actualrows="0" actualendofscans="1" actualexecutions="1" /> <runtimecountersperthread thread="1" actualrows="2110519" actualendofscans="1" actualexecutions="1" /> <runtimecountersperthread thread="0" actualrows="0" actualendofscans="0" actualexecutions="0" /> </runtimeinformation> <nestedloops optimized="true"> <outerreferences> <columnreference table="@state" alias="[s]" column="stateid" /> </outerreferences> <relop avgrowsize="11" estimatecpu="0.0285019" estimateio="0" estimaterebinds="0" estimaterewinds="0" estimaterows="1" logicalop="distribute streams" nodeid="5" parallel="true" physicalop="parallelism" estimatedtotalsubtreecost="0.031785"> <outputlist> <columnreference table="@state" alias="[s]" column="stateid" /> </outputlist> <runtimeinformation> <runtimecountersperthread thread="2" actualrows="0" actualendofscans="1" actualexecutions="1" /> <runtimecountersperthread thread="1" actualrows="1" actualendofscans="1" actualexecutions="1" /> <runtimecountersperthread thread="0" actualrows="0" actualendofscans="0" actualexecutions="0" /> </runtimeinformation> <parallelism partitioningtype="roundrobin"> <relop avgrowsize="11" estimatecpu="0.0001581" estimateio="0.003125" estimaterebinds="0" estimaterewinds="0" estimaterows="1" logicalop="clustered index scan" nodeid="6" parallel="false" physicalop="clustered index scan" estimatedtotalsubtreecost="0.0032831" tablecardinality="0"> <outputlist> <columnreference table="@state" alias="[s]" column="stateid" /> </outputlist> <runtimeinformation> <runtimecountersperthread thread="1" actualrows="1" actualendofscans="1" actualexecutions="1" /> <runtimecountersperthread thread="0" actualrows="0" actualendofscans="0" actualexecutions="0" /> </runtimeinformation> <indexscan ordered="false" forcedindex="false" noexpandhint="false"> <definedtowns> <definedtown> <columnreference table="@state" alias="[s]" column="stateid" /> </definedtown> </definedtowns> <object table="[@state]" index="[pk__#3ab788a__c3ba3b5a3c9fd11a]" alias="[s]" /> </indexscan> </relop> </parallelism> </relop> <relop avgrowsize="15" estimatecpu="0.301001" estimateio="0.478681" estimaterebinds="0" estimaterewinds="0" estimaterows="273495" logicalop="index seek" nodeid="7" parallel="true" physicalop="index seek" estimatedtotalsubtreecost="0.779682" tablecardinality="6291320"> <outputlist> <columnreference database="[locality]" schema="[dbo]" table="[town]" alias="[t]" column="countyid" /> <columnreference database="[locality]" schema="[dbo]" table="[town]" alias="[t]" column="townid" /> </outputlist> <runtimeinformation> <runtimecountersperthread thread="2" actualrows="0" actualendofscans="0" actualexecutions="0" /> <runtimecountersperthread thread="1" actualrows="2110519" actualendofscans="1" actualexecutions="1" /> <runtimecountersperthread thread="0" actualrows="0" actualendofscans="0" actualexecutions="0" /> </runtimeinformation> <indexscan ordered="true" scandirection="forward" forcedindex="false" forceseek="false" noexpandhint="false"> <definedtowns> <definedtown> <columnreference database="[locality]" schema="[dbo]" table="[town]" alias="[t]" column="countyid" /> </definedtown> <definedtown> <columnreference database="[locality]" schema="[dbo]" table="[town]" alias="[t]" column="townid" /> </definedtown> </definedtowns> <object database="[locality]" schema="[dbo]" table="[town]" index="[ncx_town_stateid_countyid_townid]" alias="[t]" indexkind="nonclustered" /> <seekpredicates> <seekpredicatenew> <seekkeys> <prefix scantype="eq"> <rangecolumns> <columnreference database="[locality]" schema="[dbo]" table="[town]" alias="[t]" column="stateid" /> </rangecolumns> <rangeexpressions> <scalaroperator scalarstring="@state.[stateid] [s].[stateid]"> <identifier> <columnreference table="@state" alias="[s]" column="stateid" /> </identifier> </scalaroperator> </rangeexpressions> </prefix> </seekkeys> </seekpredicatenew> </seekpredicates> </indexscan> </relop> </nestedloops> </relop> </parallelism> </relop> </hash> </relop> </parallelism> </relop> </queryplan> </stmtsimple> the following query plan query had estimated subtree cost of 0.14 , elapsed time of 4614 milliseconds.
<stmtsimple statementcompid="7" statementestrows="339.324" statementid="4" statementoptmlevel="full" statementoptmearlyabortreason="goodenoughplanfound" statementsubtreecost="0.142839" statementtext="select distinct
	t.countyid,
	t.townid
from dbo.town t
join @state s
	on t.stateid = s.stateid
join @county c
	on t.countyid = c.countyid

" statementtype="select" queryhash="0xf7b13fa8059b141c" queryplanhash="0x31cee9daf12e77a5"> <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" memorygrant="1584" cachedplansize="40" compiletime="5" compilecpu="5" compilememory="376"> <relop avgrowsize="15" estimatecpu="0.00454996" estimateio="0.0112613" estimaterebinds="0" estimaterewinds="0" estimaterows="339.324" logicalop="distinct sort" nodeid="0" parallel="false" physicalop="sort" estimatedtotalsubtreecost="0.142839"> <outputlist> <columnreference database="[locality]" schema="[dbo]" table="[town]" alias="[t]" column="countyid" /> <columnreference database="[locality]" schema="[dbo]" table="[town]" alias="[t]" column="townid" /> </outputlist> <memoryfractions input="1" output="1" /> <runtimeinformation> <runtimecountersperthread thread="0" actualrebinds="1" actualrewinds="0" actualrows="13027" actualendofscans="1" actualexecutions="1" /> </runtimeinformation> <sort distinct="true"> <orderby> <orderbycolumn ascending="true"> <columnreference database="[locality]" schema="[dbo]" table="[town]" alias="[t]" column="countyid" /> </orderbycolumn> <orderbycolumn ascending="true"> <columnreference database="[locality]" schema="[dbo]" table="[town]" alias="[t]" column="townid" /> </orderbycolumn> </orderby> <relop avgrowsize="15" estimatecpu="0.0571741" estimateio="0" estimaterebinds="0" estimaterewinds="0" estimaterows="339.324" logicalop="inner join" nodeid="1" parallel="false" physicalop="hash match" estimatedtotalsubtreecost="0.127028"> <outputlist> <columnreference database="[locality]" schema="[dbo]" table="[town]" alias="[t]" column="countyid" /> <columnreference database="[locality]" schema="[dbo]" table="[town]" alias="[t]" column="townid" /> </outputlist> <memoryfractions input="0" output="0" /> <runtimeinformation> <runtimecountersperthread thread="0" actualrows="2110519" actualendofscans="1" actualexecutions="1" /> </runtimeinformation> <hash> <definedtowns /> <hashkeysbuild> <columnreference table="@state" alias="[s]" column="stateid" /> </hashkeysbuild> <hashkeysprobe> <columnreference database="[locality]" schema="[dbo]" table="[town]" alias="[t]" column="stateid" /> </hashkeysprobe> <relop avgrowsize="11" estimatecpu="0.0001581" estimateio="0.003125" estimaterebinds="0" estimaterewinds="0" estimaterows="1" logicalop="clustered index scan" nodeid="2" parallel="false" physicalop="clustered index scan" estimatedtotalsubtreecost="0.0032831" tablecardinality="0"> <outputlist> <columnreference table="@state" alias="[s]" column="stateid" /> </outputlist> <runtimeinformation> <runtimecountersperthread thread="0" actualrows="1" actualendofscans="1" actualexecutions="1" /> </runtimeinformation> <indexscan ordered="false" forcedindex="false" noexpandhint="false"> <definedtowns> <definedtown> <columnreference table="@state" alias="[s]" column="stateid" /> </definedtown> </definedtowns> <object table="[@state]" index="[pk__#3ab788a__c3ba3b5a3c9fd11a]" alias="[s]" /> </indexscan> </relop> <relop avgrowsize="19" estimatecpu="0.0357739" estimateio="0" estimaterebinds="0" estimaterewinds="0" estimaterows="8558.34" logicalop="inner join" nodeid="3" parallel="false" physicalop="nested loops" estimatedtotalsubtreecost="0.066568"> <outputlist> <columnreference database="[locality]" schema="[dbo]" table="[town]" alias="[t]" column="stateid" /> <columnreference database="[locality]" schema="[dbo]" table="[town]" alias="[t]" column="countyid" /> <columnreference database="[locality]" schema="[dbo]" table="[town]" alias="[t]" column="townid" /> </outputlist> <runtimeinformation> <runtimecountersperthread thread="0" actualrows="2110519" actualendofscans="1" actualexecutions="1" /> </runtimeinformation> <nestedloops optimized="false"> <outerreferences> <columnreference table="@county" alias="[c]" column="countyid" /> </outerreferences> <relop avgrowsize="11" estimatecpu="0.0001581" estimateio="0.003125" estimaterebinds="0" estimaterewinds="0" estimaterows="1" logicalop="clustered index scan" nodeid="4" parallel="false" physicalop="clustered index scan" estimatedtotalsubtreecost="0.0032831" tablecardinality="0"> <outputlist> <columnreference table="@county" alias="[c]" column="countyid" /> </outputlist> <runtimeinformation> <runtimecountersperthread thread="0" actualrows="137" actualendofscans="1" actualexecutions="1" /> </runtimeinformation> <indexscan ordered="false" forcedindex="false" noexpandhint="false"> <definedtowns> <definedtown> <columnreference table="@county" alias="[c]" column="countyid" /> </definedtown> </definedtowns> <object table="[@county]" index="[pk__#3e88198__b68f9df7407061fe]" alias="[c]" /> </indexscan> </relop> <relop avgrowsize="19" estimatecpu="0.00957118" estimateio="0.0179398" estimaterebinds="0" estimaterewinds="0" estimaterows="8558.34" logicalop="index seek" nodeid="5" parallel="false" physicalop="index seek" estimatedtotalsubtreecost="0.027511" tablecardinality="6291320"> <outputlist> <columnreference database="[locality]" schema="[dbo]" table="[town]" alias="[t]" column="stateid" /> <columnreference database="[locality]" schema="[dbo]" table="[town]" alias="[t]" column="countyid" /> <columnreference database="[locality]" schema="[dbo]" table="[town]" alias="[t]" column="townid" /> </outputlist> <runtimeinformation> <runtimecountersperthread thread="0" actualrows="2110519" actualendofscans="137" actualexecutions="137" /> </runtimeinformation> <indexscan ordered="true" scandirection="forward" forcedindex="false" forceseek="false" noexpandhint="false"> <definedtowns> <definedtown> <columnreference database="[locality]" schema="[dbo]" table="[town]" alias="[t]" column="stateid" /> </definedtown> <definedtown> <columnreference database="[locality]" schema="[dbo]" table="[town]" alias="[t]" column="countyid" /> </definedtown> <definedtown> <columnreference database="[locality]" schema="[dbo]" table="[town]" alias="[t]" column="townid" /> </definedtown> </definedtowns> <object database="[locality]" schema="[dbo]" table="[town]" index="[ncx_town_countyid_inc_stateid_townid]" alias="[t]" indexkind="nonclustered" /> <seekpredicates> <seekpredicatenew> <seekkeys> <prefix scantype="eq"> <rangecolumns> <columnreference database="[locality]" schema="[dbo]" table="[town]" alias="[t]" column="countyid" /> </rangecolumns> <rangeexpressions> <scalaroperator scalarstring="@county.[countyid] [c].[countyid]"> <identifier> <columnreference table="@county" alias="[c]" column="countyid" /> </identifier> </scalaroperator> </rangeexpressions> </prefix> </seekkeys> </seekpredicatenew> </seekpredicates> </indexscan> </relop> </nestedloops> </relop> </hash> </relop> </sort> </relop> </queryplan> </stmtsimple>
while haven't looked query plans, have advice , suggested explanation. advice simple: not use table variables tables more 2 orders of magnitude of rows (100 rows). possible explanation performance can poor above , query plans generated can inaccurate or inefficient, per microsoft's article on table data types:
best practices
do not use table variables store large amounts of data (more 100 rows). plan choices may not optimal or stable when table variable contains large amount of data. consider rewriting such queries use temporary tables or use use plan query hint ensure optimizer uses existing query plan works scenario.
and:
limitations , restrictions
table variables not supported in sql server optimizer's cost-based reasoning model. therefore, should not used when cost-based choices required achieve efficient query plan. temporary tables preferred when cost-based choices required. typically includes queries joins, parallelism decisions, , index selection choices.
queries modify table variables not generate parallel query execution plans. performance can affected when large table variables, or table variables in complex queries, modified. in these situations, consider using temporary tables instead. more information, see create table (transact-sql). queries read table variables without modifying them can still parallelized.
...
in practice, i've found using table variables precludes generation of parallel query plan, , hurts performance simplest, least complex table variables.
instead, find temporary tables more performant in every case, , long careful clean after in timely manner (or use short-lived sessions allow server clear temporary tables) vastly better performance.
as why second query plan has lower cost higher execution time? it's there many, many counties (1000s? 10000s?) , query plan being generated assuming there small number (100s). query optimizer thinks @county have less 100 rows, , thinks result set specific, because list of towns joined 1 list of <100 entries , joined second list of <100 entries is, well, have lot fewer 100 rows.
in reality, every town has county, join isn't selective , poor choices types of joins used. in each case, nested loop used last join, nested loop join extremely inefficient large, sorted data. , crux of issue.
rerun queries using temporary tables instead of table variables , guessing different query plans different performance characteristics.
Comments
Post a Comment