Finding Execution Time of a SQL Statement
The Code which gives and execution time of given SQL statement.In the above given code,I have taken the example of DBCC command.You can use it for various maintainance statments such as update statistics,...etc:-
- In the above code,You need to change only the objects name.Replace only the object names in the following code name in('test','test1','test3').
- Replace the DBNAME with databasename in the following code SET @DML='dbcc dbreindex(''DBNAME.dbo.'+@TableName+''','''+@Indexname+''')'
- Execution time will be displayed in MS.
/*delcaration of variables*/
DECLARE
@DML nvarchar(500),
@TableName varchar(100),
@RowId int,
@LoopStatus int,
@start_time DATETIME,
@end_time DATETIME,
@name varchar(100),
@Indexname varchar(100)
/*The #Tname object is used to store name and execution time to rebuild an index*/
CREATE TABLE #Tname
(
id int identity(1,1) primary key,
name varchar(200),
ExecTimeMS int
)
/*Detailed index information is stored in the #index table*/
CREATE TABLE #index
(
id int identity(1,1) primary key,
TableName varchar(200),
IndexName varchar(200),
IndexDescription varchar(500),
IndexKeys nvarchar(2000)
)
/*clustered index information is stored in this table*/
CREATE TABLE #clustered
(
id int identity(1,1) primary key,
TableName varchaR(100),
IndexName varchaR(100)
)
INSERT INTO #Tname(name)
SELECT name
FROM sysobjects
WHERE xtype = 'U' and name in('test','test1','test3')
/*Fetching the index information for the above mentioned tables--'test','test1','test3'*/
SET @RowId=1
SET @LoopStatus=1
WHILE @LoopStatus != 0
BEGIN
SELECT @name=name FROM #Tname WHERE (ID=@RowId)
IF @@Rowcount=0
BEGIN
SET @LoopStatus=0
END
ELSE
BEGIN
INSERT INTO #index (IndexName, IndexDescription, IndexKeys)
EXEC sp_helpindex @name
UPDATE #index
SET TableName = @name
WHERE TableName is NULL
END
SET @RowId=@RowId+1
END
/*Inserting only the clusteredIndex keys into a #clustered Table*//*Replace clustered by nonclustered to check for clustered index*/INSERT INTO #clustered (TableName,IndexName)
SELECT TableName, IndexName
FROM #index
WHERE IndexDescription like 'clustered%'--/*Replace nonclustered by clustered to check for clustered index*/
/*Finding the exection time of rebuild an index for a set of objects*/
SET @RowID=1
SET @LoopStatus=1
While(@LoopStatus<>0)
Begin
SELECT @TableName=TableName,@Indexname=IndexName from #clustered where id=@RowID
IF @@Rowcount=0
BEGIN
SET @LoopStatus=0
END
ELSE
BEGIN
SET @DML='dbcc dbreindex(''DBNAME.dbo.'+@TableName+''','''+@Indexname+''')'
SET @start_time = CURRENT_TIMESTAMP
exec sp_executesql @DML
SET @end_time = CURRENT_TIMESTAMP
update #Tname
SET exectimeMS=DATEDIFF(ms, @start_time, @end_time)
where id=@RowId
END
SET @RowID=@RowID+1
END
SELECT T1.TableName,T1.IndexName,T3.ExectimeMS
FROM #clustered T1
JOIN #Tname T3 on T1.TableName=T3.Name
/*droping the temproary table*/
DROP TABLE #index
Drop TABLE #Tname
DROP TABLE #clustered 
