Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Automating SQL Server fragmentation management


By:   |   Last Updated: 2015-03-19   |   Comments   |   Related Tips: 1 | 2 | 3 | 4 | 5 | 6 | More > Fragmentation and Index Maintenance

Problem

SQL Server is a great data repository, but the real advantage of using SQL Server is the ability to query mass amounts of data to quickly find a particular set of data.  With the use of indexing we can greatly improve the time that it takes to complete the process.  Unfortunately the creation of indexes is not a one-time create and forget scenario. As with most things, maintenance is key to ensure optimum performance and this is true for indexing. 

When data is modified the index is also modified to reflect these data changes.  As new data is added SQL Server needs to add new data pages if there is not sufficient space which causes page splits to occur.  Also, when data is deleted this can leave gaps in the index pages. When data is updated this also causes the data to be reorganized in the index which can lead to further index fragmentation. As indexes get fragmented there can be a negative impact to performance, therefore it is important to understand this as well as how to maintain indexes.

Maintaining indexes can be a time consuming and resource intensive process to collect fragmentation data and to rebuild or reorganize your indexes.  Therefore it is best to be selective as to what indexes to maintain as well as performing these tasks during low-peak usage times.  Several rules can be employed to determine if an index rebuild or an index reorganization should take place such as: 

  • What fragmentation levels dictate when an index should be maintained>
  • When should an index rebuild occur versus and index reorganization?
  • If the index is very small, will there be any impact in performing index maintenance or will the fragmentation remain?
  • If an index is very large, will the index rebuild cause blocking during the operation?
  • Should the index be rebuilt using an online operation?
  • and more...

As you can see, it can get quite complex to come up with a strategy to deploy across your environment.  This continues to get even more complex when you increase the number of databases and SQL Server instances you need to manage.  Each database has unique characteristics and usage and therefore fragmentation can occur at different speeds and different levels from one database to the next as well as from server to server.  As you can imagine, fragmentation can become quite difficult to manage on a large scale.

Solution

SQL Server offers several native tools, such as DMVs (sys.dm_db_index_physical_stats) and index maintenance commands (ALTER INDEX), that you can use to check and maintain index fragmentation.  The downside is that these tools are just the starting point and you still need to develop a strategy that works for your environment.  For small, low usage databases it is not usually a problem to take a brute force approach and rebuild all indexes every night, but as database use, tables and indexes grow you are limited by time and server resources, therefore you need to be more selective. 

To automate processing, you can create scripts and setup SQL Server Agent Jobs, but this is taking a blind approach to maintenance without really knowing what is occurring.  With so much data it is hard to visualize the real impact, so having a tool that takes managing fragmentation to the next level can be a real asset especially when you have dozens of instances and hundreds of databases.

Introducing SQL Defrag Manager

SQL Defrag Manager allows us to take all of this into consideration and offers an easy way to detect, monitor and manage index fragmentation in an easy to use GUI.  By adding functionality on top of what you can get natively from SQL Server, SQL Defrag Manager provides a robust graphical environment to manage fragmentation across all of your databases and instances. 

Some of the benefits include:

  • Graphical Interface - SQL Defrag Manager provides a graphical way to see and manage index fragmentation.  Instead of having to look at a bunch of data points, you can visually see the objects that need attention.
  • Managing your entire SQL Server environment - See fragmentation issues across all of your instances, databases and indexes in a unified GUI.
  • Automation Policies - Let's you use automated policies to check for fragmentation levels and have SQL Defrag Manager either alert you or take corrective action.
  • Historical Data Repository - See historical records for fragmentation, so you can see fragmentation levels over time as well as the impact from the index rebuild or the reorganization.
  • Management Reports - There are several built-in reports that you can use to review the collected data.
  • To Do Lists - SQL Defrag Manager can create To Do lists of work to be done instead of having the tool automatically rebuild or reorganize an index.
  • System Resource Checks - Before rebuilding or reorganizing and index, SQL Defrag Manager can make sure there are sufficient system resources and minimal database usage to limit contention.

Maintaining Indexes

The primary reason for maintaining indexes is to ensure SQL Server performance is not degraded based on the index structure or the location of the index pages in the database file due to fragmentation.  As you know there are several items that can impede database performance such as the amount of data, bad query plans, missing indexes, slow disk subsystems, processor contention, network contention and more.  So from a holistic approach to achieve optimum database performance, managing and maintaining your indexes is just another part of the solution.

Being smart about how you manage fragmentation is also another key part to the solution.  Just because you can rebuild or reorganize an index doesn't necessarily mean that you should.  You need to know when fragmentation occurs, if a rebuild or a reorganization makes more sense and whether the index maintenance does indeed improve performance.  You also need to know the impact on your systems when data collection occurs as well as when the actual index maintenance occurs.  The last thing you want to do is cause more problems when you are trying to fix a problem.

Analyzing and Detecting SQL Server Fragmentation

SQL Defrag Manager allows you to analyze your entire SQL Server environment (all instances and databases) or analyze a database or even just a specific index.  After registering the SQL Server instances, you have three options for identifying fragmentation issues.

  • Fast - This is the default option when getting fragmentation statistics from SQL Server.  This creates the least amount of overhead, but doesn't collect all fragmentation details.
  • Thorough - Sampled - This option reads a sample portion of your index to gather fragmentation information as well as collects more details about fragmentation.
  • Thorough - Detailed - This option reads through all of the index pages to determine fragmentation levels and provides all details

As an example, I registered one server and I had SQL Defrag Manager analyze all of the databases for this instance of SQL Server. To do this, I clicked on the Analyze button as shown below.

sql degrag manager menu items

After clicking the Analyze button, a new window opens that allows me to select the instance, the databases and the type of analysis that I want SQL Defrag Manager to perform.

analze fragmentation

After analyzing the indexes, SQL Defrag Manager will store this data in its repository, so there is a historical record.  In addition, the data is presented in a graphical representation for each database to show where fragmentation issues exist. Below we can easily see the AdventureWorks2012 database has fragmentation (we can tell because part of the pie chart is red).

Analyze Fragmentation Databases

To weed out the noise and focus on the issues, I can use the filter as shown below. To only display Critical or Caution issues (things that are problems) as well as using the Criteria option to not include smaller tables (we only want to include indexes with at least 100 pages).

filter output

If we drill into the AdventureWorks2012 database we can then see objects that are fragmented.  In this case there are two tables and three indexes.

tables with fragmentation

If we drill into the Person.Person table we can see the specific items that have fragmentation issues.  In this case it is the IX_Person_LastName_FirstName_MiddleName index.  If we hover over each of the items below we can get more information about the fragmentation for this index.

person table fragmentation

From the image above we can see the following:

  • Average Fragmentation - 99.08% (the lower the number the better - 0% no fragmentation to 100% totally fragmented)
  • Pages for Index - 218 (number of 8K pages to store the index data)
  • Average Pages per Fragment - 1 (number contiguous pages per fragment)
  • Page Density - 69.46% (the higher the number the better - 100% means the entire page is filled with data)

In this example, I was only looking at one instance of SQL Server that had eight databases.  In a few simple clicks I could see the one database, the two tables and three indexes that were fragmented.  Take this to the next level where you have multiple instances and hundreds of databases having quick access to the data will save lots of time and allow you to get a better understanding of your environment and do a better job as a DBA.

Defragmenting Indexes

Now that I know which objects need to be addressed, I can use SQL Defrag Manager in one of many ways to fix the fragmentation issues. I will walk though these options, but the real power is the Automate option which we will cover in the next section.  With each of these options you have the ability to either rebuild, rebuild online or reorganize the fragmented indexes (the rebuild online option is a SQL Server Enterprise edition feature).

I can click on the Defragment button and fix issues for instances or databases. 

sql degrag manager menu items

After I click on the button a window pops up as shown below. This allows me to select the instances and databases and whether I want to reorganize or rebuild the indexes.  This is great if you want to tackle everything at once, but the real power comes with the Automate feature which we will look at below.

defragment options

If I want to defrag a specific instance, database, table or index I can right click on the object in the object explorer and select one of the options as shown below.

degrag index

Automating Index Maintenance

The real power of SQL Defrag Manager is the ability to automate maintenance.  When you install SQL Defrag Manager it is preloaded with eight automation policies that let the work happen behind the scenes on a scheduled basis.  You can also create your own policies and modify the preloaded policies to meet your needs. The automation processes will collect fragmentation information, rebuild or reorganize the fragmented indexes based on your settings as well as create To Do lists if you don't want to automatically rebuild or reorganize an index.  Once these settings have been tweaked to meet your needs, you can let SQL Defrag Manager handle fragmentation automatically for you.  You can then periodically use the GUI to check on things to make sure everything is processing as planned.  Let's take a closer look.

If you go to the Automation Policies tab in the tool you will see a list of the predefined polices as shown below.  As you can see from the description there are policies to rebuild, reorganize as well as to create To Do lists.  The To Do lists below are for large objects where you may not want to automatically rebuild or reorganize, but you want to know there are issues and then you can address as needed.

policies scheduled activity

If we look at the first policy "Rebuild Online Daily Top 10" we can see the details for this policy below. For this policy we are looking at only the top 10 most fragmented indexes that are over 70% fragmented.  This will get run every day at 1:00 AM and rebuild only indexes that meet this criteria.

policy details

Building a policy is pretty straightforward.  There are several components to building a policy as shown above where you can specify fragmentation levels, the size of the index, whether you want to rebuild or reorganize, the number of indexes to rebuild in a batch, and more.  The one thing that is very unique to this process is the Resource Check.

The Resource Check allows SQL Defrag Manager to look at how busy the system is before rebuilding or reorganizing indexes. This is so maintenance processing doesn't impact performance if the system is currently busy with other activities.  Usually you want to maintain indexes during low peak usage and the resource checks make sure this is the case.

Here is a list of the resources that can be checked as part of the policy.  You can look at things such as CPU usage, memory usage, active sessions, connections and more.  You can turn on one or more of these checks as well as set the threshold values you want to use for your policy.

policy resouce checks

As you can see you can get pretty granular with policies and that's why it's helpful to have these predefined policies that you can use or tailor to meet your needs.

The Schedule part of the policy is pretty much like the scheduling you can do with SQL Server Agent jobs.  SQL Defrag Manager has its own Windows service that processes the policies based on the schedule.  This does not create SQL Server Agent jobs on any of your servers.  Here is a quick look at the scheduling options.

sql defrag manager scheduler

Historical Records

Another nice feature of SQL Defrag Manager is the ability to show historical information about index fragmentation as well as when index maintenance occurred. This is in the Recent Activity section of the tool. Below we can see information related to the Person.Person table that we looked at earlier.  We can see the fragmentation levels for each of the indexes.  We can also see that this data was collected via a manual process and not a policy if we look at the Type column.

historical record of events

We can also see in the history when the index was reorganized, the current fragmentation level and the percentage change in fragmentation after the operation finished.

index fragmenation data

If we look at the detail view we can also see how long the process took and the before and after details.

index fragmentation details

Recommendation

As you can see, SQL Defrag Manager takes managing fragmentation to the next level.  The tool allows you to manage your entire SQL Server environment and visually see where there are fragmentation issues.  Whether you have one database or hundreds, having a graphical environment to see and manage fragmentation is much easier than dealing with lots of data points.  As mentioned above, the real power comes with the Automation Policies.  By setting up policies for your environment and scheduling these policies to run, you can tailor your index maintenance processing to meet your unique needs.  As your policies mature over time, based on reviewing what is occurring, you can optimize your index maintenance.  Also, having the ability to check system resources prior to an index rebuild or reorganization is key to not creating more problems when performing maintenance.

So download a free 14 day fully functional trial of SQL Defrag Manager and see for yourself.

Next Steps

MSSQLTips.com Product Spotlight sponsored by Idera makers of SQL Defrag Manager.



Last Updated: 2015-03-19


get scripts

next tip button



About the author




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools