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

  • Vivek Srivastava
    January 20, 2009 10:15 am

    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,


  • Quick question, why do you recommend against using views?

    And by the way, you’re blog is the best SQL learning tool I’ve come across. Great content!

  • Ach, “you’re” versus “your”. I hate it when I catch a spelling error one second after clicking submit.

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

  • TimothyAWiseman
    January 20, 2009 9:58 pm

    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:

  • Michale Shilling
    January 20, 2009 11:39 pm

    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.


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


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

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

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

  • 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”

  • Mark S. Rasmussen
    January 22, 2009 5:21 am

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

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

  • hi pinal,
    As you said that, we should have non clustered index.
    Can you say how to add a non-clustered apart from primary key.

  • 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!

  • Ya its a good for getting any correct output and also for optimization…..

  • Sourabh Sachdeva
    February 4, 2009 4:54 pm

    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?

  • Hi i am durgesh…..

  • Thank you for wonderful article.

  • Net Wolf (Hoapino)
    June 2, 2009 7:44 pm

    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


Leave a Reply