mardi 2 décembre 2014

Why could a SQL Server table grow far quicker than the data to it?


I've got an audit table in SQL Server 2008 R2 that has the following structure:



CREATE TABLE [AuditInstance](
[oid] [int] NOT NULL,
[AuditTransactionOID] [int] NULL,
[pid] [int] NOT NULL,
[tid] [int] NOT NULL,
[ObjectKey] [nvarchar](20) NULL,
[ObjectType] [nvarchar](40) NULL,
[ObjectName] [nvarchar](255) NULL,
[Operation] [nvarchar](20) NULL,
[AuditData] [nvarchar](max) NULL,
PRIMARY KEY CLUSTERED ([oid] ASC)


About 3 weeks ago it had 106,168K rows and a data size of 115GB (reserved size 121GB). Today it has 106,917K rows (less than 750K more) and consumes 287GB (with 294GB reserved). Each of those values are taken from SQL Server's "Disk Usage by Top Tables" report and so does not include the non-clustered indexes which have barely grown in this time. I calculate that the average growth per row for that period to be 230kB but the average size of the AuditData field for the same period is:



select avg(datalength(AuditData))/1024.0 as avgSizeInkB from AuditInstance
where oid < 107006115 and oid > 106006115

avgSizeInkB
---------------------------------------
0.530273


Even if all of the the other fields were full to capacity (which they're not) this should only add another 662 bytes per row for a total of about 1.2kB. Can anyone explain why the table seems to be growing almost 200 times quicker than this? (PAD_INDEX is off and no FILLFACTOR is set so I don't believe it relates to that, although I'd assume that would show in the reserved size rather than data size anyway).


Further info:


The results of:



SELECT type_desc, total_pages, used_pages,data_pages
FROM sys.allocation_units
WHERE container_id in (SELECT partition_id FROM sys.partitions
WHERE OBJECT_ID = OBJECT_ID('AuditInstance'))

type_desc total_pages used_pages data_pages
IN_ROW_DATA 185863 185806 185109
IN_ROW_DATA 8826738 8824615 8801687
LOB_DATA 27101311 27098965 0
ROW_OVERFLOW_DATA 0 0 0
IN_ROW_DATA 583149 581879 575613


suggests that it's mainly LOB_DATA.





Aucun commentaire:

Enregistrer un commentaire