lunes, 23 de abril de 2012 18:44
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.
Todas las respuestas
lunes, 23 de abril de 2012 21:11Usuario que responde
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:
Can also you confirm this isn't just transaction log space?
DBCC CLEANTABLE ( yourDb, 'dbo.ShipmentHistory' ) GO -- Check the space of the table
EXEC sp_spaceused 'dbo.ShipmentHistory', true GO
- Marcado como respuesta CDavidOrr lunes, 23 de abril de 2012 21:17
lunes, 23 de abril de 2012 21:17
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.
lunes, 23 de abril de 2012 21:36Usuario que responde
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.
lunes, 23 de abril de 2012 22:08
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.