Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
otto
Starting Member
21 Posts |
 Posted - 2002-06-18 : 17:04:58
|
| I'm looking to run a query to find out how much disk space is being used for XX amount of rows in a table.I have no clue where to even look for this information, let alone build a query for it.Can anyone shed some light?Thanks,Otto |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-06-18 : 17:18:00
|
| You can use the sp_spaceused system procedure to list the size of a table and it's indexes. See Books Online for more information.Also look at DBCC SHOWCONTIG, it will give you information on data page statistics and data fragmentation. This is more accurate than an estimate because data can be fragmented, or a low fill factor can be used, and would leave unavailable space on the page. |
 |
|
|
otto
Starting Member
21 Posts |
Posted - 2002-06-18 : 17:36:54
|
quote:
You can use the sp_spaceused system procedure to list the size of a table and it's indexes. See Books Online for more information.Also look at DBCC SHOWCONTIG, it will give you information on data page statistics and data fragmentation. This is more accurate than an estimate because data can be fragmented, or a low fill factor can be used, and would leave unavailable space on the page.
Thanks for the quick reply. I've looked a bit at sp_spaceused procedure, but that will tell me how much space a table is taking up. I need to look a little further and tell how much a specific set of rows is taking up.From my quick gloss over that procedure, I couldn't figure it out.Any other ideas or am I over looking something? |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-06-18 : 17:47:52
|
| You really can't do what you're asking. If you have nullable columns or varchar data, the amount of space that each row takes up with vary. There is some overhead involved with these columns that takes up space as well, above and beyond the actual column size and data size. The smallest unit you can effectively measure is a data page, which is 8K (8192 bytes total, 8096 bytes for data). Depeding on things like fill factor and page splits, the pages will hold less than the maximum possible, and the extra space is wasted.Hmmmmm, well, if you don't mind a lot of typing, you can put each column inside the DATALENGTH function:SELECT DATALENGTH(col1) + DATALENGTH(col2) + DATALENGTH(col3) AS RowSizeFROM myTable WHERE col1 BETWEEN 'AAA' AND 'AZZ'That will tell you the actual number of bytes used for that row, but again isn't really an accurate measure of how it is stored. |
|
|
|
graz
Chief SQLTeam Crack Dealer
4149 Posts |
Posted - 2002-06-18 : 19:10:22
|
| This script will tell you now much space a table uses and how many rows it has:http://www.sqlteam.com/item.asp?ItemID=282===============================================Creating tomorrow's legacy systems today.One crisis at a time. |
|
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2002-06-18 : 20:04:36
|
| While its not 100% on target, the jewel by Jay99(RIP) is pretty useful too. . .<O> |
|
|
|
|
|
|
|
|
Measuring Table Size