SQL SERVER – Rules for Optimizining Any Query – Best Practices for Query Optimization

This subject is very deep subject but today we will see it very quickly and most important points. May be following up on few of the points of this point will help users to right away improve the performance of query. In this article I am not focusing on in depth analysis of database but simple tricks which DBA can apply to gain immediate performance gain.

  • Table should have primary key
  • Table should have minimum of one clustered index
  • Table should have appropriate amount of non-clustered index
  • Non-clustered index should be created on columns of table based on query which is running
  • Following priority order should be followed when any index is created a) WHERE clause, b) JOIN clause, c) ORDER BY clause, d) SELECT clause
  • Do not to use Views or replace views with original source table
  • Triggers should not be used if possible, incorporate the logic of trigger in stored procedure
  • Remove any adhoc queries and use Stored Procedure instead
  • Check if there is atleast 30% HHD is empty – it improves the performance a bit
  • If possible move the logic of UDF to SP as well
  • Remove * from SELECT and use columns which are only necessary in code
  • Remove any unnecessary joins from table
  • If there is cursor used in query, see if there is any other way to avoid the usage of this (either by SELECT … INTO or INSERT … INTO, etc)

There are few hardware upgrades can be considered as well like separating index on different disk drive or moving tempdb to another drive. However, I am not suggesting them here as they are not quick way to improve the performance of query.

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

Best Practices, SQL Scripts
Previous Post
SQLAuthority News – CWE/SANS TOP 25 Most Dangerous Programming Errors
Next Post
SQLAuthority News – SQLAuthority News – Ahmedabad User Group Meeting January 17 2009 – Review

Related Posts

42 Comments. Leave new

  • Net Wolf (Hoapino)
    June 2, 2009 8:17 pm

    In simple terms views like any other sql statements need to be compiled then executed which comsumes time and space for large amount of data unlike stored procs which are already precompiled

    Reply
  • Hi Pinal Dave

    This is very usefull to my daily work as dba thanks alot

    Jayant

    Reply
  • Hi Hoapino and all,

    Will d views occupy place in the Database?N can some one help me the meaning of MOVE D LOGIC OF UDF TO SP

    Thanks,
    Narenndra

    Reply
  • Hello Pinal,

    I wanted I wanted to know which kind of queries can be moved to stored procedures. Can you please guide me on Ad- Hoc, dynamic and parametrized queries in reference to SP. What will be beneficial for performance improvement?

    Reply
  • Thanks for the Tips. It was useful.

    Reply
  • Pinal,

    Many time I am reading your blogs.
    In this article you have mentioend very very basic optimization technique.

    I would request you to separate your articale for expert, beginners etc.

    -Nilesh

    Reply
  • Hi

    I am suresh. Thjs is my question.

    I am inserting 8 lacks records from source table to multiple destination table with some ETL operation. First 10000 records getting inserted in 10 minutes and after that it takes 20 minutes to insert 2000 records it keeps on increasing the time. what would be the solution. plz asap

    Reply
  • Surersh,
    Create a text file of the table and use it as a source instead of directly using the table as the source.
    You can use the cmd utility called bcp.exe to create the text file. Please reply as to how it works out.

    Reply
  • Hello,
    How to write a syntax for T-SQL,user defined functions and stored block procedure.

    Reply
  • Hi Pinal,

    Thanks for putting such helpful information on your blog. It always helped me to find solutions for queries. Thanks again.

    Sandeep B

    Reply
  • Hello sir,

    \very Nice Blog of u.Thanks for putting such helpful information on your blog. It always helped me to find solutions for queries.

    Reply
  • Girijesh Pandey
    October 1, 2012 11:07 pm

    Hi Pinal,
    Nice blog!

    Reply
  • can any one plz tell me the tools used for query optimization for sql from the erspective of data warehouse

    Reply
  • What about @table and #table… how it hurts performance.

    Reply
  • Hi Pinal,

    This was really helpful information for me. Can you optimize the query while using a ORDER BY clause for quick sorting of data records?

    Reply
  • Hello Pinal,

    Quick Question..

    Following priority order should be followed when any index is created a) WHERE clause, b) JOIN clause, c) ORDER BY clause, d) SELECT clause

    Isn’t it a Join –> Where –> Order BY –> select..??

    Can you please share example to justify your statement..?

    Reply
  • subramanain rajkumar
    September 22, 2015 9:43 pm

    Yes I applied this in Production Server seems to be good

    Reply
  • subramanain rajkumar
    September 22, 2015 9:45 pm

    Could you please tell me which option is best in database replication in small size around 200 mb between peer to peer connection

    Reply
  • Thanks…

    Reply
  • arviend singh
    March 21, 2017 1:28 pm

    points to be remember…

    Reply

Leave a Reply