Archive for December, 2012

How to reorganize and rebuild indexes based on fragmentation percentage?

Index fragmentation is not an ideal situation where the data pages are not contiguous. It is caused by page splits. When data pages become full, SQL Server has to redistribute data from a page that’s full to a new data page. New data pages are created all the way back to the end of the line. When searching, instead of going from one page right to the next, SQL Server has to go to a different location to look for what’s being requested. It is similar to going to a popular club with your friends. If your friends are scattered all over the club, you will need to search for them in different floors or corners instead of being able to speaking directly to them if they were right next to you.

As data is inserted, updated, and deleted, fragmentation levels on the underlying indexes will inevitably rise. There are 2 types of fragmentation: external and internal. External fragmentation causes performance issues for requests for ordered results. It occurs when the logical order of the index does not match the physical order of the index. Internal fragmentation occurs when there is too much empty space in the index pages usually caused by insert, update, and delete operations.

Index fragmentations, both external and internal, will degrade SQL Server query performance increasing the data retrieval time. To report on the indexes fragmentation percentage, you can use the Dynamic Management Function (DMF) sys.dm_db_index_physical_stats in addition to joining it to the sys.indexes, sys.tables, and sys.schemas tables. You can then store the indexes fragmentation information into a temp table and then use the alter index statement to reorganize or rebuild indexes based on the fragmentation percentage. You can schedule a SQL Server Agent job to automate and run this index maintenance process on a desired or preferred time interval. In the following script, we will reorganize indexes with fragmentation percent between 10% to 30% and rebuild indexes if fragmentation >= 30% and rebuild online for the Enterprise editions:

-- Rebuild index if fragmentation >= 30%
-- Reorganize if fragmentation between 10% to 30%

DECLARE @dbname VARCHAR(123) -- 123 is the limit for the database length
  ,@tablename VARCHAR(125)
  ,@schemaname VARCHAR(50)
  ,@indexName VARCHAR(125)
  ,@percentfragmentation DECIMAL (10,2)
  ,@id int -- to store the sequence id of the databases
  ,@max int -- to store the number of databases
  ,@cmdfragmentationload VARCHAR(MAX) -- cmd to load the fragmentation data
  ,@cmddefragmentation VARCHAR(MAX) -- cmd to run the defragmentation process
  ,@edition varchar(50) -- sql server edition
SELECT @edition = LEFT(CAST(SERVERPROPERTY('EDITION') AS VARCHAR),3)

-- create a temp table to store the fragmentation information
IF OBJECT_ID('tempdb..#fragmentation') IS NOT NULL
DROP TABLE #fragmentation

CREATE TABLE #fragmentation
(
id int identity (1,1)
,dbname VARCHAR(123)
,tablename VARCHAR(125)
,schemaname VARCHAR(50)
,indexname VARCHAR(125)
,avgfragmentation DECIMAL (10,2)
)

-- create a temp table to store the list of databases
IF OBJECT_ID('tempdb..#databases') IS NOT NULL
DROP TABLE #databases
CREATE TABLE #databases
(
  id int identity (1,1)
  , dbname varchar(128) not null
)

-- load the database(s) into the temp table
INSERT INTO #databases (dbname)
SELECT '['+name+']'
FROM sys.databases
WHERE name not in ('tempdb') -- databases to exclude
AND state = 0 -- must be online

SELECT @id = 1, @max = max(id)
FROM #databases

-- load the index fragmentation information into the #fragmentation temp table
WHILE (@id <= @max) 
BEGIN   
   SELECT @dbname = dbname      
   FROM #databases      
   WHERE id = @id          

   SET @cmdfragmentationload = 'INSERT INTO #fragmentation   
            (dbname, tablename, schemaname, indexname, avgfragmentation)   
            SELECT '''  +@dbname+ ''' AS DBName        
               ,tb.Name AS TableName        
               ,scmas.Name AS SchemaName        
               ,ix.name AS IndexName        
               ,CONVERT(decimal(10,2),ix_physical_stats.avg_fragmentation_in_percent)   
            FROM ' +@dbname+ '.sys.dm_db_index_physical_stats(DB_ID('''+@dbname+'''), NULL, NULL,                                        NULL, ''DETAILED'') AS ix_physical_stats         
                   JOIN ' +@dbname+ '.sys.indexes ix         
                        ON ix_physical_stats.object_Id = ix.object_id 
                          AND ix_physical_stats.index_id = ix.index_id         
                   JOIN ' +@dbname+ '.sys.tables tb         
                        ON ix.object_id = tb.object_Id         
                   JOIN ' +@dbname+ '.sys.schemas scmas         
                        ON tb.schema_id = scmas.SCHEMA_ID   
            WHERE ix_physical_stats.avg_fragmentation_in_percent > 10
      AND tb.type = ''U''
      AND ix.name IS NOT NULL
      AND ix_physical_stats.page_count > 25'

   EXEC(@cmdfragmentationload) -- load data on each iteration

   set @id = @id + 1 -- increase the @id counter by 1
END

-- defrag the indexes
-- Re-organize: 10% to 30% fragmentation
-- Re-build: >= 30% fragmentation
SELECT @id = 1, @max = MAX(id)
FROM #fragmentation

WHILE (@id <= @max)
BEGIN
  SELECT @dbname = f.dbname
  , @tablename = f.tablename
  , @schemaname = f.schemaname
  , @indexName = f.indexname
  , @percentfragmentation = f.avgfragmentation
  FROM #fragmentation f
  WHERE id = @id

  IF @percentfragmentation < 30 -- fragmentation < 30%, reorganize
  BEGIN
    SET @cmddefragmentation = 'ALTER INDEX ' + '[' +@indexName +'] ON ' +@dbname+ '.[' +@schemaname+ '].[' +@tablename+ '] REORGANIZE'
    EXEC (@cmddefragmentation)
  END
  ELSE -- fragmentation >= 30%, rebuild
  BEGIN
    BEGIN TRY
      IF (@edition = 'Ent' or @edition = 'Dev') -- if enterprise version, then rebuild index online
        BEGIN
          SET @cmddefragmentation = 'ALTER INDEX ' + '[' +@indexName +'] ON ' +@dbname+ '.[' +@schemaname+ '].[' +@tablename+ '] REBUILD WITH(ONLINE = ON)'
           EXEC (@cmddefragmentation)
        END
      ELSE
        BEGIN
          SET @cmddefragmentation = 'ALTER INDEX ' + '[' +@indexName +'] ON ' +@dbname+ '.[' +@schemaname+ '].[' +@tablename+ '] REBUILD'
          EXEC (@cmddefragmentation)
        END      
    END TRY
    BEGIN CATCH
      BEGIN
        SET @cmddefragmentation = 'ALTER INDEX ' + '[' +@indexName +'] ON ' +@dbname+ '.[' +@schemaname+ '].[' +@tablename+ '] REORGANIZE'
        EXEC (@cmddefragmentation)
      END
    END CATCH        
  END

  SET @id = @id + 1 -- always remember to increase the counter by 1
END

REFERENCES:
sys.dm_db_index_physical_stats
http://msdn.microsoft.com/en-us/library/ms188917(v=sql.105).aspx

sys.indexes
http://msdn.microsoft.com/en-us/library/ms173760(v=sql.105).aspx

sys.tables
http://msdn.microsoft.com/en-us/library/ms187406(v=sql.105).aspx

ALTER INDEX
http://msdn.microsoft.com/en-us/library/ms188388(v=sql.105).aspx

How to run full backups for ad hoc emergency roll-outs

Pop Quiz Hotshot: The development team wants you to run an emergency roll-out. Now! The email or ticket has been carbon copied (CC) to your boss and your boss’s boss. You know that possible rollback may be needed if the scripts don’t go as planned (I’m sure that never happened to you). You have requested or collaborated with the development team to notify the users and the required parties of the emergency roll-out and asked them to sign out of the applications or the corresponding systems. The databases are relatively small in size (total < 5 GB). Both you and the development understand that it is a best practice to take the required full backups prior to the roll-out. You must make sure the full backups are accounted for as soon as you can. What do you do? What do you do?

1. Find out the backup location you can run the backups. Modify the path in the following script in line #11.
2. Know what databases the roll-out will affect. Include or exclude the database(s) in line #30.

Don’t worry about backup compression. It is built-in into the script for the editions and versions that have the backup compression capability. Good luck and here we go:

-- Full database(s) backup(s) with date and time appended
-- Change the backup path in line 11
-- Include or exclue the database(s) in line 30

DECLARE @db VARCHAR(123) -- database name, 123 is the limit
DECLARE @path VARCHAR(125) -- backup path
DECLARE @filename VARCHAR(250) -- backup filename
DECLARE @filedate VARCHAR(25) -- append to the backup filename

-- set the backup path
SET @path = 'C:\SQLBACKUPS\' -- set it to the desired path

-- set the date and time format to YYYYMMDD_HHMMSS
SELECT @filedate = CONVERT(VARCHAR(20),GETDATE(),112) +'_'+ REPLACE(CONVERT(VARCHAR(20),GETDATE(),108),':','')

-- create a temp table to store the databases that require backups
IF OBJECT_ID('TEMPDB..#DatabaseBackups') IS NOT NULL
DROP TABLE #DatabaseBackups

CREATE TABLE #DatabaseBackups
(
	ID int identity (1,1)
	, DatabaseName varchar(128) not null
)

-- load the database(s) into the temp table
INSERT INTO #DatabaseBackups (DatabaseName)
SELECT name
FROM SYS.DATABASES
WHERE name not in ('tempdb') -- databases to exclude from the backups
--WHERE name in ('databases to be included')

-- backups the databases
DECLARE @id TINYINT
DECLARE @max TINYINT
DECLARE @dbname VARCHAR(128) -- to store the database name

SELECT @id = 1
	,@max = MAX(ID)
FROM #DatabaseBackups

-- find the SQL Server Edition and Version
DECLARE @edition VARCHAR(50) -- backup compression available for SQL Server 2008 enterprise and 2008 R2 or above
DECLARE @sqlVersion DECIMAL(4,2) -- 8.00, 9.00, 10.00, 10.50
DECLARE @compression VARCHAR(17) -- enable compression
DECLARE @sqlcmd VARCHAR(250)
SELECT @edition = LEFT(CAST(SERVERPROPERTY('EDITION') AS VARCHAR),3)
SELECT @sqlVersion = LEFT(CAST(SERVERPROPERTY('PRODUCTVERSION') AS VARCHAR), 4)
SELECT @compression = 'WITH COMPRESSION'

WHILE (@id <= @max) 
BEGIN 	
  SELECT @dbname = DatabaseName 	
  FROM #DatabaseBackups 	
  WHERE ID = @ID 	 	

  SET @filename = @path + 'Full_' + @dbname + '_' + @filedate + '.bak' 			 	

  IF ((@edition = 'Dev' or @edition = 'Ent') AND (@sqlVersion >= 10.00))
      SELECT @sqlcmd = 'BACKUP DATABASE ' + @dbname + ' TO DISK = ''' + @filename + ''' ' + @compression
  ELSE
      SELECT @sqlcmd = 'BACKUP DATABASE ' + @dbname + ' TO DISK = ''' + @filename + ''' '

  EXEC(@sqlcmd)

  SET @id = @id + 1 -- increase the counter by 1
END

Speed – Pop Quiz Hotshot Clip:

You must determine the SQL Server collation to choose before running the installation

Add the step of “selecting the correct SQL Server collation” to your installation checklist. You do have a checklist right? If not, please consider reading “The Checklist Manifesto: How to Get Things Right” by Dr. Atul Gawande. 2 hours time well spent in understanding the importance of checklists.

Changing the server collation after installation or after the server has been in production will cause sleepless night or nights. Most likely you can’t even head to bed because you will be busy preparing, working on, and verifying the collation change. If you deteremined that you need to change the server collation for an instance that’s in production, you will need to request a scheduled maintenance downtime window of at least 3 hours (the time varies depending on the total size of the databases). Then you will need to read Tim Ford’s posts on “Rebuilding the SQL Server master database.” Yes. All 3 parts.

If you are preparing your “installation checklist” kudos to you in taking the initiative to prepare. You can confirm with the application vendor(s) or the application development team on what is the required server collation. If you are requested to build a new server and the specifications will be the same as one of the existing server, you can run the SERVERPROPERTY(‘Collation’) function to find the server collation for an existing instance.

-- Querying the server collation
SELECT
SERVERPROPERTY('ServerName') AS [InstanceName]
, SERVERPROPERTY('Collation') AS [ServerCollation]

OUTPUT: Screen-shot

REFERENCES:

Using SQL Server Collations
http://msdn.microsoft.com/en-us/library/ms144260(v=sql.105).aspx

Rebuilding the SQL Server master database
http://www.mssqltips.com/sqlservertip/1531/rebuilding-the-sql-server-master-database-part-1-of-3/

Querying the SQL Server Instance Level Info

You are interested in finding out the SQL Server version, edition, service pack, authentication mode, clustering information, collation, the number of logical CPUs, and the server memory in megabytes. Sure, you can right-click the instance in the Object Explorer then go to Properties to view these information. But, what if you have to find these instance level information for 25+ instances? It will be a royal pain in the behind to go to each of the 25+ instances and then typing or copying and pasting these information to Excel if you need to document them. This is where the SERVERPROPERTY() function and the sys.dm_os_sys_info Dynamic Management View (DMV) can save the day or at least save you an hour or more. Here we go:

-- SQL Server Instance Level Info
SELECT	
	SERVERPROPERTY('ServerName') AS [InstanceName],
	SERVERPROPERTY('ProductVersion') AS [VersionBuild],
	SERVERPROPERTY ('Edition') AS [Edition],
	SERVERPROPERTY('ProductLevel') AS [ServicePack],
	SERVERPROPERTY('IsIntegratedSecurityOnly') AS [WindowsAuthentication],
	SERVERPROPERTY('IsClustered') AS [IsClustered],
	SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS [CurrentNodeName],
	SERVERPROPERTY('Collation') AS [Collation],
	[cpu_count] AS [CPUs],
	[physical_memory_in_bytes]/1048576 AS [RAM (MB)]
FROM	
	[sys].[dm_os_sys_info]

OUTPUT: Screen-shot

REFERENCES:

SERVERPROPERTY()
http://msdn.microsoft.com/en-us/library/ms174396(v=sql.105).aspx

sys.dm_os_sys_info
http://msdn.microsoft.com/en-us/library/ms175048(v=sql.105).aspx

The Beginning…

As per Lao-tzu, “The journey of a thousand miles begins beneath one’s feet.” In this case, I will start the journey of thousands of posts with this one. There’s no better day to post the first blog of what many to come for “Hands On SQL Server” on the day after Christmas of 2012. An easy way and day to remember and recall for n years later. Please stay tuned for more posts to come as I intend to share my experiences interacting with SQL Server on a daily basis. I will try my best to make sure all posts align to the motto of practical, relevant, and informative on all the SQL Server topics to be posted.