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 (http://blog.SQLAuthority.com) , Original Article

About these ads

22 thoughts on “SQL SERVER – 2005 – Twelve Tips For Optimizing Sql Server 2005 Query Performance

  1. 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)?

    Like

  2. 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

    Like

  3. 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.

    Like

  4. 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

    Like

  5. 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?

    Like

  6. 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

    Like

  7. 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

    Like

  8. 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.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s