Query Optimization ...........1

• ----- This post is excerpt of Pinal Dave 's article published on www.sqlauthority.com ----
• ----- Try to get causes of each sentence  -----
•  
• Notes prepared for “Good, Better and Best Programming Techniques” meeting
• Do not prefix stored procedure with SP_ prefix. As they are first searched in master database, before it is searched in any other database.
• Always install latest server packs and security packs.
• Make sure your SQL Server runs on optimal hardware. If your operating system supports 64 bit SQL Server, install 64 bit SQL Server on it. Raid 10 Array.
• Reduce Network Traffic by using Stored Procedure. Return only required result set from database. If application needs paging it should have done in SQL Server instead of at application level.
• After running query check Actual Execution Plan for cost of the query. Query can be analyzed in Database Engine Tuning Advisor.
• Use User Defined Functions sparsely, use Stored Procedures instead.
• Stored Procedure can achieve all the tasks UDF can do. SP provides much more features than UDFs.
• Test system with realistic data rather than sample data. Realistic data provides better scenario for testing and reveals problems with real system before it goes to production.
• Do not use SELECT *, use proper column names to decrease network traffic and fewer locks on table.
• Avoid Cursors as it results in performance degradation. Sub Query, derived tables, CTE can perform same operation.
• Reduces the use of nullable columns.
• NULL columns consumes an extra byte on each column used as well as adds overhead in queries. Also NULL is not good for logic development for programmers.
• Reduce deadlocks using query hints and proper logic of order in columns.
• Normalized database always increases scalability and stability of the system. Do not go over 3rd normal form as it will adversely affect performance.
• Use WHERE clauses to compare assertive logic. Use IN rather than NOT IN even though IN will require more value to specify in clause.
• BLOBS must be stored filesystem and database should have path to them only. If path is common stored them in application variable and append with filename from the BLOBColumnName.
• Always perform referential integrity checks and data validations using constraints such as the foreign key and check constraints.
• SQL Server optimizer will use an index scan if the ORDER BY clause is on an indexed column.
• Stored Procedure should return same numbers of resultset and same columns in any input parameters. Result Set of Stored Procedure should be deterministic.
• Index should be created on highly selective columns, which are used in JOINS, WHERE and ORDER BY clause.
• Format SQL Code. Make it readable. Wrap it.
• Use Column name in ORDER BY clause instead of numbers.
• Do not use TEXT or NTEXT if possible. In SQL Server 2005 use VARCHAR(MAX) or NVARCHAR(MAX).
• Join tables in order that they always perform the most restrictive search first to filter out the maximum number of rows in the early phases of a multiple table join.
• Remember to SET NOCOUNT ON at the beginning of your SQL bataches, stored procedures, triggers to avoid network traffic. This will also reduct the chances of error on linked server.
• Do not use temp tables use CTE or Derived tables instead.
• Always take backup of all the data.
• Never ever work on production server.
• Ask someone for help if you need it. We all need to learn.