Introduction
This article gives an explanation of some of the basic performance tuning techniques in SQL Server and also explains how to optimize SQL Query as well as provides some tips and tricks on performance tuning in SQL Server.
To retain its users, any application or website should run quick. For mission-important environments, a few milliseconds delay in obtaining data may produce big issues. As database sizes grow day by day, we'd like to fetch data as quick as possible and write the information back to the database as quickly as possible. to make certain all operations are executing swimmingly, you have to tune your database server for performance.
What is Performance Tuning in SQL Server?
SQL Server performance tuning is the method of making certain that the SQL statements issued by the associate application run within the quickest possible time. In other words, tuning SQL statements are finding and taking the quickest route to answer your query, similar to discovering the quickest route to your home after work.
SQL Server is a relational database management system (RDBMS) and Query tuning is usually the foremost speedy way to accelerate associate SQL Server performance.
SQL Server Performance Tuning Tips And Tricks
1) Always add schema/owner name with your stored procedure name, table name and etc else SQL Server’s engine tries to find it in all sachems until the object finds it and it may take some time, So if you add schema/owner name with your procedure/table name then SQL Server’s engine will not search for the procedure/table outside of its schema/owner and you can save your execution time.
2) Create a primary key on every table you create and unless you're extremely knowledgeable enough to work out a much better plan, build it the clustered index (note that if you set the primary key in Enterprise Manager it'll cluster it by default).
3) Indexes are data structures that increase the speed of data retrieval operations on a database table, Create an associate index on any column that's a foreign key. If you recognize it'll be unique, set the flag to force the index to be unique.
4) While you use SELECT statements at that time you should avoid the use of the ( * ) operator in your SELECT statements, you should select particular columns of your table as per your need. If you will use the ( * ) operator then the SQL server will scan all the column in your table and replaces the * with all your table column names.
5) While you create a stored procedure you should Use SET NOCOUNT on at the top of each stored procedure and SET NOCOUNT OFF at the bottom of your procedure.
6) Temporary tables tend to increase the complexity of a query so try to Reduce the Use of Temp Tables.
7) You should avoid the NOT IN condition, Instead of NOT IN you should use Joins, and while you compare with any nullable columns then you should use NOT EXISTS.
8) You should use WITH NOLOCK with your SELECT Statement and WITH ROWLOCK with your INSERT, UPDATE, DELETE Statement, It helps to reduce deadlock.
9) While you working with dynamic SQL stored procedure, to execute your SQL Query String you should avoid EXEC to execute your string and you can use named parameters and sp_executesql.
10) You should avoid the use of table variables in joins. You can use Common Table Expressions (CTEs) or derived tables in joins.
11) You should Avoid ORDER BY, GROUP BY, and DISTINCT as much as possible because the SQL server engine creates a work table and puts the information/data on the work table, and then after that, it organizes this data in the work table as requested by the query and then it returns final result set and that might take some time, So you can use ORDER BY, GROUP BY and DISTINCT in your query only and only when absolutely necessary.
12) Avoid writing inner/sub-queries as much as possible and try to use Joins instead of an inner/subquery.
Summary
Complex and huge applications sometimes create complicated needs. That leads us to put in writing complex SQL queries. These simple changes to your SQL Server queries can build a large difference in the response times. thank you for reading my article. I hope it had been useful.
