StatisticsParser icon indicating copy to clipboard operation
StatisticsParser copied to clipboard

Aggregating incorrectly

Open datadill opened this issue 3 years ago • 1 comments

It is my understanding that while CPU time calculates the raw milliseconds SQL Server spent using CPU resources compounded by any ongoing parallelism, the elapsed time is essentially equivalent to run time which means that if my SSMS window reports 00:00:11 seconds duration the elapsed time should be somewhere around there as well as in the STATISTICS TIME ON output.

Below is an example output:

SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time: 
   CPU time = 62 ms, elapsed time = 120 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 7188 ms,  elapsed time = 11212 ms.

 SQL Server Execution Times:
   CPU time = 7250 ms,  elapsed time = 11333 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

Completion time: 2021-12-10T14:28:56.5910118-05:00

When pasted into statisticsparser.com, here is what the totals show: image

However, the elapsed time is nowhere close to 22 seconds (it is actually 11 seconds as reported by SSMS). For whatever reason, and perhaps this is just my ignorance, the output of STATISTICS TIME ON seems to report individual statement, but also the final aggregation.

In the example above... elapsed time = 120 ms. + elapsed time = 11212 ms is almost equal to elapsed time = 11333 ms. Is this a bug in how statistics parser reports elapsed time or an issue with STATISTICS TIME ON? Sincere apologies if this is not a bug and it has to do with how I am calling the proc.

datadill avatar Dec 10 '21 19:12 datadill

Same here. It does seem to double-up on everything for my MSSQL queries.

CrimsonVex avatar Apr 25 '24 01:04 CrimsonVex