{"id":6268,"date":"2019-03-07T12:48:29","date_gmt":"2019-03-07T12:48:29","guid":{"rendered":"https:\/\/ittutorial.org\/?p=6268"},"modified":"2019-11-28T07:06:39","modified_gmt":"2019-11-28T07:06:39","slug":"sql-server-index-usage-stats","status":"publish","type":"post","link":"https:\/\/ittutorial.org\/sql-server-index-usage-stats\/","title":{"rendered":"SQL Server Index Usage Stats"},"content":{"rendered":"<p>Hi,<\/p>\n<p>SQL Server DBA should monitor all indexes and which of indexes are using and which are not.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-2831\" src=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2012\/10\/databaseindexes1.gif\" alt=\"\" width=\"354\" height=\"403\" \/><\/p>\n<p><!--more--><\/p>\n\n<p>&nbsp;<\/p>\n<p>If the index is not using or using\u00a0very rare then SQL Server DBA should drop it to not provide extra load to the database.<\/p>\n<p>You can find all indexes usage statistic with following script.<\/p>\n<p>&nbsp;<\/p>\n<pre>select objname = OBJECT_NAME(s.object_id),\r\ns.object_id,\r\nindex_name= i.name,\r\nindex_id = i.index_id,\r\nuser_seeks, user_scans, user_lookups\r\nfrom sys.dm_db_index_usage_stats as s\r\njoin sys.indexes i on i.object_id = s.object_id and i.index_id = s.index_id\r\nwhere database_id = DB_ID('DEVECI')\r\nand OBJECTPROPERTY(s.object_id,'IsUserTable')=1\r\norder by (user_seeks + user_scans + user_lookups) desc;\r\n\r\n<\/pre>\n<p>&nbsp;<\/p>\n<h4><span style=\"color: #ff0000;\">Do you want to learn Microsoft SQL Server DBA Tutorials for Beginners, then read the following articles.<\/span><\/h4>\n<p><a href=\"https:\/\/ittutorial.org\/sql-server-tutorials-microsoft-database-for-beginners\/\">https:\/\/ittutorial.org\/sql-server-tutorials-microsoft-database-for-beginners\/<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Hi, SQL Server DBA should monitor all indexes and which of indexes are using and which are not.<\/p>\n","protected":false},"author":1,"featured_media":2831,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"_uf_show_specific_survey":0,"_uf_disable_surveys":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[3828,2227,3827],"tags":[2260,1028,1031,1600,1610,2231,2228,2259,1648,2262,2261,2232,2258,2257,2237,2236,2230,2229],"class_list":["post-6268","post","type-post","status-publish","format-standard","has-post-thumbnail","","category-sql-scripts","category-sql-server-2","category-sql-server-dba-scripts","tag-fragmentation-in-sql-server","tag-mehmet-deveci","tag-mehmet-salih-deveci","tag-sql-server-2012","tag-sql-server-2014","tag-sql-server-2019","tag-sql-server-collection-inventory-script","tag-sql-server-fragmented-index","tag-sql-server-index","tag-sql-server-index-usage","tag-sql-server-index-usage-stats","tag-sql-server-inventory-script","tag-sql-server-rebuild-index","tag-sql-server-rebuild-index-query","tag-sql-server-select-inventory","tag-sql-server-select-machine-name","tag-sql-server-useful-query","tag-sql-server-useful-script"],"aioseo_notices":[],"jetpack_featured_media_url":"https:\/\/ittutorial.org\/wp-content\/uploads\/2012\/10\/databaseindexes1.gif","jetpack_sharing_enabled":true,"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts\/6268","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/comments?post=6268"}],"version-history":[{"count":2,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts\/6268\/revisions"}],"predecessor-version":[{"id":12975,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts\/6268\/revisions\/12975"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/media\/2831"}],"wp:attachment":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/media?parent=6268"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/categories?post=6268"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/tags?post=6268"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}