This view shows most of the information necessary for
determining if you should REBUILD (formerly DBCC Reindex) or REORGANIZE
(formerly DBCC IndexDefrag) your indexes. This view
is a dependency of vwIndexPhysicalStatsTableSize.
USE Admin
IF
Object_ID('dbo.CreateVwIndexPhysicalStats') Is Not Null
DROP PROC dbo.CreateVwIndexPhysicalStats
go
CREATE PROC dbo.CreateVwIndexPhysicalStats
@Mode varchar(20) = 'Limited',
@ShowSql bit = 0,
@ShowSysObjects bit = 0
AS
/* DATE AUTHOR REMARKS
3/3/13 PPaiva Intial creation.
DESCRIPTION
Creates
a view in the Admin database to see
fragmentation
stats for all objects in all databases
on
this server.
System
objects are excluded by default;
use
@ShowSysObjects = 1 to show them.
Coded
to use Convert(varchar, ServerProperty('ServerName'))
rather
than @@ServerName since the latter can be erroneous.
USAGE
Exec
Admin..CreateVwIndexPhysicalStats
Exec
Admin..CreateVwIndexPhysicalStats 'Limited'
Exec
Admin..CreateVwIndexPhysicalStats 'Limited', 1
Exec
Admin..CreateVwIndexPhysicalStats 'Limited', 1, 1
Exec
Admin..CreateVwIndexPhysicalStats 'Sampled'
Exec
Admin..CreateVwIndexPhysicalStats 'Sampled', 1
Exec
Admin..CreateVwIndexPhysicalStats 'Sampled', 1, 1
Exec
Admin..CreateVwIndexPhysicalStats 'Detailed'
Exec
Admin..CreateVwIndexPhysicalStats 'Detailed', 1
Exec
Admin..CreateVwIndexPhysicalStats 'Detailed', 1, 1
DEBUG
SELECT
*
FROM
vwIndexPhysicalStats
WHERE
DB = 'Admin'
ORDER
BY 1, 2, 3, 4
--
Cannot rebuild
SELECT
*
FROM
vwIndexPhysicalStats
WHERE
DB = 'Admin'
AND (AllowPgLocks = 0 OR IsDisabled = 1)
ORDER
BY 1, 2, 3, 4
SELECT
*
FROM
sys.databases
*/
SET NOCOUNT ON
DECLARE @s varchar(max),
@DB varchar(100),
@MaxDB varchar(100),
@i int,
@sNow varchar(16)
SET @sNow =
Convert(varchar(16), GetDate(), 120)
SET @s =
'IF Object_ID(''dbo.vwIndexPhysicalStats'') Is Not Null
DROP
VIEW dbo.vwIndexPhysicalStats'
IF
@ShowSql = 1
Print @s
Exec(@s)
Print ''
SET @s =
'CREATE VIEW vwIndexPhysicalStats
AS
/* DATE AUTHOR REMARKS
' + Convert(varchar(10), GetDate(), 01) + ' PPaiva Initial
creation.
DESCRIPTION
Provides
a list of physical stats of all indexes in all databases
on
this instance.
This
view is auto-generated via executing
Admin.dbo.CreateVwIndexPhysicalStats.
If
a new database is added this view won''t show it unless
you
run the refresh code below.
If
a database is deleted this view will malfunction unless
you
run the refresh code below.
To
REFRESH VIEW when new databases are added:
Exec
Admin.dbo.CreateVwIndexPhysicalStats
--
Sample of view
SELECT
TOP 100 *
FROM
Admin.dbo.vwIndexPhysicalStats
ORDER
BY PercFrag desc
*/
'
-- Will refer to these system
databases more than once,
-- so load into table for easy
reference.
CREATE TABLE #SysDB(
Name varchar(30) NOT NULL
)
INSERT INTO #SysDB
VALUES ('master')
INSERT INTO #SysDB
VALUES ('tempdb')
INSERT INTO #SysDB
VALUES ('model')
INSERT INTO #SysDB
VALUES ('msdb')
INSERT INTO #SysDB
VALUES ('Admin')
INSERT INTO #SysDB
VALUES ('distribution')
SELECT name
INTO #DBs
FROM sys.databases
WHERE state_desc =
'online'
IF
@ShowSysObjects =
0
DELETE #DBs
FROM #DBs d
JOIN #SysDB s
ON s.Name = d.name
SELECT @MaxDB
= Max(Name),
@DB = '',
@i = 0
FROM #DBs
DECLARE @IsSystemDB char(1)
WHILE @DB <
@MaxDB
BEGIN
SET @i = @i + 1
SELECT @DB = Min(Name)
FROM #DBs
WHERE Name > @DB
IF @DB In (SELECT Name FROM #SysDB)
SET @IsSystemDB = '1'
ELSE
SET @IsSystemDB = '0'
SET @s = @s + '
SELECT
'
SET @s = @s + 'Convert(varchar,
ServerProperty(''ServerName'')) as Server,
''' + @DB + ''' as DB,
s.name
COLLATE SQL_Latin1_General_CP1_CI_AS SchemaName,
o.Name
COLLATE SQL_Latin1_General_CP1_CI_AS ObjName,
i.name
COLLATE SQL_Latin1_General_CP1_CI_AS IndexName,
Convert(numeric(5,1),
dmv.avg_fragmentation_in_percent) AS PercFrag,
dmv.fragment_count
AS TotalFrags,
Convert(numeric(5,1),
dmv.avg_fragment_size_in_pages) AS PagesPerFrag,
dmv.page_count
AS NumPages,
dmv.partition_number
PartNum,
dmv.index_type_desc
IndexType,
dmv.alloc_unit_type_desc
AllocDesc,
i.is_unique
IsUniq,
i.is_primary_key
IsPK,
i.is_unique_constraint
IsUniqCon,
dmv.index_depth
IndexDepth,
dmv.index_level
IndexLevel,
i.is_disabled
IsDisabled,
i.allow_page_locks
AllowPgLocks,
i.allow_row_locks
AllowRowLocks,
dmv.object_id,
dmv.database_id,
''' + @sNow + ''' ViewCreateDate
FROM
sys.dm_db_index_physical_stats(db_id('''
+ @DB + '''), Null, Null, Null, '''
+ @Mode + ''') dmv
JOIN [' + @DB
+ '].sys.indexes i
ON i.object_id = dmv.object_id
AND
i.index_id = dmv.index_id
JOIN [' + @DB
+ '].sys.objects o
ON
o.object_id = dmv.object_id
JOIN [' + @DB
+ '].sys.schemas s
ON
s.schema_id = o.schema_id
'
IF @DB <> @MaxDB
SET @s = @s + ' UNION ALL '
END
IF
@ShowSql = 1
BEGIN
Print @s
END
Exec (@s)
