Confused execution plan

Question

Post reply

Confused execution plan

Edward-445599

Hall of Fame

Points: 3147
More actions
May 9, 2011 at 5:31 am

#237114

Extremely confused I am doing a
select ID,primary key from tableA where ID =’123’
This was never a problem on compatibility mode 80 sql 2005 SP2 win2003
But now on
compatibility mode 80 sql 2005 SP4 win2008 it takes ages
What confuses me is
1)that it uses the non-clustered index with ID to do an index seek.(as expected)
2) But then it does an Index scan on the PK index?
Why would it do this when it has already used the ID index?
Many thanks
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

Please post the plan (actual plan, not estimated).

Are the index defragmented and the stats up to date?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Plus table and index definitions.

How many rows does that return?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Edward-445599 Hall of Fame Points: 3147 More actions · Answer 3

Just tested it in a SP2 enviroment and its much quicker

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 4

Edward-445599 (5/9/2011)
Just tested it in a SP2 enviroment and its much quicker

Awesome, post that execution plan too plz.

Edward-445599 Hall of Fame Points: 3147 More actions · Answer 5

Sorry about the delay here are the two plans first one good second one bad, the strange thing is that a few times when running this query in the SP4 environment (slow) it actually picked the right plan!! but 9 times out of 10 it didn't. I updated the stats on the table to no difference

--GOOD PLAN ON SP3

<?xml version="1.0" encoding="utf-16"?>

<Batch>

</OutputList>

</RunTimeInformation>

</OuterReferences>

</OutputList>

</RunTimeInformation>

</DefinedValue>

</DefinedValue>

</DefinedValue>

</DefinedValues>

</RangeColumns>

</ScalarOperator>

</RangeExpressions>

</Prefix>

</SeekPredicate>

</SeekPredicates>

</IndexScan>

</RelOp>

</OutputList>

</RunTimeInformation>

</DefinedValue>

</DefinedValues>

</RangeColumns>

</Identifier>

</ScalarOperator>

</Identifier>

</ScalarOperator>

</RangeExpressions>

</Prefix>

</SeekPredicate>

</SeekPredicates>

</IndexScan>

</RelOp>

</NestedLoops>

</RelOp>

</ParameterList>

</QueryPlan>

</StmtSimple>

</Statements>

</Batch>

</BatchSequence>

</ShowPlanXML>

--BAD PLAN SP4

<?xml version="1.0" encoding="utf-16"?>

<Batch>

</ColumnGroup>

</MissingIndex>

</MissingIndexGroup>

</MissingIndexes>

</OutputList>

</RunTimeInformation>

<Hash>

</HashKeysBuild>

</HashKeysProbe>

</Identifier>

</ScalarOperator>

</Identifier>

</ScalarOperator>

</Compare>

</ScalarOperator>

</Identifier>

</ScalarOperator>

</Identifier>

</ScalarOperator>

</Compare>

</ScalarOperator>

</Logical>

</ScalarOperator>

</ProbeResidual>

</OutputList>

</RunTimeInformation>

</DefinedValue>

</DefinedValue>

</DefinedValue>

</DefinedValues>

</RangeColumns>

</ScalarOperator>

</RangeExpressions>

</Prefix>

</SeekPredicate>

</SeekPredicates>

</IndexScan>

</RelOp>

</OutputList>

</RunTimeInformation>

</DefinedValue>

</DefinedValue>

</DefinedValue>

</DefinedValues>

</IndexScan>

</RelOp>

</Hash>

</RelOp>

</ParameterList>

</QueryPlan>

</StmtSimple>

</Statements>

</Batch>

</BatchSequence>

</ShowPlanXML>

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 6

I can't open those.

Can you save the plans as .sqlplan and upload those files?

TIA.

Edward-445599 Hall of Fame Points: 3147 More actions · Answer 7

files attached thanks!! didn't know I could attach files sorry

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 8

The estimates seem completely out of whack.

#1 - Do you have the same indexes in both environements?

#2 - Run update stats

Edward-445599 Hall of Fame Points: 3147 More actions · Answer 9

Ninja's_RGR'us (5/10/2011)
The estimates seem completely out of whack.
#1 - Do you have the same indexes in both environements?
#2 - Run update stats

Yes same indexes and i have run update stats on that table there was no differences , the bad trace is from after a stats update.

mister.magoo SSC-Forever Points: 47174 More actions · Answer 10

Can you post the table and index definitions?

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

Edward-445599 Hall of Fame Points: 3147 More actions · Answer 11

post the table? Afraid not there is sensitive information in it, what are you hoping to see from having the table would that help finding out why there is a difference between SP3 and SP4?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 12

Not the data. The definition - Create table, create index

May help to try and understand what the optimiser is thinking. It thinks that the useful index is missing, hard to say why without seeing what the table is defined as, and what indexes exist.

Is the table and index definitions identical on both servers? (can you maybe post the definitions from both, just in case there's something subtle different)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 13

Also maybe the output of DBCC SHOW_STATISTICS (TableA, IX_TableA_id) on both? Shouldn't be a problem with sensitive data, it's just the ID values.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Edward-445599 Hall of Fame Points: 3147 More actions · Answer 14

--Hope it's all there thanks for looking 🙂

--SP4 BAD

--Index

CIX_TableA_offerclustered located on PRIMARYoffer

IX_TableA_val3nonclustered located on PRIMARYval3

IX_TableA_val4nonclustered located on PRIMARYval4

IX_TableA_idnonclustered located on PRIMARYid

PK_TableAnonclustered, unique, primary key located on PRIMARYprimaryKey

--Table def

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [schemA].[TableA](

[primaryKey] [int] IDENTITY(1,1) NOT NULL,

[id] [varchar](15) NULL,

[val1] [varchar](15) NULL,

[val2] [varchar](15) NULL,

[val3] [varchar](20) NULL,

[val4] [varchar](20) NULL,

[val5] [tinyint] NULL,

[val6] [varchar](20) NULL,

[val7] [varchar](50) NULL,

[val8] [varchar](500) NULL,

[val9] [tinyint] NULL,

CONSTRAINT [PK_TableA] PRIMARY KEY NONCLUSTERED

(

[primaryKey] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

--table stats

IX_TableA_idMay 11 2011 12:29AM314984882425471930.801755111.44844YES

--SP3 GOOD

--Index

CIX_TableA_offerclustered located on PRIMARYoffer

IX_TableA_val3nonclustered located on PRIMARYval3

IX_TableA_val4nonclustered located on PRIMARYval4

IX_TableA_idnonclustered located on PRIMARYid

PK_TableAnonclustered, unique, primary key located on PRIMARYprimaryKey

--Table def

ET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [schemA].[TableA](

[primaryKey] [int] IDENTITY(1,1) NOT NULL,

[id] [varchar](15) NULL,

[val1] [varchar](15) NULL,

[val2] [varchar](15) NULL,

[val3] [varchar](20) NULL,

[val4] [varchar](20) NULL,

[val5] [tinyint] NULL,

[val6] [varchar](20) NULL,

[val7] [varchar](50) NULL,

[val8] [varchar](500) NULL,

[val9] [tinyint] NULL,

CONSTRAINT [PK_TableA] PRIMARY KEY NONCLUSTERED

(

[primaryKey] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

--table stats

IX_TableA_idMay 11 2011 12:49AM323764452525901890.797369711.46685YES