June 9, 2004 at 9:27 am
I ran sp_spaceused @updateusage='true' on a database and got:
Database Size = 421.06 MB
Unallocated Space = 36.26 MB
Reserved = 387128 KB
Data = 169960 KB
Index_size = 98648 KB
Unused = 118520 KB
After shrinking in EM I get:
Database Size = 348.88 MB
Unallocated Space = 0.07 MB
Reserved = 387128 KB
Data = 169960 KB
Index_size = 118520 KB
Unused = 118520 KB
And after a DTS import into a new database:
Database Size = 421.06 MB
Unallocated Space = 36.26 MB
Reserved = 387128 KB
Data = 169960 KB
Index_size = 118520
Unused = 118520
THe reserved, data, index_size, and unused numbers didn't change after shrinking, but the database size and unallocated numbers did. After the DTS import all of the numbers changed. Also, the sum of the reservered, data, index_size, and unused numbers is considerbly larger than the actual size of the database.
I'm guessing that the database size is the actual size of the disk files for the DB, the unallocated space is space that has not been specifically allocated as a SQL Server data page, reserved is the number of SQL data pages times the data page size (8K, correct?) data and index_size is the actual space used up by the data and indexes, and that unused is empty space on datapages. So the unused divided by reserved would give me percent fragmentation. Which in the first and second case is not affected by shrinking and in the third case the DTS defragments the whole thing as a side effect of the data import.
So my question is, is the amount of unused space a performance issue? If so, how do I get rid of it without doing a DTS import? The database is on a maintenance plan that re-organizes data and indexes, and yet it still shows 30% fragmentation. This is potentially an issue as we host 130 similar customers in a Citrix/CLustered SQL environment and we don't want unexpected performance problems.
Bob
SuccessWare Software
June 9, 2004 at 9:46 am
Space can be confusing sometimes that for sure.
But you need to take into account that your database is set to grow. The default to grow by 10% to an unlimited size. That means when the database needs space it grows 10%. So at any given time you will have something around the area of 10% of free space.
By the by, reindexing (fixing minor problems with indexes) in the maintance plan is often not enought to keep indexes down in space. Lookup in books online for DBCC SHOWCONTIG, it shows a wonderful script that can be changed to stored proc and run occassionally to keep index size down (white space or fragmentation).
June 9, 2004 at 9:59 am
Thanks. That is a nice script. What about data page fragmentation? I see alot of discussion about fragmented indexes on news groups, but not much about data. Is data fragmentation less of an issue?
Bob
SuccessWare Software
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply