maintenance – The Brazerican

I try to stay in touch with the internals of my databases, including the size of things. Growth patterns are a good indicator of where most of your database activity lies, and that in turn can help you locate activity hot-spots. For today’s post, I have a simple script will help you get all your object sizes, including tables, heaps and indexes.

As an added bonus, it’ll also give you suggestions of which indexes are just giving you maintenance overhead. Every DML also updates the indexes, but you have the potential for index overhead if you have more updates than reads. To identify such candidates, I use the formula below:

( user_seeks + user_scans + user_lookups )  / (user_updates)

The idea here is simple: if the combined seeks, scans and lookups add up to more than the updates, then the index is probably good! If you’re having more updates than reads, then the index is probably just baggage, and you should consider tossing it overboard.

There’s a few caveats to consider, however

Index stats are not preserved during service restarts.
Index stats can also be manually flushed.
It is very possible that an index is used for just one report, which only runs once a year! And of course, this one index shaves off several hours of report time execution.
sys.dm_db_index_usage_stats only holds information for indexes that have been used since the last service start up.
sys.dm_db_index_usage_stats does not return information about memory-optimized indexes (see here).

And here’s the code itself

 SELECT 
    ds.name AS filegroupname,
    df.name AS filename,
    df.physical_name,
    SCHEMA_NAME(o.schema_id) AS SCHEMAName,
    OBJECT_NAME(ps.object_id) AS objname,
    i.name indexname,
    o.create_date,
    i.index_id,
    ps.row_count,
    SUM(ps.used_page_count) / 128 AS used_mb,
    SUM(ps.reserved_page_count) / 128 AS reserved_mb,
    SUM(ps.in_row_data_page_count) / 128 In_row_mb,
    SUM(ps.in_row_used_page_count) / 128 AS row_used,
    SUM(ps.in_row_reserved_page_count) / 128 AS row_reserved,
    SUM(ps.lob_used_page_count) / 128 AS lob_mb,
    SUM(ps.lob_reserved_page_count) / 128 AS lob_reserved,
    us.user_updates AS [Total Writes],
    us.user_seeks + us.user_scans + us.user_lookups AS [Total Reads],
    case
		when user_updates > 0 then
		Convert(decimal(6,2),( user_seeks + user_scans + user_lookups )  / (user_updates* 1.0)) 
	else 
		Convert(decimal(6,2),( user_seeks + user_scans + user_lookups ) )
	end as ReadToWriteRatio
FROM sys.dm_db_partition_stats ps
    left JOIN sys.objects o
        ON o.object_id = ps.object_id
    left JOIN sys.indexes i
        ON o.object_id = i.object_id
    left JOIN sys.data_spaces ds
        ON ds.data_space_id = i.data_space_id
    left JOIN sys.database_files df
        ON df.data_space_id = ds.data_space_id
           AND df.data_space_id = i.data_space_id
    left JOIN sys.dm_db_index_usage_stats us
        ON us.object_id = i.object_id
           AND us.index_id = i.index_id
GROUP BY 
SCHEMA_NAME(o.schema_id),
         OBJECT_NAME(ps.object_id),
         us.user_seeks + us.user_scans + us.user_lookups,
         us.user_updates - (us.user_seeks + us.user_scans + us.user_lookups),
         ds.name,
         df.name,
         df.physical_name,
         i.name,
         o.create_date,
         i.index_id,
         ps.row_count,
         us.user_updates
ORDER BY objname,
         filegroupname,
         i.name;

For a fresh restore of a StackOverflow database (thanks Brent!) this is what the output will look like. You can save the results into a table (along with a time-stamp) so you can review your usage trends overtime.

Tag: maintenance

Get the size of all your objects

Other links!