SQL Server Performance

Hists for the Queries

Discussion in 'Performance Tuning for DBAs' started by krajdba, Dec 30, 2004.

  1. krajdba New Member

    Hi Gurus,

    I am migrating from Oracle to Sql Server.I would like to know the best query hints
    available in the sql server.To improve the peroformance of a query which is taking
    about 10 minutes.Not All some best ones only please.


  2. derrickleggett New Member

    1. Design the database properly in the first place.
    2. Use INNER JOIN whenever possible.
    3. Don't use OR or IN if it can be avoided.
    4. Never use cursors or loops when it can be set based. This is a thought process.
    5. Never give more information then you need to.

    Post the query. We'll see what we can do with it.


    When life gives you a lemon, fire the DBA.
  3. satya Moderator

  4. chopeen Member

    Use proper indexes.

    [Derrick, I know that indexes are part of database design, but I think it is worth to stress the significance of using them.]


    Marek 'chopeen' Grzenkowicz

    Rediscover the web
  5. Phantom New Member

    I read somewhere that you have to tell oracle what indices to use cause otherwise it will just do table scans (no flame intended, I think krajdba thinks he always must give directions to the database engine).

    SQL server has a query optimizer that it runs before execution of the query which chooses the best strategy to execute it. If there are the right indices defined and a table scan is more work it will use them.

    You can still use query hints if you think SQL server makes the wrong decissions but this happens rarely.

    Do you know about the graphical execution plan? If not give BOL (books online, the manual that comes with SQLS) a visit.

  6. mmarovic Active Member

  7. chopeen Member

    I can't stop wondering what we are talking about here -- query hints (join hints, query hints, and table hints) or general hints concerning working with SQL Server? [<img src='/community/emoticons/emotion-2.gif' alt=':D' />]<br /><br />--<br /><br />Marek 'chopeen' Grzenkowicz<br /><br /><i>Rediscover the web<br /<a target="_blank" href=http://www.mozilla.org/firefox/>http://www.mozilla.org/firefox/</a></i>
  8. mmarovic Active Member

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote">I can't stop wondering what we are talking about here -- query hints (join hints, query hints, and table hints) or general hints concerning working with SQL Server?<hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote">Uh, you are right, I lost the meaning of original question reading responses especially Derrick's [<img src='/community/emoticons/emotion-11.gif' alt='8)' />] Anyway, I think for someone coming from Oracle environment everything mentioned may be helpful.
  9. chopeen Member

    To make the situation even more complicated, check the subject of this discussion.


    Marek 'chopeen' Grzenkowicz

    Rediscover the web
  10. krajdba New Member

    Hi Guys,

    (Reading the responses after a long time)

    You all have made a mess of my Original question.I guess you Guys should go out and relax or
    watch a movie.

    I consider Satya's response as a Good one.


    Thanks for All


  11. Phantom New Member

    lol <img src='/community/emoticons/emotion-1.gif' alt=':)' /> Is it true than that you have to use query hints in Oracle if you want a certain index to be used?
  12. satya Moderator

  13. Phantom New Member

    What I read in a book (inside sql server, bit outdated) is that Oracle doesn't use indices at all unless you said so. There is no part in the query optimization that does investigate wath index is most appropriate.
    I don't know what version of Oracle it referes to but the book is from 2001.
  14. mmarovic Active Member

    No it is not the case, at least on Oracle 8 (I think) and 9 (for sure).
  15. joechang New Member

    i vaguely recall that prior to 8i or there abouts, Oracle defaults to rule based optimization, hence perhaps the greater need for index hints,
    i also recall that there were issues with the early versions of cost based opt (CBO), but these were mostly resolved by 8i

    i would not think that hints are as necessary using CBO,
    the more current versions of Oracle should be CBO,
  16. simas New Member

    Joe - you are right. CBO is now a default option (especially in 10g) and RBO is "depreciated technology" on its way out

  17. Luis Martin Moderator

    Joe remember me Albert Einstein. Don't use capital letters, period, may he use the same suite every day, and I'm sure he will not read this post.<br />But I love this guy, he realy is a GURU[<img src='/community/emoticons/emotion-1.gif' alt=':)' />].<br /><br /><br />Luis Martin<br />Moderator<br />SQL-Server-Performance.com<br /><br /><font size="1">All postings are provided “AS IS” with no warranties for accuracy.</font id="size1"><br /><br />

Share This Page