Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Forcing a Select to use a specific index Expand / Collapse
Author
Message
Posted Wednesday, September 2, 2009 9:19 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, February 6, 2012 12:43 PM
Points: 215, Visits: 640
I have been thrust into the role of DBA at my company. Unfortuanately I have no training. Hopefully the questions below make sense.

My boss and I were discussing the need to create a few indices in the database. It is my impression that you can create an index on a table but you can't always be sure that the index will be used during a specific query. My boss told me that he believes that there is a way that you could force SQL Server to use a specific index.

My questions are as follows:

1. Am I correct that you can create an index on a table but you can't always be sure that the index will be used?

2. Is my boss correct that you can force a select statement to use a specific index? If so, how?

Thanks
Post #781573
Posted Wednesday, September 2, 2009 9:28 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 3:01 PM
Points: 3,843, Visits: 3,836
1. Yes. The query optimizer will analyze the query, the table/index structure, and the data distribution and determine the most optimal query plan for that query. So you can create indexes, but depending on how your table is laid out and how efficient your queries are, it may or may not choose to use your index.

2. Yes. You can use query hints to force the optimizer to use a specific index. I would caution you on this, however, especially if you have no experience in query/index tuning. The optimizer will pick the best plan for you the vast majority of the time. Index hints should only be used as a last resort and with the full understanding on the potential negative effects. If you have a query that is not performing as you want it to, an index hint is rarely needed. Instead, look at tuning how the SQL code is written and how the tables are laid out.

Put it this way, if you create an index and SQL Server chooses to ignore it, do you presume to be more knowledgable about query execution than the service? Indexing can be a good thing, but the way you write your queries will determine how the query should be executed. Forcing the index is one of those pay now or pay later things.




John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
Post #781582
Posted Wednesday, September 2, 2009 9:32 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 2:09 AM
Points: 2,727, Visits: 1,112
damn it John i was just about to reply. lol

Totally agree with what John has said. if you new to sql. SQL will choose the best index's to use. It is unusual to use a index hint to force a query to use that index. Normally if you are not seeing the index appear in the execution plan for that query. it should make you think hard about how your query is written.


--------------------------------------------------------------------------------------
Recommended Articles on How to help us help you and
solve commonly asked questions

Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden
Managing Transaction Logs by Gail Shaw
How to post Performance problems by Gail Shaw
Help, my database is corrupt. Now what? by Gail Shaw
Post #781587
Posted Wednesday, September 2, 2009 9:36 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 3:01 PM
Points: 3,843, Visits: 3,836
Ha! I was actually quite suprised that no one snuck a post in by the time I finished typing my response.



John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
Post #781591
Posted Wednesday, September 2, 2009 10:11 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, February 6, 2012 12:43 PM
Points: 215, Visits: 640
John Rowan (9/2/2009)
1. Yes. The query optimizer will analyze the query, the table/index structure, and the data distribution and determine the most optimal query plan for that query. So you can create indexes, but depending on how your table is laid out and how efficient your queries are, it may or may not choose to use your index.

2. Yes. You can use query hints to force the optimizer to use a specific index. I would caution you on this, however, especially if you have no experience in query/index tuning. The optimizer will pick the best plan for you the vast majority of the time. Index hints should only be used as a last resort and with the full understanding on the potential negative effects. If you have a query that is not performing as you want it to, an index hint is rarely needed. Instead, look at tuning how the SQL code is written and how the tables are laid out.

Put it this way, if you create an index and SQL Server chooses to ignore it, do you presume to be more knowledgable about query execution than the service? Indexing can be a good thing, but the way you write your queries will determine how the query should be executed. Forcing the index is one of those pay now or pay later things.


Thanks for the information.

I am actually surprised by a few things you said. Years ago I used to work with a database called RDB. There was no way in RDB to influence the optimizer. How you wrote your query was irrelevent. One of the pluses of this was that you never concerned yourself with things like the order of the joins in your query.

I don't plan on using these types of hints as I agree with your statement about the the query analyzer being more knowledgable then me. I would appreciated from a totally academic standpoint an example or two of a 'query hint'.

Thanks so much.
Post #781620
Posted Wednesday, September 2, 2009 1:02 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 3:01 PM
Points: 3,843, Visits: 3,836
From BOL:


WITH ( )
Specifies that the query optimizer use an optimization or locking strategy with this table and for this statement. For more information, see Table Hint (Transact-SQL).

In SQL Server 2005, with some exceptions, table hints are supported in the FROM clause only when the hints are specified with the WITH keyword. Table hints also must be specified by using parentheses.

The table hints allowed with and without the WITH keyword are the following: NOLOCK, READUNCOMMITTED, UPDLOCK, REPEATABLEREAD, SERIALIZABLE, READCOMMITTED, FASTFIRSTROW, TABLOCK, TABLOCKX, PAGLOCK, ROWLOCK, NOWAIT, READPAST, XLOCK, and NOEXPAND. When these table hints are specified without the WITH keyword, the hints must be specified alone. For example: FROM t (fastfirstrow).

When the hint is specified with another option, as in (fastfirstrow, index(myindex), the hint must be specified with the WITH keyword, such as:

FROM t WITH (fastfirstrow, index(myindex)).

The WITH keyword is not mandatory with hints when the database has a compatibility level of 80 or earlier.




John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
Post #781744
Posted Wednesday, September 2, 2009 1:11 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
I am actually surprised by a few things you said. Years ago I used to work with a database called RDB. There was no way in RDB to influence the optimizer. How you wrote your query was irrelevent. One of the pluses of this was that you never concerned yourself with things like the order of the joins in your query.


The difference is that with SQL, while you almost never have to worry about forcing the optimizer to work a specific way, you have the option of doing so in those very rare cases where you should.

For example, in a database I used to work with, there was a query that, depending on the account number it read, would either have to deal with a very small number of rows (usually 5-10), or possibly with a much larger number of rows (up to 60,000 in some cases). Since SQL stores execution plans and re-uses them, it ran into problems if it tried to use the optimum plan for the small number of rows when it was dealing with a large number, or vice versa.

If it was using the small plan on the large number of rows, it could take up to 30-40 seconds to complete. If it used the big plan on the small number of rows, it would only take a few seconds. If it used the right plan on the right number, either one, it would take milliseconds.

By adding a couple of simple hints, I got a compromise solution that was slightly slower on each than the optimum, but much faster than the opposite plan. So I ended up with either one taking just under half a second, which was a major improvement over the Russian Roulette of the just leaving it to pick it on its own.

In nine years of being a DBA, that's the only time I've ever had to bypass the engine on picking an execution plan.

So, it's very rarely useful, but when it is, it can be quite an improvement.


- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #781753
Posted Wednesday, September 2, 2009 1:28 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 3:01 PM
Points: 3,843, Visits: 3,836
I, too, have only had to use an index hint one time. At my last company, we had a stored procedure that accepted about 16 parameters and performed a dynamic search based off of the input parameters. Depending on which parameters you passed, the optimizer would select a different plan. Once cached, the reuse of plans for this SP would end up with the wrong plan being run for the parameter combination being passed in. I ran through a number of tests to find the 'middle road' plan that worked best for all of the common parameter combinations and we used a hint to force that plan.

This was just a band-aid though because the right fix involved re-engineering the SP and some of the table design and I did not have the resource bandwidth to make that kind of change as we had a customer waiting on the fix.

Another option for both Gus's and my example woudl be to create the procedure WITH RECOMPILE to force the SP to generate a new query plan for each execution. We chose not to go this route because of how frequent the SP was called.




John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
Post #781771
Posted Wednesday, September 2, 2009 1:37 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
In my case, using With Recompile was tested, and that took longer than the compromise plan that I ended up using. Also, it can end up causing compilation locks on the proc, which means only one connection can run it at a time.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #781780
Posted Wednesday, September 2, 2009 2:00 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 3:01 PM
Points: 3,843, Visits: 3,836
So the moral of the story is that out of 15 years of SQL Server expreience, a query index hint was chosen as the solution 2 times! Just think about how many queries that the both of us have written where the idea of using an index hint wasn't even on the radar.....definately the exception and not the rule.



John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
Post #781793
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse