vwFragmentation

A developer's programmatic approach to SQL Server administrative tasks. Paul has 18 years experience with Microsoft SQL Server. He has worked in the roles of production DBA, database developer, database architect, applications developer, business intelligence and data warehouse developer, and instructor for students aspiring for MCDBA certification... . He has performed numerous data migrations and supported large databases (3 Terabyte, 1+ billion rows) with high transactions. He is a member of PASS, blogs about lessons learned from a developers approach to SQL Server administration, and has been the president of the Boulder SQL Server Users Group from January 2009 to present. To compare fragmentation before and after running a defrag, you can use this code. The vwFragmentation view contains NumPages, Rows, and TotalMB so you have all you need to make your judgement. Recall that TotalMB is more important that Rows. This is a comprehensive view for fragmentation, together with size in bytes (from vwTable). Keep in mind that when assessing fragmentation, we are concerned with larger tables in bytes, not necessarily larger tables in rows. This is because we are concerned with disk I/O. Although a tall skinny table with millions of rows may appear as a candidate to scrutinize, it is possible for a table with far fewer rows to incur more disk I/O if it is a wide table. This view works well for instances that do not have too many databases (say under 20). If there are 50-100+ databases, this becomes slower. For those, I have another solution which will be posted next month.Read More