SQL Server Performance

records per week

Discussion in 'Performance Tuning for DBAs' started by Anil, Oct 14, 2005.

  1. Anil New Member

    I have one database. In that DB the largest table is "xyz" . This runs into millions of records over time. Can we see how much this table grows by records every week.

    Thanks in advance.

    Regards,
    Anil Kumar
  2. ghemant Moderator

    Hi,
    for that you can add a column that stores date{derault getdate()} in *xyz* table , and every time record inserted in that *xyz* table a value for that new column is added / inserted then based on that you can easily count it as per yr requirement , week / month /quarter or year wise.

    but may we know why you want this ! do you wants some reports for entry or make some assumptions for db growth ratio based on this !


    Regards


    Hemantgiri S. Goswami
    ghemant@gmail.com
    "Humans don't have Caliber to PASS TIME , Time it self Pass or Fail Humans" - by Hemantgiri Goswami
  3. Anil New Member

    Hi Hemanth,

    Thanks for looking into this....

    My database is growing quite rapidely, one GB per week. To get control over my database, like how much xyz table growing per week and how much will grow in near future. My main concern is to know in which month/week the table is growing much and in which month/week it is normally growing.
    Based on that setps we need to take etc....

    I am some what unclear with your reply. It means I must create a column getdate() which will take count.

    Could you explain with an example.... in script format.

    Regards,
    Anil Kumar
  4. ghemant Moderator

    Hi,<br />i have some question for you !<br />1). what is your recovery plan !?<br />2). Are u doing Bulk Operation !?<br />3). Are you running DBREINDEX as your backup plan job !?<br /><br />yes , i am asking about a sysdate column for e.g.<br /><br /> <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><br /><br /><b>alter table xyz<br />add sysdate datetime default getdate()<br /><br />or create a rule and bind it to sysdate column<br />create default xyzdate as getdate()<br />sp_bindefault xyzdate, 'xyz.sysdate'</b><br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />and everytime you are inserting records it will add current date , and then you can compare it as per your requirement.<br /><br />and please search topics / threads for T-LOG , which also helps you for this.<br /><br />[<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br />Regards <br /><br /><br />Hemantgiri S. Goswami<br />ghemant@gmail.com<br />"Humans don't have Caliber to PASS TIME , Time it self Pass or Fail Humans" - by Hemantgiri Goswami<br />
  5. Anil New Member

    Hi,

    Fine script. I have made some changes for my requirement. I think it will work.

    Send your opinion on my script, Is it work or not?

    Thanks for sending script.


    alter table xyz
    add sysdate datetime default getdate()

    use xyz
    select count(*) from xyz where abs(datediff(day,getdate(),sysdate))<=7

    Regards,
    Anil Kumar
  6. ghemant Moderator

    Hi,
    if it serves your purpose its worth, i would suggest rather comparing it to noof days compare it for any givendate is better.

    Regards

    Hemantgiri S. Goswami
    ghemant@gmail.com
    "Humans don't have Caliber to PASS TIME , Time it self Pass or Fail Humans" - by Hemantgiri Goswami
  7. satya Moderator

    http://www.databasejournal.com/features/mssql/article.php/3339681 for your information.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  8. kpayne New Member

    quote:Originally posted by ghemant

    Hi,
    for that you can add a column that stores date{derault getdate()} in *xyz* table , and every time record inserted in that *xyz* table a value for that new column is added / inserted then based on that you can easily count it as per yr requirement , week / month /quarter or year wise.

    but may we know why you want this ! do you wants some reports for entry or make some assumptions for db growth ratio based on this !


    Regards


    Hemantgiri S. Goswami
    ghemant@gmail.com
    "Humans don't have Caliber to PASS TIME , Time it self Pass or Fail Humans" - by Hemantgiri Goswami

    If you are only interested in the week and not the day or time, you should also consider a separate table that contains the year and week # and the count of rows in your table. This will save a considerable amount of space and is technically more normalized than storing a unneccessary datetime in every row.

    You can populate the table with an insert trigger, or add the appropriate INSERT/UPDATE command into your maintenance plan.

    Keith Payne
    Technical Marketing Solutions
    www.tms-us.com
  9. 7013d0 New Member


    Hello there!


    Depending on your needs, you can create a separated table, containing only the large table's row count information, updated thru a scheduled job running daily, weekly, monthly, on your maintenance window...

    I believe this way you can save some space on your storage device, keep your insert performance the way it is and separate your business tables from your maintenance tables.

    You guys may want to check out this script:
    SET NOCOUNT ON

    DECLARE @dbidINT,
    @SQLVARCHAR(8000),
    @dbnameVARCHAR(255),
    @tbnameVARCHAR(255)

    SELECT @dbname = 'MyDatabase'-- Set your database name
    SELECT @tbname = 'MyOwner.MyLargeTable'-- Set your table name to perform a row count


    CREATE TABLE #TMP_TBINFO (QTDROWS NUMERIC(19,0))


    SELECT @SQL = 'DBCC UPDATEUSAGE (' + @dbname + ') WITH COUNT_ROWS, NO_INFOMSGS'
    PRINT @SQL
    EXEC(@SQL)

    SELECT @SQL = 'INSERT INTO #TMP_TBINFO SELECT rows FROM ' + @dbname + '..sysindexes WHERE indid < 2 AND id = OBJECT_ID(''' + @dbname + '.' + @tbname + ''')'
    PRINT @SQL
    EXEC(@SQL)


    INSERT INTO YourHistoryTable-- Set your row count history table name
    SELECT CONVERT(VARCHAR(19), GETDATE(), 120) AS REFDATE, QTDROWS FROM #TMP_TBINFO

    DROP TABLE #TMP_TBINFO


    Regards,

    Fernando Toledo
  10. satya Moderator

    Is it possible to archive the data based on the date range?

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  11. Anil New Member

    Wow folks,

    Great responce. Thanks for all....

    Satya, that is the exactly same Question I am going to ask. But u r ahead of the game...
  12. satya Moderator

    So is that a question or answer to my post [<img src='/community/emoticons/emotion-5.gif' alt=';)' />].<br />Using the criteria of table design and application rollup you have to decide whether it is possible to archive the data.<br /><br /><hr noshade size="1"><b>Satya SKJ</b><br />Moderator<br /<a target="_blank" href=http://www.SQL-Server-Performance.Com/forum>http://www.SQL-Server-Performance.Com/forum</a><br /><center><font color="teal"><font size="1">This posting is provided “AS IS” with no rights for the sake of <i>knowledge sharing.</i></font id="size1"></font id="teal"></center>
  13. Anil New Member

    Offcourse it is Question. I want to ask to you but u have asked.....
  14. ghemant Moderator

    Hi,
    we used Flag (Active=Y,NotActive=N,OnlyReporting=R) to archive the data but not using date range..

    Regards

    Hemantgiri S. Goswami
    ghemant@gmail.com
    "Humans don't have Caliber to PASS TIME , Time it self Pass or Fail Humans" - by Hemantgiri Goswami
  15. johnson_ef Member

    I do have a practice of taking sp_spaceused in day-today basis. This helped me in terms of record growth and size.

    try this in QA:

    sp_spaceused "owner.table_name"
    or
    sp_msforeachtable 'sp_spaceused"?"'

    What I do as a practice, take the result and save it in daily, and I can compare it.

    Try it
    -Johnson
  16. satya Moderator

  17. Anil New Member

    Wow Satya. That is a good link. This will archive data to another database. Offcourse it is useful for lot of databases but I have one database. I want the database data will perminently archived. Is there any other link <img src='/community/emoticons/emotion-1.gif' alt=':)' />
  18. ghemant Moderator

    Hi,<br />As Satya said *it Depend* only upto your requirement you have to choose the way / select how to archive data.<br />i feel Vyas's link is full with very good sql resources <br /><br /><br />[<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br />Regards<br /><br />Hemantgiri S. Goswami<br />ghemant@gmail.com<br />"Humans don't have Caliber to PASS TIME , Time it self Pass or Fail Humans" - by Hemantgiri Goswami<br />

Share This Page