April 14, 2008 at 9:50 am
Hi,
I have a query like:
select min(LogTime)
from tab1
where LogTime >= '20080204' and
col2 = 'ASDF' and
col3 = 'ASDF'
The Index is exactly
- LogTime
- col2
- col3
The Tuning Advisor recommend
index over col2, col3, LogTime?
But I already have an index that should fit to the query?!
Could someone help me?
thanks in advance
Helmut
April 14, 2008 at 10:27 am
The tuning advisor is telling you that your performance would be better if your index was in the correct order for the query.
That does not mean the index there is useless, it just means that the index in the correct order would improve performance.
April 14, 2008 at 11:14 am
Have you looked at an execution plan for the query? Maybe it's already using the index correctly? I wouldn't trust the results from the wizard. I've generally found them to be useless and occasionally, potentially harmful.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
April 14, 2008 at 1:05 pm
Thanks!
I think the Where-Clause and the Index-Cols are in the right order, aren't they?
Starting with logTime, Col2, Col3 in the where-clause and the same in the index. If I change the >= to an = then everything's fine.
@Grant:
The execution plan shows that the index is used. The Tuning Advisor tells me that the performance will increase with more than 30 %. In my case the query is called very often and there are a few hundred millions of datasets, that's why the 30 % increase would be great. But creating another index is not possible because of the size and the additionally maintenance of the index.
I was just wondering that the plan is not optimal if the index obviously fits to the query.
Helmut
April 14, 2008 at 1:14 pm
This is just a guess, but I would not be surprised that if you looked at the Execution Plan, that the query is doing an Index Scan, and if you had an index as indicated by DTA, that the query might actually use and index seek, which would be better.
Again, just a guess.
😎
April 14, 2008 at 1:32 pm
The optimizer does not really care much about the order of items in the WHERE clause when it generates the execution plan unless everything else is equal.
Since the tuning wizard is finding that you could have better performance with your index in another order, it is likely that the statistics on the table are indicating that the data in the table would give you a better search hierarchy with the index in the other order.
For example, if you had a table with a LastName column and a FirstLetterOfLastName column (with appropriate data of course) an index (FirstLetterOfLastName, LastName) would clearly be of more use than (LastName, FirstLetterOfLastName) in most cases because of the natural hierarchy of the data. Putting the first letter after the name does not help your search structure.
The tuning advisor sucks less in SQL 2005 because it is able to make these types of determinations based on the statistics and data in your tables, but it is also not anywhere close to perfect. Tuning is a bit of an artful process, but there will be some degree of trial and error in it.
April 14, 2008 at 1:32 pm
Just a very simple example using just 12 rows of data. Look at the difference between how they are sorted (i.e. stored in the index).
The first data set is based on your current index, the second on what DTA advised. Which do you think may work better over 1,000,000+ records?
2008-01-31 01:00:00.000 ASDD AAAA
2008-01-31 01:00:01.000 ASDD ABAA
2008-01-31 02:01:00.000 ASDF ABCC
2008-01-31 02:02:00.000 ASDF ASDF
2008-01-31 02:05:00.000 ASDF ASDG
2008-02-01 01:00:00.000 ASDD ASDF
2008-02-01 02:00:00.000 ASDF ASDF
2008-02-01 03:00:00.000 ASDF ASDF
2008-02-02 01:30:00.000 ASDD ASDF
2008-02-02 01:35:00.000 ASDF ASDF
2008-02-02 02:00:00.000 ASDG ASDF
ASDD AAAA 2008-01-31 01:00:00.000
ASDD ABAA 2008-01-31 01:00:01.000
ASDD ASDF 2008-02-01 01:00:00.000
ASDD ASDF 2008-02-02 01:30:00.000
ASDF ABCC 2008-01-31 02:01:00.000
ASDF ASDF 2008-01-31 02:02:00.000
ASDF ASDF 2008-02-01 02:00:00.000
ASDF ASDF 2008-02-01 03:00:00.000
ASDF ASDF 2008-02-02 01:35:00.000
ASDF ASDG 2008-01-31 02:05:00.000
ASDG ASDF 2008-02-02 02:00:00.000
😎
April 15, 2008 at 1:19 am
Now I got it!
I re-arranged the index columns to col2, col3, logtime. It also fits to the other statement, because col2, col3 and logtime are always used together.
Thanks to all and best regards
Helmut
April 15, 2008 at 5:30 am
Just out of curiousity, was it an index scan with the original execution plan?
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
April 15, 2008 at 6:22 am
Sorry, I cannot reproduce it, 'cause the indexes of the big table are rebuilt now. But I think I can remember that there was a Index Scan.
Regards
Helmut
April 15, 2008 at 9:14 am
Also out of curiosity, did changing the index have an impact on performance of the query?
😎
April 15, 2008 at 11:50 pm
Yeah, the performance increased. Sorry, I didn't measure it but there was definitely an increase.
Helmut
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply