Thursday, January 26, 2012

Handling Indexes Fragmentation

This is a handy SQL Template to get information about the indexes fragmentation on a particular user database. It can be used to rebuild all the indexes based on a rule (ex. index fragmentation > 30% ). or just to get a report of the current  index fragmentation status
the script has below characteristics:



  • the variable @EXECUTE is to decide whether you want to get the current index fragmentation for all the tables or proceed with the full index rebuild
  • Since it is a template, you have to specify the database name and an e-mail address (to get a report) (see the screenshot)
  • I never rebuild indexes on a database when the recovery model is FULL so the script checks for that
  •  If the SQL Edition is Enterprise, then it uses ONLINE= ON option
  •  It uses MAXDOP = 0
  •  It generates a report with the previous and current index fragmentation. This is a HTML report that you should receive by e-mail
This is how you specify the variables in the sql template




Note that @EXECUTE variable is currently 0 so the script will just report the index fragmentation. Remember to change it to 1 when you really want to start the index rebuild process


USE <Database,sysname,model>
GO
SET NOCOUNT ON
GO
DECLARE @EXECUTE bit =-- // 0 = PRINT , 1 = EXECUTE

IF @EXECUTE = 1 SELECT  'REINDEX TABLES' ELSE SELECT 'PRINTING STATEMENTS / REPORT'

SELECT @@servername,serverproperty('Edition'),getdate(),db_name() as Database_Name, recovery_model_desc as [Recovery Model]
FROM sys.databases where name = ''

--ALTER DATABASE SET RECOVERY SIMPLE
--ALTER DATABASE SET RECOVERY FULL

IF (SELECT recovery_model_desc FROM sys.databases where name = '') = 'FULL' AND @EXECUTE = 1
BEGIN
      SELECT 'The database is in FULL recovery model, Reindex cannot be executed'
      GOTO FINAL
END

DECLARE @objectid int;
DECLARE @indexid int;
DECLARE @partitioncount bigint;
DECLARE @schemaname nvarchar(130);
DECLARE @objectname nvarchar(130);
DECLARE @indexname nvarchar(130);
DECLARE @partitionnum bigint;
DECLARE @partitions bigint;
DECLARE @frag float;
DECLARE @rows int;
DECLARE @prtime datetime;
DECLARE @command varchar(max);
DECLARE @Counter int = 1;
DECLARE @Total int = 0;
DECLARE @Initial_Time datetime;
DECLARE @tableHTML_Fragmentation varchar(max);

select @Initial_Time = getdate()

IF (object_id( 'tempdb..#tablesrows' ) IS NOT NULL) DROP TABLE ..#tablesrows ;
     
SELECT object_id,OBJECT_NAME(object_id) TableName,SUM(Rows) Rows
INTO #tablesrows
FROM sys.partitions WHERE index_id < 2 GROUP BY object_id

IF (object_id( 'tempdb..#TMP_DBA_IDX_FRAG' ) IS NOT NULL) DROP TABLE ..#TMP_DBA_IDX_FRAG ;

      CREATE TABLE #TMP_DBA_IDX_FRAG (
      objectid int,
      indexid int,
      partitionnumber int,
      avg_fragmentation_in_percent float,
      new_avg_fragmentation_in_percent float,
      [Rows] bigint)

INSERT INTO #TMP_DBA_IDX_FRAG
SELECT
    IDX.object_id AS objectid,
    IDX.index_id AS indexid,
    IDX.partition_number AS partitionnum,
    IDX.avg_fragmentation_in_percent AS frag,
      0 as new_avg_fragmentation_in_percent,
      RC.Rows
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED') as IDX
INNER JOIN #tablesrows RC ON IDX.object_id=RC.object_id
WHERE index_id > 0 and avg_fragmentation_in_percent >10;


SELECT @Total = count(*) FROM #TMP_DBA_IDX_FRAG;

IF @EXECUTE = 1 PRINT 'REINDEX TABLES' ELSE PRINT 'PRINTING STATEMENTS'
PRINT '======================================================='
PRINT ''
-- Declare the cursor for the list of partitions to be processed.
DECLARE partitions CURSOR FOR SELECT objectid,indexid,partitionnumber,avg_fragmentation_in_percent,[Rows] FROM #TMP_DBA_IDX_FRAG
order by rows desc,objectid,indexid;
-- Open the cursor.
OPEN partitions;
-- Loop through the partitions.
WHILE (1=1)
    BEGIN;
        FETCH NEXT
           FROM partitions
           INTO @objectid, @indexid, @partitionnum, @frag,@rows;
        IF @@FETCH_STATUS < 0 BREAK;
        SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name)
        FROM sys.objects AS o
        JOIN sys.schemas as s ON s.schema_id = o.schema_id
        WHERE o.object_id = @objectid;
        SELECT @indexname = QUOTENAME(name)
        FROM sys.indexes
        WHERE  object_id = @objectid AND index_id = @indexid;
        SELECT @partitioncount = count (*)
        FROM sys.partitions
        WHERE object_id = @objectid AND index_id = @indexid;

            SET @prtime = getdate()
            SET @command = NULL
        --IF @frag < 30.0 and @frag > 15.0
        --    SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE; --('+cast(@frag as varchar)+'%) // '+cast(@rows as varchar) +' rows';
        IF @frag >= 30.0
            BEGIN
            SELECT @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD WITH (MAXDOP =0'+CASE serverproperty('EngineEdition') WHEN 3 THEN ',ONLINE =ON' END+');--('+cast(@frag as varchar)+'%) // '+cast(@rows as varchar) +' rows';                 
            END
        IF @partitioncount > 1
            SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS nvarchar(10));
       
            BEGIN TRY
                  IF @command is not null IF @EXECUTE = 1 EXEC (@command) ELSE PRINT (@command);            
            END TRY
            BEGIN CATCH
            PRINT cast(ERROR_NUMBER() as varchar(10))+'//'+ ERROR_MESSAGE();
                  IF ERROR_NUMBER() = 2725 SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD WITH (MAXDOP =0);--('+cast(@frag as varchar)+'%) // '+cast(@rows as varchar) +' rows (OFFLINE)'            
                  IF @EXECUTE = 1 EXEC (@command) ELSE PRINT (@command)
            END CATCH
                       
        PRINT N'Executed ('+ cast(@Counter as varchar)+'/'+cast(@Total as varchar)+'):' + @command;
            PRINT N'Execution Time: ' +CONVERT ( varchar(30) , getdate() ,120)+'   ('+SUBSTRING(CONVERT ( varchar(30) , getdate()-@prtime ,120),12,1000)+')'
            PRINT N'-----------'
             
            SET @Counter=@Counter+1
    END;
-- Close and deallocate the cursor.
CLOSE partitions;
DEALLOCATE partitions;

UPDATE #TMP_DBA_IDX_FRAG
SET new_avg_fragmentation_in_percent = IDX.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED') as IDX
WHERE objectid = IDX.object_id and indexid=IDX.index_id

select
      t.name
      ,i.name
      ,round(avg_fragmentation_in_percent,2) as  avg_fragmentation_in_percent
      ,round(new_avg_fragmentation_in_percent,2) as new_avg_fragmentation_in_percent
      ,[Rows]
from #TMP_DBA_IDX_FRAG M (nolock)
inner join sys.tables t on M.objectid =t.object_id
inner join sys.indexes i on M.indexid =i.index_id and M.objectid =i.object_id
order by 1

select @@servername as Server_Name,getdate() as [Now],avg(avg_fragmentation_in_percent) as Previous_avg_frag,avg(new_avg_fragmentation_in_percent)  as New_avg_frag
from #TMP_DBA_IDX_FRAG (nolock)
where rows > 4000

DECLARE @avg_fragmentation_in_percent float
DECLARE @new_avg_fragmentation_in_percent float

select  @avg_fragmentation_in_percent=avg(avg_fragmentation_in_percent),
            @new_avg_fragmentation_in_percent=avg(new_avg_fragmentation_in_percent)
      from #TMP_DBA_IDX_FRAG (nolock)
      where rows > 4000



SET @tableHTML_Fragmentation =     
  N'
Previous AVG Fragmentation ('+cast(@avg_fragmentation_in_percent as varchar)+') - New AVG Fragmentation ('+cast(@new_avg_fragmentation_in_percent as varchar)+')


'+      
  N'
'+     
  N'
'+     
  N'
'+  
  CAST ( ( SELECT td = t.name,       '',     
     td = i.name, '',     
    td = cast(round(avg_fragmentation_in_percent,2) as varchar(10)), '',     
     td = cast(round(new_avg_fragmentation_in_percent,2) as varchar(10)), '',      
     td = cast([Rows] as varchar(10))   
    FROM #TMP_DBA_IDX_FRAG M
      inner join sys.tables t on M.objectid =t.object_id
      inner join sys.indexes i on M.indexid =i.index_id and M.objectid =i.object_id
      ORDER BY 1    
     FOR XML PATH('tr'), TYPE      
  ) AS NVARCHAR(MAX) ) +     
  N'
Table Name
Index Name
AVG Fragmentation
NEW AVG Fragmentation
Rows
' ;  
  

DECLARE @MailText varchar(max);
DECLARE @Subj varchar(250)
Set @Subj='DBREINDEX on ('+rtrim(@@servername)+') Database () HAS FINISHED'

SELECT @MailText = 'FULL DATABASE REINDEX ON '+rtrim(@@servername)+' for STARTED AT '+CONVERT(varchar(30), @MailText, 120)+char(13)+' ,FINISHED AT ' +CONVERT(varchar(30), getdate(), 120)


IF @avg_fragmentation_in_percent IS NOT NULL AND @EXECUTE = 1
BEGIN
      EXEC msdb.dbo.sp_send_dbmail
                  @subject                      =@Subj
                  , @body                       =@tableHTML_Fragmentation
                  , @recipients                 =''  
                  , @execute_query_database = ''
                  , @body_format = 'HTML' ;
END

----------------------------------

-- Drop the temporary table.
IF (object_id( 'tempdb..#tablesrows' ) IS NOT NULL) DROP TABLE ..#tablesrows ;
IF (object_id( 'tempdb..#TMP_DBA_IDX_FRAG' ) IS NOT NULL) DROP TABLE ..#TMP_DBA_IDX_FRAG ;



FINAL:
SELECT name,compatibility_level,recovery_model_desc from sys.databases
GO

Wednesday, January 18, 2012

Check Statistics


This script helps to check when was the last time the statistics were updated for all tables/indexes in all the databases, also provides a table modification rate value (percentage) so then you can perform update stats with fullscan for tables/indexes based on a defined rule (ex. stats for tables/indexes not updated in the last 30 days and/or the modification rate is over 30%) 


select @@ServerName, getdate()
/* **************************************************************** */
use master
go
DECLARE @db sysname,
            @cmd varchar(4000)
SET NOCOUNT ON

IF (object_id( 'tempdb..#DBA_STATISTICS_STATUS' ) IS NOT NULL) DROP TABLE #DBA_STATISTICS_STATUS ;

CREATE TABLE #DBA_STATISTICS_STATUS(
id int identity primary key,
database_name sysname,
table_schema sysname,
table_name sysname,
index_name sysname,
table_id int,
index_id int,
modifiedRows int,
rowcnt int,
ModifiedPercent DECIMAL(18,8),
lastStatsUpdate datetime
)

DECLARE Cursor_Statistics CURSOR FOR
SELECT name FROM sys.databases (NOLOCK)
WHERE NAME not in ('tempdb') and Is_Read_only = 0
order by name

OPEN Cursor_Statistics

FETCH NEXT FROM Cursor_Statistics
INTO @db

WHILE @@FETCH_STATUS = 0
BEGIN
      SELECT @cmd = 'USE ['+@db+'];
Insert into #DBA_STATISTICS_STATUS
select
db_name() as database_name,
schemas.name as table_schema,
tbls.name as table_name,
i.name as index_name,
i.id as table_id,
i.indid as index_id,
i.rowmodctr as modifiedRows,
(select max(rowcnt) from sysindexes i2 where i.id = i2.id and i2.indid < 2) as rowcnt,
convert(DECIMAL(18,8), convert(DECIMAL(18,8),i.rowmodctr) / convert(DECIMAL(18,8),(select max(rowcnt) from sysindexes i2 where i.id = i2.id and i2.indid < 2))) as ModifiedPercent,
stats_date( i.id, i.indid ) as lastStatsUpdate
from sysindexes i
inner join sysobjects tbls on i.id = tbls.id
inner join sysusers schemas on tbls.uid = schemas.uid
inner join information_schema.tables tl
on tbls.name = tl.table_name
and schemas.name = tl.table_schema
--and tl.table_type=''BASE TABLE''
where 0 < i.indid and i.indid < 255
and table_schema <> ''sys''
and i.rowmodctr <> 0
and i.status not in (8388704,8388672)
and (select max(rowcnt) from sysindexes i2 where i.id = i2.id and i2.indid < 2) > 0
'
    
    
      --PRINT @cmd
      EXEC (@cmd)

      FETCH NEXT FROM Cursor_Statistics
INTO @db
END

CLOSE Cursor_Statistics
DEALLOCATE Cursor_Statistics

SELECT * from #DBA_STATISTICS_STATUS WHERE ModifiedPercent >= 20 or datediff(DD,lastStatsUpdate,getdate()) >= 30
ORDER BY database_name,rowcnt desc

DECLARE @table sysname,
            @schema sysname,
            @index sysname        

DECLARE Cursor_Statistics CURSOR FOR
SELECT database_name,table_schema,table_name,index_name from #DBA_STATISTICS_STATUS WHERE ModifiedPercent >= 20 or datediff(DD,lastStatsUpdate,getdate()) >= 30
ORDER BY database_name,rowcnt desc

OPEN Cursor_Statistics

FETCH NEXT FROM Cursor_Statistics
INTO @db,@schema,@table,@index


WHILE @@FETCH_STATUS = 0
BEGIN
      SELECT @cmd = 'USE ['+@db+'];
      UPDATE STATISTICS '+@schema+'.'+@table+' '+@index+' WITH FULLSCAN;
      '
    
      PRINT @cmd
      EXEC (@cmd)
      PRINT '--'+cast(getdate() as varchar(20))


      FETCH NEXT FROM Cursor_Statistics
      INTO @db,@schema,@table,@index
END

CLOSE Cursor_Statistics
DEALLOCATE Cursor_Statistics
GO

select @@ServerName, getdate()
/* **************************************************************** */
IF (object_id( 'tempdb..#DBA_STATISTICS_STATUS' ) IS NOT NULL) DROP TABLE #DBA_STATISTICS_STATUS ;

Tuesday, January 3, 2012

New AlwaysOn DMV on SQL 2012

This is the first post this year so I decided to publish something related to the upcoming SQL version (SQL Server 2012)

AlwaysOn is the new High Availability - Disaster Recovery Technique (HA/DR) which is a huge improvement compared with the existing Database Mirroring. (up to 4 read/only replicas. More info http://www.microsoft.com/sqlserver/en/us/future-editions/mission-critical/SQL-Server-2012-high-availability.aspx)

SQL Server 2012 has a new set of Dynamic Management Views and Functions (DMV/DMF).  Most of them are related to AlwaysOn. Note that all of them start with sys.dm_hadr_%


They provide all the state of the Availability groups, replicas, cluster, etc. Useful when you have to troubleshoot

select * from sys.dm_hadr_auto_page_repair

select * from sys.dm_hadr_availability_group_states

select * from sys.dm_hadr_availability_replica_states

select * from sys.dm_hadr_cluster

select * from sys.dm_hadr_cluster_members

select * from sys.dm_hadr_cluster_networks

select * from sys.dm_hadr_database_replica_cluster_states

select * from sys.dm_hadr_database_replica_states

select * from sys.dm_hadr_instance_node_map

select * from sys.dm_hadr_name_id_map