April 27, 2012 at 5:26 am
Which query performance is better perf1 or perf2 ?
perf1: Scan count 0, logical reads 23, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
perf2 : Scan count 1, logical reads 15, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
April 27, 2012 at 5:39 am
ESAT ERKEC (4/27/2012)
Which query performance is better perf1 or perf2 ?perf1: Scan count 0, logical reads 23, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
perf2 : Scan count 1, logical reads 15, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
perf2 is doing a table scan where as perf1 does not(seems to be reading data from the Index)...so perf1 is better.
April 27, 2012 at 5:49 am
After i wrote this topic i made another test and i give another report and this is a update query
I think perf2 is better
perf1
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
Table perf1 Scan count 0, logical reads 23, physical reads 0, read-ahead reads 0, lob logical reads 0,
lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
(1 row(s) affected)
perf2
SQL Server parse and compile time:
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 = 0 ms, elapsed time = 0 ms.
Table perf2 . Scan count 0, logical reads 15, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
(1 row(s) affected)
April 27, 2012 at 6:13 am
15 or 23 reads, the difference is insignificant, and the times show that (0ms).
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 27, 2012 at 6:14 am
April 27, 2012 at 6:15 am
vinu512 (4/27/2012)
ESAT ERKEC (4/27/2012)
Which query performance is better perf1 or perf2 ?perf1: Scan count 0, logical reads 23, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
perf2 : Scan count 1, logical reads 15, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
perf2 is doing a table scan where as perf1 does not(seems to be reading data from the Index)...so perf1 is better.
Scan count != number of time a table is scanned.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 27, 2012 at 6:19 am
Sorry...didnt see your second post carefully.
In your first post you wrote:
perf1: Scan count 0, logical reads 23, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
perf2 : Scan count 1, logical reads 15, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
In this case Perf1 is better because:
perf1: Scan count 0 < perf2 : Scan count 1
In your second post the Stats changed:
perf1
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
Table perf1 Scan count 0, logical reads 23, physical reads 0, read-ahead reads 0, lob logical reads 0,
lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
(1 row(s) affected)
perf2
SQL Server parse and compile time:
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 = 0 ms, elapsed time = 0 ms.
Table perf2 . Scan count 0, logical reads 15, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
(1 row(s) affected)
In this case both perf1 and perf2 are(more or less) the same.
April 27, 2012 at 6:20 am
GilaMonster (4/27/2012)
vinu512 (4/27/2012)
ESAT ERKEC (4/27/2012)
Which query performance is better perf1 or perf2 ?perf1: Scan count 0, logical reads 23, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
perf2 : Scan count 1, logical reads 15, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
perf2 is doing a table scan where as perf1 does not(seems to be reading data from the Index)...so perf1 is better.
Scan count != number of time a table is scanned.
What does Scan Count represent then?
April 27, 2012 at 6:26 am
Gail is right....Scan count != number of time a table is scanned.
As per Books Online, Scan Count is: Number of index or table scans performed.
Scan count is 0 if the index that you are using is a unique index or clustered index on a primary key and you are seeking for only one value. Eg. WHERE Primary_Key_Column = <value>
Scant count is 1 when you are searching for one value using a non-unique clustered index which is defined on a non-primary key column. This is done to check for duplicate values for the key value that you are searching for. Eg. WHERE Clustered_Index_Key_Column = <value>
Scan count is N when N is the number of different seek/scan started towards the left or right side at the leaf level after locating a key value using the index key.
Edit : In that case I take back what I said about Perf1 being better than Perf2. But, Gail I have a question...when scan count is 0 the scan to check for duplicates will be avoided where as when Scan Count is 1 the scan would be performed to check for duplicates. Right??....Would that decrease performance?
April 27, 2012 at 6:43 am
vinu512 (4/27/2012)
What does Scan Count represent then?
Nothing useful. I'd just ignore it.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 27, 2012 at 6:44 am
Focusing on nothin but reads, 23 is worse than 15. But as Gail says, this is a trivial difference. I'd only get worked up if this query were called hundreds of times a second or something.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 27, 2012 at 7:28 am
This are the same queries but i only remove the inculude columns in the index in the perf2 this queries are used the same index that why i cant
solve the which is the best for update query
April 27, 2012 at 7:33 am
This are same queries with the same where conditions but the diffrence between this
queries i only remove inculude columns for perf2 they are use the same index
and when i remove inculude column in the index query make scan count and the this query is
the most execute in the production server i want to improve performance....
April 27, 2012 at 7:52 am
ESAT ERKEC (4/27/2012)
and the this query is the most execute in the production server i want to improve performance....
The query executes in 0ms with ~20 reads. You are wasting your time optimising this. Find something that is a performance problem and optimise that.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 27, 2012 at 7:55 am
Grant Fritchey (4/27/2012)
Focusing on nothin but reads, 23 is worse than 15. But as Gail says, this is a trivial difference. I'd only get worked up if this query were called hundreds of times a second or something.
I wouldn't even get worried then. I'd get worried only if my performance analysis showed this was one of the 10 most resource-intensive queries overall in the server.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply