|
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
DBCC DROPCLEANBUFFERS
SET STATISTICS TIME ON
EXEC AdventureWorks2008.[dbo].[uspGetBillOfMaterials] 800, '2003-02-02'
SET STATISTICS TIME OFF
/* Partial results in Messages
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 64 ms.
*/
------------
-- 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
------------
|
