|
Keeping an index updated when data is changing is one cost, but there is also the cost of extra storage space and the increased size of backups. So if an index isn't being used, you should remove it, right? In this article, I'll show you ways to find out if an index is used. Then I'll show you a type of index that may appear to be unused even although it really is used. Hopefully this won't leave you too disheartened about the idea of researching your indexes but instead convince you of the power of SQL Server. My favorite analogy for indexes is telephone directories. I'm not sure whether I heard this analogy or came up with it-I just know that it's a way of describing indexes that everyone grasps easily. Like most analogies, it doesn't fit completely, but on the whole it's fairly good. In Australia, the company Sensis publishes two phone books: the Yellow Pages (which lists entries according to business type) and the White Pages (which lists telephone entries by surname or business name).
I assume that somewhere there is a list of all the information about the telephone numbers in Australia. Nowadays it would be electronic, but for the purpose of the analogy, we're going to consider a paper-based system. Suppose that the master list of the telephone number information is a massive folder stored at the telecommunication provider's head office. Suppose also that it's sorted by telephone number. This folder would not be useful if I wanted to look up the phone number of my friend Andrew. His number could be on any page of the folder, so I'd have to hunt through the lot-starting on page one and continuing until I found him. If he had two numbers, I would have to keep looking to the end, even if it turned out he was on page one. If the folder contains millions of phone numbers, this would not be feasible. The folder is analogous to a clustered index in SQL Server. The folder contains all the information that is available, and the phone number acts as the key to unlock it-providing order to the information and a way of identifying each individual record. The White Pages is useful for finding Andrew's phone number, though. I know Andrew's surname, so I can find him very quickly. As it's sorted by surname, I can turn straight to his record (almost-I might have to jump around a little) and see his phone number there. As I only need his phone number, the White Pages (but I'd rather call it a nonclustered index) is the only resource I need. I can say that it covers my query. But if I wanted to get another piece of information, such as his billing number, I'd have to use his phone number to look him up in the master folder. In SQL Server land, this is known as a key lookup. I won't go further into indexes now. If I never used the White Pages, there would be no point in having it in that small cupboard on which the phone sits. I could put other, more useful stuff there. What's more, whenever someone moves or changes his name or phone number, the White Pages must be updated.
Although I get a new physical copy only once a year, the online version is updated much more often. Perhaps if I were eager, I could keep my eye out for changes to the underlying data and keep my copy of the White Pages up to date. But that would be arduous. The same principle applies to indexes in databases. If we don't use an index, there's little point in having it around. Learning how to find the indexes that aren't used is a fairly useful skill for a database administrator to pick up, and SQL Server 2005 makes this easier. SQL Server 2005 introduced Dynamic Management Views (DMVs) that are useful for providing dynamic metadata in the form of queryable views. There are other types of system views, such as catalog views like the useful sys.indexes, but for finding out which indexes are used, the most useful view is the DMV sys.dm_db_index_usage_ stats. Let's look at the structure of this view, by expanding the relevant part of the Object Explorer in SQL Server Management Studio (SSMS), which is in the System Views part under the database of interest. I'm also using administrator access, although you only need VIEW DATABASE STATE permission to read from the DMV. You'll notice that the DMV lists the number of seeks, scans, lookups, and updates that users and the system perform, including when the latest of each type was done. The DMV is reset when the SQL Server service starts, but that's just a warning to people who might have thought that data remained there from long ago. An index that isn't used won't have an entry in this view. If no seeks, scans, lookups, or updates have been performed on an index, this view simply won't list the index. Incidentally, bear in mind that to get the name of the index, you may want to join this view to sys.indexes. You can also find out which indexes are used by looking at the execution plans that are being used by the queries issued against the database. This is even more useful, as the impact of an index can be easily evaluated by its impact on individual queries. If you consider the performance gain on an individual query, and examine how often this query is executed, you have a fantastic metric for the impact of an index.
Query plans make it clear which indexes are being used, as an icon is shown for each index scan or seek. SQL Server 2008 Management Studio has significantly improved the readability of execution plans by displaying only the table name and index name, rather than using the three-part naming convention for the table and the index name. Although you could use a SQL trace to collect the query plan for every query that runs, this is not practical. The impact on performance of this type of trace can be significant, and processing the plans can also be painful. Using the sys.dm_db_index_ usage_stats DMV to evaluate index usage is far easier. Querying the DMV every so often (particularly prior to any maintenance windows that might involve a service restart) can allow the information to be collected and analyzed, giving a strong indication of which indexes are used, and which are needlessly clogging up the system.