DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS   SQL 2014 PROGRAMMING   DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to measure query execution time in millisecs?

Execute the following Microsoft SQL Server T-SQL scripts using SSMS Query Editor in TEST ENVIRONMENT to find out the SQL execution time of the inner main queries in milliseconds. The execution time measurement is done by the outer (enveloping) query.

-- SQL execution time - sql server query execution time - t sql execution time

-- SQL view performance - sql server execution plan - sql elapsed time

DECLARE @StartTime datetime

DBCC DROPCLEANBUFFERS  -- Force data/index pages out of buffer cache for valid test

SET @StartTime = GETDATE() -- Measurement Starts  

SELECT * FROM AdventureWorks.Sales.vSalesPersonSalesByFiscalYears

-- Measurement Ends  

SELECT ExecutionTimeInMS = DATEDIFF(millisecond, @StartTime, getdate())

GO

/* Execution time measurement result:

 

ExecutionTimeInMS

93

*/

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

 

-- SQL stored procedure execution time - t sql script execution time

DECLARE @StartTime DATETIME, @EndTime   DATETIME

DBCC DROPCLEANBUFFERS 

SET @StartTime = GETDATE()

EXEC AdventureWorks2008.[dbo].[uspGetBillOfMaterials] 800, '2003-02-02'

SET @EndTime = GETDATE()

SELECT      StartTime= CONVERT(VARCHAR,@StartTime,121),

            EndTime = CONVERT(VARCHAR,@EndTime,121),

            DurationInMS =  CONVERT(VARCHAR, DATEDIFF(ms,@StartTime, @EndTime))

/*    StartTime               EndTime                       DurationInMS

      2012-06-30 03:34:30.267 2012-06-30 03:34:30.330       63

*/


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

-- SQL Server elapsed time - sproc execution - statistics io: reads/writes

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

DBCC DROPCLEANBUFFERS

SET STATISTICS IO ON

SET STATISTICS TIME ON

EXEC AdventureWorks2008.[dbo].[uspGetBillOfMaterials] 800, '2003-02-02'

SET STATISTICS TIME OFF

SET STATISTICS IO OFF

/* Partial results in Messages

 SQL Server Execution Times:

   CPU time = 0 ms,  elapsed time = 64 ms.

*/

/*  Partial statistics IO information

Table 'Worktable'. Scan count 2, logical reads 509, physical reads 0,

Table 'Product'. Scan count 0, logical reads 178, physical reads 4,

Table 'BillOfMaterials'. Scan count 90, logical reads 181, physical reads 4,

*/

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

 

-- Execution time of a recursive query - sql server execution time

USE AdventureWorks;

 

CHECKPOINT

DBCC FREEPROCCACHE -- Delete this line to exclude recompile time

DBCC DROPCLEANBUFFERS

go

DECLARE @StartTime datetime, @EndTime datetime

SET @StartTime = CURRENT_TIMESTAMP;

 

-- **********QUERY TO BE TUNED *************

WITH cteBOM(ProductID, [Level], Qty)

AS

(

SELECT ComponentID, [Level]=convert(int,0), convert(int,PerAssemblyQty)

FROM Production.BillOfMaterials

WHERE ComponentID = 750

AND ProductAssemblyID IS NULL

 

UNION ALL

 

SELECT bom.ComponentID, [Level]=[Level]+1, convert(int,cteBOM.Qty * bom.PerAssemblyQty)

FROM cteBOM

JOIN Production.BillOfMaterials AS bom

ON bom.ProductAssemblyID = cteBOM.ProductID

AND StartDate <= '2001-01-01'

AND (EndDate is null or EndDate >= '2001-01-01')

)

SELECT cteBOM.ProductID, cteBOM.Level,

ProductName=p.Name, Quantity=cteBOM.Qty

FROM cteBOM

JOIN Production.Product AS p

ON p.ProductID = cteBOM.ProductID

ORDER BY Level, p.ProductID;

-- *****************************************

 

-- MSSQL execution time - SQL Server performance

SET @EndTime = CURRENT_TIMESTAMP

SELECT ExecutionTimeInMS = DATEDIFF(ms, @StartTime, @EndTime)

GO

/*  Timing result - execution time in milliseconds

 

ExecutionTimeInMS

76

*/

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

 

 

-- T-SQL example for stored procedure performance timing by averaging

USE AdventureWorks2008;

DECLARE  @ExecutionTime  TABLE(

                               Duration INT

                               )

DECLARE  @StartTime DATETIME

DECLARE  @i INT = 1;

 

-- Outer performance timing measuring loop

WHILE (@i <= 10)

  BEGIN

    DBCC DROPCLEANBUFFERS

    SET @StartTime = CURRENT_TIMESTAMP -- alternate getdate()

    

    /****************** measured process ***********************/

   

    EXEC uspGetWhereUsedProductID     400 ,  '2002-03-15 00:00:00.000'

      /*  Partial results

 

      ProductAssemblyID ComponentID       ComponentDesc

759               818               Road-650 Red, 58

759               826               Road-650 Red, 58

760               818               Road-650 Red, 60

*/   

    /**************** end measured process *********************/

    INSERT @ExecutionTime

    SELECT DurationInMilliseconds = datediff(ms,@StartTime,CURRENT_TIMESTAMP)

    

    SET @i +=  1

  END -- WHILE

 

SELECT DurationInMilliseconds = AVG(Duration)

FROM   @ExecutionTime

GO

-- 219 msec average execution time

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

 

Related article:

 

Query Execution Statistics in SQL Server 2005/2008

 

SET STATISTICS TIME (Transact-SQL)

 

 

Exam Prep 70-461
Exam 70-461
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE