About Me

My photo
Colorado
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 developer’s approach to SQL Server administration, and has been the president of the Boulder SQL Server Users’ Group from January 2009 to present.

Tuesday, December 17, 2019

vwFragmentation


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.

IF OBJECT_ID('dbo.vwFragmentation') Is Not Null
       DROP VIEW dbo.vwFragmentation
GO
CREATE VIEW dbo.vwFragmentation
AS
/*     DATE          AUTHOR        REMARKS
       12/17/19      PPaiva        Initial creation.
      
       SELECT TOP 1000 *
       FROM vwFragmentation
       WHERE DB = 'MyDB'
         AND TotalMB > 100
       ORDER BY PercFrag desc
      
*/
SELECT  i.Instance,
              i.DB,
              t.Rows,
              t.TotalMB,
              PercFrag,
              i.SchemaName,
              ObjName,
              TotalFrags,
              PagesPerFrag,
              NumPages,
              IndexName,
              PartNum,
              IndexType,
              AllocDesc,
              IsUniq,
              IsPK,
              IsUniqCon,
              IndexDepth,
              IndexLevel,
              IsDisabled,
              AllowPgLocks,
              AllowRowLocks,
              i.object_id,
              database_id,
              i.ViewCreateDate,
              Convert(varchar(16), GetDate(), 120) InsertDate
FROM dbo.vwIndexPhysicalStats i
JOIN vwTable t
       ON t.Instance = i.Instance
       AND t.DB = i.DB
       AND t.TableName = i.ObjName

GO