Wednesday 8 August 2012

Perfromance Tuning in SQL Server 2008



Query Optimization

1.       Remove * from SELECT and use columns which are only necessary in code
2.       Remove any unnecessary joins from table
3.       Always use WHERE Clause in SELECT Queries while we don’t need all the rows to be returned.
4.       Avoid SELECT * FROM OrderTable WHERE LOWER(UserName)='telsa' , because case insensitive.
5.       The operators shown below are in their decreasing order of their performance.
        =
        >,>=,<, <=
       LIKE
      <> 
     ie, better to go for '=' rather than 'LIKE' or '<>'

6.       NOT IN, then this is going to offer poor performance, this can be avoided by using EXISTS or NOT EXISTS.
7.       When there is a choice to use IN or EXIST, we should go with EXIST clause for better performance.
8.       SELECT * FROM OrderTable WHERE Status = 1 AND OrderID IN (222,444,111,555)
Takes more time than
SELECT * FROM OrderTable with (INDEX=IX_OrderID) WHERE Status = 1 AND OrderID IN ( 222,444,111,555 6)
9.       While there is case to use IN or BETWEEN clauses in the query, it is always advisable to use BETWEEN for better result.
10.   Always avoid the use of SUBSTRING function in the query.
11.   Provide the least likely true expressions first in the AND. Because if the first condition is false the result also false, ie it never want to check the second condition of AND operation .
12.   It is sometimes better to combine queries using UNION ALL instead of using many OR clauses.
13.   SELECT * FROM CustomerTable WHERE City = 'Wichita' OPTION(FAST n). In some scenario's we have to display only 10-15 result set in the web page . But in database it having more than Ten Thousand row. In this time the data base will give the 'n' result set very fast, and rest of the data will update soon.
14.   Avoid Expensive Operators Such as NOT LIKE
15.   WITH NOLOCK and WITH READUNCOMMITTED
16.   Avoid Long-Running Transactions. it has to do by the sys admin.
17.   Use Set NOCOUNT ON in stored procedures.
18.    You can rewrite a subquery to use JOIN and achieve better performance.
19.   Performance increase by adding a unique key for table. (it can index the table by unique key)

7 comments:

  1. Hello Mr. Simon.
    I wanted to try the advise #8 because more often than not I find myself perfomrming queries of that kind... but the (INDEX = IX_OrderID) piece is new to me.
    This might look like a n00b question (and maybe it is) but how does it really work?

    I tried using (INDEX = IX_) but the parser returned an error: "Incorrect syntax near 'INDEX'. If this is intended as a part of a table hint, A WITH keyword and parenthesis are now required."

    I tried using "WITH (INDEX(IX_))" but I got the next error:
    "Index 'IX_...' on table 'TableName' (specified in the FROM clause) does not exist."

    I hope my question does not turn to be a whole new topic. If that's the case just let me know :P
    (ATM, I'm googling the topic, just in case it's more complex than I think... and that's quite probable haha)

    ReplyDelete
    Replies

    1. Hi Compa...

      Definitely you have to use index along with 'with' keyword..
      For this you have to give the index name property....

      Check the sample below... find the index name either through management studio or using query..

      select * from Course with (index=UNI_DEPT) where Offering_dept='ACCT'

      i think it will be fine...

      Delete
    2. Ah, I understand.
      Is not like I create the index "Out of the blue", I tell the optimizer which of the table's index to use right? :)

      I feel a little dumb now haha.
      Thanx for the help :D

      Delete
  2. Better you guys don't play with these settings without being enough confident.
    I highly recommend that you contact ServerBuddies instead, they are fantastic, I highly recommend to use these guys!
    They provide quality remote server management, including troubleshooting, Server Management, Plesk Support, Server Maintenance, Server Monitoring, Server Troubleshooting and support at a affordable rates. They also provide Server Optimization, Plesk Support, Linux Support, cPanel Support and Plesk Support.

    ReplyDelete
  3. Awesome tips, few of them like #8, #11 $ #13 are great. Thank you for all those details.

    ReplyDelete
  4. Also, please add some Query performance tips with real time scenarios and examples. This will help a lot for me. if possible please also explain how to troubleshoot with Wait types and wait types.

    ReplyDelete