When to use INDEX HINTS!

[szlamany]

INDEX HINTS are normally not needed - the query optimizer does a great job of determining what index should be used to perform a query.

I just came across a situation where the wrong index was chosen - and I thought I would share it with the group.

Some background info - Financial system - LEDGER table.

Simplied version of columns - FISCALYR (int), TRANSENTRY (int), POENTRY (int), DEBITAMT (money), ENCUMBEREDAMT (money).

Primary key - clustered - FISCALYR+TRANSENTRY

TRANSENTRY is a unique number for a fiscal year.

Alternate key - FISCALYR+POENTRY

POENTRY is repeated for all rows related to the same PURCHASE ORDER.

The QUERY used was:

Select -Sum(Encumbamt) From Ledger_T LE
	Where LE.FiscalYr = 2006
	and LE.POEntry=917 and LE.TransEntry<>3314

About 6 rows exist with POENTRY of 917.

30000 or more rows exist for a fiscal year. We are looking to exclude a single row - based on it's TRANSENTRY value.

The QUERY OPTIMIZER used the PRIMARY KEY with a TRANSENTRY < 3314 or TRANSENTRY> 3314 execution plan - basically amounting to a TABLE SCAN of 30000 rows. The fact that TRANSENTRY was found in the WHERE clause caused the PRIMARY KEY to be chosen.

Bad choice - the ALTERNATE KEY on the FISCALYR+POENTRY would amount to just the 6 rows being looked at.

This is the query with an INDEX HINT - forcing the SQL engine to choose the correct INDEX

Select -Sum(Encumbamt) From Ledger_T LE
	With (Index (AKLedger1))
	Where LE.FiscalYr = 2006
	and LE.POEntry=917 and LE.TransEntry<>3314

If you are curious how to create ALTERNATE KEY indexes - this is the T-SQL to do so...

CREATE INDEX AKLedger1 on Ledger_T (FiscalYr,POEntry)

[brucevde]

Just curious if using brackets would cause the optimizer to choose the different index.

Select -Sum(Encumbamt) From Ledger_T LE
	Where (LE.FiscalYr = 2006 And LE.POEntry=917) And 
                        LE.TransEntry<>3314

[szlamany]

Just curious if using brackets would cause the optimizer to choose the different index.

Select -Sum(Encumbamt) From Ledger_T LE
	Where (LE.FiscalYr = 2006 And LE.POEntry=917) And 
                        LE.TransEntry<>3314

I just checked to make sure - and no it does not.

My gut reaction was that it would not, as the query must be parsed into objects prior to optimizing anyway...

[kaffenils]

Alternate key - FISCALYR+POENTRY

CREATE INDEX AKLedger1 on Ledger_T (FiscalYr,POEntry)

Not that I think it would matter for the execution plan, but do you really need FISCALYR in this index? The clustered index is always included in other indexes and that means that FISCALYR (and TRANSENTRY) is already included. No need to add it another time since this is not a unique index.

Also, are you sure the statistics for the indexes are up to date? Try running UPDATE STATISTICS for the indexes.

[szlamany]

All the TRANSENTRY and POENTRY figures start at 1 again, for a given fiscal year - so are not unique in and of themselves.

We cluster the primary index on FISCALYR so that I/O for data for a given fiscal year is as fast as possible.

We need to have FISCALYR in each alternate key, since the TRANSENTRY and POENTRY start at 1 each FISCALYR...

I will try the "UPDATE STATISTICS" - just to see if the execution plan changes.

[kaffenils]

We need to have FISCALYR in each alternate key so that unique rows can be gained access to.

Are you saying that FISCALYR and POENTRY is a unique value pair in the same way as FISCALYR and TRANSENTRY?
If it is not unique then my point was that you don't need to add FISCALYR explicitly to the index since SQL Server always adds the columns in the clustered index to any other indexes. Perhaps the execution plan builder gets "confused" when FISCALYR is explicitly added to the index when it already contains FISCALYR from the clustered index, even though it sounds very strange. It's probably just a shot in the dark.

[szlamany]

Are you saying that FISCALYR and POENTRY is a unique value pair in the same way as FISCALYR and TRANSENTRY?
If it is not unique then my point was that you don't need to add FISCALYR explicitly to the index since SQL Server always adds the columns in the clustered index to any other indexes. Perhaps the execution plan builder gets "confused" when FISCALYR is explicitly added to the index when it already contains FISCALYR from the clustered index, even though it sounds very strange. It's probably just a shot in the dark.

Yes - FISCALYR + POENTRY is a unique value pair.

Even though you say that the columns of the clustered index are added to any other index, they are not part of the "key" portion of the index. Each leaf is built with FISCALYR+POENTRY as the "key" portion - so that binary lookup of a key can be accomplished.

I will create a simple new database and set of tables - without the two part key structure and see if the results are the same.

[kaffenils]

Even though you say that the columns of the clustered index are added to any other index, they are not part of the "key" portion of the index. Each leaf is built with FISCALYR+POENTRY as the "key" portion - so that binary lookup of a key can be accomplished.

Of course, only the columns you specify in a unique key are checked for uniqueness, but the clustered index values are also stored with the index, and used in queries, and will be taken into consideration when SQL Server builds an execution plan. At least as far as I know. Hope I haven't misunderstood anything.

[szlamany]

Yes - FISCALYR + POENTRY is a unique value pair.

I misunderstood what you asked - FISCALYR + POENTRY is not a unique value pair. FISCALYR+TRANSENTRY is a unique value pair (obviously, since it's the primary clustered index of the LEDGER table).

The same POENTRY (in my example 917) exists 6 times for FISCALYR 2006. The purpose of the alternate index is not for unique constraint - the purpose of the alternate index is so that this particular SUM() QUERY can be gathered for a particular POENTRY quickly.

The EXECUTION PLAN - for a WHERE statement of FISCALYR=2006 and POENTRY=917 would always use the ALTERNATE INDEX - as TRANSENTRY is not even mentioned in that WHERE clause. The optimizer looks for an index that fits the WHERE clause.

When we introduced TRANSENTRY<>3314 into the WHERE clause, we wondered what EXECUTION PLAN might be followed, since now we were referring to columns from two different indexes. I wouild have thought that the =917 would be deemed more specific by the optimizer then the <>3314, causing the ALTERNATE index to be used.

The optimizer though, stops considering options very quickly, as it does want to start processing the query as soon as possible...

Maybe I'll delve into Kalen Delaney's book, INSIDE MS SQL Server 2000, when I get to work and see if I can find so definitive reasons for why this happened.