Microsoft SQL Server 2005/2008:Gather statistics to collect the information of unused indexes details.



1.Create table to gather data in daily basis.

USE [master]
GO

/****** Object:  Table [dbo].[index_stats_hist]    Script Date: 08/06/2012 11:11:21 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[index_stats_hist](
    [Dt] [datetime] NULL,
    [Database_Name] [varchar](100) NULL,
    [ObjectName] [varchar](100) NULL,
    [IndexName] [varchar](100) NULL,
    [Index_id] [int] NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

2.Create Stored Procedure to collect information of unused indexes details.


USE [master]
GO


/******************************************************************************/   
/* Copyright: 2012 Database Experts   
*    
* Name                    : Archive: [Get_Unused_Index_Info] $   
* Version                : Revision: 1    
* Last Updated            : Modtime:  2012-07-20
* Created By            : $Author:Bhavesh Thakkar
* Modified By            : 
* Script      : Collect the statistics for sql server indexes

* Version Info:   
*   No.   Author            Date       Comments   
*   ---   ------            ----       --------   
*   1 *   Bhavesh Thakkar  2012-08-07                    
*/   
/*****************************************************************************/



SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Procedure [dbo].[Get_Unused_Index_Info] as

begin

DECLARE @dbid INT,            --Get DBID
        @name VARCHAR(50),    -- database name  
        @sql varchar(1000)
DECLARE db_cursor CURSOR FOR  
SELECT  name  
FROM master.dbo.sysdatabases  
WHERE name  not IN ('master','model','msdb','tempdb')  

 
OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @name   
 
WHILE @@FETCH_STATUS = 0   

BEGIN   


SET @sql='USE '+@name+ ' insert into master.dbo.index_stats_hist
SELECT getdate(),DB_NAME(),
OBJECTNAME = OBJECT_NAME(I.OBJECT_ID),
INDEXNAME = I.NAME,
I.INDEX_ID
FROM SYS.INDEXES I
JOIN SYS.OBJECTS O
ON I.OBJECT_ID = O.OBJECT_ID
WHERE OBJECTPROPERTY(O.OBJECT_ID,''IsUserTable'') = 1
AND I.INDEX_ID NOT IN (
SELECT S.INDEX_ID
FROM SYS.DM_DB_INDEX_USAGE_STATS S
WHERE S.OBJECT_ID = I.OBJECT_ID
AND I.INDEX_ID = S.INDEX_ID
AND DATABASE_ID = DB_ID(DB_NAME()))
ORDER BY OBJECTNAME,
I.INDEX_ID,
INDEXNAME ASC'

EXEC(@sql)

FETCH NEXT FROM db_cursor INTO @name  

END


 
CLOSE db_cursor   
DEALLOCATE db_cursor 
 
end

3.Schedule to daily basic to collect the information of indexes.







1 comment:

Dear User,

Thank you very much for your kind response