locked
How to estimate Spatial index size and table size? RRS feed

  • Question


  • I found that on SQL Server 2008 Books Online sp_spaceused (http://msdn.microsoft.com/en-us/library/ms188776.aspx)
    it said "If space usage is calculated for a database or an object that has a spatial index, the space-size columns, such as database_size, reserved, and index_size, include the size of the spatial index. "

    The description about the index size is not right according to my experience. The spatial index size is much bigger than the index size returned by sp_spaceused in my case.

    Spatial index size: 264.5KB
    Index_size (sp_spaceused): 16KB

    I am wondering how I estimate and calculate the spatial index on geometry column.
    Based on the SQL Server 2008 Books Online - Estimating the Size of a Table(http://msdn.microsoft.com/en-us/library/ms175991.aspx), nothing related to spatial index or geometry column are included in the calcuation. Should I simply add the size of heap/size of clusterred index, the size of nonclustered index and the size of spatial index together as the total size of a table? Thanks a lot.

    I created scripts as blow to generate the index size:

    CREATE TABLE TestPoints(
        [STUDY_ID] [numeric](12, 0) IDENTITY (1,1) NOT NULL,
        [OBJECTID] [int] NOT NULL,
        [STUDY_NAME] [nvarchar](64) NOT NULL,
        [ANCHOR_POINT] [geometry] NULL,
        [CREATED_DATE] [datetime2](7) NOT NULL,
        [UPDATED_DATE] [datetime2](7) NOT NULL,
        [STUDY_TYPE_ID] [numeric](2, 0) NOT NULL,
        [USER_ID] [numeric](12, 0) NOT NULL,
        [STUDY_DESCRIPTION] [nvarchar](256) NULL,
        [STUDY_CATEGORY_ID] [numeric](6, 0) NOT NULL,
     CONSTRAINT [TestPoints_PK] PRIMARY KEY CLUSTERED
    (
        [STUDY_ID] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]

    GO


    /* Create spatial index */
    CREATE SPATIAL INDEX [TestPoints_ANCHOR_POINT_SIDX] ON [TestPoints]
    (
        [ANCHOR_POINT]
    )USING  GEOMETRY_GRID
    WITH (
    BOUNDING_BOX =(-400, -400, 400, 400), GRIDS =(LEVEL_1 = MEDIUM,LEVEL_2 = MEDIUM,LEVEL_3 = MEDIUM,LEVEL_4 = MEDIUM),
    CELLS_PER_OBJECT = 16, PAD_INDEX  = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    GO


    /* Insert 10K Points */
    DECLARE @rownum int

    set @rownum = 1

    WHILE @rownum < = 10000
    begin
        insert into TestPoints
        (ObjectID,STUDY_NAME,ANCHOR_POINT,CREATED_DATE,UPDATED_DATE,USER_ID,STUDY_TYPE_ID,STUDY_DESCRIPTION,STUDY_CATEGORY_ID)
        VALUES (1,'My First Study',geometry:Tongue TiedTPointFromText('POINT (-117.1889 34.0552)',18),GETDATE(),GETDATE(),1,5,'A user creates a description and it goes here.',1);
       
        set @rownum=@rownum+1
    end


    /* excute stored procedure sp_used to get table size */
    exec sp_spaceused 'TestPoints'

    result:

    name              rows        reserved           data               index_size         unused
    ---------------------------------------------------------------------------------------------------
    TestPoints      10000       2184 KB            2112 KB            16 KB               0 KB


    /* Calculate Spatial Index Size */
     DECLARE @index_id INT
     SELECT @index_id = index_id
     FROM sys.indexes
     WHERE object_id = OBJECT_ID('TestPoints') AND name = 'TestPoints_ANCHOR_POINT_SIDX'
     
     SELECT sum(avg_record_size_in_bytes*record_count)
     FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID('TestPoints'), @index_id , NULL, 'DETAILED')

    Spatial index size:
    270860.976 Bytes = 264.5KB


    Friday, September 12, 2008 1:24 AM

Answers

  • Wei,

     

    Thanks for pointing out this issue. This is indeed a bug in sp_spaceused that is causing the spatial index's size to not be reflected in the output. The script you mention would, however, be a good way to estimate index's size. Thanks again,

     

    Rajneesh.

     

    Friday, September 19, 2008 11:37 PM
    Answerer

All replies

  • Hi Wei,

    I don't have an answer for you, but I wanted to let you know that our team is taking a look into this.  Stay tuned.

    Cheers,
    -Isaac
    Tuesday, September 16, 2008 2:20 PM
  • Wei,

     

    Thanks for pointing out this issue. This is indeed a bug in sp_spaceused that is causing the spatial index's size to not be reflected in the output. The script you mention would, however, be a good way to estimate index's size. Thanks again,

     

    Rajneesh.

     

    Friday, September 19, 2008 11:37 PM
    Answerer