Microsoft® SQL Server® 2012 Bible

990

Part VII: Monitoring and Auditing

SELECT @collector_type_uid = collector_type_uid FROM syscollector_collector_

types

WHERE name = N'Generic T-SQL Query Collector Type';

DECLARE @collection_item_id int;

EXEC sp_syscollector_create_collection_item

@name= N'Clustered Index - Fragmentation Percentages',

@parameters=N'

<ns:TSQLQueryCollector xmlns:ns="DataCollectorType">

<Query>

<Value>DECLARE @DB_ID int

, @DB_Name varchar(100)

, @Query nvarchar(max) = ' '

DECLARE @TblOutput table (DBName varchar(100), TableName varchar(100),

IndexName varchar(100), AvgFragmentationPct real)

DECLARE DB_Cursor CURSOR FOR

SELECT database_id, name

FROM sys.databases where db_name(database_id) NOT IN ('master', 'tempdb',

'model', 'msdb')

OPEN DB_Cursor

FETCH NEXT FROM DB_Cursor INTO @DB_ID, @DB_Name;

WHILE @@FETCH_STATUS = 0

BEGIN

SET @Query=

'SELECT

db_name('+convert(varchar(100),@DB_ID)+') DBName

,Object_Name(IPS.object_id, '+convert(varchar(100),@DB_ID)+') TableName

,IDX.name IndexName

,avg_fragmentation_in_percent AvgFragmentationPct

FROM

sys.dm_db_index_physical_stats('+convert(varchar(100),@DB_ID)+', NULL, NULL,

NULL, NULL) IPS

INNER JOIN '+'['+@DB_Name+']'+'.sys.indexes IDX

ON IDX.object_id=IPS.object_id AND IDX.index_id=IPS.index_id

WHERE

db_name(database_id) NOT IN (''master'', ''tempdb'', ''model'', ''msdb'')

AND index_type_desc = ''CLUSTERED INDEX''

AND avg_fragmentation_in_percent>10

AND database_id = '+convert(varchar(100),@DB_ID)+';'

INSERT INTO @TblOutput

( DBName

,TableName

c43.indd 990c43.indd 990 7/31/2012 10:18:46 AM7/31/2012 10:18:46 AM

http://www.it-ebooks.info