Actual XML data type size?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Pedja - 29 Aug 2008 18:10 GMT

Hi,

We have large partitioned table which has 14 partitions. Among other
columns, it has XML data type column. We have noticed that recent partitions
grew up significatnly, regardless of the fact that the number of rows
remained approximately the same (comparing to the old partitions).

What could be the reason? One thing that crossed my mind is that recent rows
might have larger xml data. How can we verify that?

It is sql server 2005, service pack 2.

Thanks,
Pedja

Reply to this Message

Dan Guzman - 30 Aug 2008 00:38 GMT

> What could be the reason? One thing that crossed my mind is that recent
> rows
> might have larger xml data. How can we verify that?

One method to check the actual xml column space usage by partition:

SELECT
$PARTITION.PF_PartitionFunction(PartitionKey).
SUM(CAST(DATALENGTH(xml_column) AS bigint)) AS xml_column_length,
COUNT_BIG(*) AS row_count
FROM dbo.MyPartitionedTable
GROUP BY
$PARTITION.PF_PartitionFunction(PartitionKey)

If you don't want to scan the entire table, you can instead specify a range
sampling of the desired partition in the WHERE clause and run the query
multiple times.

Signature

Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

> Hi,
>
[quoted text clipped - 12 lines]
> Thanks,
> Pedja

Reply to this Message

Pedja - 30 Aug 2008 02:37 GMT

Thanks

> > What could be the reason? One thing that crossed my mind is that recent
> > rows
[quoted text clipped - 30 lines]
> > Thanks,
> > Pedja

Reply to this Message