H

I am currently re-writing an overnight index defrag procedure and would like to audit indexes in my database - logging the before defrag action" avg fragmentation value and "after defrag action" frag value in an audit table.  This will be for all databases on the server.  I have completed the vast majority of it (cycling though all the databases, detecting which indexes need reorganising or rebuilding and inserting the information into a table) but I cannot get the audit values working properly.  For example, a sample row in my audit table would look like this:

ID   Name   DB   Table   frag_before   frag_after

1    Index2 DB1  Table6  70.33456      0.03
2    Index7 DB1  Table9  45.98         1.2567
etc

Any idea on how to accomplish this?  Some sample code would be useful.

Thanks in advance.

• Edited by warnerrj79 9 hours 11 minutes ago

You need to have an audit table and full fill before and after rebuild index

INSERT INTO Test 
SELECT ROW_NUMBER () OVER (ORDER BY s.OBject_id) FROM sys.objects s CROSS JOIN sys.objects s1


-----make some fragmentation
UPDATE Test SET col =col+80000 WHERE col BETWEEN 5000 AND 10000

 
SELECT avg_fragmentation_in_percent,object_id,index_id INTO Audit_Index FROM sys.dm_db_index_physical_stats
( 
  DB_ID('ArticlesX'),
  OBJECT_ID('dbo.Test'),
  1,
  NULL,
  NULL
);
----46.6666666666667

ALTER INDEX PK_Test ON Test
REBUILD;
GO
INSERT INTO Audit_Index
SELECT avg_fragmentation_in_percent,object_id,index_id FROM sys.dm_db_index_physical_stats 
( 
  DB_ID('ArticlesX'),
  OBJECT_ID('dbo.Test'),
  1,
  NULL,
  NULL
); 


SELECT OBJECT_NAME(s.object_id),
MAX(A.avg_fragmentation_in_percent) before,
MIN(A.avg_fragmentation_in_percent) after

 FROM Audit_Index  s JOIN Audit_Index A ON A.object_id=s.object_id AND A.index_id=s.index_id
 GROUP BY OBJECT_NAME(s.object_id)