September 2, 2009 at 9:19 am
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
September 2, 2009 at 9:28 am
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.
September 2, 2009 at 9:32 am
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.
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
September 2, 2009 at 9:36 am
Ha! I was actually quite suprised that no one snuck a post in by the time I finished typing my response.
September 2, 2009 at 10:11 am
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.
September 2, 2009 at 1:02 pm
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.
September 2, 2009 at 1:12 pm
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
September 2, 2009 at 1:28 pm
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.
September 2, 2009 at 1:37 pm
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
September 2, 2009 at 2:00 pm
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.
September 2, 2009 at 2:05 pm
John Rowan (9/2/2009)
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.
Pretty much.
- 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
September 3, 2009 at 6:59 am
I want to thank everyone for the advice. It was clear and well thought out.
Thanks again.
September 4, 2009 at 6:49 am
You're welcome.
- 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
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply