| Author |
Topic |
|
Sarat
Constraint Violating Yak Guru
265 Posts |
 Posted - 2003-03-19 : 15:03:29
|
| Hi, In an effort to estimate database growth for next 5 yrs, I totalled the pages used by all system and application tables and multiplied the number by 8 KB (2,045,422 pages * 8 KB = 16,363,376 KB or 16.5 GB approx). 1. My current db size is 23.5 GB, what else is using 7 GB(23.5-16.5) of space? 2. Is this approach correct?Approach:1. Get total pages (dbcc showcontig's scanned pages value) used by system and app tables.2. Total them and since each page is 8 KB, multiply by 8 to know how much space is utilized by data in the database.Thanks, Sarat. |
|
|
Andraax
Aged Yak Warrior
790 Posts |
Posted - 2003-03-19 : 15:24:37
|
| Don't know if it has to do with this, but there are also tracking pages in the files. For example pages containing information on free space of the datapages, Index Allocation Map pages etc. There is some more info here, as well as an MDF-file viewer.[url]http://www.i-tivity.biz/sqlserver.htm[/url] |
 |
|
|
Sarat
Constraint Violating Yak Guru
265 Posts |
Posted - 2003-03-19 : 16:00:53
|
| Hi,I went through the paper and I also checked again what DBCC showcontig says:Paper says:"Advanced" nerds will know that the .mdf file is divided into thousands of pages, each 8K in size. As you can see below, the first 8K page contains the file header. The second contains the PFS, then the GAM and the SGAM. Scattered around the rest of the .mdf file are the DATA pages and IAM's (Index Allocation Map), as well as more GAM's and SGAM'sBOL for SHOWCONTIG says:The DBCC SHOWCONTIG statement traverses the page chain at the leaf level of the specified index when index_id is specified. If only table_id is specified, or if index_id is 0, the data pages of the specified table are scanned. Pages Scanned => Number of pages in the table or index. I am inclined to believe that pages scanned gives only 2 types of page info i.e., Data and Index and not others like GAM, IAM, DCM etc.So if the above assumption is true, I need to prove that 7 GB is used by the rest of the page types.What are your thoughts?Sarat. |
|
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2003-03-19 : 16:49:53
|
| Sarat,Your indexes will also be stored in the .mdf file. That can be a substantial part of the space used.There are several topic in BOL on Estimating sizes. ex:"Estimating the Size of a Table with a Clustered Index"-Chad |
|
|
|
Sarat
Constraint Violating Yak Guru
265 Posts |
Posted - 2003-03-19 : 18:26:34
|
| Ya, I looked at the topic in BOL but I felt I was already getting that information through DBCC SHOWCONTIG's Pages Scanned because it looks at the data pages + index pages. So I added the pages for all tables and multiplied by 8 KB, I was left with 7 GB which I am unable to account for. So I assumed that I am missing the count for IAM, GAM, DCM etc for each table. So I feel if I figure out how many pages IAMS, GAM etc. occupy for each table, I can get a justification for the 7 GB space UNLESS the SHOWCONTIG's Pages Scanned doesn't count index pages then I have to do the whole 9 yards of calculating data pages + clustered indexes + non-clustered indexes + other stuff if any which I don't know yet!Thanks,Sarat |
|
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2003-03-19 : 18:38:21
|
quote:
The DBCC SHOWCONTIG statement traverses the page chain at the leaf level of the specified index when index_id is specified. If only table_id is specified, or if index_id is 0, the data pages of the specified table are scanned
If you look at what you posted above. If you only specify the table, then only the data pages are scanned (Which is tatamount to the clustered index).You need to also take non clustered indexes into account.-Chad |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-03-19 : 18:40:20
|
| Why is it that you are counting the pages to determine the database size? Usually people figure out the database size by adding up all of the tables sizes, which is found by adding up the column sizes and then multiplying that by the number of rows, then multiplying that number by 8KB. Then you have to account for indexes. Isn't this what you should be doing instead?TaraEdited by - tduggan on 03/19/2003 18:42:38 |
|
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2003-03-19 : 18:41:37
|
| BTW GAM and SGAM will not occupy that much space. There is only one for 64,000 extents (4GB).-Chad |
|
|
|
Sarat
Constraint Violating Yak Guru
265 Posts |
Posted - 2003-03-19 : 19:20:52
|
| Chad,When I did showcontig specifying the index id, I got the same result as when i didn't specify any id so I thought the scanned pages = data pages + index pages BUT you are right, I forgot about non-clustered indexes, I will calculate that now.Tara,This is why i was considering pages:Per BOL/2072 course:1. Calculate # of bytes in a row (totalling # of bytes that each column contains).2. Calculate # of rows per each page.3. Divide # of rows in a tbl by # of rows in a data page to get NUMBER OF PAGES THAT ARE NEEDED TO STORE YOUR TABLE.4. Multiply step 3 by 8KB or 8192 bytes.so that's why i was considering pages (from showcontig directly instead of calculating this way) and multiplying by 8KB.but as Chad mentioned I am non-clustered index pages count.Thanks. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-03-19 : 19:25:49
|
| Ok, I see your point now. Instead of doing the calculations, you are just using the data that is already available to you. By including non-clustered indexes, you should be very close to the number that sp_spaceused gives you for the database size.Tara |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-03-19 : 21:31:41
|
| Another thing to keep in mind is that SQL Server allocates extents, not individual pages. Usually you'll have a couple of extents that are not fully populated unless you DBREINDEX the clustered index on a regular basis. Also be wary of text columns, these use entire pages no matter how much or how little is actually stored. 1,000 rows of text data of 1 byte each still uses 1,000 pages. |
|
|
|
Sarat
Constraint Violating Yak Guru
265 Posts |
Posted - 2003-03-20 : 15:33:05
|
| Hi,I perfomed step by step calculation to get pages (data pages + clustered index pages) used by a certain table. I didn't include the non-clustered index figures as I wanted to compare it with dbcc showcontig value. It was off by 1371 pages.ex: pages scanned = 29103 and pages calculated = 27732. Why are values not same or atleast approximately same? Which one should i consider? Also, I considered Fixed-length data types as Char, Nchar and Binary + PSDATE, SmallInt and Decimal. I didn't have any variable-length data type fields. If I don't include date and integer types, my pages number is off by 11426.Thanks,Sarat. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-03-20 : 21:28:23
|
| If you've deleted any rows from this table you'll have some less-than-full pages, look at the scan density value. Anything less than 100% means some wasted space. Also look at average bytes free; that's also wasted space. Unless you DBREINDEX or INDEXDEFRAG regularly this space will most likely remain unused.Also look at any statistics you have on the tables. The data for the statistics is stored in sysindexes, and the row size of sysindexes is pretty large (run DBCC SHOWCONTIG on it!), and it has an image column, which allocates like a text column. If autocreate stats is on and you write a lot of heavy ad-hoc queries, boom, you can end up with hundreds of these that can each use up over a page of data. These statistics will never show up on a DBCC SHOWCONTIG because they're not really indexes, but they still take up space.You also cannot ignore nonclustered indexes. They take up space just like everything else, and also carry some overhead beyond the size of their key(s). Multiply this overhead for each indexed row and it adds up pretty quickly.In the end though, I think you might be trying to do brain surgery on a potted plant. Size estimations are just that: estimates. No matter how microscopic you get in your analysis you'll always miss something and end up driving yourself crazy over it. Use DBCC SHOWCONTIG and sp_spaceused and rely on them rather than any particular (rowsize X number of rows X bytes of overhead) blah blah blah to figure out the actual size of your tables. If they come out higher than what you expect, so be it: you can work on the assumption that you're using AT LEAST as much as they say you are. It's always better to overestimate how much space you're using, you'll be less likely to run out suddenly.Edited by - robvolk on 03/20/2003 21:30:55 |
|
|
|
Sarat
Constraint Violating Yak Guru
265 Posts |
Posted - 2003-03-21 : 12:21:17
|
| hey rob, that makes me a feel a lot better. i was really paranoid about following the right approach. i perform reindexing every other week and defrag 3 times a month. after performing dbreindex yesterday night, dbcc showcontig showed a number pretty near to what i got with long calcuations. pages scanned showed = 27700 and pages I calculated were 27732. I think dbcc and sp_spaceused should do the job for me. i do think doing the lengthy calculations were worth it! thanks,Sarat. |
|
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2003-03-21 : 15:30:20
|
| You also need to consider your fill factor. Your pages may (Or may not) be 100% full even after you reindex because you intentionally leave a certain percentage of the page empty.-Chad |
|
|
|
|
What else does an MDF contain?