Estimated Row Size + BBB

Question

Estimated Row Size + BBB

karthik M

One Orange Chip

Points: 29139
More actions
May 25, 2010 at 6:20 am

#219352

All,
I have seen some values like 28 B / 83 B under 'Estimated Row Size' in the execution plan.
I am not getting this 'B'. It stands for what?
Reason behind this question:
Have a table with IDENTITY column. It has created with Clustered index.
So I created the table structure with new index for other useful columns.
Create table a
(
ID INT identity primary key,
IsActive char(1),
IsDeleted char(1),
Strategy varchar(100),
AssetType_Code int NULL
)
insert into a
select 'Y','N','A1_Fund',NULL
union all
select 'Y','N','A2_Fund',NULL
union all
select 'Y','N','A3_Fund',NULL
union all
select 'Y','N','A1_Fund',NULL
union all
select 'Y','N','A2_Fund',NULL
union all
select 'Y','N','A2_Fund',NULL
union all
select 'Y','N','A2_Fund',NULL
union all
select 'Y','N','A1_Fund',NULL
union all
select 'Y','N','A1_Fund',NULL
union all
select 'Y','N','A3_Fund',NULL
union all
select 'Y','N','A1_Fund',NULL
union all
select 'Y','N','A3_Fund',NULL
SELECT ID
FROM a
WHERE Strategy = 'A3_Fund'
AND AssetType_Code IS NULL
AND IsActive = 'Y'
AND IsDeleted = 'N'
You can see
1) 22 b under 'Estimated Row Size'.
2) Clustered index scan
alter table a
drop constraint PK__a__48F164BD
create unique clustered index id on a(Strategy,AssetType_Code,ID)
You can see
1) 13 b under 'Estimated Row Size'.
2) Clustered index seek
Is my thought to recreate the index is correct?
Did I select the correct combination?
what is mean 13 B & 22 B?
karthik

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

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

karthik M One Orange Chip Points: 29139 More actions · Answer 1

I have more than 250 tables in my database. i just want to prepare the list where as the table has primary key only on IDENTITY column.

how?

karthik

spaghettidba SSC Guru Points: 105732 More actions · Answer 2

karthikeyan-444867 (5/25/2010)
I have more than 250 tables in my database. i just want to prepare the list where as the table has primary key only on IDENTITY column.
how?

COLUMNPROPERTY(table_id, column_name, 'IsIdentity')

-- Gianluca Sartori

spaghettidba SSC Guru Points: 105732 More actions · Answer 3

SELECT *

FROM INFORMATION_SCHEMA.TABLES AS T

WHERE TABLE_TYPE = 'BASE TABLE'

AND EXISTS (

SELECT *

FROM INFORMATION_SCHEMA.COLUMNS AS C

WHERE C.TABLE_CATALOG = T.TABLE_CATALOG

AND C.TABLE_SCHEMA = T.TABLE_SCHEMA

AND C.TABLE_NAME = T.TABLE_NAME

AND COLUMNPROPERTY(

OBJECT_ID(QUOTENAME(C.TABLE_CATALOG) + '.' + QUOTENAME(C.TABLE_SCHEMA) + '.' + QUOTENAME(C.TABLE_NAME))

,COLUMN_NAME

, 'IsIdentity') = 1

AND NOT EXISTS (

SELECT *

FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS K

WHERE K.TABLE_CATALOG = T.TABLE_CATALOG

AND K.TABLE_SCHEMA = T.TABLE_SCHEMA

AND K.TABLE_NAME = T.TABLE_NAME

AND K.COLUMN_NAME <> C.COLUMN_NAME

)

Note that this brings in unique constraints also.

Hope this helps

-- Gianluca Sartori

karthik M One Orange Chip Points: 29139 More actions · Answer 4

karthik M

One Orange Chip

Points: 29139

May 26, 2010 at 9:23 am

#1172821

Thanks a lot GianLuca!

B stands for ?

karthik

spaghettidba SSC Guru Points: 105732 More actions · Answer 5

Bananas. :hehe:

Stands for bytes, obviously! Can you figure out any other unit to measure row size?

BOL states:

The estimated size of the row produced by the operator (bytes).

At first I thought that BOL could have omitted this info (one could guess easily), but, surprisingly enough, Google gives out BOL as first result for "estimated row size execution plan".

Once again, no effort on your part.

Your signature sets a quite high expectation: I think you should change it or try to measure up with it.

-- Gianluca Sartori

tosscrosby SSCoach Points: 18044 More actions · Answer 6

Gianluca Sartori (5/26/2010)
Bananas. :hehe:
Stands for bytes, obviously! Can you figure out any other unit to measure row size?

I though it stood for baud, as the rate of speed with which it'll return the data after issuing a select! 😀

-- You can't be late until you show up.

TomThomson SSC Guru Points: 104768 More actions · Answer 7

tosscrosby (5/26/2010)
Gianluca Sartori (5/26/2010)
Bananas. :hehe:
Stands for bytes, obviously! Can you figure out any other unit to measure row size?
I though it stood for baud, as the rate of speed with which it'll return the data after issuing a select! 😀

Query: how much of that is intentionally crazy? My sarcasm meter seems to be broken today! :unsure:

Tom

tosscrosby SSCoach Points: 18044 More actions · Answer 8

Tom.Thomson (5/26/2010)
tosscrosby (5/26/2010)
Gianluca Sartori (5/26/2010)
Bananas. :hehe:
Stands for bytes, obviously! Can you figure out any other unit to measure row size?
I though it stood for baud, as the rate of speed with which it'll return the data after issuing a select! 😀
Query: how much of that is intentionally crazy? My sarcasm meter seems to be broken today! :unsure:

All of it - I was in that mood yesterday - and as it seems, so was Gianluca.

-- You can't be late until you show up.