How to make your SQL Server database grow exponentially with linear data
From: Pieter Myburgh (pieterm_at_korbitec.com)
Date: 04/23/04
- Next message: Daniel Reber: "Re: SET QUERY_GOVERNOR_COST_LIMIT"
- Previous message: Greg: "Linked Server: Network Access"
- Messages sorted by: [ date ] [ thread ]
Date: Fri, 23 Apr 2004 14:48:25 +0200
We have this database that we use to collect 1000's of rows of data a day.
Particularly in one table we collect about 1000 pieces of data a day in the
form of very small blobs (64k or so). Our database has been growing
steadily from Nov 2003 with an initial size of 7gb to about 25gb on 10 April
2004. Not bad, seeing the amount of data we collect. Then from 10 April to
today (23 April) it grew to 65gb in about 2 weeks. What's going on? Did
our input data increase exponentially? No, according to our logs data have
been arriving in a linear fashion. Maybe there's about 40gb of unused space
in the database? No, after we rebuild the indexes (using dbcc updateusage)
SQL Server reports no free space. Not possible you say?
Well, turns out the problem are with the blob fields. Because we want to
minimize the time it takes to search through the table, we wrote a service
that takes 2 rows, adds the blobs together, then updates the one row with
the resulting blob and deletes the other row. The service in other words
doubles up the blobs. The resulting blobs are also doubled up and so forth.
This in itself should not be a problem because theoretically the new row
should take up the same space (or even slightly less) than the original 2
rows together. Wrong! What happens is when a blob gets bigger than 64k,
SQL Server does not store the blob data with the row anymore, but moves it
to a separate location. Now when you double up a blob and try to update the
row SQL Server realizes it can't store the blob in the same space as the
previous one, so it allocates new space for it. So even though you aren't
adding any new data to the database, the database file actually grows
exponentially! Now some smaller blobs will eventually take up the old space
of a bigger blob, but they are much lower on the exponential curve than the
bigger blobs, so in total your database file grows near exponential with
linear data. This can be quite a problem when your blob size gets to about
50mb or so, and is the cause of our database growing so quickly in such a
short time.
Also, what worsens the problem is that it seems that SQL Server does not
take these deallocated blob space into account when it calculates "unused"
space in the database. Now if we deleted both rows in a double-up operation
and inserted a new row we probably would have been able to shrink the
database periodically. But there does not seem to be anything we can do to
reclaim deallocated blob space Our aim is to stop the blobs growing when
they reach a certain size and archive them off. Has anybody experienced the
same problem, or are there any suggestions as to how we can reclaim (or
avoid) this "unused" space?
Thanks,
Pieter
- Next message: Daniel Reber: "Re: SET QUERY_GOVERNOR_COST_LIMIT"
- Previous message: Greg: "Linked Server: Network Access"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
