Microsoft SQL Server 2005/2008:Gather statistics to collect the information of unused indexes details.
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
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
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.
Good information about database experts.
ReplyDeleteMicrosoft Access to SQL Server Migration