If your not monitoring your indexes on a regular basis shame on you, you should be. Depending on how many databases you are supporting this could end up being extremely tiedious. So here is what I do and you can tailor the process to your env.
1) Each production server I support has a DBA database on it.
2) Create the table IndexStats in each of the DBA databases (scripts below)
3) Create a job with the script below to capture the index statisics . (I do it once a week)
4) Now here it’s up to you on how you want to do this. I have a huge number of servers that I support so I transfer the statistics from each individual server to a central repository. I use an SSIS package which connects to the table in each database and dumps it into a table of the same format on my repository. The SSIS package uses Simple Data flows to do this and is pretty straight forward. I’m not going to go into the package in this post. If you need assistance on creating data flows in SSIS there are plenty of resources out there.
5) Create the report stored procedure to email you indexes which need to be rebuilt and schedule it based on your needs. I run it once a week then rebuild the indexes that weekend on the server in questions.
Script 1 Create Table to hold your statistics in your DBA database.
[SQLInstance] [varchar](160) NULL,
[DatabaseName] [varchar](160) NULL,
[TableName] [varchar](160) NULL,
[IndexName] [varchar](160) NULL,
[IndexType] [varchar](160) NULL,
[Databaseid] [smallint] NULL,
[object_id] [int] NULL,
[index_id] [int] NULL,
[user_seeks] [bigint] NULL,
[user_scans] [bigint] NULL,
[user_lookups] [bigint] NULL,
[user_updates] [bigint] NULL,
[last_user_seek] [datetime] NULL,
[last_user_scan] [datetime] NULL,
[last_user_lookup] [datetime] NULL,
[last_user_update] [datetime] NULL,
[system_seeks] [bigint] NULL,
[system_scans] [bigint] NULL,
[system_lookups] [bigint] NULL,
[system_updates] [bigint] NULL,
[last_system_seek] [datetime] NULL,
[last_system_scan] [datetime] NULL,
[last_system_lookup] [datetime] NULL,
[last_system_update] [datetime] NULL,
[rows] [int] NULL,
[StatisticsCaptured] [date] NULL,
[avg_fragmentation_in_percent] [float] NULL,
[page_count] [int] NULL,
[Index_Depth] [int] NULL,
[Index_level] [int] NULL,
[Avg_record_Size_in_bytes] [int] NULL
) ON [PRIMARY]GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[IndexStats] ADD DEFAULT (getdate()) FOR [StatisticsCaptured]
GO
Script 2 Schedule this script from SQL Agent to populate your IndexStats Table. I delete data from this table since my data is moved into a central repository but if you are not doing that then you can remove the delete part of the script
exec
sp_msforeachdb
‘use ?
DECLARE @dbid int
select @dbid = db_id(”?”)
select
DISTINCT
@@SERVERNAME as SQLInstance,
”?” as DB,
OBJECT_NAME(a.object_id) as [TableName],
B.name as [Index],
B.type_desc as [IndexType],
A.*,
p.rows,
getdate(),
OS.avg_fragmentation_in_percent,
OS.page_count,
OS.Index_Depth,
OS.Index_level,
OS.Avg_record_Size_in_bytes
FROM ?.sys.dm_db_index_usage_stats a
INNER JOIN ?.sys.indexes B on a.object_id = B.object_id and a.index_id = B.index_id
inner join ?.sys.partitions p on B.object_id =p.object_id and B.index_id =p.index_id
INNER JOIN ?.sys.dm_db_index_physical_stats(@dbid,NULL,NULL,NULL,”SAMPLED”) OS on
A.object_id = OS.Object_id and A.index_id =OS.Index_id
WHERE
OBJECT_NAME(a.object_id) not like ”sys%” and OBJECT_NAME(a.object_id) not like ”spt%”
and OBJECT_NAME(a.object_id) not like ”queue%”
and a.database_id = DB_ID(”?”)
order by B.namedelete from MyDBADB..IndexStats
where StatisticsCaptured > GETDATE() -14
‘
Script 3 Create the following stored procedure on your repository and schedule it to run as you see fit. This will alert you on the indexes with Fragmentation percent greater then 50.
/******************************************************************************************************
* Created By: MotherofAllGeeks.Com
* Created on: 3/2011
* Purpose gather space information for trending or possibly alerting
* Create it in master and it will gather stats for all databases
* Sample Call proc_getdatabasespace_r
* Prereq is that you must have a DBADB and you must have created FileStats table in MyDBADb.
* exec [proc_GetFragmentationReport_r] 7,’Mom@motherofallGeeks.com’, ‘MyMailProfile’
******************************************************************************************************/
CREATE PROCEDURE [dbo].[proc_GetFragmentationReport_r]
@days int ,
@recipients varchar(255),
@profile varchar(255)
AS
BEGIN
SET NOCOUNT ON
BEGIN TRY
BEGIN
DECLARE @tableHTML NVARCHAR(MAX), @currentDate date, @usagepercentage int
SET @tableHTML =
N’
| SQLInstance |
|---|
‘
IF (@tableHTML is not null)
BEGIN
declare @subject varchar(55)
SELECT @subject = ‘Fragmented Indexes ‘
exec msdb.dbo.sp_send_dbmail
@profile_name = @profile,
@recipients = @recipients,
@subject = @subject,
@body = @tableHTML,
@body_format = ‘HTML’
END
END
END TRY
BEGIN CATCH
DECLARE @ErrorMessage varchar(max)
SELECT @ErrorMessage =
‘ErrorNumber:’ + convert(varchar(7),ERROR_NUMBER()) + ‘
‘ +
‘ErrorSeverity:’ + convert(varchar(7),ERROR_SEVERITY()) +’
‘+
‘ErrorState:’ + convert(varchar(7),ERROR_STATE()) +’
‘+
‘Calling Procedure:’ + CONVERT(VARCHAR(25), OBJECT_NAME(@@PROCID))+ ‘
‘+
‘ErrorProcedure:’+ ERROR_PROCEDURE() +’
‘+
‘ErrorLine:’ + convert(varchar(7),ERROR_LINE()) +’
‘+
‘ErrorMessage:’ + ERROR_MESSAGE() +’
‘
RAISERROR(@ErrorMessage,16,1)
END CATCH
END
GO
Posted by motherofallgeeks 
