| Author |
Topic |
|
Analyzer
Posting Yak Master
115 Posts |
 Posted - 2007-09-05 : 07:18:58
|
| Currently managing a VLDB which is heavily fragmented. The applications characteristics are heavy INSERTS, UPDATES and DELETES and against my recommendations the majority of the large tables are HEAPS or have inadequate non-clustered indexes. The code is dynamic SQL, the tables dynamic and the vendor's understanding of SQL is poor which hampers my progress here.Both the OS defrag GUI and sys.dm_db_index_physical_stats show huge amounts of fragmentation, some tables are around 80%. Note: (Indexreorg jobs are in place where indexes are present)The problem I have is convincing the windows engineers whom contol the budget that deploying a defrag tool such as Idera's Defrag Manager will actually be of any use. They keep asking whether the app will actually will get inside the DB and I'm curious whether there will be any benefit on the HEAP tables should the vendor not make changes to their code.Is there any conclusive evidence I can provide these Window guys to quantify my defrag expenditure request? Any help would be appreciated |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-09-05 : 08:23:54
|
| "defrag expenditure request"Well, for what its worth, we use CONTIG from sysinternals (now owned by Microsoft) here, I hope! : http://www.sysinternals.com/Utilities/Contig.htmlwhich is free and defrags a single file. Supposedly it will work whilst the file is open, but we only ever use it when the database is offline for maintenance (or SQL Service stopped) and backed up!We use DBCC's Defrag, rather than Reindex, for large tables. All our tables have clustered indexes, so I don't know if this is possible for a table with no clustered index.Only other alternative is to export the table, drop it, and then reimport it. You could export the table "ordered" so that records more like to be deleted in the future are adjacent - e.g. in Date orderKristen |
 |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-09-05 : 22:24:20
|
| Need clustered index to defrag table. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-09-06 : 04:07:16
|
| Or Export data, Drop/Re-Create table, Re-Import data ?? (which I would rank as a PITA, but ...) |
|
|
|
Analyzer
Posting Yak Master
115 Posts |
Posted - 2007-09-06 : 05:03:38
|
| Thanks for everyones reply - this has now cemented my understanding of the situation. I'll ramp up the pressure on the vendor to change there code and implement clustered indexes. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-09-06 : 06:05:55
|
| By the by, I would regard it as Good Practice to have a clustered index on all tables. Hard to imagine a table that doesn't need one at all! If there are NON clustered index(es) on a table with NO Clustered Index then the amount of disk-activity on any update (which changes an indexes value) is extra, because each index has to be updated (with new position of record in the disk file), rather than the secondary index pointing to the Key(s) of the Clustered Index.Although I suppose that this makes a Secondary Index lookup slower, as there is then the secondary lookup in the Clustered index??Kristen |
|
|
|
pootle_flump
1064 Posts |
Posted - 2007-09-06 : 08:20:43
|
| In case you don't have it yet, you also need to get a handle on logical vs physical fragmentation. Logical fragmentation is your biggest conern though, as pointed out, a heap cannot be fragmented or defragmented since it has no logical order.Linky:http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-09-06 : 10:07:43
|
| "a heap cannot be fragmented or defragmented since it has no logical order"... other than that it can be exported and re-imported which gets rid of any "holes" caused by deletions; which, I assume, may help a bit with table scans.Kristen |
|
|
|
pootle_flump
1064 Posts |
Posted - 2007-09-06 : 12:23:35
|
Yeah - you can change the page density but you still can't make any headway into logical fragmentation. I think we are back again to "always have clustered indexes"  |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-09-06 : 12:28:47
|
| "I think we are back again to "always have clustered indexes""I ain't going to argue with that! |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2007-09-06 : 17:12:20
|
quote:
Originally posted by Analyzer
Thanks for everyones reply - this has now cemented my understanding of the situation. I'll ramp up the pressure on the vendor to change there code and implement clustered indexes.
Sounds like conclusive evidence that vendor's database "designers" are a bunch of dumb-asses, so I wouldn't count on them being helpful or even admitting there is a problem.Be prepared for a bunch of BS meant to make you look bad to your boss. Good luck!CODO ERGO SUM |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-09-07 : 02:47:14
|
Fair chance they won't notice if you create a Clustered Index then ...  |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2007-09-07 : 09:19:37
|
quote:
Originally posted by Kristen
Fair chance they won't notice if you create a Clustered Index then ...
It’s always easier to get forgiveness than to get permission.CODO ERGO SUM |
|
|
|
Analyzer
Posting Yak Master
115 Posts |
Posted - 2007-09-07 : 10:11:14
|
| I spend most of my day explaining to this large vendor how to program SQL and not use cross-platform code which they also use for Oracle deployments. Easy enough to create clustered indexes on-line, however their app is dynamic and drops\recreates tables continuously which negates most of my optimisation and tuning changes since they need to change their source code.If these guys can write an app and sell it for millions (recently acquire by Business Objects in fact) then I shall write my own. There's hope for us all.... |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-09-07 : 14:49:00
|
| Good luck! the beers are on you when you succeed! |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2007-09-07 : 15:02:32
|
| also, you can avoid most external fragmentation by:1. avoid filling a drive completely. 2. avoid shrinking datafiles (turn off autoshrink)3. avoid continually growing datafiles by small amounts. set them to an appropriate initial size. Autogrow should be in sizeable chunks (500MB, 2GB, etc), not in 10MB chunks.-ec |
|
|
|
|
Conclusive Fragmentation Evidence Required