Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2004
    Posts
    23

    Unanswered: Total page writes/total amount of data change in a set period of time

    Does anyone know how I can determine the number of page writes that have been performed during a set period of time? I need to figure out the data churn in that time period.

    TIA

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Another internals question....hmmmm

    Do you know how big each page is?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Feb 2004
    Posts
    23
    I think so... the default size for a page in SQL2K is 8k (isn't it?)

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Yes it is...and I asked you that to level set...

    I've done a bunch of this type of thing in DB2

    http://weblogs.sqlteam.com/brettk/ar...01/30/825.aspx

    But mostly to identify the most effecient allocation. That's because we do it at the tablespace level (which sql server doesn't have)

    To do what you're asking would require you to run profiler...identify every insert statement...know the length of each statement, calculate it's internal size, add them all up....

    are you just trying to forecast db growth?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Feb 2004
    Posts
    23
    Hmm. I've just finished shuddering at the thought of identifying all INSERT, UPDATE and DELETE statements and then calculating the "hit" in terms of the respective size of data affected for each statement. ;-)

    What I'm trying to achieve is a guestimate of how large a differential backup would be given a period of time following a full backup. I need this to predict the extra occupancy I would need with an external data vault provider based on retention periods, etc.

    I was hoping that there might be an easier way of doing this through Profiler or whatever else...

    The only other way that has occurred to me is to use Windows Performance Monitor and to monitor the 'Page Writes/sec' counter every 2 seconds, say, over the appropriate period (24 hours, say). That way, I can make a very crude assumption that the number of pages written corresponding to each data point is A x 2 x 8 kbytes, where A is the number of page writes/sec. I can sum these subtotals and make an estimate of churn that way. Does that seem OK-ish?

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    As long as you never change (insert/update/delete) the same page twice, that should work nicely. I'll listen for the nice young men on their flying pigs to fly by, singing of your magical powers should that be the case.

    The best guesstimate would come from actually doing it. It isn't that tough to set up a backup schedule and measure the output for a week or whatever period you decide is adequate.

    Keep in mind that most databases have monthly/quarterly/etc usage patterns, so be careful to either pick the worst case or allow for it in your estimates.

    -PatP

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •