Performance Optimization & Troubleshooting with MS Dynamics NAV and MS SQL Server
Performance Optimization & Troubleshooting with MS Dynamics NAV and MS SQL ServerDBCC DBREINDEX vs. DBCC INDEXDEFRAG
The following matters only for those who still run on SQL Server 2000, as since SQL Server 2005 the DBCC DBREINDEX and DBCC INDEXDEFRAG actually have been replaced by the ALTER INDEX REBUILD/REORGANIZE command (I don’t know if the following problem also affects ALTER INDEX).
Why executing anyway?
Well, Indexes will fragment over time, as data is inserted, modified or deleted anytime when processing NAV (or other) transactions. Basically it’s the same like a hard-disk-drive could fragment. The more fragmented an index is, the longer it takes to retrieve the required data from it; the SQL Server has to perform more "Page Reads". Having too many "Reads" slows down the performance as it is more time consuming and could cause too much I/O. Hence, to avoid performance issue it is necessary to periodically maintain the indexes. Thus, "maintaining indexes" actually means defragmenting indexes.
The DBCC methods:
To maintain indexes several options would be available:
- DROP and re-CREATE the index (not recommended, not discussed here)
- Run DBCC DBREINDEX (takes care about "external fragmentation", most efficient but heavily locking, restores index fill-factors)
- Run DBCC INDEXDEFRAG (takes care about "internal fragmentation", less efficient but less blocking, compresses LOBs)
(see "Books Online" for details)
The DBCC re-indexing tasks are supported by SQL Server "Maintenance Plans", so it is easily possible to implement some kind of index maintenance. IMHO the minimum requirement is to defrag indexes at least once per week, preferably using the DBCC DBREINDEX (again, I’m talking about SQL Server 2000 here!).
But with large databases this could be a problem: the defragmentation could take very long, and when defragmenting all indexes of a database, the "Transaction Log" could grow to the size of the database itself. So, systems with small time-frames for maintenance will encounter problems, or those who perform "Transaction Log Shipping" (if the TLog backup (TRN) is supersize).
The situation:
Actually I’m blogging about this, because I encountered an issue with the DBCC thing and TLog Shipping …
… we have a system with very small "maintenance-windows", actually the databases are online and used 24/7, so we just have a couple of minutes at night; additionally we do "Log Shipping" (LS) here – in some cases over WAN with pretty small bandwidth – so it is crucial to have very small TRN files to ship. A full index-defragmentation e.g. at weekend would be impossible here (as this would screw up our LS).
What we implemented:
Firstly we have a "scheduler" where we can define which table is to be defragmented on which weekday. We set up a somewhat even distribution, means that every day of the week we have 1/7th of the NAV tables as potential defrag-candidates.
Before defragmenting, we measure the actual fragmentation degree – using the DBCC SHOWCONTIG feature – of the candidate indexes.
Now our daily SQL Agent job executes a procedure which looks up the candidates and checks the "Logical Fragmentation". Depending on it, it decides if/which method to execute:
The idea is to only defragment those indexes which really need it. The DBCC INDEXDEFRAG is supposed to be the "smoother" way of maintenance; it is less effective than DBREINDEX, but it is establishing less locks (24/7!). To control our LS we also tried to determine the "impact" of the defragmentation:
The DBCC SHOWCONTIG shows how many "Pages" an index occupies ("Leaf Node" level), so we calculate:
"Estimated Impact on TRN (MB)" = "Pages" x 8KB / 1024
(BTW: all the features described here are part of the "NAV/SQL Performance Toolbox" http://www.stryk.info/english/toolbox.html 
)
The problem:
Even though we tried to minimize the load on the TRN it frequently happened that the TLog grew way beyond our forecast! For example, some "Non-Clustered Indexes" of a table used in total about 130.000 "Pages", so our estimated max. impact is 130.000 x 8 / 1024 =~ 1GB. But the TLog usage was actually 7GB – seven-times of our estimate, and actually the double size of the whole table (data plus all indexes!). Of course, this was a disaster for our "Log Shipping" …
So the "million dollar" question was: What is causing this overload??? 
The reason:
After lots (!) of research – also involving the Microsoft Support Team (thank you!) – we found out that it is the DBCC INDEXDEFRAG which is causing the problem! Even though it is supposed to be the "softie" … . The reason is probably this: DBCC INDEXDEFRAG is operating on a smaller granularity than DBCC DBREINDEX. That’s actually an advantage, as it establishes "smaller" locks, that’s why it also called an "online" operation. But obviously the downside here is, that is also writing a lot of stuff into the TLog!
The solution:
Finally we modified the defragmentation procedure to NOT switch to the INDEXDEFRAG mode; hence our new scale is
- Index smaller than 100 "Pages" = do nothing
- "Logical Fragmentation" < 10% = do nothing
- "Logical Fragmentation" >= 10% = DBCC DBREINDEX
Since then we did never encounter the "TRN Overload Problem" again, the actually TLog usage is always below our maximum estimate. Downside is now, that we establish more locks, thus we could encounter more blocking issues …
So, why am I telling all this?
At first I want to inspire you about dealing with index maintenance. Secondly I want to point out how "tricky" this business sometimes could be, as it is a very good example that performance tuning & troubleshooting is always a matter of balance: Read vs. Write, Parallelism vs. Serialization, Locking vs. TLog Usage, REINDEX vs. DEFRAG … (or as I sometimes dare to say: the infamous choice between "Pest and Cholera") …
"The Eternal Quest for the Hay in the Needle-Stack" …
Chitrarasan's Blog 