Wednesday, August 8, 2012

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)

If you are searching life partner. your searching end with kpmarriage.com. now kpmarriage.com offer free matrimonial website which offer free message, free chat, free view contact information. so register here : kpmarriage.com- Free matrimonial website

Related Posts:

  • SQL INNER JOIN Keyword SQL INNER JOIN Keyword The INNER JOIN keyword returns rows when there is at least one match in both tables. SQL INNER JOIN Syntax SELECT column_n… Read More
  • ORDER BY clause ORDER BY clause ORDER BY is an optional clause which will allow you to display the results of your query in a sorted order (ei… Read More
  • sql_in_between_operatos IN & BETWEEN IN operator Syntax : SELECT col1, SUM(col2) FROM "list-of-tables" WHERE col3 IN … Read More
  • SQL JOIN table.reference tr.fixzebra { background-color: #F6F4F0; } table.reference th { background-color: #555555; border: 1px solid #555555; … Read More
  • SQL RIGHT JOIN SQL RIGHT JOIN Keyword The RIGHT JOIN keyword returns all the rows from the right table (table_name2), even if there are no matches in the left tab… Read More

0 comments:

Post a Comment