Tuesday, December 06, 2011

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

Thursday, December 01, 2011

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