SQL SERVER – 2005 – Twelve Tips For Optimizing Sql Server 2005 Query Performance

I recently came across very nice article about optimization tips for SQL Server 2005. Here is the list of those 12 tips.

Twelve Tips For Optimizing Sql Server 2005 Query Performance

1. Turn on the execution plan, and statistics
2. Use Clustered Indexes
3. Use Indexed Views
4. Use Covering Indexes
5. Keep your clustered index small.
6. Avoid cursors
7. Archive old data
8. Partition your data correctly
9. Remove user-defined inline scalar functions
10. Use APPLY
11. Use computed columns
12. Use the correct transaction isolation level

Reference : Pinal Dave (https://blog.sqlauthority.com) , Original Article

Best Practices, Database
Previous Post
SQL SERVER – 2008 – Choosing the Right Edition for Your Needs
Next Post
SQL SERVER – PIVOT Table Example

Related Posts

22 Comments. Leave new

  • What do you mean by Keep your clustered index small?

    Reply
  • Darshan shah
    June 12, 2008 5:18 pm

    Hi pinal ,

    How to use ‘Use APPLY’?

    Reply
  • Darshan shah
    June 12, 2008 5:18 pm

    Hi Pinal ,

    What is archive your old data?

    Reply
  • Hi pinal ,

    what’s the difference between Covering Indexes and Clustered Indexes ?

    how to use clustered index small ?

    Reply
  • why to use veiws ?and how to use it.and why veiws used
    what are indexes ?and why indexes are used

    Reply
  • for question#1 correct me if i am wrong Pinal.
    is this the same thing our cluster value shuld be small rather than long (sting values)?

    Reply
  • HI Pinal,

    could you please reply on the followings:

    1. Keep your clustered index small.
    2. Use Apply
    3. Avoid cursors – When we have a situation that we can not avoid the use of cursor than what is the alternate solution? is there anything we can use instead of cursor to perform the desired task? which optiomize the peroformance too.

    Thanks in advance.

    Regards,
    Rupesh

    Reply
  • Hi, could you explain detail of these tips?

    Reply
  • Alkesh khedle
    August 6, 2008 1:51 pm

    Could you please tell me the difference between Hash join,Merge Join and Nested Join in Execution plan often seen.
    Which scenario they come to the picture.And the performance
    order of the three.

    Reply
  • Hi Pinal,

    I am not sure whether this is right or not. As i am giving link of other website to your website reader. My intention is only to help them. If you think it is not right then let me know so that i can stop.

    Thanks,
    Vijay

    Reply
  • Sir ,

    i studied that indexes will increase the performance

    by decreasing table scans and i/o cost……..

    SO

    could you please explain

    what is table scan?

    what is i/o cost?

    Reply
  • Dear Mr. Dave,

    I knew this 12 tips before I read your articles, but I’ve a question that just raised in my head..

    Sometimes we must use cursors for some reasons, how can anyone avoid using it. Do you have any alternative of CURSORS?

    If yes, then please let me know..

    rgds,

    Sidd

    Reply
  • sidd,

    You can use correlated subqueries,temporary tables and common table expessions as an alternative to cursors.

    suggest me if iam wrong.

    Thanks
    lakshmi

    Reply
  • Even the subqueriew will drag your query performence down ‘

    a simple join can answer your degraded performence
    with subquries
    usage of table variables and CTE will best replace the
    cursors

    Reply
  • This is for LAKSHMI,

    Lakshmi u r absolutely Correct…

    Reply
  • 1. Outer joins vs Sub query :which is best considering the performance .

    2. @tables vs #tables :which is best considering the performance .

    Reply
    • if the data to store in table is less then use @tables as it is stored in memory. for large records use #tables

      Reply
  • Hi pinal,

    tell me what is USE APPLY

    regards,
    Hari

    Reply
  • APPLY allows to invoke a function (tabled-valued) for each row of a query’s result set:

    SELECT c.NAME, tf.PHONE_NUMBER
    FROM dbo.CONTRACTOR AS c
    CROSS /*OUTER*/ APPLY
    dbo.ufnGetContractorsDefaultPhoneNumber(c.CONTRACTORS_ID) AS tf;

    Wouldn’t that mean that decreasing a query performance?
    Even in a the training kit for 70-433 there is a hint that its a long running query, for a result set containing 20k rows.

    Reply

Leave a Reply