find out access pattern of all tables in all user database

Index usage:

WITH    IndexSize
          AS ( SELECT   OBJECT_NAME(I.object_id) AS table_name ,
                        I.name AS index_name ,
                        8 * SUM(A.used_pages) / 1024 AS index_size_mb
               FROM     sys.indexes I
                        JOIN sys.partitions P ON P.object_id = I.object_id
                                                 AND P.index_id = I.index_id
                        JOIN sys.allocation_units A ON A.container_id = P.partition_id
               GROUP BY I.object_id ,
                        I.index_id ,
                        I.name
             ),
        UnusedIndex
          AS ( SELECT   OBJECT_NAME(S.object_id) AS object_name ,
                        I.name AS index_name ,
                        S.user_updates ,
                        S.user_seeks + S.user_scans + S.user_lookups AS user_reads
               FROM     sys.dm_db_index_usage_stats AS S
                        INNER JOIN sys.indexes AS I ON I.object_id = S.object_id
                                                       AND I.index_id = S.index_id
               WHERE    OBJECTPROPERTY(S.object_id, 'IsUserTable') = 1
                        AND S.database_id = DB_ID() -- USE CURRENT DB --                        
             )
    SELECT  S.table_name ,
            S.index_name ,
            S.index_size_mb ,
            U.user_updates ,
            U.user_reads ,
            IIF(U.user_reads = 0, -1, CAST(U.user_updates * 1.0 / U.user_reads AS DECIMAL(18, 2))) AS update_read_ratio
    FROM    IndexSize S
            INNER JOIN UnusedIndex U ON S.table_name = U.object_name
                                        AND S.index_name = U.index_name
    ORDER BY 3 DESC;

Select allOpen in new window

 

The script depends on the SQL Server version, so you need to make minor changes to run it under 2008.

But you should be carful, even when an index is not used often, it may be vital for a certain query. Just remove unused (reads=0) indices, when you have confirmed that it is not used. Your time span for sampling should be long enough.

Those numbers are also your table "access pattern".

Sorry as I am on new job, boss here keep giving me sth to do everyday and we have morning meeting everyday !! why so much !

so no time to reply you except now, our holiday !

"so you need to make minor changes to run it under 2008."

what need to change? when I run on SQL server 2008 with SP4, it returns:

Msg 102, Level 15, State 1, Line 29
Incorrect syntax near '='.

and which is :

 IIF(U.user_reads = 0, -1, CAST(U.user_updates * 1.0 / U.user_reads AS DECIMAL(18, 2))) AS update_read_ratio

so you means this '=' do not have problem on SQL 2012/2014 ?

"Your time span for sampling should be long enough."

what is that mean?

my script to find unused index is :

					-- Create the temp table to store the unused indexes
						IF OBJECT_ID('TEMPDB..#UnusedIndexes') IS NOT NULL
						DROP TABLE #UnusedIndexes

						CREATE TABLE #UnusedIndexes
						(
						ID int identity(1,1)
						,DatabaseName VARCHAR(123)
						,SchemaName VARCHAR(123)
						,TableName VARCHAR(123)
						,IndexName VARCHAR(123)
						,User_Updates INT
						,User_SeeksScansLookups INT
						,System_Updates INT
						,System_SeeksScansLookups INT  
						)

						-- Load the unused indexes into the temp table
						EXEC sp_MSForEachDB 'USE [?];
						INSERT INTO #UnusedIndexes
						SELECT 
							DB_NAME() AS DatabaseName
							, SCHEMA_NAME(o.Schema_ID) AS SchemaName
							, OBJECT_NAME(ix_usage_stats.object_id) AS TableName
							, ix.name AS IndexName
							, ix_usage_stats.user_updates
							, ix_usage_stats.user_seeks + ix_usage_stats.user_scans + ix_usage_stats.user_lookups
													AS [User SeeksScansLookups]
							, ix_usage_stats.system_updates							
							, ix_usage_stats.system_seeks + ix_usage_stats.system_scans + ix_usage_stats.system_lookups
													AS [System SeeksScansLookups]
						FROM sys.dm_db_index_usage_stats ix_usage_stats
						INNER JOIN sys.indexes ix ON ix_usage_stats.object_id = ix.object_id
							AND ix_usage_stats.index_id = ix.index_id
						INNER JOIN sys.objects o ON ix.object_id = o.object_id
						WHERE ix_usage_stats.database_id = DB_ID()
							AND ix.name IS NOT NULL -- exclude heaps
							AND ix.type <> 1 -- exclude the clustered indexes
							AND o.is_ms_shipped = 0 -- exclude system objects
							AND o.type NOT IN(''F'', ''UQ'') -- exclude the foreign keys and unique contraints
							AND ix_usage_stats.user_seeks = 0
							AND ix_usage_stats.user_scans = 0
							AND ix_usage_stats.user_lookups = 0
						'

						-- Query the top 100 order by the user updates and generate the TSQL statement to drop the unused indexes
						SELECT top 100 uix.DatabaseName
						,uix.SchemaName 
						,uix.IndexName
						,uix.TableName
						,uix.User_Updates
						,uix.User_SeeksScansLookups
						,uix.System_Updates
						,uix.System_SeeksScansLookups
						,'DROP INDEX [' +uix.IndexName+ '] ON [' +uix.DatabaseName+ '].[' +uix.SchemaName+ '].[' +uix.TableName+ ']' as [DROP INDEX Statement]
						FROM #UnusedIndexes uix
						ORDER BY uix.User_Updates desc

						drop table #UnusedIndexes;

Select allOpen in new window

so for this script if read = 0, then this index can also be delete once verified that it is no use by showplan_all too ? will my script has any conflict with the result by your script ?

IIF(condition, first, second) is SQL Server 2012+. Replace it with CASE WHEN condition THEN first ELSE second END.

"Those numbers are also your table "access pattern". "

are you talking about the access of index pattern also my table access pattern ? as it access the index page first anyway ? if so my index unused script already done that, right?

"IIF(condition, first, second) is SQL Server 2012+."

tried it on SQL server 2012 with sp1, yeah, it is working for that.

"Replace it with CASE WHEN condition THEN first ELSE second END. "

which means:

CASE when U.user_reads = 0 THEN

 update_read_ratio=-1
 ELSE

update_read_ratio=CAST(U.user_updates * 1.0 / U.user_reads AS DECIMAL(18, 2) )  

END

?

one thing, your script seems not telling the write(update/insert/delete) pattern but only read, right?

No, the user_updates covers that, cause any INSERT or DELETE requires that indices are touched, UPDATES may be covered, when indices on those columns exist.

ok, then the user_updates over all writes, right?

"Replace it with CASE WHEN condition THEN first ELSE second END. "

 which means:

 CASE when U.user_reads = 0 THEN

  update_read_ratio=-1
  ELSE

 update_read_ratio=CAST(U.user_updates * 1.0 / U.user_reads AS DECIMAL(18, 2) )  

 END

 ?

marrowyung, what do you need more about this question?

I think I need an update from ste5an

Ok. Maybe he just forgot and let see if he received notification for new comments on this question so he can reply to you.

so this script is the find out the index access pattern of table and the higher the index insert/update/read, the busier is the table is ?

tks, sorry, busy on my new join company !

tks  ste5an . you are great !