| Author |
Topic |
|
jbates99
Constraint Violating Yak Guru
396 Posts |
 Posted - 2011-10-02 : 19:28:40
|
| Hi all,My question is about a 2005 Standard database that is a size of 110 GB (the data file). I backup the t-log every 15 minutes and the average size is 1.3 GB (when the maint olan is not running).I run a weekly maint plane which reorganzies indexes for all tables and views, leaving 10 % free space.While the plan is executing (4 hours), the .trn file size grows and eventually reaches a size of 8 GB for each 15 minute interval. If the backup job does no complete within 15 minutes, sometimes I just have 1 .trn file for an hours time and that file is sometimes 30 GB.My question is - what about reorganizing indexes causes the t-log file size to grow so much - 30 % of the database size in only an hour?Is this normal? (The db is in light use while the plan runs)Thanks, John |
|
|
jbates99
Constraint Violating Yak Guru
396 Posts |
Posted - 2011-10-02 : 21:54:32
|
| ... These are the hourly .trn file sizes during the execution of the Maint Plan:Hour 1 - 31 GBHour 2 - 30 GBHour 3 - 48 GBHour 4 - 52 GBthen it completedFor a database that is only 110 GB size. |
 |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-10-03 : 02:10:52
|
| Reorg or rebuild?Rebuild needs log space = size of index being built, so large log backup files are expected.--Gail ShawSQL Server MVP |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2011-10-03 : 04:20:08
|
| "leaving 10 % free space"The regulars here have switched to using 100% fill, no free space, in the vast majority of cases.The indexes being more densely packed means fewer reads, in general, to find records. Leaving 10% free will allow one, or a couple, or keys to be inserted into an index block, but inserts are often clustered, so more than a couple will be inserted into the page forcing a page split anyway.Setting all indexes to 10% free will include those indexes based on IDENTITY or Dates that are monotonically increasing - and its a huge waste to have free space in those."weekly maint plane which reorganzies indexes for all tables and views"Assuming this is the Maintenance Wizard? its a very blunt instrument. It just rebuilds all indexes whether they need it or not.We have some home-grown SQL that rebuilds indexes only when they are fragmented (above some threshold), and we reorganise larger indexes and only rebuild smaller ones. We don't bother at all on tables that have very few pages.We increase the rate of Tlog backups to every 2 minutes during index rebuild to reduce the chance of the LDF file being extended.There are plenty of scripts around to do this type of more "considered" index maintenance. |
|
|
|
jbates99
Constraint Violating Yak Guru
396 Posts |
Posted - 2011-10-03 : 16:51:38
|
| Thanks Gail and Kristen.yes it is a Maint plan.I may change it to leave 0 % free space to see the results.The last execution of the plan caused the .LDF to grow from 33 GB to 99 GB. OuchThanks for the tips. John |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-10-03 : 17:42:56
|
| Be careful with 0%. Means that any updates that grow the row, any inserts at all cause page splits and page splits are expensive operations. Generally a lower full factor is recommended for active tables. It also has the effect of reducing the need to rebuild indexes all the time.--Gail ShawSQL Server MVP |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2011-10-03 : 19:35:14
|
quote:
Originally posted by jbates99
The last execution of the plan caused the .LDF to grow from 33 GB to 99 GB. Ouch
That may be inefficient because of the way the files have been extended. If you need an LDF that big then it might be better to manually generate it. Run:DBCC LOGINFO()which will tell you how many VLFs you have. I would think that 100 max was a reasonable target, I expect your have thousands?More info: http://www.sqlskills.com/BLOGS/KIMBERLY/post/Transaction-Log-VLFs-too-many-or-too-few.aspxI suggest you set up a Tlog backup job that runs every few minutes during the index rebuild window. Our index rebuild generates log backups of about about 1GB / minGail is right that 0% fill means that there will be page splits, but there are posts here of empirical tests that show better performance at 100% fill - provided that you have more reads than inserts - which OLTP systems generally will.Either way, setting 10% free on all indexes is going to hurt on the ones that don't need it, so it would be better to tell the maintenance Wizard to NOT change the fill-factor, reset all indexes to 100% fill, and then set specific indexes, by exception, to have some slack on those where you find that it is necessary. |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-10-04 : 05:59:06
|
quote:
Originally posted by Kristen
Gail is right that 0% fill means that there will be page splits, but there are posts here of empirical tests that show better performance at 100% fill - provided that you have more reads than inserts - which OLTP systems generally will.
OLTP systems are usually heavy insert systems. It's OLAP (data warehouse, decision support) that's primarily read and with OLAP I probably would go high fill factors. With OLTP, high for lookup tables, lower for transactional tables--Gail ShawSQL Server MVP |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2011-10-04 : 06:21:08
|
| Doesn't work for us Gail (nor Tara / Russell ... I'll dig out the thread if I can find it). Extra reads from carrying slack-space in the indexes outweight the increase cost of INSERTs.The only high-insert, low-read, tables we have are IDENTITY clustered index, and the secondary indexes (such as date) are also adding-at-one-end. If we have indexes where the keys are added at "random" they will be there for queries, so more-read-than-write. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2011-10-04 : 06:28:06
|
| http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=138072&SearchTerms=fillfactor,fill%20factor#538475 |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-10-04 : 08:23:44
|
| But that's not true for every case. I have an OLTP system I'm busy reducing the fill factor of indexes because the inserts and updates cause so many page splits that I have average 60% full pages and 80% fragmentation within a week. I'd rather configure the tables to have 20% free to start than have the page splits and resultant half empty pages.If you have inserts at the end and no updates growing the row then you can set 100% fill factor. If you don't, then it's no where near as clear cut an answer.--Gail ShawSQL Server MVP |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2011-10-04 : 08:51:19
|
| Well, we can agree to disagree. I think for a newie starting with 100% fill on everything, and then setting some slack on indexes by exception, is better than having (say) 10% slack.As such IMHO the "Set all indexes to 10% slack" option in Maintenance Wizard is the work of the devil (or maybe a summer intern ...)"If you have inserts at the end and no updates growing the row then you can set 100% fill factor"Will updates growing the row be an issue if there is a clustered index? (I'm thinking that the secondary indexes will be pointing to clustered index by key, rather than by record-location-address (sorry forgotten the correct term))But I may be overlooking a more significant side effect? |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-10-04 : 09:10:05
|
quote:
Originally posted by Kristen
Will updates growing the row be an issue if there is a clustered index? (I'm thinking that the secondary indexes will be pointing to clustered index by key, rather than by record-location-address (sorry forgotten the correct term))But I may be overlooking a more significant side effect?
Absolutely, and it's got nothing to do with the nonclustered indexes.Take a page that's almost full (50 bytes free) and run an update that updates 2 rows on that page and adds 30 bytes to the row's size (null -> non-null or larger variable-length column). Now those rows don't fit on that page, but they have to be there (because of the clustered index order), so the page splits. Allocate new page, move half the rows, link new page in. Result, one page (at least) out of order and 2 pages that are now only 50% full.Repeat a few thousand times and now my table has average 50% page usage. Not good for space, memory, read efficiency or anything else for that matter.That's why, unless I have a table that gets no updates or has only fixed, non-null data types, I prefer to leave some space free on the pages. It's not just inserts that page split.--Gail ShawSQL Server MVP |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2011-10-04 : 10:14:08
|
| Ah good point. Instructive as ever, thanks Gail.Is there a way to get a row to "reserve" space for a following update?We have one table that is promptly followed by an update (which sets some NULL column to actual values). I've thought about initialising a column to, say, 10 bytes and then clearing that at the UPDATE so that the row would still fit into the originally allocated space. However, I've not done any experiments. I don't particularly want to set the columns to "dummy" values, as I would much prefer NULL for "unknown". |
|
|
|
jbates99
Constraint Violating Yak Guru
396 Posts |
Posted - 2011-10-04 : 22:50:28
|
| Very good info here, lots to digest.As you probaly guessed, this is purchased software and the tables are not of a good design - all almost all non-PK columns are nullable.7,000 tables in the largest database. I track table growth, so I may have the M Plan set all tables to 0 free space, then as a final step set those 50 to 100 fastest-growing tables to 5 % free.thanks for your posts. John |
|
|
|
jbates99
Constraint Violating Yak Guru
396 Posts |
Posted - 2011-10-04 : 22:57:14
|
| .... 6 months ago, I replaced the Rebuild index task with Reorganize Indexes and 'Compact Large Objects' is NOT checked.But after switching from Rebuild to Reorganize I didn't see any improvement in t-log size. |
|
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2011-10-05 : 15:00:00
|
| You wouldn't see any differences in log usage for either rebuild or reorganize, unless you happen to be in simple recovery model or bulk_logged recovery model. In either of those, a rebuild operation could be minimally logged - but a reorganize will always be fully logged.Jeff |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2011-10-06 : 02:26:55
|
| I would have expected reorganise to "move / change" fewer pages than reindex? |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-10-06 : 04:54:32
|
| Depends how fragmented the index is.--Gail ShawSQL Server MVP |
|
|
|
|
Maint Plan Causes Trans Log to Explode in Size