April 29, 2013 at 11:56 pm
1) As I understood from here
"elapsed time" is the total time of statement's execution, but how than it can be less then CPU time, it should includes it?
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
SQL Server Execution Times:
CPU time = 16 ms, elapsed time = 5 ms.
2) I receive this message for a statement executed right after DBCC DROPCLEANBUFFERS, why 193 and 203, where from it reads remaining 10 pages, I've just cleaned the cache?
Table 'InsertTest'. Scan count 1, logical reads 203, physical reads 0, read-ahead reads 193, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
April 30, 2013 at 1:33 am
On a multi-core cpu, CPU time can be more than elapsed time. Try the query again with MAXDOP = 1 and CPU time will be less or equal to elapsed time.
Regarding read-ahead, I'd say it only needs 193 pages from disk and then re-reads 10 pages from cache.
May 1, 2013 at 9:42 am
MaxNevermind (4/29/2013)
1) As I understood from here"elapsed time" is the total time of statement's execution, but how than it can be less then CPU time, it should includes it?
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
SQL Server Execution Times:
CPU time = 16 ms, elapsed time = 5 ms.
2) I receive this message for a statement executed right after DBCC DROPCLEANBUFFERS, why 193 and 203, where from it reads remaining 10 pages, I've just cleaned the cache?
Table 'InsertTest'. Scan count 1, logical reads 203, physical reads 0, read-ahead reads 193, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
As @foxxo noted, parallel execution plans run on more than one processor at a time. The CPU time is the sum of the times used by each thread, so it can more than the elapsed time.
A query can require more than one logical read of the same page from the page cache (memory), but only one physical read (read-ahead reads are a kind of physical read) of each page will be required (as long as the page cache is large enough to keep the page in memory between logical reads). We'd really need to see the execution plan of your query to explain why your IO STATISTICS output looks the way it does with any specificity.
Jason Wolfkill
May 1, 2013 at 10:31 am
It looks like all of the data was read from cache, since you had 203 logical rads and 0 physical reads.
If you do not run CHECKPOINT before running DBCC DROPCLEANBUFFERS, you may not get a completely cold cache (see BOL).
If you are using STATISTICS IO, you do not really need to clear the cache.
What you are looking for is number of reads (logical or physical) and number of scans per table.
May 1, 2013 at 11:36 am
arnipetursson (5/1/2013)
It looks like all of the data was read from cache, since you had 203 logical rads and 0 physical reads.If you do not run CHECKPOINT before running DBCC DROPCLEANBUFFERS, you may not get a completely cold cache (see BOL).
If you are using STATISTICS IO, you do not really need to clear the cache.
What you are looking for is number of reads (logical or physical) and number of scans per table.
The 193 read-ahead reads represent the data pages being read from disk into cache. Read-ahead reads are counted separately from physical reads, but both are reads from disk.
Jason Wolfkill
May 1, 2013 at 3:15 pm
wolfkillj (5/1/2013)
arnipetursson (5/1/2013)
It looks like all of the data was read from cache, since you had 203 logical rads and 0 physical reads.If you do not run CHECKPOINT before running DBCC DROPCLEANBUFFERS, you may not get a completely cold cache (see BOL).
If you are using STATISTICS IO, you do not really need to clear the cache.
What you are looking for is number of reads (logical or physical) and number of scans per table.
The 193 read-ahead reads represent the data pages being read from disk into cache. Read-ahead reads are counted separately from physical reads, but both are reads from disk.
You are absolutely correct.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply