Skip to content

Commit b375c3c

Browse files
ktaranovktaranov
authored
Fix bug with FinishBenchmarkTime column update in log table
Add more documentation in comments
1 parent 0f43aa1 commit b375c3c

1 file changed

Lines changed: 42 additions & 41 deletions

File tree

‎Stored_Procedure/dbo.sp_BenchmarkTSQL.sql‎

Lines changed: 42 additions & 41 deletions
Original file line numberDiff line numberDiff line change
@@ -25,22 +25,22 @@ ALTER PROCEDURE dbo.sp_BenchmarkTSQL(
2525
Run SQL statement specified times, show results, insert execution details into log table master.dbo.BenchmarkTSQL.
2626
2727
.PARAMETER @tsqlStatementBefore
28-
TSQL statement that executed before tested main TSQL statement.
28+
TSQL statement that executed before tested main TSQL statement - not taken into account when measuring @tsqlStatement. Default is NULL.
2929
3030
.PARAMETER @tsqlStatement
31-
TSQL statement for benchmarking.
31+
TSQL statement for benchmarking. Mandatory parameter.
3232
3333
.PARAMETER @tsqlStatementAfter
34-
TSQL statement that executed after tested TSQL statement.
34+
TSQL statement that executed after tested TSQL statement - not taken into account when measuring @tsqlStatement. Default is NULL.
3535
3636
.PARAMETER @numberOfExecution
3737
Number of execution TSQL statement.
3838
3939
.PARAMETER @saveResults
40-
Save benchmark details to master.dbo.BenchmarkTSQL table if @saveResults = 1. Create table if not exists (see 245 line: CREATE TABLE master.dbo.BenchmarkTSQL …).
40+
Save benchmark details to master.dbo.BenchmarkTSQL table if @saveResults = 1. Create table if not exists (see 243 line: CREATE TABLE master.dbo.BenchmarkTSQL …).
4141
4242
.PARAMETER @skipTSQLCheck
43-
Checking for valid TSQL statement. Default value is 0 (false) - skip checking.
43+
Checking for valid TSQL statement. Default value is 1 (true) - skip checking.
4444
4545
.PARAMETER @clearCache
4646
Clear cached plan for TSQL statement. Default value is 0 (false) - not clear.
@@ -52,7 +52,7 @@ ALTER PROCEDURE dbo.sp_BenchmarkTSQL(
5252
PRINT detailed step information: step count, start time, end time, duration.
5353
5454
.PARAMETER @durationAccuracy
55-
Duration accuracy calculation, possible values: ns, mcs, ms, ss, mi, hh, dd, wk. Default value is ss - seconds.
55+
Duration accuracy calculation, possible values for this stored procedure: ns, mcs, ms, ss, mi, hh, dd, wk. Default value is ss - seconds.
5656
See DATEDIFF https://docs.microsoft.com/en-us/sql/t-sql/functions/datediff-transact-sql
5757
5858
.PARAMETER @dateTimeFunction
@@ -126,9 +126,9 @@ The above copyright notice and this permission notice shall be included in all c
126126
THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.
127127

128128
.NOTE
129-
Created date: 2017-12-14 by Konstantin Taranov
130-
Version: 5.0
131-
Last Modified: 2019-03-17 by Konstantin Taranov
129+
Version: 5.1
130+
Created: 2017-12-14 by Konstantin Taranov
131+
Modified: 2019-03-28 by Konstantin Taranov
132132
Main contributors: Konstantin Taranov, Aleksei Nagorskii
133133
Source: https://rebrand.ly/sp_BenchmarkTSQL
134134
*/
@@ -137,10 +137,11 @@ BEGIN TRY
137137

138138
SET NOCOUNT ON;
139139

140+
DECLARE @startTime DATETIME2(7) = CASE WHEN @dateTimeFunction = 'SYSDATETIME' THEN SYSDATETIME()
141+
WHEN @dateTimeFunction = 'SYSUTCDATETIME' THEN SYSUTCDATETIME()
142+
END;
143+
140144
DECLARE @originalLogin SYSNAME = QUOTENAME(ORIGINAL_LOGIN()); /* https://sqlstudies.com/2015/06/24/which-user-function-do-i-use/ */
141-
DECLARE @startTime DATETIME2(7) = CASE WHEN @dateTimeFunction = 'SYSDATETIME' THEN SYSDATETIME()
142-
WHEN @dateTimeFunction = 'SYSUTCDATETIME' THEN SYSUTCDATETIME()
143-
END;
144145
DECLARE @err_msg NVARCHAR(MAX);
145146
DECLARE @RaiseError NVARCHAR(2000);
146147

@@ -163,14 +164,14 @@ BEGIN TRY
163164
THROW 55003, '@tsqlStatement is empty, please specify TSQL statement.', 1;
164165

165166
IF @durationAccuracy NOT IN (
166-
'ns' /* nanosecond */
167-
, 'mcs' /* microsecond */
168-
, 'ms' /* millisecond */
169-
, 'ss' /* second */
170-
, 'mi' /* minute */
171-
, 'hh' /* hour */
172-
, 'dd' /* day */
173-
, 'wk' /* week */
167+
'ns' /* nanosecond */
168+
, 'mcs' /* microsecond */
169+
, 'ms' /* millisecond */
170+
, 'ss' /* second */
171+
, 'mi' /* minute */
172+
, 'hh' /* hour */
173+
, 'dd' /* day */
174+
, 'wk' /* week */
174175
)
175176
THROW 55004, '@durationAccuracy accept only this values: ns, mcs, ms, ss, mi, hh, wk, dd. See DATEDIFF https://docs.microsoft.com/en-us/sql/t-sql/functions/datediff-transact-sql' , 1;
176177

@@ -330,19 +331,21 @@ BEGIN TRY
330331
IF @tsqlStatementBefore IS NOT NULL AND @tsqlStatementBefore <> ''
331332
EXECUTE sp_executesql @tsqlStatementBefore;
332333

333-
SET @runTimeStamp = CASE WHEN @dateTimeFunction = 'SYSDATETIME' THEN SYSDATETIME()
334-
WHEN @dateTimeFunction = 'SYSUTCDATETIME' THEN SYSUTCDATETIME()
335-
END;
334+
BEGIN /* Run bencmark step and calculate it duration */
335+
SET @runTimeStamp = CASE WHEN @dateTimeFunction = 'SYSDATETIME' THEN SYSDATETIME()
336+
WHEN @dateTimeFunction = 'SYSUTCDATETIME' THEN SYSUTCDATETIME()
337+
END;
336338

337-
IF @additionalInfo = 1
338-
EXEC sp_executesql @tsqlStatement, N'@additionalXMLOUT XML OUTPUT', @additionalXMLOUT = @additionalXML OUTPUT SELECT @additionalXML;
339+
IF @additionalInfo = 0
340+
EXEC sp_executesql @tsqlStatement;
339341

340-
IF @additionalInfo = 0
341-
EXEC sp_executesql @tsqlStatement;
342+
IF @additionalInfo = 1
343+
EXEC sp_executesql @tsqlStatement, N'@additionalXMLOUT XML OUTPUT', @additionalXMLOUT = @additionalXML OUTPUT SELECT @additionalXML;
342344

343-
SET @finishTime = CASE WHEN @dateTimeFunction = 'SYSDATETIME' THEN SYSDATETIME()
344-
WHEN @dateTimeFunction = 'SYSUTCDATETIME' THEN SYSUTCDATETIME()
345-
END;
345+
SET @finishTime = CASE WHEN @dateTimeFunction = 'SYSDATETIME' THEN SYSDATETIME()
346+
WHEN @dateTimeFunction = 'SYSUTCDATETIME' THEN SYSUTCDATETIME()
347+
END;
348+
END;
346349

347350
SET @duration = CASE WHEN @durationAccuracy = 'ns' THEN DATEDIFF(ns, @runTimeStamp, @finishTime)
348351
WHEN @durationAccuracy = 'mcs' THEN DATEDIFF(mcs, @runTimeStamp, @finishTime)
@@ -380,7 +383,6 @@ BEGIN TRY
380383
, @additionalXML
381384
);
382385

383-
384386
IF @saveResults = 1
385387
BEGIN
386388
INSERT INTO master.dbo.BenchmarkTSQL(
@@ -403,9 +405,9 @@ BEGIN TRY
403405
SELECT @TSQLStatementGUID AS TSQLStatementGUID
404406
, @stepNumber AS StepRowNumber
405407
, StartBenchmarkTime
406-
, CASE WHEN @dateTimeFunction = 'SYSDATETIME' THEN SYSDATETIME()
407-
WHEN @dateTimeFunction = 'SYSUTCDATETIME' THEN SYSUTCDATETIME()
408-
END AS FinishBenchmarkTime
408+
/* it does not matter which function use (this is NOT NULL column)
409+
becasue we update this column later with correct values */
410+
, SYSDATETIME() AS FinishBenchmarkTime
409411
, RunTimeStamp
410412
, FinishTimeStamp
411413
, Duration
@@ -440,9 +442,7 @@ BEGIN TRY
440442

441443
END;
442444

443-
SELECT @min = MIN(Duration)
444-
, @avg = AVG(Duration)
445-
, @max = MAX(Duration)
445+
SELECT @min = MIN(Duration), @avg = AVG(Duration), @max = MAX(Duration)
446446
FROM @BenchmarkTSQL;
447447

448448
DECLARE @FinishBenchmarkTime DATETIME2(7) = CASE WHEN @dateTimeFunction = 'SYSDATETIME' THEN SYSDATETIME()
@@ -452,8 +452,8 @@ BEGIN TRY
452452
IF @saveResults = 1
453453
BEGIN
454454
UPDATE dbo.BenchmarkTSQL
455-
SET FinishTimeStamp = @FinishBenchmarkTime
456-
WHERE TSQLStatementGUID = @TSQLStatementGUID;
455+
SET FinishBenchmarkTime = @FinishBenchmarkTime
456+
WHERE TSQLStatementGUID = @TSQLStatementGUID;
457457
END;
458458

459459
IF @calcMedian = 1
@@ -499,7 +499,7 @@ BEGIN TRY
499499
DECLARE @endTime DATETIME2(7) = CASE WHEN @dateTimeFunction = 'SYSDATETIME' THEN SYSDATETIME()
500500
WHEN @dateTimeFunction = 'SYSUTCDATETIME' THEN SYSUTCDATETIME()
501501
END;
502-
502+
503503
IF @saveResults = 1
504504
BEGIN
505505
UPDATE dbo.BenchmarkTSQL
@@ -547,6 +547,7 @@ BEGIN CATCH
547547
IF ERROR_NUMBER() = 535
548548
PRINT('Your @durationAccuracy = ' + @durationAccuracy +
549549
'. Try to use @durationAccuracy with a less precise datepart - seconds (ss) or minutes (mm) or days (dd).' + @crlf +
550-
'But in log table master.dbo.BenchmarkTSQL all times saving with DATETIME2(7) precise! You can manualy calculate difference after decreasing precise datepart.');
550+
'But in log table master.dbo.BenchmarkTSQL all times saving with DATETIME2(7) precise! You can manualy calculate difference after decreasing precise datepart.' + @crlf +
551+
'For analyze log rable see latest example in document section.');
551552
END CATCH;
552553
GO

0 commit comments

Comments
 (0)