Identifying fragmented tables, bad indexes in a database

Fragmented tables

USE database
GO

SELECT OBJECT_NAME(object_id) ObjectName,
index_id,
index_type_desc,
avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats
(DB_ID(),NULL, NULL, NULL, ‘LIMITED’)
WHERE avg_fragmentation_in_percent > 30
ORDER BY OBJECT_NAME(object_id)

Bad indexes

Indexes that have many more writes than reads are possible candidates for elimination.


SELECT object_name(s.object_id) AS ‘Table Name’,
i.name AS ‘Index Name’, i.index_id,
user_updates AS ‘Total Writes’,
user_seeks + user_scans + user_lookups AS ‘Total Reads’,
user_updates – (user_seeks + user_scans + user_lookups) AS ‘Difference’
FROM sys.dm_db_index_usage_stats AS s
INNER JOIN sys.indexes AS i
ON s.object_id = i.object_id
AND i.index_id = s.index_id
WHERE objectproperty(s.object_id,’IsUserTable’) = 1
AND s.database_id = db_id() — Filter for current database
AND user_updates > (user_seeks + user_scans + user_lookups )
ORDER BY ‘Difference’ DESC, ‘Total Writes’ DESC, ‘Total Reads’ ASC;

Advertisements

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