Hello and welcome to this ApexSQL Defrag demo introduction. ApexSQL Defrag is a powerful application used for scheduled analyzing and defragmenting of SQL server database indexes. Its wide range of features allows users to create and manage custom policies, monitor index fragmentation on multiple servers, as well as to create index fragmentation reports. In this video, we'll demonstrate some of its features and capabilities.
To begin, let's first connect to a SQL server by clicking the Add button from the Servers Group in the Home tab. Select the SQL server and choose the authentication type. To add more SQL servers, click on the Add button again and repeat these same steps. After connecting to a SQL server, the main grid will appear, showing information like names of indexes, fragmentation percentage level, from which table indexes are and types of indexes, databases they belong to.
From here, this data can be further filtered by any of the presented grid columns. Let's group them by the database column, which provides insight into index fragmentation percentage level by our databases. In the Fragmentation tab, index, job, and scan mode-related options are presented, as well as grid and tree view filters. The final result of index fragmentation analysis depends on the selected scan mode. Choose between limited, sampled, and detailed scan modes.
Limited mode-- this is the fastest mode that scans the smallest number of pages. In limited mode, only the pages above leaf level of an index get scanned. Sample mode-- only 1% of all pages in an index or heap get scanned. Detailed mode-- all pages are scanned and included in statistics. It is the slowest mode and takes the most resources when compared with other modes. But it returns the most accurate statistics.
Let us now select AdventureWorks2014 database from the Server panel. Check the Index checkbox from the grid to include all of its indexes. Leave, by default, limited scan mode checked. And click on the Analyze button. And from the dropdown menu, choose the Fast Analysis option. The main grid displays the result of the performed fast scan. In the grid filters group, the fragmentation grid can be customized to show indexes based on their fragmentation level-- high, medium, or low.
Results can also be filtered by fragmentation threshold, where the user can manually specify the percentage of index fragmentation threshold. So indexes with a percentage below the specified threshold will not be shown. Tree view filters filter which databases are going to be shown in the Tree View Explorer based on their fragmentation. For instance, if we deselect high and low filters, then in Tree View Explorer, only databases with medium fragmented indexes will be shown.
Let's now perform a deep detailed scan of all instance indexes by selecting all instances from the server panel, checking the index checkbox from the grid. Selecting detailed scan mode, and choosing the Deep Analysis option from the Analyze dropdown menu. The solution to fragmented indexes is rebuild or reorganize indexes. So let us delve a little deeper into these two terms. Index reorganization is a process where the SQL server goes through an existing index and optimizes it, mitigating index fragmentation more quickly but not as effectively as a complete rebuild.
Index rebuilds are a more heavy-duty process, where the index is deleted and then recreated from scratch with an entirely new structure, free from accumulated fragments and empty space. While index reorganization is a robust clean-up operation which leaves the system state intact, i.e. without locking out affected tables and views, the rebuild process locks affected tables and views for the whole rebuild period, which may result in long downtime that could be unacceptable in some environments.
So which one should we choose? Well, the answer to that question lies in the fragmentation percentage level. If fragmentation is less than 10%, no deep fragmentation is required. It is generally accepted that in a majority of environments, index fragmentation less than 10% is negligible. And its performance impact on the SQL server is minimal. If fragmentation is between 10% and 30%, then it is suggested to perform index reorganization.
And lastly, if fragmentation is higher than 30%, it is highly suggested to perform an index rebuild. These percentages are just rules of thumb and can be fully customized by the user. Lets connect to a SQL server by clicking the Add button from the Servers Group in the Home tab. Select the SQL server and choose the authentication type. Let us now perform index defragmentation for indexes with moderate fragmentation.
In order to perform that, we should do the following. Leave only Medium fragmentation level and the Grid Filter. Select SQL Server from the Server panel if we want to defragment all its indexes, or select a particular database to do the same. Now, check all indexes shown in the grid and click on the Reorganize button. We can pause, stop, or cancel any running job. This is useful if we see any performance issues or blocking due to a particular index rebuild.
The end result will look like this. Now, we have successfully defragmented the selected indexes. We did this manually, but next, we'd like to configure a process to do this automatically and unattended. To automate and schedule SQL server index fragmentation, let's navigate to the Policies tab and click on the Create button. And from the dropdown menu, choose Custom. In the Create policy wizard under the General tab, insert a policy name, optionally, a description.
By clicking on the Target Browse button, the Policy targets explorer opens and presents the opportunity to choose any amount of indexes for maintenance. By clicking on the OK button, the number of all the selected indexes is shown in the targets box. The only thing left to add here in the General tab is the rebuild option, either offline or online. But before selection, let's first distinguish between online and offline index rebuilds.
An online index rebuild makes it possible to