| Author |
Topic |
|
mark_b
Starting Member
24 Posts |
 Posted - 2008-07-29 : 09:21:55
|
| Hi all,i wonder if you can help me here to see if i am missing something totally obvious.I am currently going through a table purge process (for the first time in 13 years on these databases!!) and I am finding the amount of space i am recovering does not really tie into amount of rows that i am deleting.For example, one of my smallest tables that i am purging, pre purge had 138Mb allocated to it, but after the purge this only went down to 125Mb even though i deleted over 50% of the rows.The process i am following is : - Purge data - Index Defrag on the indexes on the table (I have to do this rather than full reindex as they will not let me take the databases off line!) - DBCC UPDATEUSAGE - check space.I have looked at DBCC SHOWCONTIG and the indexes are nicely compacted to > 90% so i can not find anything to fault with the indexes, but i was wondering if there was something similar i need to do to compact the Data portion of the table.I have looked through google and books online, but everything i find relating to compacting and defragmentation is relating to the indexes and not the data portion. I look forward to any insights or hints and tips you might have,Thank you in advance,Mark |
|
|
pootle_flump
1064 Posts |
Posted - 2008-07-29 : 09:47:43
|
As I recall index defrag shouldn't release any pages. I might need to check that. What is the 90% figure you are quoting?EDIT - nope it does - sorry  |
 |
|
|
mark_b
Starting Member
24 Posts |
Posted - 2008-07-29 : 10:12:59
|
The 90% i was talking about was from the DBCC SHOWCONTIG statement. Output is as follows :DBCC SHOWCONTIG scanning 'dmhi' table...Table: 'dmhi' (1002486650); index ID: 0, database ID: 65TABLE level scan performed.- Pages Scanned................................: 13493- Extents Scanned..............................: 1804- Extent Switches..............................: 1803- Avg. Pages per Extent........................: 7.5- Scan Density [Best Count:Actual Count].......: 93.51% [1687:1804]- Extent Scan Fragmentation ...................: 43.07%- Avg. Bytes Free per Page.....................: 4281.5- Avg. Page Density (full).....................: 47.10%DBCC execution completed. If DBCC printed error messages, contact your system administrator. |
|
|
|
pootle_flump
1064 Posts |
Posted - 2008-07-29 : 10:32:08
|
| Although that figure is the one you are usually most interested in when looking at fragmentation, in your case you are really more interested in the Page density and Bytes free per page (they are both expressing the same thing). The point is - your pages are, on average, over half empty.The bit of confusion I had earlier was that I was sure DBCC INDEXDEFRAG does not move data between pages. I was wrong - it says it does in the docs. But I am reasonably sure it does not do it very efficiently. Assuming that you don't have a fill factor of about 50% then your results appear to bear that out. |
|
|
|
mark_b
Starting Member
24 Posts |
Posted - 2008-07-29 : 10:55:16
|
| Hmmm that might be it. I dont know what fill was specified when the indexes were set up, and DEFRAG just reverts back to the that value. Will have a dig around to see if there is anyway of finding out the fill factor. am sure there will be!Thanks for the help so far, and if anyone else has any thoughts, please let me know. |
|
|
|
pootle_flump
1064 Posts |
Posted - 2008-07-29 : 11:05:59
|
| [code]SELECT OrigFillFactorFROM dbo.sysindexesWHERE OBJECT_NAME(OBJECT_ID) = 'my_table_name' AND indid = 1[/code] |
|
|
|
pootle_flump
1064 Posts |
Posted - 2008-07-29 : 11:10:55
|
| Whoah - hang on - just reread your showcontig - this is a heap n'ést pas? |
|
|
|
mark_b
Starting Member
24 Posts |
Posted - 2008-07-29 : 11:13:36
|
got there at just the same time you posted that! :) thanks anyway, handy to have. Fill factor is 0 which means that it should be filling up the pages and leaving a little bit of space (according to [url]http://msdn.microsoft.com/en-us/library/aa196711(SQL.80).aspx[/url])pertinent quote from that page is : quote:
A fill factor value of 0 does not mean that pages are 0 percent full. It is treated similarly to a fill factor value of 100 in that SQL Server creates clustered indexes with full data pages and nonclustered indexes with full leaf pages. It is different from 100 in that SQL Server leaves some space within the upper level of the index tree.
which does not really help me as that does not explain why i am only getting my pages half filled!Will keep digging to see what else i can find. |
|
|
|
pootle_flump
1064 Posts |
Posted - 2008-07-29 : 11:18:46
|
| All bets are off when dealing with heaps. They behave totally differently to indexes. For starters - how can a table with no logical data order ever be out of data order (AKA logical fragmentation)? The way in which pages are filled, emptied, deallocated etc all changes. Have a looksy (for example):http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/clusivsh.mspxThe only way I know of to "defragment" a heap (and I use quotes because you aren't truly defragmenting it since it is not ever really fragmented) is to add a clustered index and optionally drop it later. Personally I would add one and keep it. |
|
|
|
pootle_flump
1064 Posts |
Posted - 2008-07-29 : 11:19:41
|
| Hmm - you ran my exact code or removed the "AND indid = 1"? |
|
|
|
pootle_flump
1064 Posts |
Posted - 2008-07-29 : 11:21:21
|
| I'm leaving the orifice now but a final point - heaps don't have a fill factor either - a page is filled then it moves on to the next, fills that, next - no splits hence no need for a fill factor. |
|
|
|
mark_b
Starting Member
24 Posts |
Posted - 2008-07-29 : 11:23:54
|
quote:
Originally posted by pootle_flump
Hmm - you ran my exact code or removed the "AND indid = 1"?
i had already created my own query to pull back the info i needed. Will look up 'Heaps' to try and understand them in this context (and not memory dumps!!) Thanks for your help today. |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2008-07-29 : 11:25:38
|
Your problem is that you do no have a clustered index on the table. Notice the "index ID: 0"? That means that the table is a heap, which is what you get when you do not have a clustered index. Because it is a heap, reindex or index defrag cannot help you to compact the table.Table: 'dmhi' (1002486650); index ID: 0, database ID: 65 CODO ERGO SUM |
|
|
|
pootle_flump
1064 Posts |
Posted - 2008-07-29 : 18:05:29
|
| he he - the memory dumps were because I spotted what Michael has quoted ten minutes before I had to leave the office - no time for dialogue or in depth comments.Anyhoo- BoL is as ever a good start. Kalen Delaney's book on internals is super. Google of course. It is not just understanding the structural difference between heaps and clustered indexes - there are quite some nuances that are quite significant. |
|
|
|
mark_b
Starting Member
24 Posts |
Posted - 2008-07-30 : 09:32:51
|
well following on from your comments i read alot about the clustered indexes and then decided to check the rest of our production tables. not a single one, has a clustered index ... all tables are heaps (heap of wasted space that is!).I then pulled one database into test and for kicks and giggles picked two of the tables and created a clustered index on them. Immediately, reduced the space they took up by half, and i think this showcontig looks a lot better :)DBCC SHOWCONTIG scanning 'dmhi' table...Table: 'dmhi' (1002486650); index ID: 1, database ID: 8TABLE level scan performed.- Pages Scanned................................: 6495- Extents Scanned..............................: 814- Extent Switches..............................: 813- Avg. Pages per Extent........................: 8.0- Scan Density [Best Count:Actual Count].......: 99.75% [812:814]- Logical Scan Fragmentation ..................: 4.56%- Extent Scan Fragmentation ...................: 54.67%- Avg. Bytes Free per Page.....................: 128.3- Avg. Page Density (full).....................: 98.41% needless to say, am now going back to the developers to see why they set up the tables this way and if there are any logical reasons why i shouldnt set up a clustered index on all tables! Dont you love inherited application databases!Many thanks for all your help yesterday Pootle (havent seen the Flumps in for ever!) and its always good to learn something new. I must admit i never really understood the real difference between clustered and non clustered indexes until yesterday.Mark |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2008-07-30 : 10:09:19
|
| Heap tables can cause serious performance issues and a massive waste of space.A few weeks ago I discovered heap tables in a vendor supplied application that were using over 4 GB each while containing less than 200 rows each. Performance on the whole server was terrible, because the application was doing table scans on these tables.CODO ERGO SUM |
|
|
|
pootle_flump
1064 Posts |
Posted - 2008-07-30 : 10:11:15
|
quote:
Originally posted by mark_b
Many thanks for all your help yesterday Pootle
My pleasure  quote:
Originally posted by mark_bhavent seen the Flumps in for ever!
You don't see many of us nowadays as we have been hunted to near extinction  Don't forget to support your local arm of the RSPCF.Check out the BoL articles on clustered indexes - once you have decided you want clustered indexes (and the default should always be YES I DO!) then the tricky part is picking the right column(s). Typically they want to be:- Unique- Often narrow- Very often the primary key ESPECIALLY if you use composite keys in all your tables (means you get to use lots of merge joins in your queries - ignore this if it means nowt to you)There's other rules and ideas in BoL but those are the commonly accepted main three. |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2008-07-30 : 10:52:50
|
| An simple rule of thumb is to create an integer or bigint identity column in each table (depending on the expected number of rows), and make it the clustered primary key. Define additional unique constraints on all natural keys. Following this rule will rarely, if ever, get you into trouble.CODO ERGO SUM |
|
|
|
|
Space Recovery after Deletions