Feb 14 2009
You may have a full-time DBA where you work, but a lot of us share the CF developer and DB developer hats. Your SQL Server's performance can be an easy thing to overlook if your database is very small, or your website gets very little traffic. The easy (but not always correct) answer to most performance problems is "add an index". Some indexes are an obvious help, but how do you tell if the less obvious ones are really being used? It is possible to have too many indexes. In addition to bloating the size of your database, they take time to update which can actually slow your application DOWN on inserts and updates.It is always difficult to determine how much of the SQL performance iceberg to scrape off in a single post; especially since there is already a host of information out there on the topic. What I would like to focus on here is being able to accurately analyze your existing indexes. You are doing a disservice to yourself if you use the "shotgun" approach and litter your database with indexes without really needing half of them.
Ok, how do I get the data?This post, like many will be pretty specific to SQL Server 2005. Mainly because 2005 is the first version to offer this data and I don't know how to get it out of other DBMS's. SQL Server 2005 has a host of information you can retrieve about the usage of your indexes. It is very important to note that this data is erased every time you restart your SQL server. What you need to do is let your application run for a good amount of time ensuring an even and typical usage of all your apps functions have been performed. Below is some SQL I have thrown together. I think it is mostly bug-free, but I don't have a particularly large database to test it on right now. Paste it into Management Studio and run it against a database of your choice.
[code]DECLARE @Index_stats AS TABLE (table_name varchar(100), index_name varchar(300), index_type varchar(50), is_primary_key bit, seeks int, scans int, lookups int, updates int, records_in_index int, index_size_in_MB float) INSERT @Index_stats SELECT t.name AS table_name, i.name AS index_name, i.type_desc AS index_type, i.is_primary_key, isnull(u.user_seeks,0) AS seeks, isnull(u.user_scans,0) AS scans, isnull(u.user_lookups,0) AS lookups, isnull(u.user_updates,0) AS updates, sum(CASE WHEN a.type = 1 THEN p.rows ELSE 0 END) AS records_in_index, -- Only count the rows once (sum(a.total_pages) * 8) / 1024.00 AS index_size_in_MB -- Pages are 8 Bytes in size FROM sys.indexes i INNER JOIN sys.tables t ON i.object_id = t.object_id INNER JOIN sys.partitions p ON i.object_id = p.object_id INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id AND i.index_id = p.index_id LEFT OUTER JOIN sys.dm_db_index_usage_stats u ON u.index_id = i.index_id AND u.object_id = i.object_id WHERE t.type_desc = 'USER_TABLE' AND i.type_desc in ('CLUSTERED','NONCLUSTERED') and t.name not like 'sys%' -- Some system tables show as user tables (?) GROUP BY t.name, i.name, i.type_desc, i.is_primary_key, u.user_seeks, u.user_scans, u.user_lookups, u.user_updates, u.object_id, u.index_id -- Most used indexes SELECT TOP 30 PERCENT * from @index_stats ORDER BY seeks + scans + lookups DESC -- Least used indexes SELECT TOP 30 PERCENT * from @index_stats WHERE seeks + scans + lookups > 0 ORDER BY seeks + scans + lookups -- COMPLETELY unused indexes SELECT * from @index_stats WHERE seeks + scans + lookups = 0 -- Most updated indexes SELECT TOP 30 PERCENT * from @index_stats ORDER BY updates DESC -- Most common Bookmark Lookups SELECT TOP 30 PERCENT table_name, lookups from @index_stats ORDER BY lookups DESC[/code]Please feel free to play around with this to see the data you want. you can filter for certain tables etc. The SQL above is just a starting point for you.
What does the data mean?Here are the columns in use and what they mean:
- table_name - The table name that the index is on
- index_name - The name of the index
- index_type - CLUSTERED (only one per table, most commonly on primary keys) or NONCLUSTERED
- seeks - Number of times an index seek has been used on this index. Seeks are used to find a unique value and are the fastest. (Imagine thumbing through the Yellow pages directly to the R's.)
- scans - Number of times an index scan has been used on this index. Scans are commonly used to find a range of values.
- lookups - Applies only to Clustered Indexes. This is the number of times this index was used in a bookmark lookup. This happens in an un-covered query where the records were found using another index, but additional columns are required to be returned, so SQL Server turns to this index to get the remaining data using a "bookmark" pointer stored in the non clustered index.
- updates - This is the number of times this index was updated due to an insert or update on the table. Updates are a requirement, but they are a big trade off since they are sometimes costly.
- records_in_index - This is the number of records in the index. Index scans and updates slow down as this number increases.
- index_size_in_MB - This is the size of the data in the index in MBs. If database size is important to you, large indexes should be noted.
Most used indexesThese are your favorite indexes. They are saving your database a lot of work. What you need to look at here though are index scans versus index seeks. If you are commonly selecting out a range of values, then scans are inevitable.
[code]SELECT * FROM sales WHERE order_total between 200 AND 500[/code]Be careful though as string manipulation and unnecessary data type conversion can turn a perfectly good seek into a scan.
[code]SELECT * FROM person WHERE ltrim(rtrim(last_name)) = 'Smith'[/code]