Its poorly understood and it just surfaced again Few Days back so I thought I'd share it.....In recent blog search i came across a blog and a question or call it a sittuation here it goes.....
Situation: A SQL User keeps his indexes nicely defragemented during the day so that his range scan queries perform well. Every morning, he comes in to find the logical scan fragmentation up around is shoot up! What's going on?
Answer: automatic SHRINKDATABASE job running at night.
Logical fragmentation ( 'logical scan fragmentation') is where the physical order of pages in the leaf level of an index (clustered or non-clustered) is not the same as the logical order of the pages at the leaf level, as dictated by the index key order. In other words, to do a range scan (following the logical order of the index), the disk head has to jump back and forth following the page linkage instead of smoothly progressing in one direction.
This is critical in range scans because range scans use readahead, which are more efficient when less fragmentation is present. The SQL IO system can do 1, 8, or 32-page IOs of unbroken contiguous pages. If the index is logically fragmented, a series of 8 contiguous pages cannot be built up and so 8 x 1-page IOs have to be issued, a significant perf drop.
The act of rebuilding an index (using DBCC DBREINDEX in SQL Server 2000 or ALTER INDEX ... REBUILD in SQL Server 2005) or defragmenting an index (using DBCC INDEXDEFRAG in SQL Server 2000 or ALTER INDEX ... REORGANIZE in SQL Server 2005) removes logical fragmentation.
Shrinking a database file moves pages from the end of the allocated range of a database file to somewhere at the front of the file, below the shrink threshold. It pays no attention to any index ordering or potential fragmentation. Hence, shrink operations on data files are excellent ways to introduce significant logical fragmentation.
I've seen people who's maintenance plans are a nightly rebuild of all indexes, followed by a database shrink - total waste of time!
About the only time you should ever run shrink is when you've deleted a large chunk of data and you're not intending to reuse the space by further inserts into the database.
So - don't run shrink!
