Some issues in query plan's interpretation

Question

Post reply

Some issues in query plan's interpretation

huhaoranxing

Valued Member

Points: 58
More actions
January 11, 2010 at 3:32 am

#214488

Hi there,
I'm researching the sql server query plan issues,but now get into trouble.As you know,the logical&physical operator "Assert" exists in sql server query plan usual with an argument,such as "Assert(WHERE : (If NOT(([b][size="3"]Expr1006[/size][/b] IS NULL)) then 0 else NULL))".
But What's the "Expr1006" in the argument represents for? Where exists the definition for it?
Thanks
May

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 1

It'll be defined earlier in the plan. There's probably a compute scalar operator somewhere.

If you want some details, post the plan (as a .sqlplan file, zipped) and we'll take a look.

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

huhaoranxing Valued Member Points: 58 More actions · Answer 2

Thanks for your reply.And My test environment is sql server 2000 and Northwind.Products Table. The sql statement and estimated query plan is shown below and a fully information available in the attachment query plan1.rar(may be opened be your browser) :

StmtText

delete from Products where ProductID = 1

|--Assert(WHERE: (If NOT(([Expr1006] IS NULL)) then 0 else NULL))

|--Nested Loops(Left Semi Join, OUTER REFERENCES: ([Products].[ProductID]), DEFINE: ([Expr1006] = [PROBE VALUE]))

|--Clustered Index Delete(OBJECT: ([Northwind].[dbo].[Products].[PK_Products]), WHERE: ([Products].[ProductID]=Convert([@1])))

|--Index Seek(OBJECT: ([Northwind].[dbo].[Order Details].[ProductsOrder_Details]), SEEK: ([Order Details].[ProductID]=[Products].[ProductID]) ORDERED FORWARD)

Thanks for your kindness

May

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 3

huhaoranxing (1/11/2010)
Thanks for your reply.And My test environment is sql server 2000 and Northwind.Products Table.

Then why is it posted in the SQL 2005 forum?

DEFINE: ([Expr1006] = [PROBE VALUE]))

There's the definition of the Expr. Unfortunately, the 2000 exec plan format is rather limited, so it's hard to see, from what you've posted, what the [probe value] really is. I'm guessing, on SQL 2005, there'd be more information either in the plan or in the properties

I would guess, looking at the rest of the plan, that the probe value is [Order Details].[ProductID], and that the Assert check is checking a foreign key constraint.

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

huhaoranxing Valued Member Points: 58 More actions · Answer 4

GilaMonster (1/12/2010)
huhaoranxing (1/11/2010)
Thanks for your reply.And My test environment is sql server 2000 and Northwind.Products Table.
Then why is it posted in the SQL 2005 forum?
DEFINE: ([Expr1006] = [PROBE VALUE]))
There's the definition of the Expr. Unfortunately, the 2000 exec plan format is rather limited, so it's hard to see, from what you've posted, what the [probe value] really is. I'm guessing, on SQL 2005, there'd be more information either in the plan or in the properties
I would guess, looking at the rest of the plan, that the probe value is [Order Details].[ProductID], and that the Assert check is checking a foreign key constraint.

Yeah,Thanks for your apply. And i agree with your guess.but it's only a guess,[Order Details].[ProductID] never appears in the plan. May sql 2005 give more info.

But I guess there should be a parse tree before generating the query plan.But the tree's format is a secret to us,do you known anything about it? If so,it would be helpful to fully understand the query plan.

Thanks

May

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 5

huhaoranxing (1/12/2010)
But I guess there should be a parse tree before generating the query plan.But the tree's format is a secret to us,do you known anything about it?

If it's secret, why would I know anything about it?

I've never seen an actual parse tree (though iirc there's something on it in one of Itzik's books - Inside SQL Server 2005 T-SQL Querying) , I've never heard of a way to get the SQL engine to return it for examination.

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

huhaoranxing Valued Member Points: 58 More actions · Answer 6

oh,yeah,i agree with the contrary,and you own a good sense of logical.

the parse tree may a new topic.

i'll try the query plan in sql server 2005 and then share it here.Thanks for your help!