Thursday, March 23, 2017

Query Optimization in SQL Server Database

In the last post, I have discussed 10 ways to prevent SQL database corruption and now, I am going to discuss query optimization in SQL Server database.

Microsoft SQL Server performs most of the activities by query execution. The database management system helps to get expected results by the query and this process called optimization. The most frequent queries used with a SELECT command for data retrieval and it required optimization. We should think that not only SELECT queries need the optimization but also other objects like index and view.

How to perform SQL Server query optimization

There are few techniques by which, you can improve the query performance, and these are:
  • Improvement in Indexes
  • Use of query optimizer
  • Understand the response time
Let us discuss them one by one.

Improvement in Indexes

SQL database supports different types of indexes like Clustered index, Nonclustered index, Unique index, Columnstore index, Filtered index, XML index, Full-text index, Spatial Index, Index with computed columns, Index with included columns, etc. Now, user has to create a useful index to achieve the better query performance. To create the useful index, you should have the understanding the use of data. By useful index, you can get the expected result with fewer disk I/O operations.

Examine the query performance first and if, it is taking a long time to execute then add an index with the query and rerun it. If an index is not helping then, remove the index. Few queries strongly support indexes. For example, SELECT queries perform very well with most of the indexes. The DML (Data Manipulation Language) commands like INSERT, UPDATE and DELETE are slow because most of the indexes maintain the operation. In other words, if your most of the queries are SELECT then, you can improve your performance in good manner, and if, they are DML operations then, you should be conservative with the indexes.

Use of Query Optimizer

The two major component of SQL Server database engine is storage engine and the query processor. Storage engine reads the data between disk and memory to maintain the data integrity. The query processer receives all the queries and creates a plan to for the optimal execution. After that, complete the execution to deliver the result.

As SQL server uses the SQL language, which is a high-level language. It only works on what data to get from the database not the algorithm process to do it. Now, for each query, the query process create the plan for best and fast execution.

Understand the Response Time

As a SQL database user, you should be aware of the response time and total time of the result. Response time is the time in which the query returns the first result. Total time is the time to return all the records from the query. Look on the following image to check SQL server execution time.

 After executing the query: SELECT* from subtext_config, you can get the Total SQL server execution time and the number of records.

Suppose the query return 1000 records and you need first 50 records. In this case, you will not think about how long time the query will take to return 1000 records instead, you will think about the first 50 records quickly so that, you can settle the list.

Conclusion: These are the major point of using query optimisation techniques in SQL Server. Choose any techniques to make you maintain the database integrity. I hope this will help you to know about the Query Optimization in SQL database.

No comments:

Post a Comment