none
Database size dramatically increases with the addition of an XML Primary Index RRS feed

  • Question

  • Hi All,

    I am new to working with XML in SQL and have experienced something that just doesn't seem right.

    I have a table defined as:

    CREATE TABLE [dbo].[ShipmentHistory](
    	[ShipmentHistoryID] [int] IDENTITY(1,1) NOT NULL,
    	[ShipmentID] [int] NOT NULL,
    	[DateUpdated] [datetime] NOT NULL,
    	[UpdatedBy] [varchar](50) NOT NULL,
    	[ShipmentXML] [xml] NOT NULL,
     CONSTRAINT [PK_ShipmentHistory] PRIMARY KEY CLUSTERED 
    (
    	[ShipmentHistoryID] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    

    This table has about 500,000 rows in it. The summed length of the ShipmentXML column for all rows is about 1.3 Gb.  The XML on average has about 200 nodes, none of which are more than 3 levels deep.

    When I add a primary xml index onto this table, the size of our database goes from about 54 Gb to over 100 Gb. I know that the XML index takes quite a bit of space but this seemed excessive.

    Can anyone offer any insight on this?  Does this seem reasonable?

    Any input is appreciated.

    Thanks,

    David

    Monday, April 23, 2012 6:44 PM

Answers

  • Yes this is a bit of a feature of XML indexes.  I've heard figures of between 2-6 times space to store XML.

    I have seen space swell when converting between typed and untyped XML which can be reclaimed using DBCC CLEANTABLE but don't think this applies to untyped XML:

    DBCC CLEANTABLE ( yourDb, 'dbo.ShipmentHistory' ) GO -- Check the space of the table

    EXEC sp_spaceused 'dbo.ShipmentHistory', true GO

    Can also you confirm this isn't just transaction log space?
    • Marked as answer by CDavidOrr Monday, April 23, 2012 9:17 PM
    Monday, April 23, 2012 9:11 PM
    Answerer

All replies

  • Yes this is a bit of a feature of XML indexes.  I've heard figures of between 2-6 times space to store XML.

    I have seen space swell when converting between typed and untyped XML which can be reclaimed using DBCC CLEANTABLE but don't think this applies to untyped XML:

    DBCC CLEANTABLE ( yourDb, 'dbo.ShipmentHistory' ) GO -- Check the space of the table

    EXEC sp_spaceused 'dbo.ShipmentHistory', true GO

    Can also you confirm this isn't just transaction log space?
    • Marked as answer by CDavidOrr Monday, April 23, 2012 9:17 PM
    Monday, April 23, 2012 9:11 PM
    Answerer
  • wBob,

    Thanks for responding.  I was not aware of the sp_spaceused procedure.  When I ran it the results show the table taking just under 15 gb of space.  So the increase of about 47 gb of data for the index is consistent with what you stated and what I have read; that the index takes about 3 times the space.  I guess I just didn't realize that table was taking that much space.

    Also, it is not transaction log space.  This is the size of the .mdf file only.

    Thanks again and this clears it up for me.

    David

    Monday, April 23, 2012 9:17 PM
  • You're getting a great performance boost for your XML queries though right?  XML indexes can transform queries using the XML datatype and methods ( .value, .query, .exist, .modify and .nodes ) but it is a trade-off with the space.

    Monday, April 23, 2012 9:36 PM
    Answerer
  • Well, because of the dramatic increase in size I was unable to leave the index in place.  So, unfortunately, I didn't get a chance to test it with the index in place.  I need to do some rearranging to accomodate this and then I will be able to test it.

    Thanks again for your help.

    Monday, April 23, 2012 10:08 PM