{"id":493,"date":"2015-12-27T21:57:37","date_gmt":"2015-12-28T03:57:37","guid":{"rendered":"https:\/\/jackdonnell.com\/?p=493"},"modified":"2015-12-28T08:10:54","modified_gmt":"2015-12-28T14:10:54","slug":"simple-index-usage-query","status":"publish","type":"post","link":"https:\/\/jackdonnell.com\/?p=493","title":{"rendered":"Simple Index Usage Query"},"content":{"rendered":"<p>Yes, another index usage query. I adapted this a tiny bit to add a time element to it. DMVs will flush out any information when the services are restarted. I omitted system usage information to gear it more toward user created objects. Also, added the users statistics together to get a better Idea of total usage. It gives the user a good starting point to see utilization of current indexing. Conversely, Microsoft provides a &#8220;Missing indexes&#8221; DMV named  <a href=\"https:\/\/msdn.microsoft.com\/en-us\/library\/ms345434.aspx\" target=\"_blank\">sys.dm_db_missing_index_details<\/a>. Don&#8217;t have blind faith in the suggestions. They are based on specific activity since last restart.<br \/>\n<!--more--><\/p>\n<p>Simple query to identify Index Usage. Play around with it and make it your own.<\/p>\n<pre lang=\"tsql\" line=\"1\" escaped=\"true\">\r\nSET NOCOUNT ON\r\nGO\r\n\/*\r\nAdapted from: <a href=\"http:\/\/www.mssqltips.com\/sqlservertutorial\/256\/discovering-unused-indexes\/\" target=\"_blank\">http:\/\/www.mssqltips.com\/sqlservertutorial\/256\/discovering-unused-indexes\/<\/a>\r\n*\/\r\nDECLARE @last_startup_time AS DATETIME\r\nSELECT @last_startup_time = [last_startup_time] FROM [master].[sys].[dm_server_services]\r\n\r\nSELECT DISTINCT\r\nRTRIM(DB_NAME()) [databases]\r\n,QUOTENAME(OBJECT_SCHEMA_NAME(i.[object_id])) +'.'+QUOTENAME(Object_name(i.[object_id]))[tablename]\r\n,i.[name] AS [index]\r\n,s.index_id\r\n,i.type_desc\r\n,(S.[user_seeks] + S.[user_scans] + S.[user_lookups]) [total_usage]\r\n,S.[user_seeks]\r\n,S.[user_scans]\r\n,S.[user_lookups]\r\n,S.[user_updates]\r\n,CONVERT(VARCHAR(20),@last_startup_time,22)[last_startup_time]\r\n,DATEDIFF(day,@last_startup_time, GETDATE()) [up_time_days]\r\nFROM SYS.DM_DB_INDEX_USAGE_STATS AS S\r\nRIGHT JOIN SYS.INDEXES AS I ON i.[object_id] = S.[object_id]\r\nAND i.index_id = S.index_id\r\nWHERE OBJECTPROPERTY(S.[object_id],'IsUserTable') = 1 -- Exclude system tables( mostly)\r\nAND S.database_id = DB_ID() -- Current Database\r\n--AND i.[object_id] in (,OBJECT_ID('dbo.mytable1'),OBJECT_ID('dbo.sometable'),OBJECT_ID('dbo.anothertable') ) -- Review for specific objects\r\nORDER BY 2, 4\r\n\r\nGO<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>Yes, another index usage query. I adapted this a tiny bit to add a time element to it. DMVs will flush out any information when the services are restarted. I omitted system usage information to gear it more toward user &hellip;<\/p>\n<p class=\"read-more\"><a href=\"https:\/\/jackdonnell.com\/?p=493\">Read more &raquo;<\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_exactmetrics_skip_tracking":false,"_exactmetrics_sitenote_active":false,"_exactmetrics_sitenote_note":"","_exactmetrics_sitenote_category":0,"footnotes":""},"categories":[273,266,113,22,5],"tags":[263,306,304,36,295,308,307,291,305],"class_list":["post-493","post","type-post","status-publish","format-standard","hentry","category-administration","category-dba","category-reportingbi","category-sql-server","category-t-sql","tag-dmv","tag-dmvs","tag-index","tag-sql","tag-sql-server","tag-sys-dm_db_index_usage_stats","tag-sys-indexes","tag-t-sql","tag-usage"],"_links":{"self":[{"href":"https:\/\/jackdonnell.com\/index.php?rest_route=\/wp\/v2\/posts\/493","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/jackdonnell.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/jackdonnell.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/jackdonnell.com\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/jackdonnell.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=493"}],"version-history":[{"count":6,"href":"https:\/\/jackdonnell.com\/index.php?rest_route=\/wp\/v2\/posts\/493\/revisions"}],"predecessor-version":[{"id":499,"href":"https:\/\/jackdonnell.com\/index.php?rest_route=\/wp\/v2\/posts\/493\/revisions\/499"}],"wp:attachment":[{"href":"https:\/\/jackdonnell.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=493"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/jackdonnell.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=493"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/jackdonnell.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=493"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}