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

• karthik M

  One Orange Chip

  Points: 29139

  More actions

  May 25, 2010 at 6:22 am

  #1172008

  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

  May 25, 2010 at 6:27 am

  #1172009

  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

  May 25, 2010 at 6:41 am

  #1172020

  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

  May 26, 2010 at 9:23 am

  #1172821

  Thanks a lot GianLuca!

  B stands for ?

  karthik

• spaghettidba

  SSC Guru

  Points: 105732

  More actions

  May 26, 2010 at 10:06 am

  #1172859

  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

  May 26, 2010 at 11:42 am

  #1172919

  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

  May 26, 2010 at 2:27 pm

  #1173017

  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

  May 27, 2010 at 6:29 am

  #1173273

  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.