SQL Server Performance

sp_spaceused

Discussion in 'General Developer Questions' started by Reddy, Aug 20, 2007.

  1. Reddy New Member

    I wud like to know how i can change sp_spaceused store proc for
    getting data for the table where datepart(yyyy,convert(char(10),cast(date - 36161 as datetime),101))=1995
  2. martins New Member

    The sp_spaceused proc is in the master database. You should be able to modify it if you have the appropriate permissions. I would not suggest that you do that though. Copy the details of this proc into your own instead and use that.
  3. Reddy New Member

    Note: changed my question above please read again, sorry for that
    yeah I wud like to create identical store proc and change it.
  4. satya Moderator

    Don't risk in changing that, rather create as another SP and go on with your changes.
  5. martins New Member

    Unfortunately you won't be able to do that with modifying sp_spaceused.
  6. Reddy New Member

    so what wud be your suggestion for this. I hope u got wht am trying to do.
    space used for the data in the year 1995
  7. martins New Member

    Yip, got it [:)]
    It's very difficult to get a 100% accurate figure for this, especially because of varchar datatypes that do not always use all the allocated space. My suggestion would be to get an estimate amount of bytes used per record (either from the combined potential sizes of all your fields or from the actual size of the table). Then you can multiply this value with the amount of records you have for 1995.
    Hope this helps.
  8. Reddy New Member

    thats where my calculations are going wrong...
    I calculated row length of each table and multiplied by the no .of rows but tht doesnt seem to be good result i have.
  9. martins New Member

    You will have to do this for each table individually if you want the most accurate answer. Remember that calculating it in this way will not take indexes into consideration, and that might be a reason for your figure looking very low.
    Post your calculation, maybe we can spot the problem.
  10. Reddy New Member

    I calculatedrow length for each individual table and summed up which came to 87940 and mutiplied that with row count for the enitre Db and size toooo much than what iwas expecting
  11. martins New Member

    Ok, lets try this:
    Say you have a table called Table1. Run sp_spaceused 'Table1', which will give you space used for data and indexes as well as the record count. If the record count = 330147 and space used = 49320 KB (data) + 272 KB (index) then your estimate size per row = (49320 + 272) / 330147
    That gives you 0.15 KB per record. Now run a query on that table to return a record count for 1995 and you get 55000, then your space used (in this table) for 1995 is 0.15 * 55000 = 8250 KB.
    I hope you are doing something similar to this. You will have to do it like this for every table separately if you want fairly accurate results.

  12. Reddy New Member

    This looks ok but am not sure if this is the exact number.
    for the year 1995
    Total space used = 141656
    Total RecordCount = 277121
    RecordCount for 1995 = 5046
    so totallu space used for the year 1995 came up to 2.5MB
  13. martins New Member

    Reddy, your calculation as described above is as close as you are going to get. This will change over time, especially if you change the table definition. If I were you I would stick with what you have as probably an 80% accurate estimate. Do this calculation periodically to see if there is an upward or downward trend.
    I don't think there is a way that you will get a more accurate figure though...
  14. martins New Member

    Maybe to give you an idea what we do...
    I have a 1.2 TB datawarehouse (growing by 1 GB every day) and we do daily loads. I track the total space used in my databases and save the results to a table after each load. In doing that it is easy to see the trend and get an average growth for a period.
    Hope this helps.
  15. ndinakar Member

  16. Reddy New Member

    ndinakar..
    do u want to share anything or shed some light on what martin has advised me to do in this post?
  17. ndinakar Member

    My question would be how will that information be of any use? You cannot accurately find the table size going back to history. You can get an approximate size but it may not be accurate. You can measure the % of growth today and see how much it is next week or next month and project the growth rate. You can also measure by number of rows as"We are growing by 1 million rows every month" or "we are growing by 5 GB every month".
  18. Reddy New Member

    you are right dinakar, thts exactly what i need growth for each year. i do not need any accurate calculation, just need an approx growth for each year.
    So do you think am doing the rgith thing ?
  19. satya Moderator

Share This Page