Rebuild or Reorganize

In OLTP database applications, Indexes are subjected to data modification, which makes indexes fragmented (gaps in data pages).

Gaps in data pages can reduce the number of rows that can be stored in SQL Server’s data cache, leading to increased disk I/O. Logical fragmentation can cause extra disk activity, as the disk subsystem has to work harder to find the data on disk and move it to the data cache. The only way to remove wasted space and logical fragmentation is to rebuild or reorganize the indexes on a regular basis.


Rebuild: physically drop and rebuilds indexes from scratch. This removes index fragmentation and updates statistics at the same time. If you use this option, you do not want to run the Reorganize Index or the Update Statistics task, as doing so would be redundant.



Reorganize Index is more of a gentle reshuffling of the leaf-level pages of an index, such that the physical ordering matches the logical ordering, and wasted space is minimized.



Difference between Rebuild and Reorganize:


Reorganize Index Rebuild Index
Performs a less thorough index defragmentation than Rebuild Index Virtually all wasted free space and logical fragmentation is removed.
If an index does not have any fragmentation, then it is not reorganized, saving resources. All indexes are rebuilt from scratch, whether they need it or not.
Does not require long blocking locks. Requires potentially long blocking locks that prevent users from accessing the indexes being rebuilt.
An online task that allows users to access the database during the task. A task that should be performed offline, though with the Enterprise Edition of SQL Server, you can use the online version of rebuilding an index.
Generally takes longer to run than the Rebuild Index Task. Generally runs faster than the Reorganize Index Task.
Uses less disk space than the Rebuild Index Task. Uses more disk space than the Reorganize Index Task.
Uses less space in the transaction log than the Rebuild Index Task. More space is required in the transaction log than the Reorganize Index task.
Index and column statistics must be updated separately. This adds to the administration hassle. Index and column statistics are automatically updated as part of this step, using the FULLSCAN option.




Tracking Index Fragmentation

By using the system function sys.dm_db_index_physical_stats, you can detect fragmentation in a specific index, all indexes on a table or indexed view, all indexes in a database, or all indexes in all databases.


Sample Query to check Fragmentation in a Database:


Sample Output:



Recommended Operation based on Fragmentation Percent:

avg_fragmentation_in_percent value Corrective statement

* Rebuilding an index can be executed online or offline. Reorganizing an index is always executed online.

If the fragmenation % is between 5% and 30%, then Index Reorganize is sufficient. However, If it exceeds 30%, then index Rebuild is recommended.


Index Maintenance frequency and Time

It is difficult to give general advise on how frequently index maintenance (Rebuild/Reorganise/Update Statistics) should be performed, as it depends on the nature of data, indexes and query which used them. However few considerations/recommendations are as below.


* If your indexes fragment rapidly and you have maintenance window every night, then Rebuild indexes every night. There is no harm in doing so.

* If it can’t be run nightly, then at a minimum, perform this activity weekly.

* If you don’t have maintenance window long enough weekly, then consider alternatives :

— Rebuild Indexes Online (only available in Enterprise Edition)

— Reorganize indexes and then update indexes



 –Hope this was helpful!!!!!!



How to Configure Database Mail

Database Mail is an enterprise solution for sending e-mail messages from the SQL Server Database Engine. Using Database Mail, your database applications can send e-mail messages to users.

Configuring Database Mail using SSMS is the easiest way. Please see below the steps


Navigate to the Management folder of the appropriate server, right-click on Database Mail, and select Configure Database Mail



Click Next to pass the welcome screen



select the option Set up Database Mail by performing the following tasks and click Next


If database mail has not yet been enabled for this SQL Server instance, then you will see the screen shown. If it has been enabled, you won’t see the screen. Click Yes and proceed




A profile is a collection of one or more SMTP accounts that can be used by SQL Server to send messages. In other words, when SQL Server wants to send a message, the message is sent to the profile, and then the profile is responsible for seeing that the e-mail is actually delivered. For fault tolerance, a profile can include more than one SMTP account.

To create a new profile, you must enter a profile name, an optional description, and then add and configure one or more SMTP accounts.

Enter Profile Name and Optional Description and click Add to create and configure SMTP Account.



Database Mail Account page.


To create/configure database mail account you need to fill in the details accordingly

Account Name

Optional Description

Outgoing Mail Server (SMTP). This specifies attributes of the SMTP Server that will be sending the e-mail, including these six options:

E-mail address – the e-mail account that has been set up for use with SQL Server’s database mail (for example,

Display name – the display name of the above e-mail address that is shown as part of an e-mail.

Reply e-mail – the e-mail address used if someone should reply to an e-mail sent from the e-mail address entered above. Database Mail can’t respond to e-mails it receives, so you can either leave this option empty, or add your own e-mail address, just in case someone should respond to an e-mail received from SQL Server.

Server name – the name of the SMTP mail server.

Port number – the port number used by your organization’s SMTP server

This server requires a secure connection (SSL) – some SMTP servers require that SSL be turned on for additional security


There are three authentication modes available. In this tutorial, we have used Anonymous authentication (This option is rarely used because it allows anyone to access the SMTP server).  So, select accordingly.

Click OK to return to New Profile page




click on Next to reach the Manage Profile Security screen. I already had two profiles configured, that is why the below screen shows two more profiles.

Now you have to assign the profile as either public or private. A private profile is only usable by specific users or roles, while a public profile allows any user or role (with access to msdb) to send mail.


Click Next to move on to the Configure System Parameters screen.

This option in the Database Mail Configuration Wizard allows you to set the values of specific Database Mail parameters for the profile. Leave them to default and click Next



Review of the summary screen, click on Finish



If the Configuring… screen reports success, then Database Mail has been successfully set up



Now, In order to check whether the configured Database mail is working, right-click on the Database Mail folder in SSMS and select Send Test E-Mail,



In Database Mail Profile, select the profile just created and in To, type in your email address and click on Send Test E-Mail.





Database Mail has been configure.



Configure a SQL Server Agent Operator

configure a user to receive notifications about Microsoft SQL Server Agent jobs

When we configure a Maintenance Plan to send an e-mail, created with either the Maintenance Plan Wizard or the Maintenance Plan Designer, we can’t enter an e-mail address directly into the Maintenance Plan. Instead, we configure e-mails to be sent to an operator.

An operator is an alias for a specific person (such as yourself), or a group (such as a DBA mail group). This alias is more than just a name; it is actually a set of attributes that include the operator’s name, the operator’s contact information, and the operator’s availability schedule.


To create a new operator, open SSMS, navigate to the SQL Server instance you wish to configure, open up the SQL Server Agent folder, navigate to the Operators folder, right-click on it and select New Operator



New Operator screen has lots of options, we will focus only on the three that are most important.
Name – enter the name of the person or group who you want to receive notification e-mails.
Enabled – this option is selected by default, and you don’t want to change it, otherwise you won’t be able to receive any notices from SQL Server.
E-mail name – enter your e-mail address, or the group’s e-mail address here.




That’s it. All the other options are optional, and you can use them if you want to, we have left them to blank. click on OK


How To configure SQL Server Agent to use Database Mail


  • In Object Explorer, expand a SQL Server instance.
  • Right-click SQL Server Agent, and then click Properties.
  • Click Alert System.
  • Select Enable Mail Profile.
  • In the Mail system list, select Database Mail.
  • In the Mail profile list, select a mail profile for Database Mail.
  • Restart SQL Server Agent.


 I hope this was helpful.



Changes in Management Plan overwrites owner of SQL Job


In one of the production SQL Servers, I created a new database.  After which I added that database into the existing Maintenance Plan, meant for Daily backup (This maintenance plan was created long back by another DBA, with his account).  Next day, I noticed that the backup job had failed.  While investigating the issue, I found that the job owner was changed from sa account to old DBA account (Old DBA moved out of organisation long back).



When you change anything on a maintenance plan created by another person, the original creator will be stored as the owner of the job every time you make a change in the SSIS package. This happens because SQL will try to keep the owner of both objects in sync. But the owner of the package is not exposed, so you have to update MSDB by hand to change it to any other thing – lets say “sa” for example.



Code to update the package owner.  Please refer the below link as well.


--view all packages
select * from msdb.dbo.sysssispackages
--update plans ownership
update msdb.dbo.sysssispackages
set [ownersid] = suser_sid('sa')
where [name] like 'MaintenancePlan%'
Note: Replace the name and owner account in the script as per your requirement.

Installing SQL Server 2012

This post will serve as a step by step guide to install SQL Server 2012.   Once you are ready with the OS setup and good to go for SQL Server 2012 Installation follow the below steps.


Copy the sql server 2012 setup onto the machine where you want to install it.



Right click on setup and Click Run as administrator

Go to Installation page and click on New SQL Server stand-alone installation




Setup Support Rules screen check whether all prerequisites are in place.


Click on show details to confirm all rules have passed and proceed by clicking OK. (if there are any failures then exit setup by clicking cancel and resolve the issue before running the setup again).




Setup Support Rules screen check whether all prerequisites are in place.  Click Next to proceed.



In my case, I already have a named instance installed on my machine. That is why add features to an existing instance of SQL 2012 is enabled.

however, ignore this and select Perform a new installation of SQL Server 2012 and click Next


Enter the product key and click Next



Read and accept the licence Terms to proceed.


Here you have two option for installation. first is custom installation, which means you can pick and choose the features you want to install.

Second is to install all Features with Default settings. It is always recommended to install only those features which are required.


Select the features to install and click Next


Setup will run some rules to foresee any issue. Click Next once complete.


Here you have two options to select the type of instance.

Default instance or Named Instance. You can install only only one default instance but can install multiple named instances.



Review the disk space summary. It will not allow you to proceed if you don’t have enough disk space.



Select the accounts to run SQL Services and Click Next.


You can change the collation if you want. Better leave it default and click Next.



Choose the authentication mode. Recommended mode is Windows Authentication, as it is more secure. However, if business require select mixed mode. Enter Sysadmin (sa) password twice and select SQL Server admins for this instance and click Next.




Select the Data Directory. If you want you can change it to some other drive. I have left it default.

As per best practices, Data, log, tempdb and backup files should all be on separate drives.


FILESTREAM enables SQL Server-based applications to store unstructured data, such as documents and images, on the file system. If required, select this feature, else click Next to proceed.



If you have selected Analysis Services in Feature Selection page then you will get this.  Specify Users to have Administrative permissions and click on Data Directories tab.


you can change the file locations if you want.



You will get this screen, if you have selected Reporting Services in Feature Selection Page. You have two options here, first to install and configure Reporting Service or Install only.


Distributed Replay feature helps you assess the impact of future SQL Server upgrades. Similar to SQL Server Profiler, you can use Distributed Replay to replay a captured trace against an upgraded test environment. Unlike SQL Server Profiler, Distributed Replay is not limited to replaying the workload from a single computer.

Specify users who will have permissions for Distributed Replay controller service.  Group accounts are not support. If you enter group account, it will throw validation error as below.




Specify controller name and click Next.


If you want to send windows and SQL Server Error reports to MS then click on check box and click Next.




Click on Install button to start Installation.




After a successful installation, the screen should be all green (as below).


Now you have successfully installed SQL Server 2012. Open the management studio to check.





I hope this article was helpful.


Applying service packs (Hotfixes) to SQL Server Cluster 2008R2

When an instance of SQL Server is configured for HA like Cluster, then it is important to install service packs (hot fixes) in proper sequence.

Installing Service Pack SQL Server 2008/R2 in failover cluster is very different than the SQL Server 2005 cluster failover.

With SQL Server 2005,  install service pack (or hotfix) on the active node and the Setup will launch simultaneously  “remote silence” on all passive nodes. All nodes in the cluster containing the SQL Server instance are updated in the same time.

With SQL Server 2008, to reduce the downtime, the method of deployment is changed.

Below is the sequence to apply Service Pack, Cumulative Update or Hotfix in SQL Server 2008/R2 Cluster (2 Node Active/Passive :

1.  Apply the hotfix on pasive node (Node 2)
2.  Reboot the passive node (Node 2)
3.  Failover SQL resources from Node 1 to Node 2. This will make Node 2 Active and Node 1 Passive.
4.  Apply the hotfix on now passive node (i.e Node 1)
5.  Reboot the passive node Node 1.


Below are the screenshots for reference:

Download the Service Pack 1 and run the .exe (Node 2– Passive Node)


Click Next to Proceed





Accept licence terms and click Next


Make sure the SQL instance which needs to the patched is selected and click Next


Click Next, once files in Use completes.



Click Update to upgrade Service pack.





Once finished, Click close.



This complete the step 1.  Now reboot the currently patched node and then failover SQL resources to Node 2 from Node 1.

Repeat the same process of applying patch, on Node 1 now.  Once finished, take reboot of Node1 and check the status.

This completes the task of applying Service pack on SQL Cluster 2008/R2.


Hope this was helpful.

Installing SQL Server 2008 R2 Cluster

Microsoft Cluster

A Microsoft cluster is a high-availability system designed to keep the database up as much as possible. It does this by resuming processing on the secondary server in the event of a failure on the principal server. Since the data resides on a shared disk, a Microsoft cluster will not survive a data corruption.  This technology is high availability, not disaster recovery. All data resides in the same data center and on the same disk. A cluster can be used with other technologies, such as database mirroring.

Below is the SQL Cluster Architechture:





In order to Install SQL Server Cluster, we need to have Windows cluster ready as a base with some other pre-requisites as well. [ Installing Windows Cluster is out of scope of this article.]

1- Windows Cluster

2- DTC

3- SQL

Below IPs needs to be reserved well in advance to save yourself some time. IP Addresses and Hostnames can be of your choice.



We then need to setup 2 Network Interfaces to be used in production and the other as a heartbeat.

Once the setup is ready to install SQL Server Cluster. Please follow the below steps to configure 2 Node Active/Passive Cluster[SQL Server 2008 R2].



Steps to be performed on Node 1.

  1. Run setup.exe from the installation media to launch SQL Server Installation Center. Click on the Installationlink on the left-hand side1
  2. Click the New SQL Server failover cluster installation link. This will run the SQL Server 2008 R2 Setup wizard2


3. In the Setup Support Rules dialog box, validate that the checks return successful results and click Next.


4. In the Product Key dialog box, enter the product key and click Next.


5. In the License Terms dialog box, click the I accept the license terms check box and click Next.


6. In the Setup Support Rules dialog box, click Install. Validate that the checks return successful results. If the checks returned a few warnings, make sure you fix them before proceeding with the installation.


7. Select the features to install and click Next.



8. Configure the Network Name ( this is going to be the DB cluster name ) and instances



9. In the Disk Space Requirements dialog box, check that you have enough space on your local disks to install the SQL Server binaries and click Next.


10. Choose your Cluster Resource Group ( or leave to default )



11. In the Cluster Disk Selection dialog box, select the available disk groups that are on the cluster for SQL Server to use.



12. In the Cluster Network Configuration dialog box, enter the IP address and subnet mask.



13. In the Cluster Security Policy dialog box, accept the default value of Use service SIDs (recommended).


14. In the Server Configuration dialog box, enter the credentials that you will use for your SQL Server service accounts in the Service Accounts tab. Configure the Service accounts Users and Password as per best practices.



15.  Select the appropriate Authentication Mode. If you want to add the currently logged on user to be a part of the SQL Server administrators group, click the Add Current User button.


16. On the Data Directories tab, enter the path where your system and user database files will be created.


17. Install, But do not configure is the only option available as we haven’t installed the pre-requisites for Reporting Service.



18. Make Sure everything passes on the Cluster installation Rules



19. Review your install Summary and Click Install



20. Now wait for the installation to finish



21. In the Complete dialog box, click Close. Cluster Installation on Node 1 is finished.




Now, start installation on Node 2.

1. Run setup.exe and Click the Add node to a SQL Server failover cluster link


2.  Install Support rules and Make Sure all passes.  Few steps are similar to Node 1 Installation










3. You will see that setup has detected the current Single node cluster from above, click Next




4. Enter the same credentials and click Next.




5. Make sure everything is green.





6. In the Ready to Update dialog box, verify that all configurations are correct and click Install.




7. In the Complete dialog box, click Close. This concludes installing the passive node of a SQL Server 2008 R2 Failover Cluster




Verifying The Cluster





Hope this was helpful.

SQL Server Best Practices – Part 1


  • Use a dedicated server for your database [it will prevent security breaches and leaks. Manageability and Availability will be enhanced]


  • Install only required components. [It is good practice to install only needed features and by doing so it will reduce the attack surface and better resource utilization]


  • Do not install sample databases and sample codes on SQL servers in the production environment. [If needed, install the samples only in the development and test environments]


  • Windows Authentication mode is more secure than SQL Authentication. [Windows Authentication mode is the default and recommended authentication mode]


  • If there is still a need to use SQL Authentication – enforce strong password policy.


  • Disable the SA account and rename it. Do not use this account for SQL server management. [Before Disabling/renaming the SA account, verify that another account with administrative privileges exists in order to have access to the SQL Server.]


  • Keep minimum administrators as it can be.


  • Allocate different administrative accounts if there is more than one administrator. [This will set accountability]


  • Remove BUILDIN\Administrators group from the SQL Server Logins. [Because it gives SQL Server System Administrator rights to any account in the Windows Local Administrators group].


  • Change default SQL Server ports associated with the SQL Server installation to keep hackers from port scanning the server. [Default port (i.e 1433) is well known and is common target for hackers. Therefore it is recommended to change default port associated with the SQL Server installation]


  • Enable logging SQL Server login attempts (failed & successful). [The default mode is:Failed Logins Only. Thus, it is recommended to set the auditing mode to be Both Failed and Successful Logins.]


  • Hide SQL Server Instance from Broadcasting Information [The SQL Server Browser service enumerates SQL Server information on the network. In such way attackers can use SQL Server clients to browse the current infrastructure and retrieve a list of running SQL Server instances. Thus it is strongly recommended to hide SQL instances from being shown in the network]



  • Recent service packs and critical fixes should be installed for SQL Server and Windows. [Service Packs contain important fixes for a product and are tested even more rigorously than incremental releases like cumulative updates, so they’re pretty important to install. But like everything else, they may contain something that has unexpected side effects– so you need to test your application with the service pack before you change it in production.]


No global profile is configured. Specify a profile name in the @profile_name parameter

Yesterday, a developer from application team sent an email saying, he is not able to send mail alerts using database mail from a production server.

I asked which DB Profile name [@profile_name] you are using.   As we have different profiles in different servers.  [Which is not a best practice.  We should use same profile name within our environment to keep uniformity]

He was using the correct profile. Then I requested him to share the error details.

Error Details were as below:

“No global profile is configured. Specify a profile name in the @profile_name parameter”

I realized the profile was not configured as default profile.  So, I made the DB mail profile as default and it resolved the issue.


Below is the procedure to configure Global Profile in Database Mail.

  1. Connect to the target SQL Server with SSMS (SQL Server Management Studio)
  2. Expand the Managementsection
  3. Right-click on Database mailand select Configure Database Mail


4.  From the Wizard, click on Next >


5.  From the Task screen, select Manage Profile Security and click on Next>


6.  From the Manage Profile Security screen, select the target profile, then click in the Default Profile tab to activate the drop-down. Select Yes from the drop-down then click on Next >



7.  From the Complete the Wizard screen, click on Finish




Database Properties

SQL Server includes several database property configuration options that can impact performance and recoverability in a production environment. In this post we will discuss the recommended configuration value as per the best practices of Microsoft.

  • Auto Close
  • Auto Create Statistics
  • Auto Shrink
  • Recovery – Page Verify
  • Recovery Model
  • Auto Update Statistics
  • Auto Update Statistics Asynchronously
  • Compatibility Level


Auto Close

When enabled, the AUTO_CLOSE option causes the database to shut down cleanly and the resources to be freed after the last database user exits. New connections to the database reopen it.

The AUTO_CLOSE option is not intended to be used for frequently accessed, highly concurrent shared databases. Thus, it should be enabled only for single-user desktop applications. Additionally, some of the performance issues experienced with AUTO_CLOSE prior to SQL Server 2005 have been resolved. However, performance issues can still occur in SQL Server 2005 because a database closure results in objects being cleared from the plan cache. Recommended/Default value is FALSE.

Auto Create Statistics

Statistics contain a sampling of values from the index key and the first column of the index key for composite indexes. The query optimizer determines whether an index is useful for a specific query by evaluating the stored statistics. If AUTO_CREATE_STATISTICS is disabled, the statistics will not be created on columns that are not indexed when needed by the query optimization process. The performance of incoming queries may suffer if AUTO_CREATE_STATISTICS is disabled. Thus, you should always keep this option enabled for each user database. Additionally, statistics are essential to the query optimization process. If new statistics are needed but have not been created, query performance will be affected. Recommended/Default value is TRUE.

Auto Shrink

The AUTO_SHRINK option, when enabled, causes data and log files to be automatically shrunk if more than 25 percent of a file has unused space. This option can cause performance and concurrency issues during the shrink process. It can also cause index fragmentation after a shrink process has been performed. Ensure that AUTO_SHRINK is turned off by default. If free space must be generated, do that by using the DBCC SHRINKFILE or DBCC SHRINKDATABASE commands. In addition, always rebuild or reorganize the indexes after a shrink operation in order to fix any fragmentation that might have occurred in the data files.

Torn Page Detection (Page Verify)

When set to ON, this option causes a bit to be reversed for each 512-byte sector in an 8-kilobyte database page where the page is written to disk. If a bit is in the wrong state when the page is later read by SQL Server, the result will indicate that a torn page was detected and that the page was initially written incorrectly.

Recovery Model

The recovery model controls transaction log maintenance and can have serious effects on a system if it is set incorrectly or inappropriately. If the wrong recovery model is specified for the back-up strategy being used, data loss can be experienced during a restore, or log files can grow to the point where they consume all space on the drive.

When the recovery model is set to Simple, no log backups are taken and log space is automatically reclaimed to keep space requirements small. This essentially eliminates the need to manage the transaction log space. However, in Simple mode, you can only recover to the end of the last backup. This is because there are no transaction log backups to apply in order to bring the database to a consistent current state.

When the recovery model is set to Full or Bulk-Logged, log backups are taken and the system can be restored to a current state or to a previous state. When Bulk-Logged is being used, certain operations are not logged. This reduces log space usage by special logging during most bulk operations. In addition, when the Bulk-Logged model is used, point in time recovery is not supported.

Auto Update Statistics

Statistics contain a sampling of values from the index key and the first column of the index key for composite indexes. The query optimizer determines whether an index is useful for a specific query by evaluating the stored statistics. If the statistics become outdated and significant changes have occurred against the underlying data, this can result in less than optimal query performance. If AUTO_UPDATE_STATISTICS is disabled, out-of-date statistics will not be automatically updated.

Ensure that AUTO_UPDATE_STATISTICS is enabled for each user database on the SQL Server instance. For highly volatile data, consider more frequent statistics updates using the system stored procedure, sp_updatestats, or the UPDATE STATISTICS command.

Auto Update Stats Async

AUTO_UPDATE_STATISTICS_ASYNC provides asynchronous statistics updating. When this option is set to On, queries do not wait for the statistics to be updated before compiling. The query and any other concurrent queries compile immediately by using the existing out-of-date statistics. Because there is no delay as a result of waiting for updated statistics, query response times are predictable. However, the out-of-date statistics may cause the query optimizer to choose a less efficient query plan.

You should consider setting the AUTO_UPDATE_STATISTICS_ASYNC option to On when the following characteristics apply to your application:

  • Client request timeouts caused by one or more queries waiting for updated statistics have been experienced.
  • Predictable query response times are required, even at the expense of occasionally running queries with less efficient query plans because of outdated statistics.

Compatibility Level

 Setting the databases to a lower compatibility level should be done only as an interim aid during migration. Keeping the databases at the same level indefinitely will disable some of the new features that later versions provide.