Friday, February 24, 2012

elapsed time vs cpu time

Table 'Worktable'. Scan count 3, logical reads 8, physical reads 0,
read-ahead reads 0.
Table 'vgn_version'. Scan count 8, logical reads 875, physical reads 0,
read-ahead reads 0.
Table 'vgn_version_tag'. Scan count 2, logical reads 4, physical reads 0,
read-ahead reads 0.
Table 'model_grp_v'. Scan count 1, logical reads 258, physical reads 0,
read-ahead reads 0.
Table 'model_v'. Scan count 1, logical reads 350, physical reads 0,
read-ahead reads 0.
SQL Server Execution Times:
CPU time = 571 ms, elapsed time = 17560 ms.
When i run statistics io , and statistics time, on a query (query not
mentioned here), i got above mentioned results.
From the info given above, I see there are no physical reads, so all data is
there in the memory. I am fuzzled with the cpu time and elapsed time values.
When cpu has taken half a second to process the data, why elapsed time is
17.5 sec. I was monitoring cpu when the query being executed, cpu
utilization less than 5% of processor time. So can i derive from this that
remaining 17 sec has been caused to move data between dynmic memory and cpu
memory cache. Or are there some thing i am missing here. Can you please
bring your ideas and let me know if you need any more info.
I have run the query in completely quite machine after reboot.
Thanks,
Subbu.subbu wrote:
> Table 'Worktable'. Scan count 3, logical reads 8, physical reads 0,
> read-ahead reads 0.
> Table 'vgn_version'. Scan count 8, logical reads 875, physical reads
> 0, read-ahead reads 0.
> Table 'vgn_version_tag'. Scan count 2, logical reads 4, physical
> reads 0, read-ahead reads 0.
> Table 'model_grp_v'. Scan count 1, logical reads 258, physical reads
> 0, read-ahead reads 0.
> Table 'model_v'. Scan count 1, logical reads 350, physical reads 0,
> read-ahead reads 0.
> SQL Server Execution Times:
> CPU time = 571 ms, elapsed time = 17560 ms.
> When i run statistics io , and statistics time, on a query (query not
> mentioned here), i got above mentioned results.
> From the info given above, I see there are no physical reads, so all
> data is there in the memory. I am fuzzled with the cpu time and
> elapsed time values. When cpu has taken half a second to process the
> data, why elapsed time is
> 17.5 sec. I was monitoring cpu when the query being executed, cpu
> utilization less than 5% of processor time. So can i derive from this
> that remaining 17 sec has been caused to move data between dynmic
> memory and cpu memory cache. Or are there some thing i am missing
> here. Can you please bring your ideas and let me know if you need any
> more info.
> I have run the query in completely quite machine after reboot.
> Thanks,
> Subbu.
How many rows were returned? Where did you run the SQL Statement from?
The total time for a SQL statement includes the time to tech all the
result set pages. Either there were a lot of rows generated and they
took time to fetch or the client application fetched rows slowly.
David Gugick - SQL Server MVP
Quest Software|||... or there was some blocking going on...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:u1WodhrTGHA.792@.TK2MSFTNGP10.phx.gbl...
> subbu wrote:
> How many rows were returned? Where did you run the SQL Statement from? The
total time for a SQL
> statement includes the time to tech all the result set pages. Either there
were a lot of rows
> generated and they took time to fetch or the client application fetched ro
ws slowly.
> --
> David Gugick - SQL Server MVP
> Quest Software
>|||No blocking at all. This is the only query running at that time
Thanks,
Subbu.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:ufcrY1rTGHA.5500@.TK2MSFTNGP12.phx.gbl...
> ... or there was some blocking going on...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "David Gugick" <david.gugick-nospam@.quest.com> wrote in message
> news:u1WodhrTGHA.792@.TK2MSFTNGP10.phx.gbl...
The total time for a SQL[vbcol=seagreen]
there were a lot of rows[vbcol=seagreen]
rows slowly.[vbcol=seagreen]
>|||Two (2) rows returned.
I run sql stmt in query analyser on the machine where sql server installed,
but connected to server machine using Terminal Services (RDP).
I have run same query on another machine, which is in the same place like
previous one did run the query in similar way, it returned results
differently. Elapsed time is almost matching with CPU. Here also 2 rows
returned.
Table 'vgn_version'. Scan count 8, logical reads 796, physical reads 0,
read-ahead reads 0.
Table 'vgn_version_tag'. Scan count 2, logical reads 4, physical reads 0,
read-ahead reads 0.
Table 'model_grp_v'. Scan count 1, logical reads 255, physical reads 0,
read-ahead reads 0.
Table 'model_v'. Scan count 1, logical reads 347, physical reads 0,
read-ahead reads 0.
SQL Server Execution Times:
CPU time = 156 ms, elapsed time = 167 ms.
SQL Server Execution Times:
CPU time = 156 ms, elapsed time = 171 ms.
SQL Server Execution Times:
CPU time = 156 ms, elapsed time = 173 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.
Thanks,
Subbu.
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:u1WodhrTGHA.792@.TK2MSFTNGP10.phx.gbl...
> subbu wrote:
> How many rows were returned? Where did you run the SQL Statement from?
> The total time for a SQL statement includes the time to tech all the
> result set pages. Either there were a lot of rows generated and they
> took time to fetch or the client application fetched rows slowly.
> --
> David Gugick - SQL Server MVP
> Quest Software
>|||Perhaps ODBC tracing is turned on at the slow machine?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"subbu" <subbaiahd@.hotmail.com> wrote in message news:%23ufnL6rTGHA.4864@.TK2MSFTNGP12.phx.gb
l...
> Two (2) rows returned.
> I run sql stmt in query analyser on the machine where sql server installed
,
> but connected to server machine using Terminal Services (RDP).
> I have run same query on another machine, which is in the same place like
> previous one did run the query in similar way, it returned results
> differently. Elapsed time is almost matching with CPU. Here also 2 rows
> returned.
> Table 'vgn_version'. Scan count 8, logical reads 796, physical reads 0,
> read-ahead reads 0.
> Table 'vgn_version_tag'. Scan count 2, logical reads 4, physical reads 0,
> read-ahead reads 0.
> Table 'model_grp_v'. Scan count 1, logical reads 255, physical reads 0,
> read-ahead reads 0.
> Table 'model_v'. Scan count 1, logical reads 347, physical reads 0,
> read-ahead reads 0.
> SQL Server Execution Times:
> CPU time = 156 ms, elapsed time = 167 ms.
> SQL Server Execution Times:
> CPU time = 156 ms, elapsed time = 171 ms.
> SQL Server Execution Times:
> CPU time = 156 ms, elapsed time = 173 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.
> Thanks,
> Subbu.
> "David Gugick" <david.gugick-nospam@.quest.com> wrote in message
> news:u1WodhrTGHA.792@.TK2MSFTNGP10.phx.gbl...
>|||No. ODBC tracing has not started, i have verified just now.
Thanks,
Subbu.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eO6yBFsTGHA.424@.TK2MSFTNGP12.phx.gbl...
> Perhaps ODBC tracing is turned on at the slow machine?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "subbu" <subbaiahd@.hotmail.com> wrote in message
news:%23ufnL6rTGHA.4864@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
installed,[vbcol=seagreen]
like[vbcol=seagreen]
0,[vbcol=seagreen]
not[vbcol=seagreen]

No comments:

Post a Comment