December 11, 2008 at 1:07 pm
Hi all,
this is the scenario (I apologize for the length of that story, but I'd like to be detailed, not missing anything):
MS SQL Server 2000 STD x86 (Build 2040)
(MS Dynamics NAV 3.70 - but doesn't matter here)
My customer has to perform "Log shipping" over WAN, having just small bandwidth; thus it is crucial for us to have very small TRN files (Transaction Log Backups) for sending. Once the TRN was generated, we compress before sending and uncompress on remote site before restoring ...
Of course we have to maintain the tables/indexes to avoid performance problems here. We have set up this process:
- We have a scheduler where we can define which table should be reindexed on which weekday thus we spread the load over the full week (NOT having a single maintenance job working on the whole database at once, as this would screw up our LS)
- Before defragmenting we run a DBCC SHOWCONTIG on the tables, saving the fragmenation degree (LogicalFragmentation) into a dedicated table
- When maintaining the indexes, our procedure decides depending on the fragmentation:
< 10% - do nothing
>= 10% & < 30% - run DBCC INDEXDEFRAG
>= 30% - run DBCC DBREINDEX
If the index (leaf nodes) occupies less than 100 pages it will be also ignored.
Hence, the idea is to spread the workload over several days and to minimize the actual index-defragmentation workload; thus we should have minimal impact on the TRN.
But this strange thing happens frequently:
For example, a heavily used table (like "Delivery Entry") frequently fragments and requires periodic maintenance. The total table size (sp_spaceused; data + index) is about 3GB.
We check the fragmentation degree and we get about 10 canditate indexes (non-clustered) from this table.
The fragmentation there is >= 10% & < 30 thus we run a DBCC INDEXDEFRAG
When summing the "Pages" (from DBCC SHOWCONTIG) of all those affected indexes we get about 130.000, thus we expect an "impact" on the Transaction Log of about 1GB (130.000 pages x 8kB = 1GB).
"Impact" means the amount of data added to the TLog, hence the growth of the used TLog size (not physical file size!) - that's the size we expect for our TRN.
(If we would run a full DBCC DBREINDEX on the whole table our expectation is max. 3GB (size of the table) of the TRN)
But when executing the DBCC INDEXDEFRAG on those 10 indexes, the TLog fills with about 7GB, which is 7 times our expectation for the index candidates, and twice our max. estimate for the whole table :w00t:
And when this weird thing happens this IS a problem for out Log Shipping!
It is granted that NO other transactions run during this maintenance, no auto growth happening, no nothing - we run this alone on the database.
So the questions are: What is causing this huge load? How can we avoid this? How can we determine the impact on the TLog when reindexing? What's going on?
Problem is, that this does not happen always - there is no obvoius reason or incident or pattern etc.; mostly the process behaves as we expect. But once in a while we get such a super-load which causes a sever problem for the LS ...
The table mentioned above is just an example, this happens on other large tables, too.
I appreciate all kind of hints, suggestions or comments - thanks in advance!
Best regards,
Jörg
Jörg A. Stryk
MVP - MS Dynamics NAV
December 15, 2008 at 12:16 pm
Not sure if changing the recovery model of the database to bulk logged would help in any way, as you are shipping the logs, but if the reindex job finishes fast it would be worth trying to change the recovery model..
Thanks!!
The_SQL_DBA
MCTS
"Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."
December 15, 2008 at 1:06 pm
Thanks for your reply. I'll try the BULK thing and come back with the results ASAP!
Jörg A. Stryk
MVP - MS Dynamics NAV
December 29, 2008 at 4:45 am
Hi!
So, finally we found out what was causing this "overload": it always happens when the DBCC INDEXDEFRAG was executed!
We supposed the DEFRAG being the "smoother" (but less effective) one, but obviously that's not completely true. DEFRAG is causing less locking issues as it is working on a smaller granularity than DBCC DBREINDEX, but it seems that exactly this "small granularity" causes this huge amout of TLog data ...
We did not test if the BULK LOGGED Recovery Model would solve this, but we now simply always run the REINDEX! Since that, the "impact" on the TLog is always below our "estimated maximum" (Pages x 8 / 1024), we do not have any exceptional overloads anymore! Thus, we could give a relyable forecast about the TRN sice to control our Log Shipping, preventing any trouble!
The downside is, that with always perfroming a full REINDEX we increase the probability of encountering blocking conflicts, but so far that's just a theoretical risk ...
HAPPY NEW YEAR! 😛
Jörg
Jörg A. Stryk
MVP - MS Dynamics NAV
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply