Index tipp required

Question

Index tipp required

HelmutR

Ten Centuries

Points: 1184
More actions
April 14, 2008 at 9:50 am

#184428

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

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 1

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.

Grant Fritchey SSC Guru Points: 398999 More actions · Answer 2

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 Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

HelmutR Ten Centuries Points: 1184 More actions · Answer 3

Thanks!

@michael-2:

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

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.

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 5

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.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

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

HelmutR Ten Centuries Points: 1184 More actions · Answer 7

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

Grant Fritchey SSC Guru Points: 398999 More actions · Answer 8

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 Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

HelmutR Ten Centuries Points: 1184 More actions · Answer 9

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 10

Also out of curiosity, did changing the index have an impact on performance of the query?

HelmutR Ten Centuries Points: 1184 More actions · Answer 11

Yeah, the performance increased. Sorry, I didn't measure it but there was definitely an increase.

Helmut

Index tipp required

Cookies on SQLServerCentral