How to return the unused index on all databases? How to save index Statistics? in the previous post i explained How to check the (unused index , duplicated index , missed index) but in one single database yesterday I updated the script of Unused index to can run on all databases exists in the server and by this new DMV I can save the result (index Statistics) in the table because if you do restart for SQL Server or do failover for the cluster you will lose the Index Statistics but by the below steps you can save it easily and return for it in any time to check all My posts and my Friends posts in SQL Server Index GO HERE
.
Steps for Saving index Statistics:
- Create Table Index_Statistics on MSDB database
USE [msdb]
GO
/****** Object: Table [dbo].[Index_Statistics] Script Date: 01/02/2015 01:58:31 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Index_Statistics](
[ID] [INT] IDENTITY(1,1) NOT NULL,
[Database_name] NVARCHAR(200),
[ObjectName] [sysname] NOT NULL,
[IndexName] [sysname] NULL,
[IndexID] [INT] NOT NULL,
[UserSeek] [BIGINT] NOT NULL,
[UserScans] [BIGINT] NOT NULL,
[last_user_seek] [DATETIME] NULL,
[last_user_scan] [DATETIME] NULL,
[UserLookups] [BIGINT] NOT NULL,
[UserUpdates] [BIGINT] NOT NULL,
[TableRows] [BIGINT] NULL,
[drop statement] [NVARCHAR](790) NULL,
[Execution_time] [DATETIME] NOT NULL,
PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON,FILLFACTOR=80,DATA_COMPRESSION=PAGE) ON [PRIMARY]
) ON [PRIMARY]
GO
- Create Stored procedure Unused index
USE msdb
Go
Create PROC UnusedIndex
AS
BEGIN
SET NOCOUNT ON
DECLARE @name NVARCHAR(200)
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
DECLARE @SQL NVARCHAR(MAX)=N’
Use ‘+@name+’
SELECT ”’+@name+”’ As Database_name,
o.name AS ObjectName
, i.name AS IndexName
, i.index_id AS IndexID
, dm_ius.user_seeks AS UserSeek
, dm_ius.user_scans AS UserScans
,dm_ius.last_user_seek
,dm_ius.last_user_scan
, dm_ius.user_lookups AS UserLookups
, dm_ius.user_updates AS UserUpdates
, p.TableRows
, ”DROP INDEX” + QUOTENAME(i.name)
+ ” ON ” + QUOTENAME(s.name) + ”.” + QUOTENAME(OBJECT_NAME(dm_ius.object_id)) as ” DROP STATEMENT ”
,GETDATE() AS Execution_time
FROM sys.dm_db_index_usage_stats dm_ius
INNER JOIN sys.indexes i ON i.index_id = dm_ius.index_id AND dm_ius.object_id = i.object_id
INNER JOIN sys.objects o on dm_ius.object_id = o.object_id
INNER JOIN sys.schemas s on o.schema_id = s.schema_id
INNER JOIN (
SELECT SUM(p.rows) TableRows, p.index_id, p.object_id
FROM sys.partitions p GROUP BY p.index_id, p.object_id
) p
ON p.index_id = dm_ius.index_id AND dm_ius.object_id = p.object_id
WHERE OBJECTPROPERTY(dm_ius.object_id,”IsUserTable”) = 1
AND dm_ius.database_id = DB_ID()
AND i.type_desc = ”NONCLUSTERED”
AND i.is_primary_key = 0
AND i.is_unique_constraint = 0
ORDER BY o.name ASC , I.name ASC ‘
EXEC SP_ExecuteSQL @SQL
FETCH NEXT FROM db_cursor INTO @name
END
CLOSE db_cursor
DEALLOCATE db_cursor
END
- Create Job to Execute the unused index (optional)
Because if you create the job I recommended to run it manually not schedule it because our target here to save the index statistics before restart the server or failover it so after this happened I should not overwrite on my data exists in index Statistics table .
Follow the author:
View all my tips , LinkedIn , Website , Slideshare
DB Cloud TECH 