use of WITH INDEX clause when tuning queries
-
When is it appropriate to tell SQL which index to use in a query. I am seeing some impressive improvements in query run time by specifying indexes to use, but the idea of telling SQL which index to use doesn't seem right to me. For example, maybe SQL 2005 will be able to execute the same query more efficiently in which case I wouldn't want the with index statement.
-
It's not often that I see a query improve performance by applying Index Hints. Usually, it's the other way around... When you say you are seeing some impressive improvements in query run time, are you first running the query without the hint, then adding the hint and running it again? Chances are, if you did nothing to the query, it would still run substantially faster because of caching and the automatic development of statistics.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
A good bit of DML & DDL goes a long way. If you provide the query, table structure and index structure we could maybe make some suggestions as to why a particular index is helpful, or a hinderance.
Index hints are probably the last thing I'd consider when tuning a query.
--------------------
Colt 45 - the original point and click interface
-
Jeff, Phil - thanks for confirming my hunch regarding the use of index hints.
Turns out that with some fiddling with indexes, I was able to achieve the same performance improvements without index hints.
Are there any good reference sources explaining how the SQL optimizer selects indexes?
-
Joe Chang is the man for all of the math related to optimizer choices. Pretty complex stuff that basically boils down to "if using the index costs less then use it, otherwise do not". 🙂
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service -
I've used index hints, but rarely. Sometimes the cost to use an index hint is higher, but the results are returned MUCH faster - something that is important to our customers. Generally we don't have to specify any hints, though. General DBA answer here - "it depends". Sometimes SQL Server's optimizer doesn't choose the best index combination for your query. I'd revisit that periodically, especially after service packs, but there's a reason we have index hints as an option.
Also, we are sure to clear out the cache when doing our tests - select with hint, clear cache, select without hint - do compare of the plans to see what's going on.
-Pete
-
Sure... I always start with Books Online and go from there... lookup "indexes, designing" in Books Online to start with...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Related question:
I have two variations of a query. According to the estimated execution plan, both queries should have about the same cost. In actual execution, however, one of the queries is MUCH less expensive both in terms of I-O and CPU usage.
Why is this? My guess is that the SQL optimizer makes decisions and cost estimates based upon average statistics and average index cardinality, etc. In actual execution, however, the queries might use those index key values referencing smaller numbers of rows. What I mean is something like this. Suppose we have a key value column containing a letter of the alphabet with a total of, say, 1000 rows in the table. Now, each letter of the alphabet might be associated with approximately equal number of rows or the data might be skewed with one letter identifying 900 entries and the other letters only 3 or 4 each. In this case, then actual results would differ considerably from estimated results depending on whether you select all rows for a letter identifying 900 rows versus a letter identifying 3 rows.
Is this right, or is the optimizer smart enough to understand the distribution of values.
-
This is not correct. Review DBCC SHOW_STATISTICS. You will see average stuff as well as distribution stats. The latter are used for specific value queries like you present and would result in a table scan for your 900-row-matching value and index seek/bookmark lookups for the remainder of parameter values.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service -
I would also play around with UPDATE STATISTICS WITH FULLSCAN (if you have time to run this). You stats may be out of date in your original setup and in fiddling around with the indexes caused some of the stats to be rebuilt. Outside of having the AUTO UPDATE STATS checked in the database properties, I am not sure at what would cause the stats to be automatically rebuilt. I rebuild mine nightly on my most used tables to eliminate the wrong index being chosen.
I do see a difference in query times after I have rebuilt the stats after the daily load. That's reason enough for me to put it in a daily job.
Live to Throw
Throw to Live
Will Summers -
Given appropriate indexes, current statistics, etc. the optimizer will come up with the same or better query plan than a human most (say 99%) of the time.
That said, there is the occaisional query where it seems like my tricky little monkey brain can do better - until I end up at the same point you did and find that I've missed a column in an index or have out of date statistics, etc.
Avoid "overtuning" a query by using index hints, forced joins (e.g. INNER LOOP JOIN) if at all possible - by forcing the database engine to approach a particular query in the same fashion every time you can code yourself into a pretty tight corner. Better to spend the time identifying better indexes, etc.
Joe
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply