Thursday 5 February 2015

SQL Server INDEX Fragmentation – Detection and Elimination




     Index should be rebuild when index fragmentation is great than 40%.
   Index should be reorganized when index fragmentation is between 10% to 40%.

--Fragmentation Checking SQL Statement
SELECT OBJECT_NAME(OBJECT_ID) as ObjectName, index_id,index_type_desc,index_level,
avg_fragmentation_in_percent,avg_page_space_used_in_percent,page_count
FROM sys.dm_db_index_physical_stats
(DB_ID(N'DatabaseName'), NULL, NULL, NULL , NULL)
ORDER BY avg_fragmentation_in_percent DESC


avg_fragmentation_in_percent: This is a percentage value that represents external fragmentation. For a clustered table and leaf level of index pages, this is Logical fragmentation, while for heap, this is Extent fragmentation. The lower this value, the better it is. If this value is higher than 10%, some corrective action should be taken.
avg_page_space_used_in_percent: This is an average percentage use of pages that represents to internal fragmentation. Higher the value, the better it is. If this value is lower than 75%, some corrective action should be taken.


---------- Rebuild Index----------
USE DatabaseName
DECLARE @TableName VARCHAR(255)
DECLARE @sql NVARCHAR(500)
DECLARE @fillfactor INT
SET @fillfactor = 80
DECLARE TableCursor CURSOR FOR
SELECT OBJECT_SCHEMA_NAME([object_id])+'.'+name AS TableName
FROM sys.tables
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = 'ALTER INDEX ALL ON ' + @TableName + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')'
EXEC (@sql)
FETCH NEXT FROM TableCursor INTO @TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor
GO

---------- Reorganize Index----------
USE DatabaseName
DECLARE @TableName VARCHAR(255)
DECLARE @sql NVARCHAR(500)
DECLARE TableCursor CURSOR FOR
SELECT OBJECT_SCHEMA_NAME([object_id])+'.'+name AS TableName
FROM sys.tables
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = 'ALTER INDEX ALL ON ' + @TableName + ' REORGANIZE'
EXEC (@sql)
FETCH NEXT FROM TableCursor INTO @TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor
GO

Thursday 29 January 2015

SQL Server DB 2008 Suspect to Normal

-- From Suspect to Normal---
Command 1: EXEC sp_resetstatus 'Tfs_Configuration';
Command 2: ALTER DATABASE Tfs_Configuration SET EMERGENCY
Command 3: DBCC checkdb('Tfs_Configuration')
Command 4: ALTER DATABASE Tfs_Configuration SET SINGLE_USER WITH ROLLBACK IMMEDIATE
Command 5: DBCC CheckDB ('Tfs_Configuration', REPAIR_ALLOW_DATA_LOSS)
Command 6:ALTER DATABASE [Tfs_Configuration]  SET MULTI_USER

Note:
1. Here Tfs_Configuration is the database name
2. All command should be execute from master db

-- From Emergency to previous may be suspect/normal---
ALTER DATABASE Tfs_Configuration SET online

Thursday 29 May 2014

Temp table Limitation


Temp tables are constructs which exist for a limited time in SQL Server, usually the lifetime of a connection. There are a few restrictions for temp tables, like they cannot be partitioned and cannot have FKs. They can have indexes and constraints. 

  • Temporary tables cannot be partitioned
  • Temporary tables cannot reference or be referenced by FOREIGN KEY constraints
  • When a session creates temporary table that contains a named constraint inside a transaction, another sessions cannot create a temporary table of the same name until the first session commits the transaction.


Wednesday 26 June 2013

Retrieve the files (data file, and log files) name and location path fron sql server backup file

Retrieve the files (data file, and log files) name and location path fron sql server backup file say abc.bak

By using the following sql statement we can retrieve the same :)

RESTORE FILELISTONLY FROM DISK='E:\abc.bak'

Saturday 22 June 2013

-- Recursive CTE to return the management chain leading all the way up to the CEO for a specified employee

-- Recursive CTE to return the management chain leading all the way up to the CEO for a specified employee

With EmpCTE as(
Select empid,mgrid, firstname, lastname, 0 as distance
from HR.Employees
where empid = 9

Union ALL

Select M.empid,M.mgrid, M.firstname, M.lastname, S.distance+1 as distance
from EmpCTE As S
Inner Join HR.Employees As M ON M.empid = S.mgrid
)
Select * from EmpCTE

Friday 21 June 2013

NOT IN as same with <> ALL

NOT IN
========
SELECT *
 FROM Emptable
 WHERE (Departmentid NOT IN (SELECT Departmentid
                              FROM Emptable AS Emptable_1
                              WHERE (Empid < 5)
                             )
        )
 GO

<> ALL
======
SELECT *
 FROM Emptable
 WHERE (Departmentid <> ALL (SELECT Departmentid
                              FROM Emptable AS Emptable_1
                              WHERE (Empid < 5)
                             )
        )
 GO

***both query will produce same output.

Thursday 6 June 2013

Why we prefer COALESCE standard function instead of ISNULL Non-standard function

-- Why we prefer COALESCE standard function instead of ISNULL Non-standard function
-- Execute the  following sql statement and judge the output


DECLARE
    @x AS VARCHAR(3) = NULL,
    @y AS VARCHAR(10) = '1234567890';

SELECT ISNULL(@x,@y) AS [ISNULL], COALESCE(@x,@y) AS [COALESCE]