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.

Command: ALTER INDEX REBUILD WITH (ONLINE = ON)*

 

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.

Command: ALTER INDEX REORGANIZE 

 

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.

 

Vs

 

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:

1

Sample Output:

2

 

Recommended Operation based on Fragmentation Percent:

avg_fragmentation_in_percent value Corrective statement
> 5% and < = 30% ALTER INDEX REORGANIZE
> 30% ALTER INDEX REBUILD WITH (ONLINE = ON)*

* 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!!!!!!

 

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.

1

 

Right click on setup and Click Run as administrator
2

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

 

3

 

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

4

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).

 

5

 

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

 

6

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

7

Enter the product key and click Next

8

 

Read and accept the licence Terms to proceed.

9

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.

10

Select the features to install and click Next

11

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

12

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.

 

13

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

14

 

Select the accounts to run SQL Services and Click Next.

15

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

16

 

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.

 

 

17

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.

18

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.

19

 

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.

20

you can change the file locations if you want.

 

21

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.

22

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.

23

 

24

Specify controller name and click Next.

25

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

26

 

27

Click on Install button to start Installation.

28

 

29

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

30

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

 

 

31

 

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)

1

Click Next to Proceed

 

9

 

 

Accept licence terms and click Next

10

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

11

Click Next, once files in Use completes.

12

 

Click Update to upgrade Service pack.

 

13

 

14

Once finished, Click close.

15

 

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.

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.]

 

Linked Server Error–remote proc transaction promotion

Problem:  

I have two SQL Servers Server A and Server B. Both have SQL Server 2008. I needed to fetch data from server B to server A and insert it into a table on server A.  For this purpose, I had created a stored proc on server B and for calling that SP I have added server B as linked server on server A.  When I executed this SP from server A using server B, it returns data without any issue.

However when I try to insert this data to a local table on server A, I am getting following error:

OLE DB provider “SQLNCLI10” for linked server “LinkedServerName” returned message “The transaction manager has disabled its support for remote/network transactions.”.
Msg 7391, Level 16, State 2, Server <ServerName, Procedure <SP Name>, Line 47
The operation could not be performed because OLE DB provider “SQLNCLI10” for linked server “LinkedServerName” was unable to begin a distributed transaction.

 

Solution:

Resolved the issue by changing the linked server property with below command.

EXEC sp_serveroption @server = ‘ServerB’,@optname = ‘remote proc transaction promotion’, @optvalue = ‘false’ ;