{"id":6265,"date":"2019-03-07T12:10:51","date_gmt":"2019-03-07T12:10:51","guid":{"rendered":"https:\/\/ittutorial.org\/?p=6265"},"modified":"2019-11-28T07:06:52","modified_gmt":"2019-11-28T07:06:52","slug":"sql-server-rebuild-index-query","status":"publish","type":"post","link":"https:\/\/ittutorial.org\/sql-server-rebuild-index-query\/","title":{"rendered":"SQL Server Rebuild Index Query"},"content":{"rendered":"<p>Hi<\/p>\n<p>All Indexes in the SQL Server database will be fragmented After related tables got many update, delete transactions.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-3685\" src=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2014\/03\/index3.png\" alt=\"\" width=\"1221\" height=\"913\" srcset=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2014\/03\/index3.png 1221w, https:\/\/ittutorial.org\/wp-content\/uploads\/2014\/03\/index3-300x224.png 300w, https:\/\/ittutorial.org\/wp-content\/uploads\/2014\/03\/index3-768x574.png 768w, https:\/\/ittutorial.org\/wp-content\/uploads\/2014\/03\/index3-1024x766.png 1024w\" sizes=\"auto, (max-width: 1221px) 100vw, 1221px\" \/><\/p>\n<p><!--more--><\/p>\n\n<p>&nbsp;<\/p>\n<p>If the index in the table is fragmented then all transactions related with that table will be very slow. So SQL Server DBA should frequently monitor indexes and which of indexes are fragmented and which are not like following screenshot.<\/p>\n<p>&nbsp;<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-6266\" src=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/03\/rebuild-index2.png\" alt=\"\" width=\"884\" height=\"357\" srcset=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/03\/rebuild-index2.png 884w, https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/03\/rebuild-index2-300x121.png 300w, https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/03\/rebuild-index2-768x310.png 768w\" sizes=\"auto, (max-width: 884px) 100vw, 884px\" \/><\/p>\n<p>&nbsp;<\/p>\n<p>You can check fragmented index with following script. Type SQL Server Database name is correctly in the first line then query will find fragmented indexes.<\/p>\n<p>&nbsp;<\/p>\n\n<p>&nbsp;<\/p>\n<pre>declare @db int\r\nselect @db=DB_ID('DEVECI')<\/pre>\n<pre>select 'ALTER INDEX [' + i.name +'] on '+OBJECT_NAME(s.object_id)+' REBUILD WITH (ONLINE = ON)',\r\nobjname = OBJECT_NAME(s.object_id),\r\ns.object_id,\r\nindex_name= i.name,\r\nindex_type_desc, \r\navg_fragmentation_in_percent\r\nfrom sys.dm_db_index_physical_stats(@db,null,null,null,null) as s\r\njoin sys.indexes i on i.object_id = s.object_id and i.index_id = s.index_id \r\nwhere avg_fragmentation_in_percent&gt;30\r\norder by avg_fragmentation_in_percent desc, page_count desc;\r\n\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 All Indexes in the SQL Server database will be fragmented After related tables got many update, delete transactions.<\/p>\n","protected":false},"author":1,"featured_media":3685,"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,2233,2232,2258,2257,2237,2236,2234,2235,2230,2229],"class_list":["post-6265","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-instance-name","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-total-database-datafile-size","tag-sql-server-total-database-log-size","tag-sql-server-useful-query","tag-sql-server-useful-script"],"aioseo_notices":[],"jetpack_featured_media_url":"https:\/\/ittutorial.org\/wp-content\/uploads\/2014\/03\/index3.png","jetpack_sharing_enabled":true,"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts\/6265","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=6265"}],"version-history":[{"count":2,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts\/6265\/revisions"}],"predecessor-version":[{"id":12976,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts\/6265\/revisions\/12976"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/media\/3685"}],"wp:attachment":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/media?parent=6265"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/categories?post=6265"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/tags?post=6265"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}