Performance MSSQL – Page 6

May 8, 2013May 8, 2013 Mustafa EL-Masry Performance MSSQL

Check Trace File information

by this Script you will Check the trace file information :

SELECT *

FROM fn_trace_getinfo(default);
GO

Result Set Description
Traceid Unique identifier for the trace
Property = 1 Configured trace options
Property = 2 Trace file name
Property = 3 Max file size for the *.trc file
Property = 4 Stop time for the trace session
Property = 5 Current trace status (1 = On and 0 = Off)
Value Current value for the traceid\property combination
*/

–Return that information for all traces running on the system
SELECT traceid, value FROM [fn_trace_getinfo](NULL)
WHERE [property] = 2;
SELECT *
FROM [fn_trace_gettable](‘C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log\log_33.trc’, DEFAULT)
ORDER BY StartTime;

May 5, 2013May 8, 2013 Mustafa EL-Masry Performance MSSQL

Troubleshooting Locking and Blocking Transaction in SQL Server

Important for All DBA , Developer SQL Server

1- How to Monitor Lock and block transaction

At the first we will do example on Lock , block transaction to test on it how to catch this bad transaction

Create Table Table1
(ID int , name Nvarchar(50))
go
Create Table Table2
(ID int , Adress Nvarchar(50))

–Insert Data
insert into Table1 values
(1,’Mostafa’)
GO
insert into Table2
Values (1,’Elmasry’)

Open new window no 1 in SQL Server

BEGIN TRAN Insert_Data

INSERT INTO Table1(ID, Name)

VALUES (2, ‘Moahmed’)

Open new Window No 2 in SQL Server

BEGIN TRAN
UPDATE Table2
SET ID = ‘3’
WHERE Name = ‘Moahmed’;
ROLLBACK TRAN

Now We have more ways I collect it to you this ways like:

Stored procedure
DMV (Dynamic management view)
SQL Server Management Studio Activity Monitor
SQL Server Management Studio Report

Stored procedure

Use master
go
Exec Sp_Who2

DMV (Dynamic management view)

–sys.dm_exec_requests
USE Master
GO
SELECT *
FROM sys.dm_exec_requests
WHERE blocking_session_id <> 0;
GO

–sys.dm_os_waiting_tasks
USE Master
GO
SELECT session_id, wait_duration_ms, wait_type, blocking_session_id
FROM sys.dm_os_waiting_tasks
WHERE blocking_session_id <> 0
GO

— Query for specific lock types
SELECT resource_type, request_session_id, resource_database_id, resource_associated_entity_id, resource_subtype, resource_description, request_status, request_owner_type, request_mode
FROM sys.dm_tran_locks
WHERE resource_type IN (‘PAGE’, ‘KEY’, ‘EXTENT’, ‘RID’);
GO

–sys.dm_tran_locks Join With sys.dm_os_waiting_tasks
SELECT tl.request_session_id, wt.blocking_session_id, DB_NAME(tl.resource_database_id) AS DatabaseName, tl.resource_type, tl.request_mode, tl.resource_associated_entity_id
FROM sys.dm_tran_locks as tl
INNER JOIN sys.dm_os_waiting_tasks as wt
ON tl.lock_owner_address = wt.resource_address;
GO

—View Lockng in Current Database
USE HD
GO
SELECT DTL.resource_type,
CASE
WHEN DTL.resource_type IN (‘DATABASE’, ‘FILE’, ‘METADATA’) THEN DTL.resource_type
WHEN DTL.resource_type = ‘OBJECT’ THEN OBJECT_NAME(DTL.resource_associated_entity_id, SP.[dbid])
WHEN DTL.resource_type IN (‘KEY’, ‘PAGE’, ‘RID’) THEN
(
SELECT OBJECT_NAME([object_id])
FROM sys.partitions
WHERE sys.partitions.hobt_id =
DTL.resource_associated_entity_id
)
ELSE ‘Unidentified’
END AS requested_object_name, DTL.request_mode, DTL.request_status,
DEST.TEXT, SP.spid, SP.blocked, SP.status, SP.loginame
FROM sys.dm_tran_locks DTL
INNER JOIN sys.sysprocesses SP
ON DTL.request_session_id = SP.spid
–INNER JOIN sys.[dm_exec_requests] AS SDER ON SP.[spid] = [SDER].[session_id]
CROSS APPLY sys.dm_exec_sql_text(SP.sql_handle) AS DEST
WHERE SP.dbid = DB_ID()
AND DTL.[resource_type] <> ‘DATABASE’
ORDER BY DTL.[request_session_id];

—View Blocking in Current Database
SELECT DTL.resource_type,
CASE
WHEN DTL.resource_type IN (‘DATABASE’, ‘FILE’, ‘METADATA’) THEN DTL.resource_type
WHEN DTL.resource_type = ‘OBJECT’ THEN OBJECT_NAME(DTL.resource_associated_entity_id)
WHEN DTL.resource_type IN (‘KEY’, ‘PAGE’, ‘RID’) THEN
(
SELECT OBJECT_NAME([object_id])
FROM sys.partitions
WHERE sys.partitions.hobt_id =
DTL.resource_associated_entity_id
)
ELSE ‘Unidentified’
END AS requested_object_name, DTL.request_mode, DTL.request_status,
DOWT.wait_duration_ms, DOWT.wait_type, DOWT.session_id AS [blocked_session_id],
sp_blocked.[loginame] AS [blocked_user], DEST_blocked.[text] AS [blocked_command],
DOWT.blocking_session_id, sp_blocking.[loginame] AS [blocking_user],
DEST_blocking.[text] AS [blocking_command], DOWT.resource_description
FROM sys.dm_tran_locks DTL
INNER JOIN sys.dm_os_waiting_tasks DOWT
ON DTL.lock_owner_address = DOWT.resource_address
INNER JOIN sys.sysprocesses sp_blocked
ON DOWT.[session_id] = sp_blocked.[spid]
INNER JOIN sys.sysprocesses sp_blocking
ON DOWT.[blocking_session_id] = sp_blocking.[spid]
CROSS APPLY sys.[dm_exec_sql_text](sp_blocked.[sql_handle]) AS DEST_blocked
CROSS APPLY sys.[dm_exec_sql_text](sp_blocking.[sql_handle]) AS DEST_blocking
WHERE DTL.[resource_database_id] = DB_ID()

SELECT session_id, command, blocking_session_id, wait_type, wait_time, wait_resource, t.TEXT
FROM sys.dm_exec_requests
CROSS apply sys.dm_exec_sql_text(sql_handle) AS t
WHERE session_id > 50
AND blocking_session_id > 0
UNION
SELECT session_id, ”, ”, ”, ”, ”, t.TEXT
FROM sys.dm_exec_connections
CROSS apply sys.dm_exec_sql_text(most_recent_sql_handle) AS t
WHERE session_id IN (SELECT blocking_session_id
FROM sys.dm_exec_requests
WHERE blocking_session_id > 0)

SELECT t1.resource_type
,db_name(resource_database_id) as [database]
,t1.resource_associated_entity_id as [blk object]
,t1.request_mode
,t1.request_session_id — spid of waiter
,(SELECT text FROM sys.dm_exec_requests as r — get sql for waiter
cross apply sys.dm_exec_sql_text(r.sql_handle)
WHERE r.session_id = t1.request_session_id) as waiter_text
,t2.blocking_session_id — spid of blocker
, (SELECT TOP 1 request_mode
FROM sys.dm_tran_locks t1
JOIN sys.dm_os_waiting_tasks t2
ON t1.request_session_id = t2.blocking_session_id
WHERE request_mode NOT LIKE ‘IX%’
AND resource_type NOT LIKE ‘DATABASE’
AND resource_type NOT LIKE ‘METADATA%’
ORDER BY request_mode desc) AS blocking_lock
,(SELECT text FROM sys.sysprocesses AS p — get sql for blocker
cross apply sys.dm_exec_sql_text(p.sql_handle)
WHERE p.spid = t2.blocking_session_id) AS blocker_text
FROM
sys.dm_tran_locks as t1,
sys.dm_os_waiting_tasks as t2
WHERE
t1.lock_owner_address = t2.resource_address

SQL Server management studio Active monitor.

Write Click on your Instance >>>> Active Monitor >>>> Processes .

SQL Server Management Studio Reports .

Write Click on your Instance >>> Reports >>> Standard Reports >>>> Activity – All blocking Transactions.

Fixing the Locking Issues

it isn’t as simple as just killing the connection that is causing the lock

KILL 56

Some common lock types are:

RID – single row lock
KEY – a range of keys in an index
PAG – data or index page lock
EXT – Extent Lock
TAB – Table Lock
DB – Database Lock

Some common lock modes are:

S – Shared lock
U – Update Lock
X – Exclusive lock
IS – Intent shared
IU – Intent Update
IX – Intent Exclusive
BU – Bulk update

~~Thanks for All~~

~~Eng. Mustafa Elmasry~~

~~MSSQL DBA~~

April 13, 2013May 8, 2013 Mustafa EL-Masry Performance MSSQL

Why UNION ALL is faster than UNION?

Why UNION ALL is faster than UNION?

SQL with Manoj

UNION and UNION ALL are used to combine data or record sets from two different tables. One could combine more than 2 tables. Just like JOINS, UNION combines data into a single record-set but vertically by adding rows from another table. JOINS combine data horizontally by adding columns from another table.

UNION insures you get DISTINCT records from both the tables.
UNION ALL pulls out all records from both the tables with duplicates.

SQL Scripts below proves why UNION ALL is faster than UNION:

Comparison:

Query Plan for UNION:
  |--Sort(DISTINCT ORDER BY:([Union1008] ASC, [Union1009] ASC))
       |--Concatenation
            |--Table Scan(OBJECT:([tempdb].[dbo].[#tempTable1]))
            |--Table Scan(OBJECT:([tempdb].[dbo].[#tempTable2]))

Query Plan for UNION ALL:
  |--Concatenation
       |--Table Scan(OBJECT:([tempdb].[dbo].[#tempTable1]))
       |--Table Scan(OBJECT:([tempdb].[dbo].[#tempTable2]))

Query Plan for UNION ALL with DISTINCT:
  |--Concatenation
       |--Sort(DISTINCT ORDER BY:([tempdb].[dbo].[#tempTable1].[FirstName] ASC, [tempdb].[dbo].[#tempTable1].[LastName] ASC))
       |    |--Table Scan(OBJECT:([tempdb].[dbo].[#tempTable1]))
       |--Sort(DISTINCT ORDER BY:([tempdb].[dbo].[#tempTable2].[FirstName] ASC, [tempdb].[dbo].[#tempTable2].[LastName] ASC))
            |--Table Scan(OBJECT:([tempdb].[dbo].[#tempTable2]))

The Query plan for:
– UNION ALL SELECT above…

View original post 91 more words

December 28, 2011July 20, 2013 Mustafa EL-Masry Index

Identifying Which Databases Have Index Fragmentation Problems

If Not Exists(Select [object_id] From sys.tables Where name = N’dba_indexDefragLog’)
Begin
— Drop Table dbo.dba_indexDefragLog
Create Table dbo.dba_indexDefragLog
(
indexDefrag_id    int identity(1,1)   Not Null
, databaseID        int                 Not Null
, databaseName      nvarchar(128)       Not Null
, objectID          int                 Not Null
, objectName        nvarchar(128)       Not Null
, indexID           int                 Not Null
, indexName         nvarchar(128)       Not Null
, partitionNumber   smallint            Not Null
, fragmentation     float               Not Null
, page_count        int                 Not Null
, dateTimeStart     datetime            Not Null
, durationSeconds   int                 Not Null
Constraint PK_indexDefragLog Primary Key Clustered (indexDefrag_id)
)

Print ‘dba_indexDefragLog Table Created’;
End

If ObjectProperty(Object_ID(‘dbo.dba_indexDefrag_sp’), N’IsProcedure’) = 1
Begin
Drop Procedure dbo.dba_indexDefrag_sp;
Print ‘Procedure dba_indexDefrag_sp dropped’;
End;
Go
CREATE PROCEDURE [dbo].[dba_indexDefrag_sp]

/* Declare Parameters */
@minFragmentation     FLOAT           = 5.0
/* in percent, will not defrag if fragmentation less than specified */
, @rebuildThreshold     FLOAT           = 30.0
/* in percent, greater than @rebuildThreshold will result in rebuild instead of reorg */
, @executeSQL           BIT             = 1
/* 1 = execute; 0 = print command only */
, @DATABASE             VARCHAR(128)    = Null
/* Option to specify a database name; null will return all */
, @tableName            VARCHAR(4000)   = Null — databaseName.schema.tableName
/* Option to specify a table name; null will return all */
, @onlineRebuild        BIT             = 1
/* 1 = online rebuild; 0 = offline rebuild; only in Enterprise */
, @maxDopRestriction    TINYINT         = Null
/* Option to restrict the number of processors for the operation; only in Enterprise */
, @printCommands        BIT             = 0
/* 1 = print commands; 0 = do not print commands */
, @printFragmentation   BIT             = 0
/* 1 = print fragmentation prior to defrag;
0 = do not print */
, @defragDelay          CHAR(8)         = ’00:00:05′
/* time to wait between defrag commands */
, @scanMode             NVARCHAR(8)     = N’Limited’
/* scan level to be used with dm_db_index_physical_stats. Options are DEFAULT, NULL, LIMITED, SAMPLED, or DETAILED. The default (NULL) is LIMITED */
, @debugMode            BIT             = 0
/* display some useful comments to help determine if/where issues occur */
AS
/*********************************************************************************
Name:       dba_indexDefrag_sp

Author: Michelle Ufford, http://sqlfool.com

Purpose: Defrags all indexes for one or more databases

Notes:

CAUTION: TRANSACTION LOG SIZE MUST BE MONITORED CLOSELY WHEN DEFRAGMENTING.

@minFragmentation defaulted to 10%, will not defrag if fragmentation
is less than that

@rebuildThreshold defaulted to 30% as recommended by Microsoft in BOL;
greater than 30% will result in rebuild instead

@executeSQL 1 = execute the SQL generated by this proc;
0 = print command only

@database Optional, specify specific database name to defrag;
If not specified, all non-system databases will
be defragged.

@tableName Specify if you only want to defrag indexes for a
specific table, format = databaseName.schema.tableName;
if not specified, all tables will be defragged.

@onlineRebuild 1 = online rebuild;
0 = offline rebuild

@maxDopRestriction Option to specify a processor limit for index rebuilds

@printCommands 1 = print commands to screen;
0 = do not print commands

@printFragmentation 1 = print fragmentation to screen;
0 = do not print fragmentation

@defragDelay time to wait between defrag commands; gives the
server a little time to catch up

@scanMode scan level to be used with dm_db_index_physical_stats.
Options are DEFAULT, NULL, LIMITED, SAMPLED, or
DETAILED. The default (NULL) is LIMITED

@debugMode 1 = display debug comments; helps with troubleshooting
0 = do not display debug comments

Called by: SQL Agent Job or DBA

Date        Initials Description
—————————————————————————-
2008-10-27 MFU         Initial Release for public consumption
2008-11-17 MFU         Added page-count to log table
, added @printFragmentation option
2009-03-17 MFU         Provided support for centralized execution,
, consolidated Enterprise & Standard versions
, added @debugMode, @maxDopRestriction
, modified LOB and partition logic
2009-05-12 JAP         Added @scanMode
*********************************************************************************
Exec dbo.dba_indexDefrag_sp
@executeSQL           = 0
, @minFragmentation     = 80
, @printCommands        = 1
, @debugMode            = 1
, @printFragmentation   = 1
, @database             = ‘AdventureWorks’
, @tableName            = ‘AdventureWorks.Sales.SalesOrderDetail’;
*********************************************************************************/

SET NOCOUNT ON;
SET XACT_Abort ON;
SET Quoted_Identifier ON;

BEGIN

IF @debugMode = 1 RAISERROR(‘Dusting off the spiderwebs and starting up…’, 0, 42) WITH NoWait;

/* Declare our variables */
DECLARE   @objectID             INT
, @databaseID           INT
, @databaseName         NVARCHAR(128)
, @indexID              INT
, @partitionCount       BIGINT
, @schemaName           NVARCHAR(128)
, @objectName           NVARCHAR(128)
, @indexName            NVARCHAR(128)
, @partitionNumber      SMALLINT
, @partitions           SMALLINT
, @fragmentation        FLOAT
, @pageCount            INT
, @sqlCommand           NVARCHAR(4000)
, @rebuildCommand       NVARCHAR(200)
, @dateTimeStart        DATETIME
, @dateTimeEnd          DATETIME
, @containsLOB          BIT
, @editionCheck         BIT
, @debugMessage         VARCHAR(128)
, @updateSQL            NVARCHAR(4000)
, @partitionSQL         NVARCHAR(4000)
, @partitionSQL_Param   NVARCHAR(1000)
, @LOB_SQL              NVARCHAR(4000)
, @LOB_SQL_Param        NVARCHAR(1000);

/* Create our temporary tables */
CREATE TABLE #indexDefragList
(
databaseID        INT
, databaseName      NVARCHAR(128)
, objectID          INT
, indexID           INT
, partitionNumber   SMALLINT
, fragmentation     FLOAT
, page_count        INT
, defragStatus      BIT
, schemaName        NVARCHAR(128)   Null
, objectName        NVARCHAR(128)   Null
, indexName         NVARCHAR(128)   Null
);

CREATE TABLE #databaseList
(
databaseID INT
, databaseName VARCHAR(128)
);

CREATE TABLE #processor
(
[INDEX]           INT
, Name              VARCHAR(128)
, Internal_Value    INT
, Character_Value   INT
);

IF @debugMode = 1 RAISERROR(‘Beginning validation…’, 0, 42) WITH NoWait;

/* Just a little validation… */
IF @minFragmentation Not Between 0.00 And 100.0
SET @minFragmentation = 5.0;

IF @rebuildThreshold Not Between 0.00 And 100.0
SET @rebuildThreshold = 30.0;

IF @defragDelay Not Like ’00:[0-5][0-9]:[0-5][0-9]’
SET @defragDelay = ’00:00:05′;

/* Make sure we’re not exceeding the number of processors we have available */
INSERT INTO #processor
EXECUTE XP_MSVER ‘ProcessorCount’;

IF @maxDopRestriction IS Not Null And @maxDopRestriction > (SELECT Internal_Value FROM #processor)
SELECT @maxDopRestriction = Internal_Value
FROM #processor;

/* Check our server version; 1804890536 = Enterprise, 610778273 = Enterprise Evaluation, -2117995310 = Developer */
IF (SELECT SERVERPROPERTY(‘EditionID’)) In (1804890536, 610778273, -2117995310)
SET @editionCheck = 1 — supports online rebuilds
ELSE
SET @editionCheck = 0; — does not support online rebuilds

IF @debugMode = 1 RAISERROR(‘Grabbing a list of our databases…’, 0, 42) WITH NoWait;

/* Retrieve the list of databases to investigate */
INSERT INTO #databaseList
SELECT database_id
, name
FROM sys.databases
WHERE name = IsNull(@DATABASE, name)
And database_id > 4 — exclude system databases
And [STATE] = 0; — state must be ONLINE

IF @debugMode = 1 RAISERROR(‘Looping through our list of databases and checking for fragmentation…’, 0, 42) WITH NoWait;

/* Loop through our list of databases */
WHILE (SELECT COUNT(*) FROM #databaseList) > 0
BEGIN

SELECT TOP 1 @databaseID = databaseID
FROM #databaseList;

SELECT @debugMessage = ‘ working on ‘ + DB_NAME(@databaseID) + ‘…’;

IF @debugMode = 1
RAISERROR(@debugMessage, 0, 42) WITH NoWait;

/* Determine which indexes to defrag using our user-defined parameters */
INSERT INTO #indexDefragList
SELECT
database_id AS databaseID
, QUOTENAME(DB_NAME(database_id)) AS ‘databaseName’
, [OBJECT_ID] AS objectID
, index_id AS indexID
, partition_number AS partitionNumber
, avg_fragmentation_in_percent AS fragmentation
, page_count
, 0 AS ‘defragStatus’ /* 0 = unprocessed, 1 = processed */
, Null AS ‘schemaName’
, Null AS ‘objectName’
, Null AS ‘indexName’
FROM sys.dm_db_index_physical_stats (@databaseID, OBJECT_ID(@tableName), Null , Null, @scanMode)
WHERE avg_fragmentation_in_percent >= @minFragmentation
And index_id > 0 — ignore heaps
And page_count > 8 — ignore objects with less than 1 extent
OPTION (MaxDop 1);

DELETE FROM #databaseList
WHERE databaseID = @databaseID;

END

CREATE CLUSTERED INDEX CIX_temp_indexDefragList
ON #indexDefragList(databaseID, objectID, indexID, partitionNumber);

SELECT @debugMessage = ‘Looping through our list… there”s ‘ + CAST(COUNT(*) AS VARCHAR(10)) + ‘ indexes to defrag!’
FROM #indexDefragList;

IF @debugMode = 1 RAISERROR(@debugMessage, 0, 42) WITH NoWait;

/* Begin our loop for defragging */
WHILE (SELECT COUNT(*) FROM #indexDefragList WHERE defragStatus = 0) > 0
BEGIN

IF @debugMode = 1 RAISERROR(‘ Picking an index to beat into shape…’, 0, 42) WITH NoWait;

/* Grab the most fragmented index first to defrag */
SELECT TOP 1
@objectID         = objectID
, @indexID          = indexID
, @databaseID       = databaseID
, @databaseName     = databaseName
, @fragmentation    = fragmentation
, @partitionNumber = partitionNumber
, @pageCount        = page_count
FROM #indexDefragList
WHERE defragStatus = 0
ORDER BY fragmentation DESC;

IF @debugMode = 1 RAISERROR(‘ Looking up the specifics for our index…’, 0, 42) WITH NoWait;

/* Look up index information */
SELECT @updateSQL = N’Update idl
Set schemaName = QuoteName(s.name)
, objectName = QuoteName(o.name)
, indexName = QuoteName(i.name)
From #indexDefragList As idl
Inner Join ‘ + @databaseName + ‘.sys.objects As o
On idl.objectID = o.object_id
Inner Join ‘ + @databaseName + ‘.sys.indexes As i
On o.object_id = i.object_id
Inner Join ‘ + @databaseName + ‘.sys.schemas As s
On o.schema_id = s.schema_id
Where o.object_id = ‘ + CAST(@objectID AS VARCHAR(10)) + ‘
And i.index_id = ‘ + CAST(@indexID AS VARCHAR(10)) + ‘
And i.type > 0
And idl.databaseID = ‘ + CAST(@databaseID AS VARCHAR(10));

EXECUTE SP_EXECUTESQL @updateSQL;

/* Grab our object names */
SELECT @objectName = objectName
, @schemaName = schemaName
, @indexName = indexName
FROM #indexDefragList
WHERE objectID = @objectID
And indexID = @indexID
And databaseID = @databaseID;

IF @debugMode = 1 RAISERROR(‘ Grabbing the partition count…’, 0, 42) WITH NoWait;

/* Determine if the index is partitioned */
SELECT @partitionSQL = ‘Select @partitionCount_OUT = Count(*)
From ‘ + @databaseName + ‘.sys.partitions
Where object_id = ‘ + CAST(@objectID AS VARCHAR(10)) + ‘
And index_id = ‘ + CAST(@indexID AS VARCHAR(10)) + ‘;’
, @partitionSQL_Param = ‘@partitionCount_OUT int OutPut’;

EXECUTE SP_EXECUTESQL @partitionSQL, @partitionSQL_Param, @partitionCount_OUT = @partitionCount OUTPUT;

IF @debugMode = 1 RAISERROR(‘ Seeing if there”s any LOBs to be handled…’, 0, 42) WITH NoWait;

/* Determine if the table contains LOBs */
SELECT @LOB_SQL = ‘ Select Top 1 @containsLOB_OUT = column_id
From ‘ + @databaseName + ‘.sys.columns With (NoLock)
Where [object_id] = ‘ + CAST(@objectID AS VARCHAR(10)) + ‘
And (system_type_id In (34, 35, 99)
Or max_length = -1);’
/* system_type_id –> 34 = image, 35 = text, 99 = ntext
max_length = -1 –> varbinary(max), varchar(max), nvarchar(max), xml */
, @LOB_SQL_Param = ‘@containsLOB_OUT int OutPut’;

EXECUTE SP_EXECUTESQL @LOB_SQL, @LOB_SQL_Param, @containsLOB_OUT = @containsLOB OUTPUT;

IF @debugMode = 1 RAISERROR(‘ Building our SQL statements…’, 0, 42) WITH NoWait;

/* If there’s not a lot of fragmentation, or if we have a LOB, we should reorganize */
IF @fragmentation < @rebuildThreshold Or @containsLOB = 1 Or @partitionCount > 1
BEGIN

SET @sqlCommand = N’Alter Index ‘ + @indexName + N’ On ‘ + @databaseName + N’.’
+ @schemaName + N’.’ + @objectName + N’ ReOrganize’;

/* If our index is partitioned, we should always reorganize */
IF @partitionCount > 1
SET @sqlCommand = @sqlCommand + N’ Partition = ‘
+ CAST(@partitionNumber AS NVARCHAR(10));

END;

/* If the index is heavily fragmented and doesn’t contain any partitions or LOB’s, rebuild it */
IF @fragmentation >= @rebuildThreshold
BEGIN

/* Set online rebuild options; requires Enterprise Edition */
IF @onlineRebuild = 1 And @editionCheck = 1
SET @rebuildCommand = N’ Rebuild With (Online = On’;
ELSE
SET @rebuildCommand = N’ Rebuild With (Online = Off’;

/* Set processor restriction options; requires Enterprise Edition */
IF @maxDopRestriction IS Not Null And @editionCheck = 1
SET @rebuildCommand = @rebuildCommand + N’, MaxDop = ‘ + CAST(@maxDopRestriction AS VARCHAR(2)) + N’)’;
ELSE
SET @rebuildCommand = @rebuildCommand + N’)’;

SET @sqlCommand = N’Alter Index ‘ + @indexName + N’ On ‘ + @databaseName + N’.’
+ @schemaName + N’.’ + @objectName + @rebuildCommand;

END;

/* Are we executing the SQL? If so, do it */
IF @executeSQL = 1
BEGIN

IF @debugMode = 1 RAISERROR(‘ Executing SQL statements…’, 0, 42) WITH NoWait;

/* Grab the time for logging purposes */
SET @dateTimeStart = GETDATE();
EXECUTE SP_EXECUTESQL @sqlCommand;
SET @dateTimeEnd = GETDATE();

/* Log our actions */
INSERT INTO dbo.dba_indexDefragLog
(
databaseID
, databaseName
, objectID
, objectName
, indexID
, indexName
, partitionNumber
, fragmentation
, page_count
, dateTimeStart
, durationSeconds
)
SELECT
@databaseID
, @databaseName
, @objectID
, @objectName
, @indexID
, @indexName
, @partitionNumber
, @fragmentation
, @pageCount
, @dateTimeStart
, DATEDIFF(SECOND, @dateTimeStart, @dateTimeEnd);

/* Just a little breather for the server */
WAITFOR Delay @defragDelay;

/* Print if specified to do so */
IF @printCommands = 1
PRINT N’Executed: ‘ + @sqlCommand;
END
ELSE
/* Looks like we’re not executing, just printing the commands */
BEGIN
IF @debugMode = 1 RAISERROR(‘ Printing SQL statements…’, 0, 42) WITH NoWait;

IF @printCommands = 1 PRINT IsNull(@sqlCommand, ‘error!’);
END

IF @debugMode = 1 RAISERROR(‘ Updating our index defrag status…’, 0, 42) WITH NoWait;

/* Update our index defrag list so we know we’ve finished with that index */
UPDATE #indexDefragList
SET defragStatus = 1
WHERE databaseID       = @databaseID
And objectID         = @objectID
And indexID          = @indexID
And partitionNumber = @partitionNumber;

END

/* Do we want to output our fragmentation results? */
IF @printFragmentation = 1
BEGIN

IF @debugMode = 1 RAISERROR(‘ Displaying fragmentation results…’, 0, 42) WITH NoWait;

SELECT databaseID
, databaseName
, objectID
, objectName
, indexID
, indexName
, fragmentation
, page_count
FROM #indexDefragList;

END;

/* When everything is said and done, make sure to get rid of our temp table */
DROP TABLE #indexDefragList;
DROP TABLE #databaseList;
DROP TABLE #processor;

IF @debugMode = 1 RAISERROR(‘DONE! Thank you for taking care of your indexes!  :)’, 0, 42) WITH NoWait;

SET NOCOUNT OFF;
RETURN 0
END

——————————————————————-

Exec

dbo.dba_indexDefrag_sp

@executeSQL = 0, @minFragmentation = 80

, @printCommands = 1, @debugMode = 1, @printFragmentation = 1, @database =NULL, @tableName =NULL;

Share this:

Share this:

Share this:

Share this: