Different Index size value for Indexed View

• Mahesh Bote

  SSCrazy Eights

  Points: 9107

  More actions

  January 23, 2019 at 8:00 am

  #381258

  Hi,

  I have one indexed view. when I checked it through SP_SPACEUSED and DMVs, it gave me different Index size values.
  when I used sp_spaceused 'vw_Intraday_Indexed', it gave me below O/P

  name    rows    reserved    data    index_size    unused
  vw_Intraday_indexed    48600262        18511936 KB    18235376 KB    276416 KB    144 KB

  -- Find Index size with it's usage
  SELECT OBJECT_NAME(S.[OBJECT_ID]) AS [OBJECT NAME],
     I.[NAME] AS [INDEX NAME],
    
       Convert(Numeric(10, 2), ((8 * SUM(a.used_pages)) / 1024.00)) AS 'Indexsize(MB)'     
  FROM SYS.DM_DB_INDEX_USAGE_STATS AS S
     INNER JOIN SYS.INDEXES AS I ON I.[OBJECT_ID] = S.[OBJECT_ID] AND I.INDEX_ID = S.INDEX_ID
          JOIN sys.partitions AS p ON p.OBJECT_ID = i.OBJECT_ID AND p.index_id = i.index_id
          JOIN sys.allocation_units AS a ON a.container_id = p.partition_id
  WHERE I.Object_ID = Object_ID('db.vw_Intraday_indexed')
     AND S.database_id = DB_ID()
  group by OBJECT_NAME(S.[OBJECT_ID]) ,
     I.[NAME]

  And when I used above query, it gave me below O/p

  OBJECT NAME    INDEX NAME    Indexsize(MB)
  vw_Intraday_indexed    vw_UI_Otc_Intraday_indexed_idx    18077.92

  so why does the SP_SPACEUSED sproc is given different size value for Index_size?

  Please advice.

  Thanks in advance,

  MH-09-AM-8694

• Jeff Moden

  SSC Guru

  Points: 1004340

  More actions

  January 25, 2019 at 5:09 am

  #2020084

  Mahesh Bote - Wednesday, January 23, 2019 8:00 AM

  Hi,

  I have one indexed view. when I checked it through SP_SPACEUSED and DMVs, it gave me different Index size values.
  when I used sp_spaceused 'vw_Intraday_Indexed', it gave me below O/P

  name    rows    reserved    data    index_size    unused
  vw_Intraday_indexed    48600262        18511936 KB    18235376 KB    276416 KB    144 KB

  -- Find Index size with it's usage
  SELECT OBJECT_NAME(S.[OBJECT_ID]) AS [OBJECT NAME],
     I.[NAME] AS [INDEX NAME],
    
       Convert(Numeric(10, 2), ((8 * SUM(a.used_pages)) / 1024.00)) AS 'Indexsize(MB)'     
  FROM SYS.DM_DB_INDEX_USAGE_STATS AS S
     INNER JOIN SYS.INDEXES AS I ON I.[OBJECT_ID] = S.[OBJECT_ID] AND I.INDEX_ID = S.INDEX_ID
          JOIN sys.partitions AS p ON p.OBJECT_ID = i.OBJECT_ID AND p.index_id = i.index_id
          JOIN sys.allocation_units AS a ON a.container_id = p.partition_id
  WHERE I.Object_ID = Object_ID('db.vw_Intraday_indexed')
     AND S.database_id = DB_ID()
  group by OBJECT_NAME(S.[OBJECT_ID]) ,
     I.[NAME]

  And when I used above query, it gave me below O/p

  OBJECT NAME    INDEX NAME    Indexsize(MB)
  vw_Intraday_indexed    vw_UI_Otc_Intraday_indexed_idx    18077.92

  so why does the SP_SPACEUSED sproc is given different size value for Index_size?

  Please advice.

  Thanks in advance,

  It's easy.  Look at the code for sp_SpaceUsed and see how they calculate it.  Then look at the reports for the instance and see that those are different, as well.  Like so many companies, many different people wrote the code and they didn't all do it the same way nor did they necessarily include the same things.

  I'll also tell you that there are precisely 128 pages per MB so you can simplify the formula for counting MB based on pages as #ofPages/128.0.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)