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.







Comments

Post a Comment

Dear User,

Thank you very much for your kind response

Popular posts from this blog

Agent Installation on Windows Server. SQL Server (Failover Cluster) target addition in OEM 12c

Oracle 10g/11g Linux:SMS the alert logs ORA- errors generated in timestamp

Oracle 11g: Install Instant Client 11.2.0.3.0 on Linux x86_64 Server.