/*
EXECUTE admin.dbo.indexMaintenancePlan alerts
select * from admin.dbo.fraglist
truncate table admin.dbo.fraglist
drop table admin.dbo.fraglist
select cast(getdate() as varchar(12))


exec master.dbo.sp_msforeachDB 'if ''?'' not in (''admin'',''master'',''model'',''tempdb'')
begin
print ''?''
exec admin.dbo.MakeIndexMaintenancePlan ''?''
end'

*/
use admin
drop procedure dbo.MakeIndexMaintenancePlan
go
create procedure dbo.MakeIndexMaintenancePlan
	@DatabaseName varchar(50)
as
/*******************************************************************************************************
*	admin.dbo.indexMaintenancePlan
*	Creator: Bill Wunder	
*	Date: 	5/7/2002
*	Project:	Database administration
*	Project Mgr:	
*	Dev Contact:	
*	Outline:	Identify which indexes need to be maintained and which maintenance operation to
*		        use. If the scan density is less than 90% maintain the index. If the index has
*			over 10000 paged defrag it otherwise reindex it. If a table has a clustered index
*                       that will be maintained by a reindex do not maintain other indexes because they
*                       will be rebuilt when the clustered index is reindexed. Keep all fragmentation 
*			history for further analysis.
*
* Statistic 			Description 
* Pages Scanned			Number of pages in the table or index. 
* Extents Scanned 		Number of extents in the table or index. 
* Extent Switches 		Number of times the DBCC statement moved from one extent to another 
*				while it traversed the pages of the table or index. 
* Avg. Pages per Extent 	Number of pages per extent in the page chain. 
* Scan Density 			[Best Count: Actual Count] Best count is the ideal number of extent 
*				changes if everything is contiguously linked. Actual count is the actual 
*				number of extent changes. The number in scan density is 100 if everything 
*				is contiguous; if it is less than 100, some fragmentation exists. Scan 
*				density is a percentage. 
* Logical Scan Fragmentation 	Percentage of out-of-order pages returned from scanning the leaf pages of 
*				an index. This number is not relevant to heaps and text indexes. An out of 
*				order page is one for which the next page indicated in an IAM is a 
*				different page than the page pointed to by the next page pointer in the 
*				leaf page. 
* Extent Scan Fragmentation 	Percentage of out-of-order extents in scanning the leaf pages of an index. 
*				This number is not relevant to heaps. An out-of-order extent is one for 
*				which the extent containing the current page for an index is not physically 
*				the next extent after the extent containing the previous page for an index. 
* Avg. Bytes free per page 	Average number of free bytes on the pages scanned. The higher the number, 
*				the less full the pages are. Lower numbers are better. This number is also 
*				affected by row size; a large row size can result in a higher number. 
* Avg. Page density (full) 	Average page density (as a percentage). This value takes into account row 
*				size, so it is a more accurate indication of how full your pages are. The 
*				higher the percentage, the better. 
*
* status 
*	0 - under consideration
*	1 - defrag
*	2 - defrag in progress
*	3 - defrag complete
*	4 - reindex
*	5 - reindex in progress
*	6 - reindex complete	
*	9 - eliminated from consideration
*     +10 - historical 
*
*	usage:		EXECUTE admin.dbo.indexMaintenancePlan admin
*	Notes:		
*			
*	Modifications   
*                       
********************************************************************************************************/
declare @sqlstr nvarchar(4000)
declare @ObjectOwner varchar(128), 
	@ObjectName varchar(255),
	@IndexName varchar(255)

if object_id('admin.dbo.fraglist','U') is null
	CREATE TABLE admin.dbo.fraglist (
	   DatabaseName varchar (128),
	   ObjectOwner varchar(128),		
	   ObjectName varchar (255),
	   ObjectId int,
	   IndexName varchar(255),
	   IndexId int,
	   Lvl int,
	   CountPages int,
	   CountRows int,
	   MinRecSize int,
	   MaxRecSize int,
	   AvgRecSize int,
	   ForRecCount int,
	   Extents int,
	   ExtentSwitches int,
	   AvgFreeBytes int,
	   AvgPageDensity int,
	   ScanDensity decimal,
	   BestCount int,
	   ActualCount int,
	   LogicalFrag decimal,
	   ExtentFrag decimal,
	   RecCreatedDt datetime default getdate(),
	   Status tinyint default 0,
	   constraint pkc_fraglist__DatabaseName__ObjectOwner__ObjectName__IndexName_RecCreatedDt 
		primary key clustered (DatabaseName, ObjectOwner, ObjectName, IndexName, RecCreatedDt))


set nocount on

update admin.dbo.fraglist
set status = status + 10
where DatabaseName = @DatabaseName
and status < 10

select @sqlstr = 'use ' + @DatabaseName + '
declare @table_id int
declare @table varchar(100)

if object_id(''tempdb.dbo.#fraglist'',''U'') is not null
	drop table #fraglist
CREATE TABLE #fraglist (
   ObjectName varchar (255),
   ObjectId INT,
   IndexName varchar (255),
   IndexId INT,
   Lvl INT,
   CountPages INT,
   CountRows INT,
   MinRecSize INT,
   MaxRecSize INT,
   AvgRecSize INT,
   ForRecCount INT,
   Extents INT,
   ExtentSwitches INT,
   AvgFreeBytes INT,
   AvgPageDensity INT,
   ScanDensity DECIMAL,
   BestCount INT,
   ActualCount INT,
   LogicalFrag DECIMAL,
   ExtentFrag DECIMAL)

set nocount on

select @table_id = min(id) from dbo.sysobjects where type = ''U''
while @table_id is not null
	begin
		select @table = object_name(@table_id)
select @table
		if @table not in (''dtproperties'')
			insert #fraglist exec (''dbcc showcontig('' + @table + '') WITH TABLERESULTS, ALL_INDEXES'')
		select @table_id = min(id) from dbo.sysobjects where type = ''U'' and id > @table_id
	end		

insert admin.dbo.fraglist (
		DatabaseName,
		ObjectOwner,
		ObjectName,
		ObjectId,
		IndexName,
		IndexId,
		Lvl,
		CountPages,
		CountRows,
		MinRecSize,
		MaxRecSize,
		AvgRecSize,
		ForRecCount,
		Extents,
		ExtentSwitches,
		AvgFreeBytes,
		AvgPageDensity,
		ScanDensity,
		BestCount,
		ActualCount,
		LogicalFrag,
		ExtentFrag,
		Status)
select db_name(), 
	user_name(objectproperty(ObjectId, ''OwnerId'')),
	ObjectName,
	ObjectId,
	IndexName,
	IndexId,
	Lvl,
	CountPages,
	CountRows,
	MinRecSize,
	MaxRecSize,
	AvgRecSize,
	ForRecCount,
	Extents,
	ExtentSwitches,
	AvgFreeBytes,
	AvgPageDensity,
	ScanDensity,
	BestCount,
	ActualCount,
	LogicalFrag,
	ExtentFrag,
	Status = case 	when IndexId = 0 -- heap
				then 9 
			when IndexId = 1 -- clustered index
				then case when ScanDensity > 90
						then 9
						else case when CountPages > 10000
								then 1 -- defrag 
								else 4 -- reindex
								end
						end
			when IndexId between 1 and 254 -- nonclustered index
				then case when 	(select count(1) -- clustered will be defragged
						from #fraglist 
						where ObjectId = ObjectId
						and IndexId = 1
						and ScanDensity < 90
						and CountPages > 10000) = 1
						and ScanDensity < 90 -- this index needs attention	
						then 1
					when 	(select count(1) -- clustered will be reindexed
						from #fraglist 
						where ObjectId = ObjectId
						and IndexId = 1
						and ScanDensity < 90
						and CountPages < 10000) = 1
						then 9 
					else -- no clustered index on table
						case when ScanDensity > 90
							then 9
							else case when CountPages > 10000
									then 1 -- defrag 
									else 4 -- reindex
									end
							end
					end
			else 9 -- IndexId = 0 for the heap IndexId = 255 for text column pointer  
			end
from #fraglist'
exec sp_executesql @sqlStr
/*

declare defragcur cursor for
select DatabaseName, ObjectOwner, ObjectName, IndexName
from admin.dbo.fraglist
where DatabaseName = @DatabaseName
and status in (1)
open defragCur
*/