SQL Server index is used mainly to retrieve data from the database tables quickly, by seeking the requested records in the table index directly, rather than scanning the overall table records. The database transactional tables are changing very frequently, and these changes will be reflected in the related table indexes. Due to these changes, the index becomes fragmented over time, with its pages stored in an unordered manner.
As a database administrator, you need to keep monitoring the index fragmentation percentage and fix that issue by reorganizing or rebuilding the index, based on the index fragmentation percentage, as this index will affect the system performance, rather than speeding up the data retrieval processes.
Such SQL Server index maintenance tasks should be performed during non-peak hours, in order not to affect the system performance. Scheduling these maintenance tasks are performed by automation tools, such as the SQL Server Agent Windows service, that is widely used in automating different types of critical and complex business and administrative tasks, to be executed based on a predefined schedule.
But the database administrator cannot sleep beside the server till this maintenance task that may take few hours finishes completely or failed to check the reason behind this failure. So, there should be a notification mechanism that is configured to inform the database administrator the result of that critical maintenance task, in order to take the correct decision.
In this article, we will see how we could configure an email notification for the SQL Server Agent jobs and for the ApexSQL Defrag defragmentation scheduled policies that are used for SQL Server index maintenance.
Set email notification using SQL Server Management Studio
SQL Server Management Studio can be used to set email notification for a new SQL Server Agent job or an existing SQL Agent job in four steps:
- Configure SQL Server Database Mail
- Configure SQL Server Agent to use the Database Mail
- Create a new Operator
- Configure the SQL Server Agent job to send notification via the defined Database Mail
Let us discuss each step in detail!
Configure SQL Server Database Mail
Database Mail is a SQL Server feature that is used to send e-mail messages, that contain queries execution results or SQL Agent job alert, from the SQL Server Database Engine to specific e-mail addresses.
Database Mail can be configured using SQL Server Management Studio by connecting to the SQL Server instance, expanding the Management node under the Object Explorer then right-click on the Database Mail node and choose Configure Database Mail option, as shown below:
From the displayed Database Mail Configuration Wizard, read the brief description that is provided in the Welcome page then click on Next. To prevent showing the Welcome page in the future, check the Skip this page in the future check box before clicking on Next, as below:
From the Select Configuration Task page, select the first option to Set up Database Mail by performing the tasks that are specified under that options, then click Next to proceed:
After enabling the Database Mail feature, if not enabled previously, the New Profile page will be displayed, on which you will be requested to provide a unique name for the Database Mail Profile then click on Add to move to the New Database Mail Account page and provide the requested information to create a new email account. This information includes the name of that account, the email address that will be used to send the notification from about the SQL Server index defrag job, display name for the email account that will be shown in the sent messages, the SMTP mail server address and port number and the SMTP authentication method, as shown below:
Once all required accounts are added to the created profile, click on Next to proceed:
The Manage Profile Security page allows us to configure the email account, under the Public Profiles tab, that can be used by all users who have access on the msdb system database to send email notifications from SQL Server index defrag job, as follows:
Under the Private Profiles tab, specify the users who will use the private profile specified in the same page, then click on Next to proceed:
The Configure System Parameters page provides you will the ability to tune a number of parameters related to the emails, such as a number of accounts retries. Tune the available settings to meet your requirements then click on Next to proceed:
Review all your selections in the Complete the Wizard page, then click on Finish to start the Database Mail configuration process, as follows:
Once the Database Mail configuration steps completed successfully, click on Close to finish the wizard:
Configure SQL Server Agent to use the Database Mail
After configuring the Database Mail profile and accounts, we need to configure the SQL Server Agent to use that Database Mail to notify the users when a specific event is fired or with the status of the SQL Server index defrag job. To achieve that, right-click on the SQL Server Agent, under the Object Explorer, and select the Properties option, as follows:
From the displayed SQL Server Agent Properties page, navigate to the Alert System tab then check the Enable mail profile box and specify the mail profile that will be used by the SQL Server Agent. Click on OK then restart the SQL Server Agent service to proceed:
Create a new Operator
SQL Server Agent Operator is an alias for the users who will receive an email notification when a SQL Agent jobs have completed or an alert has been raised. Check Operators for more information.
To create a new SQL Agent operator, expand the SQL Server Agent node under the Object Explorer, and select New Operator option, as below:
From the displayed New Operator page, provide a unique name and email address for the operator, then tick the checkbox beside the operator name, if not ticked automatically, to enable the operator and click on OK to proceed with the operator creation, as below:
Configure the SQL Server Agent job to send notification via the defined Database Mail
Now we have the Database Mail configured and ready to be used within the SQL Server Agent. In order to configure the SQL Server Agent job to send notifications for the status of the SQL Server index defrag job, we should make sure that the SQL Server Agent Service is up and running, using the SQL Server Configuration Manager, as shown below:
After that, we will create a new Maintenance Plan that rebuilds the indexes on the specified database based on the configured fragmentation threshold. To achieve that, expand the Management node under the Object Explorer, right-click on the Maintenance Plans node and choose the Maintenance Plan Wizard option, as below:
In the displayed Maintenance Plan Wizard, follow the steps to provide:
- A meaningful name for the maintenance plan
- Configure when and how frequent the plan will be executed
- Rebuild Indexes as the maintenance plan task
- The database tables that will be included in this plan
- The fragmentation percentage threshold for the indexes that will be processed by the maintenance plan, as shown clearly below:
For more information about the SQL Server maintenance plans, check SQL Server 2016 Maintenance Plans Enhancements.
Once the maintenance plan created, a SQL Server Agent job will be created to execute the task configured in the maintenance plan based on the specified schedule. To configure a notification for that job, expand the Jobs node under the SQL Server Agent and right-click on the maintenance plan relative job, with SubPlan in the job name, and choose Properties option, as below:
In the SQL Server index defrag Job Properties window, browse to the Notifications tab and check the box beside the Email to send email notification using the created Database Mail when the job failed or completed successfully to the operator, based on your requirements, then click OK to apply the change on the job, as below:
Set email notification using ApexSQL Defrag
ApexSQL Defrag Overview
ApexSQL Defrag tool is a 3rd party SQL index defragmentation tool that you can easily use to review the SQL Server index fragmentation and usage information and apply or schedule the proper maintenance task to fix the index fragmentation issue.
ApexSQL Defrag provides you with the ability to analyze the SQL Server indexes fragmentation information, generate summary reports about the SQL indexes fragmentation issue at different levels, then defrag these fragmented indexes by performing the suitable solution, and all from the same centralized location. By applying the correct filters that ApexSQL Defrag provides us with, you can easily review and maintain only the indexes that you are interested in.
Installing ApexSQL Defrag
Before installing ApexSQL Defrag to your server, you need to download it from ApexSQL Download Center.
ApexSQL Defrag can be easily installed to your server by following the straight-forward installation wizard, as below:
You will be requested first to provide the service account to work as an execution context for the ApexSQL Defrag agent and sends commands to the SQL Server instance, as below:
Then, you need to provide the location where the ApexSQL Defrag tool will be installed and if you need a shortcut icon for the tool to be created in the desktop. After checking all prerequisites, including having 127MB as minimum free space, ApexSQL Defrag will be installed completely, with the ability to start ApexSQL Defrag directly. If the ApexSQL Defrag tool is running for the first time, you should confirm the creation of the central repository database, where the SQL Server indexes historical data and the tool configuration information is saved, as follows:
Add New Server
In order to set an email notification for the SQL Server index defragmentation policy on a specific SQL Server instance, you should connect to that instance and add it to that tool. To do that, click on the Add button, under the Home tab, as shown below:
In the displayed Connect to SQL Server window, provide the name of the SQL Server instance that you plan to use and the credentials for the authenticated user to connect to that server, as shown below:
After validating the provided connection information, the SQL Server instance will be added to ApexSQL Defrag, all the instance databases will be listed and summary information about all indexes at the selected level will be displayed, as shown below:
Configure ApexSQL Defrag Policy to send email notification
Rather than going through the complex procedure of defining the Database Mail, creating email profiles, add accounts to the profile, create an operator and use it to receive email notifications, ApexSQL Defrag provides us with an easy way, with few steps, to create a SQL Server index defragmentation policy, schedule it and configure it to send the policy result via email notification.
To achieve that, click on the Email button, under the Configuration tab, and provide the SMTP server address, port number, email address and password in the Edit Email Configuration window, as shown below:
Then click on the Test Configuration button, provide a test email address to receive the test email and ensure that the email will be received by that email address, as below:
To create a new defragmentation policy, click on the Create button, under the Policies tab, and choose from the available templates or create your customized policy, as shown below:
In the Create Policy window, provide a unique name for that policy then choose the database name, the table name or the list of indexes that will be processed in the created policy, as follows:
Under the Thresholds tab, specify the SQL Server index fragmentation percentage range for both the index reorganize and rebuild operations. ApexSQL Defrag allows you also to specify the threshold for the resources that are consumed by that policy, as shown below:
Similar to the SQL Server Agent jobs, you can easily draw and customize the schedule that defines when and how frequent this policy will be executed, with a helpful summary that describes your choices, as below:
Moving to the Advanced tab, you will be able to set a number of filters that control the SQL Server indexes that will be processed by that policy, as shown below:
And finally, from the Notification tab, specify the policy statuses on which the email notification will be sent and the email addresses for the users who will receive the generated email notifications, as shown below:
After providing all requested information, click on Finish to create the policy. To run the policy manually, select the created policy then click on the Run button under the Policies tab. To ensure that the policy is executed successfully, check the policy result beside the policy name or check the fragmentation percentage for the target indexes, and you will see that the indexes are defragmented successfully as shown below:
Also, you can see the detailed execution logs for that policy under the Alerts tab, with a log if the message was failed to be sent, as below:
If one of the monitored job statuses is fired, a notification email will be received by the configured email address, as shown below:
Conclusion
It is clear from the previous demo that, in a few clicks, we can create a SQL Server index defragmentation policy and configure it to send an email notification about the policy status to a predefined email address, without the need to follow the complex procedure in SQL Server Management Studio to achieve the same goal.
June 13, 2019
