SQLTeam.com | Weblogs | Forums

Database Design SQL server

Hmm. I guess convert the xml to nvarchar(max) text and post the text?!

Yes how about I just post the xml script instead of the file ?

If that works, sure.

> <ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.2" Build="12.0.2269.0"><BatchSequence><Batch><Statements><StmtSimple StatementText="-- Execute a query. &#xd;&#xa;SELECT *&#xd;&#xa;FROM dbo.A1 a INNER JOIN dbo.H1 h ON a.d = h.d and a.Name = h.Name&#xd;&#xa;INNER JOIN dbo.R1 r ON a.d = r.d and a.Name = r.Name&#xd;&#xa;WHERE a.d = &apos;2015-01-01&apos; and a.name = &apos;Record&apos; and r.prcd = &apos;am1&apos;" StatementId="1" StatementCompId="1" StatementType="SELECT" RetrievedFromCache="false" StatementSubTreeCost="0.389348" StatementEstRows="5974.29" StatementOptmLevel="FULL" QueryHash="0x89F1808A23F1CC54" QueryPlanHash="0x8C0A689EB0A2BE60" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" CardinalityEstimationModelVersion="120"><StatementSetOptions QUOTED_IDENTIFIER="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false"/><QueryPlan CachedPlanSize="32" CompileTime="4" CompileCPU="4" CompileMemory="520"><MissingIndexes><MissingIndexGroup Impact="84.4107"><MissingIndex Database="[fbH]" Schema="[dbo]" Table="[A1]"><ColumnGroup Usage="EQUALITY"><Column Name="[d]" ColumnId="1"/><Column Name="[name]" ColumnId="2"/></ColumnGroup></MissingIndex></MissingIndexGroup></MissingIndexes><MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0"/><OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="416492" EstimatedPagesCached="104123" EstimatedAvailableDegreeOfParallelism="2"/><RelOp NodeId="0" PhysicalOp="Nested Loops" LogicalOp="Inner Join" EstimateRows="5974.29" EstimateIO="0" EstimateCPU="0.0249725" AvgRowSize="96" EstimatedTotalSubtreeCost="0.389348" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fbH]" Schema="[dbo]" Table="[A1]" Alias="[a]" Column="d"/><ColumnReference Database="[fbH]" Schema="[dbo]" Table="[A1]" Alias="[a]" Column="name"/><ColumnReference Database="[fbH]" Schema="[dbo]" Table="[A1]" Alias="[a]" Column="vol"/><ColumnReference Database="[fbH]" Schema="[dbo]" Table="[H1]" Alias="[h]" Column="d"/><ColumnReference Database="[fbH]" Schema="[dbo]" Table="[H1]" Alias="[h]" Column="Name"/><ColumnReference Database="[fbH]" Schema="[dbo]" Table="[H1]" Alias="[h]" Column="smallName"/><ColumnReference Database="[fbH]" Schema="[dbo]" Table="[H1]" Alias="[h]" Column="number"/><ColumnReference Database="[fbH]" Schema="[dbo]" Table="[R1]" Alias="[r]" Column="d"/><ColumnReference Database="[fbH]" Schema="[dbo]" Table="[R1]" Alias="[r]" Column="Name"/><ColumnReference Database="[fbH]" Schema="[dbo]" Table="[R1]" Alias="[r]" Column="prcd"/><ColumnReference Database="[fbH]" Schema="[dbo]" Table="[R1]" Alias="[r]" Column="p1"/><ColumnReference Database="[fbH]" Schema="[dbo]" Table="[R1]" Alias="[r]" Column="p2"/></OutputList><Warnings NoJoinPredicate="1"/><NestedLoops Optimized="0"><RelOp NodeId="1" PhysicalOp="Nested Loops" LogicalOp="Inner Join" EstimateRows="128.754" EstimateIO="0" EstimateCPU="0.000538192" AvgRowSize="53" EstimatedTotalSubtreeCost="0.337091" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fbH]" Schema="[dbo]" Table="[A1]" Alias="[a]" Column="d"/><ColumnReference Database="[fbH]" Schema="[dbo]" Table="[A1]" Alias="[a]" Column="name"/><ColumnReference Database="[fbH]" Schema="[dbo]" Table="[A1]" Alias="[a]" Column="vol"/><ColumnReference Database="[fbH]" Schema="[dbo]" Table="[R1]" Alias="[r]" Column="d"/><ColumnReference Database="[fbH]" Schema="[dbo]" Table="[R1]" Alias="[r]" Column="Name"/><ColumnReference Database="[fbH]" Schema="[dbo]" Table="[R1]" Alias="[r]" Column="prcd"/><ColumnReference Database="[fbH]" Schema="[dbo]" Table="[R1]" Alias="[r]" Column="p1"/><ColumnReference Database="[fbH]" Schema="[dbo]" Table="[R1]" Alias="[r]" Column="p2"/></OutputList><Warnings NoJoinPredicate="1"/><NestedLoops Optimized="0"><RelOp NodeId="2" PhysicalOp="Table Scan" LogicalOp="Table Scan" EstimateRows="4.89898" EstimateIO="0.203866" EstimateCPU="0.0715426" AvgRowSize="29" EstimatedTotalSubtreeCost="0.275408" TableCardinality="64896" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fbH]" Schema="[dbo]" Table="[A1]" Alias="[a]" Column="d"/><ColumnReference Database="[fbH]" Schema="[dbo]" Table="[A1]" Alias="[a]" Column="name"/><ColumnReference Database="[fbH]" Schema="[dbo]" Table="[A1]" Alias="[a]" Column="vol"/></OutputList><TableScan Ordered="0" ForcedIndex="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[fbH]" Schema="[dbo]" Table="[A1]" Alias="[a]" Column="d"/></DefinedValue><DefinedValue><ColumnReference Database="[fbH]" Schema="[dbo]" Table="[A1]" Alias="[a]" Column="name"/></DefinedValue><DefinedValue><ColumnReference Database="[fbH]" Schema="[dbo]" Table="[A1]" Alias="[a]" Column="vol"/></DefinedValue></DefinedValues><Object Database="[fbH]" Schema="[dbo]" Table="[A1]" Alias="[a]" IndexKind="Heap" Storage="RowStore"/><Predicate><ScalarOperator ScalarString="[fbH].[dbo].[A1].[d] as [a].[d]=&apos;2015-01-01&apos; AND [fbH].[dbo].[A1].[name] as [a].[name]=&apos;Record&apos;"><Logical Operation="AND"><ScalarOperator><Compare CompareOp="EQ"><ScalarOperator><Identifier><ColumnReference Database="[fbH]" Schema="[dbo]" Table="[A1]" Alias="[a]" Column="d"/></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="&apos;2015-01-01&apos;"/></ScalarOperator></Compare></ScalarOperator><ScalarOperator><Compare CompareOp="EQ"><ScalarOperator><Identifier><ColumnReference Database="[fbH]" Schema="[dbo]" Table="[A1]" Alias="[a]" Column="name"/></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="&apos;Record&apos;"/></ScalarOperator></Compare></ScalarOperator></Logical></ScalarOperator></Predicate></TableScan></RelOp><RelOp NodeId="3" PhysicalOp="Clustered Index Seek" LogicalOp="Clustered Index Seek" EstimateRows="26.2818" EstimateIO="0.003125" EstimateCPU="0.00018591" AvgRowSize="33" EstimatedTotalSubtreeCost="0.00403577" TableCardinality="519168" Parallel="0" EstimateRebinds="0" EstimateRewinds="3.89898" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fbH]" Schema="[dbo]" Table="[R1]" Alias="[r]" Column="d"/><ColumnReference Database="[fbH]" Schema="[dbo]" Table="[R1]" Alias="[r]" Column="Name"/><ColumnReference Database="[fbH]" Schema="[dbo]" Table="[R1]" Alias="[r]" Column="prcd"/><ColumnReference Database="[fbH]" Schema="[dbo]" Table="[R1]" Alias="[r]" Column="p1"/><ColumnReference Database="[fbH]" Schema="[dbo]" Table="[R1]" Alias="[r]" Column="p2"/></OutputList><IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[fbH]" Schema="[dbo]" Table="[R1]" Alias="[r]" Column="d"/></DefinedValue><DefinedValue><ColumnReference Database="[fbH]" Schema="[dbo]" Table="[R1]" Alias="[r]" Column="Name"/></DefinedValue><DefinedValue><ColumnReference Database="[fbH]" Schema="[dbo]" Table="[R1]" Alias="[r]" Column="prcd"/></DefinedValue><DefinedValue><ColumnReference Database="[fbH]" Schema="[dbo]" Table="[R1]" Alias="[r]" Column="p1"/></DefinedValue><DefinedValue><ColumnReference Database="[fbH]" Schema="[dbo]" Table="[R1]" Alias="[r]" Column="p2"/></DefinedValue></DefinedValues><Object Database="[fbH]" Schema="[dbo]" Table="[R1]" Index="[rIDX]" Alias="[r]" IndexKind="Clustered" Storage="RowStore"/><SeekPredicates><SeekPredicateNew><SeekKeys><Prefix ScanType="EQ"><RangeColumns><ColumnReference Database="[fbH]" Schema="[dbo]" Table="[R1]" Alias="[r]" Column="d"/><ColumnReference Database="[fbH]" Schema="[dbo]" Table="[R1]" Alias="[r]" Column="Name"/><ColumnReference Database="[fbH]" Schema="[dbo]" Table="[R1]" Alias="[r]" Column="prcd"/></RangeColumns><RangeExpressions><ScalarOperator ScalarString="&apos;2015-01-01&apos;"><Const ConstValue="&apos;2015-01-01&apos;"/></ScalarOperator><ScalarOperator ScalarString="&apos;Record&apos;"><Const ConstValue="&apos;Record&apos;"/></ScalarOperator><ScalarOperator ScalarString="&apos;am1&apos;"><Const ConstValue="&apos;am1&apos;"/></ScalarOperator></RangeExpressions></Prefix></SeekKeys></SeekPredicateNew></SeekPredicates></IndexScan></RelOp></NestedLoops></RelOp><RelOp NodeId="4" PhysicalOp="Table Spool" LogicalOp="Lazy Spool" EstimateRows="46.4008" EstimateIO="0.01" EstimateCPU="0.000108752" AvgRowSize="51" EstimatedTotalSubtreeCost="0.0272842" Parallel="0" EstimateRebinds="0" EstimateRewinds="127.754" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fbH]" Schema="[dbo]" Table="[H1]" Alias="[h]" Column="d"/><ColumnReference Database="[fbH]" Schema="[dbo]" Table="[H1]" Alias="[h]" Column="Name"/><ColumnReference Database="[fbH]" Schema="[dbo]" Table="[H1]" Alias="[h]" Column="smallName"/><ColumnReference Database="[fbH]" Schema="[dbo]" Table="[H1]" Alias="[h]" Column="number"/></OutputList><Spool><RelOp NodeId="5" PhysicalOp="Clustered Index Seek" LogicalOp="Clustered Index Seek" EstimateRows="46.4008" EstimateIO="0.003125" EstimateCPU="0.000208041" AvgRowSize="51" EstimatedTotalSubtreeCost="0.00333304" TableCardinality="477744" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fbH]" Schema="[dbo]" Table="[H1]" Alias="[h]" Column="d"/><ColumnReference Database="[fbH]" Schema="[dbo]" Table="[H1]" Alias="[h]" Column="Name"/><ColumnReference Database="[fbH]" Schema="[dbo]" Table="[H1]" Alias="[h]" Column="smallName"/><ColumnReference Database="[fbH]" Schema="[dbo]" Table="[H1]" Alias="[h]" Column="number"/></OutputList><IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[fbH]" Schema="[dbo]" Table="[H1]" Alias="[h]" Column="d"/></DefinedValue><DefinedValue><ColumnReference Database="[fbH]" Schema="[dbo]" Table="[H1]" Alias="[h]" Column="Name"/></DefinedValue><DefinedValue><ColumnReference Database="[fbH]" Schema="[dbo]" Table="[H1]" Alias="[h]" Column="smallName"/></DefinedValue><DefinedValue><ColumnReference Database="[fbH]" Schema="[dbo]" Table="[H1]" Alias="[h]" Column="number"/></DefinedValue></DefinedValues><Object Database="[fbH]" Schema="[dbo]" Table="[H1]" Index="[hIDX]" Alias="[h]" IndexKind="Clustered" Storage="RowStore"/><SeekPredicates><SeekPredicateNew><SeekKeys><Prefix ScanType="EQ"><RangeColumns><ColumnReference Database="[fbH]" Schema="[dbo]" Table="[H1]" Alias="[h]" Column="d"/><ColumnReference Database="[fbH]" Schema="[dbo]" Table="[H1]" Alias="[h]" Column="Name"/></RangeColumns><RangeExpressions><ScalarOperator ScalarString="&apos;2015-01-01&apos;"><Const ConstValue="&apos;2015-01-01&apos;"/></ScalarOperator><ScalarOperator ScalarString="&apos;Record&apos;"><Const ConstValue="&apos;Record&apos;"/></ScalarOperator></RangeExpressions></Prefix></SeekKeys></SeekPredicateNew></SeekPredicates></IndexScan></RelOp></Spool></RelOp></NestedLoops></RelOp></QueryPlan></StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML>

are you there ?

Hi Scott,

Thanks for sharing with me your skills. I think you are great and hope we can engage more into this.

Sorry, been very busy. I couldn't get the thing to load as xml; I'm not at all any type of expert on XML, just on data base design and coding.

It’s Ok, I still value your skills and engaging with you. I still think you are great, and admire you. Can you look at the image I had in my previous post rather than the xml codes. Take your time, I am not in a hurry or rush. If this is end of this topic is Ok too. I guess we can speak about other topics that are similar when I or you post again. Thx

Copy the xml above to notepad and then save as johnse.sqlplan then following below

  1. In SQL Server Management Studio, on the File menu, choose Open , and then click File .
  2. In the Open File dialog box, set Files of type to Execution Plan Files (*.sqlplan) to produce a filtered list of saved XML query plan files.
  3. Select the XML query plan file that you want to view, and click Open .As an alternative, in Windows Explorer, double-click a file with extension .sqlplan . The plan opens in Management Studio.

But isn't something missing from the file above? It starts with a ">", not an XML "identifier line".

You might have created that part, but I don't know how to do that. Or can the xml start at "<ShowPlanXML ...": I didn't think that was valid, but maybe it is?!

yes clean that first ">" and you should be good.

I thought that <?xml...?> was required? If not, I'll try that. Again, xml NOT my area of expertise.

OK, got it. That's an estimated plan only, so it doesn't show the actual row counts.

If there are a lot of rows in table "A1"/"a", then force hash joins rather than a loop join with a seek for every row:

SELECT *
FROM dbo.A1 a
INNER HASH JOIN dbo.H1 h ON a.d = h.d and a.Name = h.Name
INNER HASH JOIN dbo.R1 r ON a.d = r.d and a.Name = r.Name
WHERE a.d = '2015-01-01' and a.name = 'Record' and r.prcd = 'am1'

If that performs noticeably better, it's safe to leave the hint in. A HASH join won't be disastrous with just a few rows, but with a huge number of rows a loop join can perform horribly.

xml is ubiquitous, worth adding that to your cap. it is honestly very simple.

JSON is preferred in our shop. It's whatever you prefer, really.

1 Like

For what it's worth, for some reason the developers in our shop hate xml and, thus not surprisingly, strongly prefer JSON (pronounced "jason" now, apparently).

I frankly don't care either way, that's not my area of expertise nor concern.

1 Like

We use both. At the end of the day it's all strings to me

OP / @Johnse:

Did the HASH join hint work for you? Or does something still need done?

Let me check and get back to you.

Unfortunately I tried logging onto my SQL server but is having some issues. Not sure why, working on the access then I could let you know when I do. Might end up needing to reinstall Mgment Studio unless someone know how I can fix it.