Index Statistics for all Databases Exists on your Server


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 

Advertisements

About Mustafa EL-Masry

I love fixing problems and learning new technologies that can help me fix those problems faster and better than I did the time before. In my spare time I like to read, speak, write blogs and learn new things. I currently work as a Principal Database Administrator where I solve fairly interesting problems on fairly large databases. And troubleshooting High sensitive Performance Cases ► I am Microsoft Certified in SQL Server Administration and Development 2008 , 2012 MCTS, MCTIP , MCSA , MCSE ► I have deep practical knowledge about T-SQL performance , HW Performance issues, Data Warehousing and data mart solutions , SQL Server Replication, Clustering solutions (Active Active and active passive)and Database Designs for different kinds of systems , HAG, I worked on All SQL Server Versions (2005,2008,2008R2,2012,2014) diving deeply more check the below information about me : ► Founder of Community :SQL Server Consultation :http://MostafaElmasry.com/ ► Co-Founder : http://www.SQLGULF.net ► Audience Marketing Manager and Executive Board member: SQLSERVER PERFORMANCE TUNING http://sqlserver-performance-tuning.net/ ► LinkedIn: https://www.linkedin.com/in/mostafaelmasry ► My Community Annual report : http://mostafaelmasry.com/2014/annual-report/ ► I am Technical Writer and Reviewer : http://www.slideshare.net/MostafaElmasry3/ ► Fluent Participator at Microsoft Forums of SQL Server at http://Social.technet.microsoft.com ► +200 blogs in SQL Server technology English and Arabic: http://mostafaelmasry.com/community-founder/all-tips/ ► Microsoft Profile: https://www.mcpvirtualbusinesscard.com/VBCServer/EngMostafaElamsry/profile For any inquiries you can contact me on my phone or by mail: Mobile: +966 543990968, +2 01114668821 Mail: dbconsultant@mostafaelmasry.com & SQLGULF@mostafaelmasry.com
Gallery | This entry was posted in Home. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s