July 29, 2010 at 3:40 am
- are you actually on SQL2008 ? @@version
- Are your statistics up to date ? (autoupdate statistics = on / sp_updatestats after load)
- what's the fragmentation rate of your table / indexs ?
- can you provide cardinality of your [SentTarih] column ?
- are you actually querying using hard coded dates (or dynamic sql with hard coded dates) or will the actual query be using @variables ?
I've loaded 2M rows in your table and it uses the correct index, even with the order by clause.
(only provided non nullable columns and loaded [SentTarih] every hour per day starting from '2000-01-01')
SELECT TOP 1
*
FROM tblSMSArsiv with ( nolock )
where SentTarih >= '2010-07-01'
and SentTarih < '2010-07-06'
order by SentTarih
btw:
- many NULLable columns --> avoid NULLable columns. e.g. If NULL = '' (blanc) use NOT NULL default ''
- many CHAR columns --> may be valid if the actual content mostly has the length of the definition
- "date" columns declared as char(14) --> use the correct datatype = datetime !! (It will bite you in the long run)
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
July 29, 2010 at 4:21 am
ALZDBA (7/29/2010)
are you actually on SQL2008?
Optimiser behaviour with a row goal is the same on 2005.
what's the fragmentation rate of your table / indexes?
The optimiser does not directly care about fragmentation - it only cares about the number of pages (which fragmentation might indirectly affect).
can you provide cardinality of your [SentTarih] column
Are you asking for a count of the rows that match the predicate given?
I've loaded 2M rows in your table and it uses the correct index, even with the order by clause.
Do you mean without the ORDER BY clause? Adding the ORDER BY was one of the fixes.
Paul
July 29, 2010 at 4:35 am
Appartently something went wrong with the attached sqlplan.
Just double checking we're testing with the correct engine version. (some still use sql6.5/sql7.sql2k 😉 )
Indeed even without the order by the index gets used with the table of 2M rows.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
July 29, 2010 at 4:39 am
ALZDBA (7/29/2010)
Appartently something went wrong with the attached sqlplan.Just double checking we're testing with the correct engine version. (some still use sql6.5/sql7.sql2k 😉 )
Indeed even without the order by the index gets used with the table of 2M rows.
Ok. I just wondered 🙂
edit: If you force a scan (INDEX(0)) without the order by, what estimated query cost do you see? Also, seven million rows is a lot more than two 😉
July 29, 2010 at 4:50 am
btw Paul: Nice series on execution plans at sqlblog :smooooth:
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
July 29, 2010 at 5:06 am
ALZDBA (7/29/2010)
btw Paul: Nice series on execution plans at sqlblog :smooooth:
Thanks! :blush:
Did you see my edit?
July 29, 2010 at 5:21 am
Paul White NZ (7/29/2010)[hrDid you see my edit?
Missed it !
Here's the index(0) execution plan.
And the new full executions plan.
execution stats: ( time and io ON )
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 = 31 ms, elapsed time = 39 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
(1 row(s) affected)
Table 'tblSMSArsiv'. Scan count 1, logical reads 7, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 74 ms.
(1 row(s) affected)
Table 'tblSMSArsiv'. Scan count 1, logical reads 7, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 102 ms.
(1 row(s) affected)
Table 'tblSMSArsiv'. Scan count 1, logical reads 7, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 122 ms.
(1 row(s) affected)
Table 'tblSMSArsiv'. Scan count 3, logical reads 91337, physical reads 23, read-ahead reads 395, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 656 ms, elapsed time = 919 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
(1 row(s) affected)
Table 'tblSMSArsiv'. Scan count 1, logical reads 4203, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 31 ms, elapsed time = 220 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.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
July 29, 2010 at 5:46 am
Hi,
avg_fragmentation_in_percent : 0.4
SQL Server 2008 Enterprise SP1,
Our db engine chooses to clustered index scan beyond 250,000 lower than 250,000 uses the index (both using TOP 1); I guess it is about the inner workings of TOP statement, why should one chooses to scan entire table, although it is possible with 1 index seek?
July 29, 2010 at 5:55 am
ALZDBA (you must let me know your real name at some point :-))
Paul White NZ (7/29/2010)
edit: If you force a scan (INDEX(0)) without the order by, what estimated query cost do you see?
ALZDBA
Indeed even without the order by the index gets used with the table of 2M rows.
I apologise if I missed the point, but I was looking for the plan with INDEX(0) and *without* an ORDER BY. I just wanted to compare the estimated operator cost you got. It's not a huge thing, I was just curious to compare it with the seek.
Paul
July 29, 2010 at 6:49 am
sporoy (7/29/2010)
I guess it is about the inner workings of TOP statement, why should one chooses to scan entire table, although it is possible with 1 index seek?
Let me have another crack at explaining this.
Imagine every row in your table had a SentTarih column value between '2010-07-01' and '2010-07-06'. I know that is not the case, but just imagine it is.
In that case, the engine should scan the table: the very first row it finds will satisfy your query with a single logical read.
If it used the non-clustered index, it would have to navigate down the levels of the B+ index tree to find the first index record. Assuming the index is 4 levels deep, that's 5 logical reads already. Worse, you want column data that isn't stored in the index, so there's a bookmark lookup to do as well. It's a large table, so let's say the clustered index has 6 levels. That's 7 logical reads, for a total of 12 logical reads. It would also use a bit more CPU, but lets not worry about that.
So, in that extreme case, the seek would be at least 12 times less efficient than the scan.
At the other extreme, with the only record that matches your query right at the end of the table (in clustered index order), the engine would have to read the entire table before it found a row to return. Clearly, the seek would be a better choice.
I hope you can see that there is a balancing act here, and the calculation depends on the selectivity of the SentTarih column with respect to the range values you specify.
It's a bit more complex than that, since a single physical read in a scan of the clustered index can return up to 1MB of data using read-ahead - that's 128 pages of data. Assuming your rows are 370 bytes wide each, that's 354 rows per read. So, 12 reads could potentially bring in over 4000 rows. If the optimiser estimates that one in 4000 rows will match your predicate, the scan is better than the seek, at least according to its model of the world.
One last question: I don't suppose your database has the Forced Parameterization setting ON, does it?
Paul
July 29, 2010 at 8:35 am
Thanks Paul that was a very great explanation, so it is db engine that expects to find at least one row, in shorter time than index seek, and starts off a table scan; by the way although it is not clustered index, my index field [SentTarih] is also in increasing order, so a range predicate should not find a row if it a range of latter part of the table,
can it be the case the optimizer thinks my data is not ordered but randomly distributed?
July 30, 2010 at 12:00 am
Who am I ? Sometimes this is me[/url] :alien: and most of the time this is me :hehe:
Paul White NZ (7/29/2010)
.. but I was looking for the plan with INDEX(0) and *without* an ORDER BY. I just wanted to compare the estimated operator cost you got.....
Off course... that wasn't included in my test ... missed a copy/paste ... need summer holliday :blush:
tablescan without the order by with the use of index(0) ... io stats wins.
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 Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
(1 row(s) affected)
Table 'tblSMSArsiv'. Scan count 1, logical reads 7, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 117 ms.
(1 row(s) affected)
Table 'tblSMSArsiv'. Scan count 1, logical reads 7, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 16 ms, elapsed time = 545 ms.
(1 row(s) affected)
Table 'tblSMSArsiv'. Scan count 1, logical reads 7, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 16 ms, elapsed time = 176 ms.
(1 row(s) affected)
Table 'tblSMSArsiv'. Scan count 1, logical reads 4203, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 31 ms, elapsed time = 205 ms.
(1 row(s) affected)
Table 'tblSMSArsiv'. Scan count 3, logical reads 91337, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 593 ms, elapsed time = 440 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
(1 row(s) affected)
Table 'tblSMSArsiv'. Scan count 1, logical reads 4203, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 32 ms, elapsed time = 148 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.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
July 30, 2010 at 12:58 am
Hi ALZDBA What is the utility you use for getting these info? Thanks
July 30, 2010 at 1:10 am
sporoy (7/30/2010)
Hi ALZDBA What is the utility you use for getting these info? Thanks
Sql server management studio.
Enable "include actual execution plan" to get the execution plans [Query\include actual execution plan] or ctrl+M. (you knew that)
To get execution io and time data :
- you can do that using query settings for SSMS
Tools\options\query execution\advanced tick "set statistics io" and "set statistics time"
- in your tsql you can do that using
set statistics io, time on
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 14 posts - 16 through 29 (of 29 total)
You must be logged in to reply to this topic. Login to reply