As a developer or DBA, we write T-SQL queries all the time. We should always make sure that our queries are optimized for better performance. Query performance is measured by looking at the query execution time. In this blog, I will explain some best practices to follow. This will help you understand how to write efficient queries and how to optimize SQL Server resources for better performance.

• Do not use “Select *” in your queries. Only specify the columns you need.
• While making Joins, only use tables which are required.
• Try to use stored procedures as much as you can. Convert Views to stored procedures where ever it is possible as stored procedure execution is much faster compared to views.
• Implement indexes for better performance
• Partitioning a table is recommended when you have large tables. For example, in data warehouse, consider partitioning Fact tables.
• Avoid using Functions in Select statements and Join conditions. Imagine if there are 10,000 records in query result set. The function call will be made 10,000 times during query execution.
• Avoid using Cursors as Cursors are very slow.
• For recursion, use Common Table Expressions (CTE) instead of functions
• Make sure to check the query execution plan. You can turn on query execution plan by shortcut “Ctrl + L” or by selecting menu item “Query > Display Estimated Execution Plan”. When you execute query, look at the actual execution plan and see where query is taking longer. Once you know where query is taking longer, implement indexes or change Joins accordingly.
• When your query is taking long time to execute, use query hint Option MAXDOP (Maximum degree of parallelism). I will be writing a separate blog to explain this query hint option and how to use it.