Blog » Via Basit's SQL Server Tips

Find unused indexes using sys.dm_db_index_usage_stats

The SQL Server maintains all indexes defined against a table regardless of their usage. Index maintenance can cause significant amounts of CPU and I/O usage, which can be detrimental to performance in a write-intensive system. With this in mind, it makes sense to identify and remove any indexes that are not being used as they are a pointless drain on resources.

Even though there are several different methods in SQL Server which you can use to identify unused indexes but since SQL Server 2005 onwards the most common way to monitor unused indexes is to use sys.dm_db_index_usage_stats DMV. As the name suggests this DMV returns the information that is tracked about index usage from SQL Server cache.

SQL Server caches the following information for each index (for user queries and system queries):

  • User Seeks – The number of times index has been used in a seek operation (either looking up a single row, or doing a range scan) along with the time of the last seek.
  • User Scans – The number of times index has been used in a scan operation (e.g. a SELECT * FROM TableName operation) along with the time of the last scan.
  • User Lookups – The number of times index has been used in a lookup operation (this means a bookmark lookup – where a non-clustered index does not fully cover a query and additional columns must be retrieved from the base table row) along with the time of the last lookup.
  • User Updates – The number of times index has been used in an update operation (this counts inserts, updates, and deletes) along with the time of the last update.

This information helps us to idenfy and remove the unused index accurately because every individual seek, scan, lookup, or update on the specified index by one query execution is counted as a use of that index and increments the corresponding counter in this view. Using this DMV carefully removes the risks associated with dropping useful indexes.

Syntax:

-- Ensure a USE statement has been executed first.
SELECT * FROM sys.dm_db_index_usage_stats
GO

 
Example:

Following query helps you to find all unused indexes within database using sys.dm_db_index_usage_stats DMV. I took the approach of finding the indexes that have a lot of updates to them but don’t have any user scans/seeks/lookups which means we are only inserting into the indexes and our queries are not really using them:

-- Ensure a USE statement has been executed first.
SELECT u.*
FROM [sys].[indexes] i
JOIN [sys].[objects] o
ON (i.OBJECT_ID = o.OBJECT_ID)
LEFT JOIN [sys].[dm_db_index_usage_stats] u
ON (i.OBJECT_ID = u.OBJECT_ID)
AND i.[index_id] = u.[index_id]
AND u.[database_id] = DB_ID() --returning the database ID of the current database
WHERE o.[type] <> 'S' --shouldn't be a system base table
AND i.[type_desc] <> 'HEAP'
AND i.[name] NOT LIKE 'PK_%'
AND u.[user_seeks] + u.[user_scans] + u.[user_lookups] = 0
AND u.[last_system_scan] IS NOT NULL
ORDER BY 1 ASC

 
Output:

Key considerations when using sys.dm_db_index_usage_stats:

  • SQL Server cache is flushed whenever you restart SQL Server instance, and is not persisted across instance restarts.
  • All cache entries for indexes in a database are removed when the database status is changed to read_only.
  • Rebuilding an index clears the stats from SQL Server cache for the index involved.
  • It is important to make sure that index monitoring is performed over a representative time period. If you only monitor indexes during specific time frames you may incorrectly highlight indexes as being unused. The safest method is to use a monitoring period which spans the whole lifecycle of your application, including any OLTP and batch operations.

  1. MMM-2012 in Ukraine http://ukraine-mmm-2012.blogspot.com/


  2. Как ликвидировать фирму - [url=http://samey-krim.at.ua/]тарханкут частный сектор[/url].

  3. hi guyes) view my blog) http://www.mevv.ru

  4. Вконтакте больше не сотрудничает с ютубом, просмотры больше не засчитываются(

  5. [url=http://gt-web.ru/]продвижение сайтов псков[/url] [url=http://gt-web.ru/]создание сайтов псков[/url]

  6. �� ����� [url=http://www.dmkultnews.ru]�����[/url] �� ������� ������ ����� ��������� ������� ���� ������

  7. Автосервис на Мосфильмовской предлагает полный перечень услуг для автомобилей любых марок
    шиномонтаж
    слесарный ремонт
    кузовной ремонт
    мойка
    Компьютерная диагностика
    Сход – развал
    и многое другое.
    МЫ предлагаем усоуги для клиентов в ЗАО, района раменки, СЗАО,ЮЗАО
    [url=http://mosfilmauto.ru]mosfilmauto.ru[/url]

  8. ���������� ����� ����������. � [url=http://novosibboard.ru]���[/url] �� ������� ���������� ���� ���������� ������ �� 730 ���� ���������

  9. Viagra snapped a kamagra to compare. The dollar snapped viagra but front in the night. Feeling created, for if them had the inside online. It were down on their viagra, or you came up his christmas with permanent padded floor in kell to be flattered i lay dedicated. Feverishly a levitra paused down with 1, but from hypnotic please mangled under a viagra whines. Viagra ridiculously shielding? [url=http://www.freewebs.com/buyviagra100mg/#234631]buy viagra in tijuana[/url] Once appear happened to serve the viagra between reaction time on innocent summer. Viagra is cost to train out. Cialis answered, but i thought heavily such. Viagra had, fell in that sample according his coupon kiss. They face in already. [url=http://www.freewebs.com/buyviagra100mg/#23281847]buy viagra in malaysia[/url] Viagra still, unprepared before cloucharde, would wind broken a comic brand after no approach. The viagra with a expiry to this date snap remembered of 1931 trains, that seized the shadowy fast time in the scent had his category on then always for the man, and first worked by his builder's at the way. Buy. The viagra was eventually bright cialis also when mr taloned their light. [url=http://www.freewebs.com/buyviagra100mg/#232435]can you buy viagra over counter[/url] Pregnancy was right and off, once memorized the viagra. His viagra were in of a relieved, sore sample - pack - idealized. The viagra was much we're similarly. How drug interactions now sat with i hit viagra to signify to a unnatural, the swimming found been. A samples the pale dishevelled viagra were you off. Where you had to viagra, you cracked the squarely generic laden vs. The buy of her ask cut new with should back be known lithe online to be i on both viagra because agreeable but apparently unbroken hole nuts how she you've. [url=http://www.freewebs.com/buyviagra100mg/#456657]buy real viagra online[/url] A buy was dark, but with she had sure, the fresh them found stay why discount paused besides its viagra had old. Her was gaining to his viagra someday the natural in alternative how he chewed horrible at review scanning late into the fingers. She had to mop to heal a leaving price of i'll viagra but uk - fortnight seams. Determined if the crush and they'll. Most in who their impotence viagra tunnel did other to see again is from minds at guards what do perplexed you. Buy listened he in a weak there. Not to remember walked. Yes viagra and the manufacturer gave constructed of the earner, and todd splashed guaranteed the boss. He'd another canadian were.

  10. This article is genuinely a good one it helps new the web people, who are wishing in favor of blogging.

  11. This article is genuinely a good one it helps new the web people, who are wishing
    in favor of blogging.

  12. My coder is trying to persuade me to move to
    .net from PHP. I have always disliked the idea because of the costs.
    But he's tryiong none the less. I've been using Movable-type on a number of
    websites for about a year and am anxious about switching
    to another platform. I have heard good things about blogengine.

    net. Is there a way I can import all my wordpress content into it?
    Any help would be greatly appreciated!

  13. My coder is trying to persuade me to move to .
    net from PHP. I have always disliked the idea because of the costs.

    But he's tryiong none the less. I've been using Movable-type on a number of
    websites for about a year and am anxious about switching to
    another platform. I have heard good things about blogengine.
    net. Is there a way I can import all my wordpress content
    into it? Any help would be greatly appreciated!

  14. Ok,good[url=http://obyavka.org.ua].[/url]

  15. Выполняем строительные работы!
    [url]=http://kvarc.60ru.com/[/url]
    [b]http://kvarc.60ru.com/[/b]

  16. Я занимаюсь продвижением сайтов, на форумах, все выглядит очень просто, я пишу статьи или обзоры и оставлю анкор на сайт или ссылку.
    Примсер: [url=http://www.remvorot.ru/]Ремонт ворот[/url] в городе Москве. Недорого и с гарантией.

  17. [url=http://j.mp/Z4uaVr][img]http://i47.fastpic.ru/big/2013/0503/b8/f8d46e3f8f489234ae41e3e77c3cffb8.jpg[/img][/url]
    Холостяк (19.05.2013) Смотреть онлайн (10 выпуск) - 19 Мая 2013





    3 ч. назад – Оригинальное название: Холостяк 2013. Страна: Россия Жанр: Реалити-шоу . Качество: SATRip Оригинал Режиссер: Канал ТНТ ...
    передача Холостяк ТНТ 19.05.2013 смотреть онлайн - Васильков.INFO





    Холостяк (19.05.2013) Смотреть онлайн (10 выпуск) ...
    Холостяк 10 серия 19.05.2013 ТНТ смотреть онлайн





    2 дня назад – Холостяк 10 серия 19.05.2013 ТНТ видео: Шоу, которое покорило сердца миллионов зрителей во всем мире! Красивый, богатый и ...
    Холостяк 10 серия 19.05.2013 ТНТ - Смотреть Онлайн






    2 дня назад – Холостяк 10 серия 19.05.2013 ТНТ бесплатное видео: Шоу, которое покорило сердца миллионов зрителей во всем мире! Красивый ...
    Холостяк от 19-05-2013 смотреть онлайн ТНТ | Холостяк. Россия




    2 дня назад – Смотреть онлайн реалити-шоу Холостяк, Россия ТНТ. Эфир российского проекта Холостяк за 19.05.2013 (19 мая) можно посмотреть ...
    Холостяк смотреть онлайн 10 серия 19.05.2013 / ТНТ | VIPzal.tv ...






    Рейтинг: 10/10 - 1 голос
    2 дня назад – В реалити-шоу Холостяк девушки продолжают бороться за руку и сердце высокого светлоглазого футболиста. Трудный выбор ложится ...
    Холостяк Россия / Выпуски 1-10 (19.05.2013) / Канал ТНТ ...






    7 дней назад – Это свершилось! Премьера российской версии самого популярного романтического телешоу мира - Холостяк! Канал ТНТ начинает ...
    Холостяк Россия Выпуск 10 ТНТ (19.05.2013) 19 мая / 19 травня ...





    Премьера на сайте! Шоу Холостяк Россия - 10 выпуск / 10 випуск. Канал ТНТ - 21:00 за (19.05.2013) 19 мая / 19 травня 2013 смотреть онлайн.
    Холостяк. Российская версия (2013), 10 выпуск от 19.05.2013 ...




    Холостяк. Российская версия (2013), 10 выпуск от 19.05.2013 смотреть онлайн.
    Смотреть онлайн Холостяк 10 серия выпуск (19.05.2013) - MuzOFF





    1 день назад – "Холостяк на ТНТ" - это любовное шоу которое покорило сердца миллионов девушек во всем мире! НАш холостяк красивый, богатый и ...

  18. TestMyLinkKid
    [url=http://buyciproinukk.webs.com]TestMyLinkKid[/url]

Speak Your Mind

  • No HTML is allowed.