\n
1:<\/span> -- Get fragmentation info for all indexes above a certain size in the current database (Query 62) (Index Fragmentation)<\/span><\/pre>\n<\/p>\n
2:<\/span> -- Note: This query could take some time on a very large database<\/span><\/pre>\n<\/p>\n
3:<\/span> SELECT<\/span> DB_NAME(ps.database_id) AS<\/span> [Database<\/span> Name], OBJECT_NAME(ps.OBJECT_ID) AS<\/span> [Object<\/span> Name], <\/pre>\n<\/p>\n
4:<\/span> i.name AS<\/span> [Index<\/span> Name], ps.index_id, ps.index_type_desc, ps.avg_fragmentation_in_percent, <\/pre>\n<\/p>\n
5:<\/span> ps.fragment_count, ps.page_count, i.fill_factor, i.has_filter, i.filter_definition<\/pre>\n<\/p>\n
6:<\/span> FROM<\/span> sys.dm_db_index_physical_stats(DB_ID(),NULL<\/span>, NULL<\/span>, NULL<\/span> , N'LIMITED'<\/span>) AS<\/span> ps<\/pre>\n<\/p>\n
7:<\/span> INNER<\/span> JOIN<\/span> sys.indexes AS<\/span> i WITH<\/span> (NOLOCK)<\/pre>\n<\/p>\n
8:<\/span> ON<\/span> ps.[object_id] = i.[object_id] <\/pre>\n<\/p>\n
9:<\/span> AND<\/span> ps.index_id = i.index_id<\/pre>\n<\/p>\n
10:<\/span> WHERE<\/span> ps.database_id = DB_ID()<\/pre>\n<\/p>\n
11:<\/span> AND<\/span> ps.page_count > 2500<\/pre>\n<\/p>\n
12:<\/span> ORDER<\/span> BY<\/span> ps.avg_fragmentation_in_percent DESC<\/span> OPTION<\/span> (RECOMPILE);<\/pre>\n<\/p>\n
13:<\/span> <\/pre>\n<\/p>\n
14:<\/span> -- Helps determine whether you have framentation in your relational indexes<\/span><\/pre>\n<\/p>\n
15:<\/span> -- and<\/span> how effective your index<\/span> maintenance strategy is<\/pre>\n<\/div>\n<\/div>\n
Figure 2: Query #62 Index Fragmentation<\/strong><\/p>\nThis query shows you the current fragmentation status for your heap tables and all indexes above 2500 pages in the current database. This helps you evaluate your index maintenance effectiveness. It is also useful when you are thinking about how you do your index maintenance and whether you might want to consider changing the fill factor on some indexes to reduce how often you need to rebuild or reorganize them.<\/p>\n
I would say that most of the customers that I see fall into one of three categories regarding their index maintenance practices: <\/p>\n
\n- Organizations with \u201cAccidental DBAs\u201d who don\u2019t even know what index maintenance is, much less the difference between rebuild and reorganize, or about things like fill factors\n
\n- These organizations often have extremely high index fragmentation across most of their indexes and databases<\/li>\n<\/ol>\n
- Organizations that are using the existing SQL Maintenance Plan Wizard (which is evil, BTW), which makes it far too easy for them to do redundant things like reorganize, then rebuild, then update statistics, etc.\n
\n- The organizations sometimes have less fragmentation, but they also often have a lot of extra transaction log activity, from redundant index maintenance activity\n
- The new SQL Maintenance Plan Wizard in SQL Server 2016 is a huge improvement, at least as far as the UI goes<\/li>\n<\/ol>\n
- Organizations that are using something like Ola Hallengren\u2019s free SQL Server Maintenance Solution<\/a> for their index maintenance\n
\n- These organizations are usually in the best shape<\/li>\n<\/ol>\n<\/li>\n<\/ol>\n","protected":false},"excerpt":{"rendered":"
For Day 27 of this series, we start out with Query #61, which is Volatile Indexes. This query retrieves information from the sys.objects object catalog view, the sys.stats object catalog view, and the sys.dm_db_stats_properties dynamic management function about the most volatile indexes and statistics in the current database. Query #61 is shown in Figure 1. […]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[273],"tags":[272],"class_list":["post-1055","post","type-post","status-publish","format-standard","hentry","category-dmv-diagnostic-queries-detailed","tag-dmv-diagnostic-queries-detailed"],"yoast_head":"\n
SQL Server Diagnostic Information Queries Detailed, Day 27 - Glenn Berry<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-27\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"SQL Server Diagnostic Information Queries Detailed, Day 27 - Glenn Berry\" \/>\n<meta property=\"og:description\" content=\"For Day 27 of this series, we start out with Query #61, which is Volatile Indexes. This query retrieves information from the sys.objects object catalog view, the sys.stats object catalog view, and the sys.dm_db_stats_properties dynamic management function about the most volatile indexes and statistics in the current database. Query #61 is shown in Figure 1. […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-27\/\" \/>\n<meta property=\"og:site_name\" content=\"Glenn Berry\" \/>\n<meta property=\"article:published_time\" content=\"2016-01-27T16:51:53+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2017-04-13T19:28:53+00:00\" \/>\n<meta name=\"author\" content=\"Glenn Berry\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Glenn Berry\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-27\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-27\/\",\"name\":\"SQL Server Diagnostic Information Queries Detailed, Day 27 - Glenn Berry\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/#website\"},\"datePublished\":\"2016-01-27T16:51:53+00:00\",\"dateModified\":\"2017-04-13T19:28:53+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/#\/schema\/person\/57a8972435106bac7970692fcf5edfa7\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-27\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-27\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-27\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"SQL Server Diagnostic Information Queries Detailed, Day 27\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/#website\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/\",\"name\":\"Glenn Berry\",\"description\":\"Semi-random musings about SQL Server performance\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/#\/schema\/person\/57a8972435106bac7970692fcf5edfa7\",\"name\":\"Glenn Berry\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/64bdac8830f25f2f8cc780f8a1286c66ff1182218009271e7a953639596f7e25?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/64bdac8830f25f2f8cc780f8a1286c66ff1182218009271e7a953639596f7e25?s=96&d=mm&r=g\",\"caption\":\"Glenn Berry\"},\"sameAs\":[\"https:\/\/www.sqlskills.com\/blogs\/glenn\/\"],\"url\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/author\/glenn\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"SQL Server Diagnostic Information Queries Detailed, Day 27 - Glenn Berry","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-27\/","og_locale":"en_US","og_type":"article","og_title":"SQL Server Diagnostic Information Queries Detailed, Day 27 - Glenn Berry","og_description":"For Day 27 of this series, we start out with Query #61, which is Volatile Indexes. This query retrieves information from the sys.objects object catalog view, the sys.stats object catalog view, and the sys.dm_db_stats_properties dynamic management function about the most volatile indexes and statistics in the current database. Query #61 is shown in Figure 1. […]","og_url":"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-27\/","og_site_name":"Glenn Berry","article_published_time":"2016-01-27T16:51:53+00:00","article_modified_time":"2017-04-13T19:28:53+00:00","author":"Glenn Berry","twitter_misc":{"Written by":"Glenn Berry","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-27\/","url":"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-27\/","name":"SQL Server Diagnostic Information Queries Detailed, Day 27 - Glenn Berry","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/glenn\/#website"},"datePublished":"2016-01-27T16:51:53+00:00","dateModified":"2017-04-13T19:28:53+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/glenn\/#\/schema\/person\/57a8972435106bac7970692fcf5edfa7"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-27\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-27\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-27\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/glenn\/"},{"@type":"ListItem","position":2,"name":"SQL Server Diagnostic Information Queries Detailed, Day 27"}]},{"@type":"WebSite","@id":"https:\/\/www.sqlskills.com\/blogs\/glenn\/#website","url":"https:\/\/www.sqlskills.com\/blogs\/glenn\/","name":"Glenn Berry","description":"Semi-random musings about SQL Server performance","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqlskills.com\/blogs\/glenn\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.sqlskills.com\/blogs\/glenn\/#\/schema\/person\/57a8972435106bac7970692fcf5edfa7","name":"Glenn Berry","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqlskills.com\/blogs\/glenn\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/64bdac8830f25f2f8cc780f8a1286c66ff1182218009271e7a953639596f7e25?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/64bdac8830f25f2f8cc780f8a1286c66ff1182218009271e7a953639596f7e25?s=96&d=mm&r=g","caption":"Glenn Berry"},"sameAs":["https:\/\/www.sqlskills.com\/blogs\/glenn\/"],"url":"https:\/\/www.sqlskills.com\/blogs\/glenn\/author\/glenn\/"}]}},"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/posts\/1055","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/users\/7"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/comments?post=1055"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/posts\/1055\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/media?parent=1055"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/categories?post=1055"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/tags?post=1055"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}