find out access pattern of all tables in all user database

Dear all,

any script to find out if the table is insert/update more or read more than insert/update?

I would like to see if an index of a table should be drop.

if write more than read, then we should seriously consider to drop the index of that table.
LVL 1
marrowyungSenior Technical architecture (Data)Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ste5anSenior DeveloperCommented:
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;

Open 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".
0
marrowyungSenior Technical architecture (Data)Author Commented:
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;

Open 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 ?
0
ste5anSenior DeveloperCommented:
IIF(condition, first, second) is SQL Server 2012+. Replace it with CASE WHEN condition THEN first ELSE second END.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

marrowyungSenior Technical architecture (Data)Author Commented:
"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

?
0
marrowyungSenior Technical architecture (Data)Author Commented:
one thing, your script seems not telling the write(update/insert/delete) pattern but only read, right?
0
ste5anSenior DeveloperCommented:
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.
0
marrowyungSenior Technical architecture (Data)Author Commented:
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

 ?
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
marrowyung, what do you need more about this question?
0
marrowyungSenior Technical architecture (Data)Author Commented:
I think I need an update from ste5an
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Ok. Maybe he just forgot and let see if he received notification for new comments on this question so he can reply to you.
0
ste5anSenior DeveloperCommented:
Using CASE:

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 ,
            CASE WHEN U.user_reads = 0 THEN -1
                 ELSE CAST(U.user_updates * 1.0 / U.user_reads AS DECIMAL(18, 2))
            END 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;

Open in new window


Writes are covered by updates on primary key indices.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
marrowyungSenior Technical architecture (Data)Author Commented:
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 ?
0
marrowyungSenior Technical architecture (Data)Author Commented:
tks, sorry, busy on my new join company !
0
marrowyungSenior Technical architecture (Data)Author Commented:
tks  ste5an . you are great !
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.