{"id":11158,"date":"2020-10-08T15:20:34","date_gmt":"2020-10-08T13:20:34","guid":{"rendered":"https:\/\/www.codemotion.com\/magazine\/?p=11158"},"modified":"2020-10-08T17:06:39","modified_gmt":"2020-10-08T15:06:39","slug":"sql-server-index-fragmentation","status":"publish","type":"post","link":"https:\/\/www.codemotion.com\/magazine\/backend\/sql-server-index-fragmentation\/","title":{"rendered":"Identifying and Resolving SQL Server Index Fragmentation"},"content":{"rendered":"\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t
\n\t\t\t\t\t\t\tTable Of Contents\t\t\t\t\t\t<\/div>\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t
\n\t\t\t\t\t\t
  1. The reason for variations in Index Fragmentation percentage<\/a>
  2. Improving DB performance and resolving Index Fragmentation<\/a>
  3. How to determine the usage of the equation?<\/a>
  4. Conclusions<\/a><\/ol>\t\t\t\t\t<\/div>\n\t\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\n\n\n

    In this SQL technology guide<\/strong>, we explore how to spot and resolve Index<\/span> Fragmentation<\/span> in SQL Server<\/span><\/strong>. As administrators<\/a> will know, identification of Index<\/span> fragmentation and maintenance<\/span> of the same are crucial elements in database maintenance<\/span>.<\/p>\n\n\n\n

    MS SQL Server<\/span><\/strong> always updates index statistics whenever an Insertion, Updating, or Deletion activity is performed on the table<\/span>. Index<\/span> fragmentation is the index performance<\/span> value, described in percentages, which can easily be fetched by the SQL Server<\/span> DMV. Based on the index performance<\/span> value, users<\/span> can easily correct the indexes in maintenance<\/span> by simply revising the fragmentation percentage, using the Reorganize or Rebuild operations.<\/p>\n\n\n\n

    \"microsoft<\/figure><\/div>\n\n\n\n

    The reason for variations in Index Fragmentation percentage<\/h2>\n\n\n\n

    There could be variations in Index<\/span> Fragmentation percentages<\/span><\/strong> because the page<\/span> orders may not coordinate with the actual order of the physical pages<\/span> in the page<\/span> allocation index. If data<\/span> is modified in the table<\/span>, information may be resized on the data<\/span> page<\/span>. The page<\/span> may be too full even before the update operation is executed throughout the table<\/span>.<\/p>\n\n\n\n

    However, it’s possible to create free space on the data<\/span> page<\/span><\/strong> by performing<\/span> an update operation on the table<\/span>. The users<\/span> can then observe page<\/span> order on the table<\/span> by running a massive deletion operation. The page<\/span> must not be either too full or empty when running<\/span> these updating and deletion operations. Un-utilized free space may again cause an order mismatch between the logical page<\/span> and the actual physical page<\/span>, which increases fragmentation and ends up compromising query performance<\/span>. It may also consume more server resources<\/span> and tends to increase the load<\/strong>.<\/p>\n\n\n\n

    It is also essential to say that index fragmentation will adversely affect query performance<\/span><\/strong> only with a page<\/span> scan. In this case, it will increase the chances of poor performance<\/span> on other SQL<\/a> requests, and queries with highly-fragmented indices across the table<\/span> may take additional time to execute. Such queries may consume a lot more resources<\/span> – CPU<\/span>, Cache<\/span>, and IO<\/span>. The remaining<\/span> SQL<\/span> requests may find it tough to complete the operation<\/span> using the inconsistent server resources<\/span>. Tasks may even be blocked by running the Delete<\/span> or Update<\/span> operations. The optimizer may not gather any information on index fragmentation while generating the given query execution plan<\/span>.<\/p>\n\n\n\n

    Improving DB performance and resolving Index Fragmentation<\/h2>\n\n\n\n

    In this case, there may be many indexes created on a given table<\/span> with combinations of different columns, and each of these indexes may show varying fragmentation percentages. Before deciding what is appropriate or considering an index that is in maintenance, users<\/span> may have to find the right threshold value for the given database. For performance tuning<\/span><\/strong> of your enterprise database there are advanced solutions, such as RemoteDBA.com<\/a><\/strong>. Providers like Remote DBA have gained the relevant skills and expertise in performance tuning<\/span>, database fragmentation, troubleshooting, and various other tasks by handling many projects<\/span> with a subtle combination of all these use cases<\/span>.

    Finding the object details ideally uses the T-SQL<\/span> statement<\/span> given below:<\/p>\n\n\n\n

    \"T-SQL<\/figure><\/div>\n\n\n\n

    It is noticeable that 99% is the maximum average fragmentation, which may be engaged with another action to reduce fragmentation using choices such as REBUILD<\/code> or REORGANIZE<\/code>. These are commands<\/span> for index maintenance<\/span>, which can be executed within the statement<\/span> of ALTER INDEX<\/code>. Users<\/span> can also execute this command<\/span> using the SSMS.<\/p>\n\n\n\n

    You may also note that REORGANIZE<\/code> and REBUILD<\/code> are two choices to execute the trim operation<\/span> over the given page<\/span>. However, this operation<\/span> needs to be executed during off-peak hours only to avoid any adverse impact<\/span> on users<\/span> or transactions<\/span>. The Enterprise<\/span> Edition of Microsoft SQL<\/span> Server supports the online<\/span> indexes, and the offline<\/span> features are supported by the REBUILD<\/code> index.<\/p>\n\n\n\n

    INDEX REBUILD<\/code> tends to first drop the index and then reproduce it with new index pages. However, this task can be run simultaneously using the online<\/span> option, which is available in the Enterprise<\/span> Edition using the ALTER INDEX<\/code> command<\/span>. However, this may not affect the run request or tasks running on other similar tables<\/span>. The REBUILD<\/code> Index<\/span> can also be set offline<\/span> by using the command<\/span> below:<\/p>\n\n\n\n