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

> 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.

--
Hope this s.

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

"Pedja" <Pedja@discussions.microsoft.com> wrote in message
news:15D63E54-D851-4C1D-BC99-ECC9A1081863@microsoft.com...
> 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

Thanks

"Dan Guzman" wrote:

> > 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.
>
> --
> Hope this s.
>
> Dan Guzman
> SQL Server MVP
> http://weblogs.sqlteam.com/dang/
>
> "Pedja" <Pedja@discussions.microsoft.com> wrote in message
> news:15D63E54-D851-4C1D-BC99-ECC9A1081863@microsoft.com...
> > 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
>
>