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.
Other links!
sys.dm_db_index_physical_stats – get fragmentation values!
Where in the disk are my indexes? – similar to this, but without the usage values
