How to calculate database Rate of Change?

I need to determine the rate of change (ROC) in bytes/day for my production
databases. You might ask why I'm doing this. We have a new NetApp SAN, and it
has the ability to perform SQL Server snapshots for amazingly fast short-term
backup and recovery. In order to implement this, however, I need to size the
volumes appropriately and not waste a lot of space. The formula I have been
provided is this:
Space for Snapshot copies = ROC in bytes per day * number of Snapshot copies
Does anybody have any ideas as to how I might calculate ROC? I think using
DCM (database change maps) would be perfect if I could somehow obtain that
information through a fairly simple sql query. Otherwise I thought of using
tran log backup sizes, but I'm not sure how accurately that reflects actual
data change in the databases.
Any help is appreciated.
Thanks,
Aaron Sentell
Sr. DBA
Compass Group USA
It depends in they increment multiple times for the same data. If not, you
could create a database snapshot. Average the size for several days to get a
good number. However, I would throughly test when it comes to relying on a
3rd party product for database recovery.
Jason Massie
Web: http://statisticsio.com
RSS: http://feeds.feedburner.com/statisticsio
"Aaron Sentell" <Aaron Sentell@.discussions.microsoft.com> wrote in message
news:4A5BA321-B82C-4F41-A94D-5F2B5B816600@.microsoft.com...
>I need to determine the rate of change (ROC) in bytes/day for my production
> databases. You might ask why I'm doing this. We have a new NetApp SAN, and
> it
> has the ability to perform SQL Server snapshots for amazingly fast
> short-term
> backup and recovery. In order to implement this, however, I need to size
> the
> volumes appropriately and not waste a lot of space. The formula I have
> been
> provided is this:
> Space for Snapshot copies = ROC in bytes per day * number of Snapshot
> copies
> Does anybody have any ideas as to how I might calculate ROC? I think using
> DCM (database change maps) would be perfect if I could somehow obtain that
> information through a fairly simple sql query. Otherwise I thought of
> using
> tran log backup sizes, but I'm not sure how accurately that reflects
> actual
> data change in the databases.
> Any help is appreciated.
> Thanks,
> Aaron Sentell
> Sr. DBA
> Compass Group USA
>
|||I don't believe that for sql server ROC will be useful for properly
determining space needed for snapshots. Supposes you change one byte in one
row of one table and everything else is the same. What is key here for
snapshot size is how much physical disk space the snapshot mechanism marks
off as changed. I assure you it isn't 1 byte. Probably more like 8-64K or
possibly even more. Now, suppose you change 1 byte on every physically
minimum structure used in snapshotting. You may have changed say 100000
bytes but have to save off 6GB worth of space as changed. At least that is
my understanding.
To answer your question however, you can examine the output of sp_spaceused.
This is not guaranteed to be accurate however unless you first run dbcc
updateusage (which can be intensive on the server). On 2005 you can add up
pages on several DMVs related to storage (such as
sys.dm_db_index_physical_stats). Search the web for several canned queries
for this.
Tran log backups are not exactly equal to actual data change but can be a
gross estimate. The issue here is that the same data can get updated over
and over which will be logged entries but not necessarily be more physical
"ROC".
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
kgboles a earthlink dt net
"Aaron Sentell" <Aaron Sentell@.discussions.microsoft.com> wrote in message
news:4A5BA321-B82C-4F41-A94D-5F2B5B816600@.microsoft.com...
>I need to determine the rate of change (ROC) in bytes/day for my production
> databases. You might ask why I'm doing this. We have a new NetApp SAN, and
> it
> has the ability to perform SQL Server snapshots for amazingly fast
> short-term
> backup and recovery. In order to implement this, however, I need to size
> the
> volumes appropriately and not waste a lot of space. The formula I have
> been
> provided is this:
> Space for Snapshot copies = ROC in bytes per day * number of Snapshot
> copies
> Does anybody have any ideas as to how I might calculate ROC? I think using
> DCM (database change maps) would be perfect if I could somehow obtain that
> information through a fairly simple sql query. Otherwise I thought of
> using
> tran log backup sizes, but I'm not sure how accurately that reflects
> actual
> data change in the databases.
> Any help is appreciated.
> Thanks,
> Aaron Sentell
> Sr. DBA
> Compass Group USA
>
|||Aaron,
An extra thought. Assuming that you are running differential backups is to
calculate from the size of the differentials. A differential marks an 8
page extent (64 KB) for backing up whenever anything changes on a page in
that extent. If the SAN is also snapshotting at that granularity, you might
get an idea.
Of course, once an extent is marked, it continues in the differential bit
map only once, although it may be updated every day. Perhaps you could get
the measure of the day, by doing something like the following for a little
while:
11:00 PM - Differential backup
12:00 AM - Full backup
That way, every 11:00 PM differential would only be one day's worth of
changes. You would still need some way of estimating the velocity of
changes per extent, but you might be able to examine your work load and make
a semi-educated guess.
FWIW,
RLF
"Aaron Sentell" <Aaron Sentell@.discussions.microsoft.com> wrote in message
news:4A5BA321-B82C-4F41-A94D-5F2B5B816600@.microsoft.com...
>I need to determine the rate of change (ROC) in bytes/day for my production
> databases. You might ask why I'm doing this. We have a new NetApp SAN, and
> it
> has the ability to perform SQL Server snapshots for amazingly fast
> short-term
> backup and recovery. In order to implement this, however, I need to size
> the
> volumes appropriately and not waste a lot of space. The formula I have
> been
> provided is this:
> Space for Snapshot copies = ROC in bytes per day * number of Snapshot
> copies
> Does anybody have any ideas as to how I might calculate ROC? I think using
> DCM (database change maps) would be perfect if I could somehow obtain that
> information through a fairly simple sql query. Otherwise I thought of
> using
> tran log backup sizes, but I'm not sure how accurately that reflects
> actual
> data change in the databases.
> Any help is appreciated.
> Thanks,
> Aaron Sentell
> Sr. DBA
> Compass Group USA
>