what is "query hint"
-
hey all
i would like to know what is "query hint" and what are its benefits?
thanx
Rajiv.
-
Normally sql will determine the way a sql statement is executed but it doesn't always get it right, hints will allow you to "hard code" a method of how sql will access your data (such as which indexes are used).
There is some great help in Book online (BOL) as well as http://www.msdn.microsoft.com
Just search for "query hints"
Max
-
Well, I would say, usually SQL Server is doing a good job at determine a good - optimal way to execute a statement. However, as has been said, a "hint" is a friendly description for to force SQL Server doing something in the way you specific. While this might be or better now, it might prove suboptimal when factors like statistics change significantly. SQL Server must still execute the hint, while there might be a better way to execute the statement.
Or in short, use hints only as a kind of "last resort". You shouldn't use them regularly. You can write good queries without hints.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
In addition to Max and Frank's clarifications :
One of the downsides of hints is that you'll have to check if they are needed with every hotfix/servicepack/upgrade you perform.
Because execution plans are based on the db's statistics for the used objects, before you provide hints, have your db and db-statistics optimized.
Execute the maintenanceplan, dbcc updateusage, sp_updatestats before you start analysing the queries.
If you're performing performance-CPR
first check the statistics before you perform the maintenanceplan/reindex,... Maybe this behaviour is "normal life" for your object and you might have to get your help elsewhere (PAD_INDEX / FILLFACTOR ).Keep in mind db-engines get more intelligent, so the path you want to have it using for that query may not be the most optimal in time.
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Great discussion guys
Indexes and stats are often regarded as "the thing that should just work" but in reality they do need a bit of help sometimes.
Hints should be the last step in optimizing your queries and should be kept track of. It is far better to put the optimizations in sql's hands as well as your dbcc tasks that you should be running on a regular basis. Keep a hand close to the rudder though!
Max
Max
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply