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

About these ads

38 thoughts on “SQL SERVER – Rules for Optimizining Any Query – Best Practices for Query Optimization

  1. Hey Pinal,

    This is really a useful input for performance issue. will appreciale if you shed sme light on locks as well which is equally important for query performance.

    Thanks again,

    Vivek

  2. Are you sure the point about Stored Procedures still holds true? According to Dino Esposito’s recent book, “Microsoft .Net: Architecting Applications for the Enterprise”, ppg. #333-338, the performance advantage of Stored Procedures is a myth when it comes to SQL Server 2005+ and Oracle DBs, because ALL queries are cached and reused instead of recompiled.

  3. I think Andy is right about the stored procedures. I have read the same thing in other sources and I have never seen a performance difference between an SP and the same commands in a script in 2005(in 2000 this was a different story).

    Also, are you sure about the views? I have conducted testing in 2005 and never found an advantage of a direct query over using a view. In fact, if the view is indexed, it can be substantially faster than accessing the base tables. I discussed some of that with views at: http://www.sqlservercentral.com/articles/Indexed+Views/63963/

  4. Timothy and Andy,

    I work in microsoft development team and I agree with Dave here. Views are really slow and reduces performance.

    In test environment with simple query and no real production load, view will work fine but in real life, I have seen people suffering.

    Michale
    (MSFT)

  5. Dave,

    Good article, but can you please explain your statement “Triggers should not be used if possible, incorporate the logic of trigger in stored procedure” .

    Thanks,
    Krash

  6. I’d to comment on some of the previous statements:

    – Yes, dynamic queries are cached but they may not be reused and would be taking up space in the plan cache. If you’re going to use dynamic SQL, use parameterized queries which will promote plan reuse.

    – Views perform about as well as accessing the tables directly as long as the query in the view has been optimized and the tables indexed appropriately. However, using a view may require slightly more overhead than using the tables directly because the view must be rendered before the results are returned, similar to a derived table.

    – Be careful when using indexes views as these views are materialized in the database. This means that every time a base table in the index view is updated, the materialied table must also be updated (along with it’s indexes). Indexed views are great for read-only reporting or OLAP databases, but may cause performance issues with OLTP databases.

    – Using triggers requires more overhead in the database than just incorporating the logic in a stored procedure. That does not mean you should not use triggers, just use them wisely and only were necessary.

  7. About the stored procedures, most of the time the performance gain is that when you use stored procedures you have to use parameters, and most people when they do not use stored procedures do not use Parameters, they just put the values inline. This is a major reason stored procedures win over inline SQL. But the other reason is that when you use inline SQL, the application is less maintainable when you find a logic problem in the retrieval of data. You can just fix the stored procedure and not have to recompile the code. But in a high load situation, stored procedures are preferred over inline SQL.

  8. No Views?

    Far as I know the optimizer does not care.

    That said, the real problem occurs where people build views of views of views and end up going after the same table N times when once would do it.

    Ad Hoc vs SP?

    Ad Hoc queries are a security hole.

    I can change the logic in the sp, adding temp tables, forcing joins etc. with having to redeploy code.

    Don’t forget

    Daily / weekly index rebuilds of overly fragmented indexes
    Don’t reindex, it only does leaf nodes and can cause creeping performance issues.

  9. In fact, with 2005 watch out for parameter sniffing with stored procs, what works wonderfully with a sql block, runs for minutes withing a stored proc.

    google “parameter sniffing”

  10. “Table should have primary key”
    How would you add more than one?

    “Remove any adhoc queries and use Stored Procedure instead”
    As long as there’s no logic warranting a stored procedure, any prepared statement will perform just as well. Just make sure it’s completely parameterized so we reuse the plan and avoid exhausting the plan cache with duplicates.

  11. In a high-load situation, inline SQL can cause high utilization of the network bandwidth – also, if you have a query with a length greather than 8000 chars (a coomplicated search, for example) you have to use a sproc.

  12. I have the question about the following 2 points:

    -Table should have minimum of one clustered index
    Q: Can there be more than one clustered index on a table? If Yes, how and when they shall be used?

    -Table should have appropriate amount of non-clustered index
    Q: Creating unnecessarily huge number of non-clustered index(s) also brings the performance down for a table. Is it a good practice to create non-clustered index(s) on the fly in the stored procs as and when they should be needed? Could there be any downside of it?

    Your blog is great… thanQ very much!

  13. Hey Pinal Nice artical man,

    Its really useful for optimizing sql qry or procedure. But can anyone tell that why dave says that do not to use Views or replace views with original source table?

  14. Hi Sourabh
    If you look closely at Dave’s article, you’ll want to change the article to “Tips for db optimization”. This is because he touched on table maintanance, views Vs stored procs, triggers and we use queries in all the above processes but tables.

    Now to attend to your question, views act like replicated table combination and are updated instantly as the tables get data, so in the testing enviroment they appear to behave due to small amount of data and reduced concurrency on data access thats why Michale says he has seen people suffering. In addition it is even a hastle to troubleshoot mishaps. All in all, try to check views vs stored procedures.

    As for you Dave, keep up the good work

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

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

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

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

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

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

  21. Hi Pinal,

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

    Sandeep B

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

  23. Pingback: SQL SERVER – Weekly Series – Memory Lane – #013 « SQL Server Journey with SQL Authority

  24. Pingback: SQL SERVER – Weekly Series – Memory Lane – #014 « SQL Server Journey with SQL Authority

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

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