CHANGE RATE OF DATA

Question

CHANGE RATE OF DATA

Deb Anderson

SSCrazy

Points: 2116
More actions
March 28, 2007 at 6:48 am

#123260

I've posted this before. No one ever responded. I've continued to do research, no luck.
I need to determine the daily volume of pages that are changed for my SQL data. I need this for planning and purchasing a SAN replication solution for DR. ANY help would be appreciated
😎

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

Deb

You don't say what version of SQL Server you're using, but if it's 2005, have you tried the dynamic management views? They begin with sys.dm_ and they have a lots of different information in them. Maybe one of them will have what you're looking for.

John

Deb Anderson SSCrazy Points: 2116 More actions · Answer 2

thanks for the reply ...

i'm running about 10 of each 2000 and 2005. i have looked through the 2005 views. i looked even more closely at sys.dm_os_sys_info, sys.dm_os_performance_counters, and sys.dm_db_file_space_usage. no joy.

i'm beginning to wonder if my approach is flawed or i'm just nuts. in the end i want something akin to what you would get in a differential backup. however my understanding is that difs operate at the extext level and not the page level. perhaps this is a function i should be looking at via the OS???

you help is very appreciated ... thanks again

😎

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 3

Deb

Can't you just count the extents and multiply by 8? I take it it's only an approximation you're after?

John

Deb Anderson SSCrazy Points: 2116 More actions · Answer 4

and extent is 64 KB while a page is 8 KB ... if I rely on extents to be my measurement, then I have the possiblity of overestimating my change rate by 8 times. however, I suppose it's better to go too high than too low on this kind of number. i'll have to give using extents a look - i suppose this would mean setting up a differential on each of my databases and start keeping track of the size differential over each day. ouch.

thanks again.

😎

SQL ORACLE One Orange Chip Points: 27807 More actions · Answer 5

The number of pages may not be a good parameter for planning a disk capacity. I would like to use the data of used space instead.

Deb Anderson SSCrazy Points: 2116 More actions · Answer 6

I understand what you are saying. We aren't really capacity planning in the sense of the amount of space we need. We are planning for the amount of data that will need to be pushed (via hardware replication) to a DR site SAN. So, we need to know space required, yes; but also, rate of change, speed of the devices, throughput on our lines, etc. The solution we are contemplating will take pages changed rather than all of the data - which I why I asked.

Thanks for your reply.

😎