Recovery Models in SQL Server

Here we are going to discuss about database recovery model in SQL Server.

Recovery Models:
1. Simple
2. Bulk Logged
3. Full

Simple Recovery Model:
a)All the operations are not logged in Log file.
b)Log file size won't grow much.
c)Suitable for LOCAL and STAGE Environment.
d)Can't recover the database to certain point in time state.

Bulk Logged Recovery Model:
a)Only minimal information logged in log file during BULK operation. So, its also called as "Minimal Logging"
b)Log File won't grow much during bulk operation.
c)Suitable during bulk operation, migration.
d)Can't recover the bulk operation data.

Full Recovery Model:
a)All the operation logged in log file.
b)Log file size grow rapidly depends upon transactions.
c)Suitable for PRODUCTION environment.
c)Can recover the database to certain point in time (if we followed proper backup plan).

Here is script to switch to different recovery model.

 ALTER DATABASE testdb SET RECOVERY SIMPLE  
 GO  
 ALTER DATABASE testdb SET RECOVERY BULK_LOGGED  
 GO  
 ALTER DATABASE testdb SET RECOVERY FULL  
 GO  

Rebuilding and Reorganizing Indexes based on Fragmentation in SQL Server

Why we have to Rebuild and Reorganize the indexes?
As the time passes, data pages are scattered/fragmented and not in order due to insert, update and delete operation. So, its better the rebuild / reorganize the indexes to improve the query performance. Otherwise, query response would be slow because its would take more time to collect the data's which is scattered.

Rebuilding Indexes:
1. Its nothing but dropping and creating the indexes.
2. More CPU resources need.
3. Deadlock would happen while rebuilding the indexes.

Reorganizing Indexes:
1. Rearranging the leaf pages in correct order.
2. Less CPU resources need.
3. Deadlock possibility is very less.

When to use REORGANIZE and REBUILD?
Generally if fragmentation percentage for each index is from 5 - 30 percent, reorganize the index. if fragmentation percentage is more than 30 percent, its better to REBUILD the index.

SQL Script to REORGANIZE and REBUILD each index in each table:

 DECLARE @ReOrgLowPercent INT,  
   @ReOrgHighPercent INT,  
   @TableName VARCHAR(200)  
 SET @ReOrgLowPercent = 5  
 SET @ReOrgHighPercent = 30  
 DECLARE @IndexTable TABLE ( AlterStatement VARCHAR(4000) )  
 DECLARE C CURSOR  
   FOR SELECT name  
     FROM  sys.tables  
 OPEN C  
 FETCH NEXT FROM C INTO @TableName  
 WHILE ( @@FETCH_STATUS = 0 )  
   BEGIN  
     --PRINT @TableName  
     INSERT INTO @IndexTable ( AlterStatement )  
         SELECT CASE when avg_fragmentation_in_percent between @ReOrgLowPercent and @ReOrgHighPercent  
                then 'ALTER INDEX ' + i.name + ' ON ' + t.name  
                  + ' REORGANIZE;'  
                when avg_fragmentation_in_percent > @ReOrgHighPercent  
                then 'ALTER INDEX ' + i.name + ' ON ' + t.name  
                  + ' REBUILD WITH(ONLINE=ON);' --- enterpise version obly  
             END  
         FROM  sys.dm_db_index_physical_stats(DB_ID(),  
                             OBJECT_ID(@TableName),  
                             NULL, NULL, NULL) AS a  
             JOIN sys.indexes AS i ON a.object_id = i.object_id  
                          AND a.index_id = i.index_id  
             JOIN sys.tables AS t ON t.object_id = i.object_id  
     FETCH NEXT FROM C INTO @TableName  
   END  
 CLOSE C  
 DEALLOCATE C  
 SELECT AlterStatement  
 FROM  @IndexTable  
 WHERE  
      AlterStatement IS NOT NULL