How to tune the Query in SQL server 2005?
1. Before start the query tuning Need to understand the database structure and Query execution procedure.
Whenever any query fire in the SQL server it will follow some process to get the results
1.1 SQL server check the query syntax .After checking the syntax SQL server allows compiling the query.
1.2 At the time of compiling it will generate the query process tree .In these processes it will attach what are the tables are required.
1.3 After this process it will move to the optimizer Where it will looked at what are the tables are used in the query processor according that it will take the data statistics. Where it will analysis the how many records have in table and what are the constraints and indexes are used.
1.4 According the optimizer it will generate the Estimation execution plan which will just estimate the execution process.
1.5 But when query start the execution in SQL server then estimated execution plan move to the Actual execution plan means at the time of query running which plan will be the best for the SQL server based on the tables, records, constraints and indexes SQL server use that particular plan is called as Actual execution plan.
2. Physical operator and Logical operator
Physical operator is the actual plan of execution.
Logical operator is the conceptual plan of execution.
E.g.:
SELECT E.Name, D.Name, E.Salary
FROM EMP E
INNER JOIN Dep D
ON E.ID = D.EmpID
On above query INNER JOIN is called as Logical operator but when query execute in the SQL server it will move to actual plan that time it will use the MERGE JOIN or NESTED LOOP to fetch the records from the data page is called Physical operator.
Please go through the below link to know about more the physical and logical operator
3. Physical read and Logical read
It will show how query has performing in SQL server. Whenever query fire first time in the SQL server it will read the data from the data page that called as Physical read. Next time when query fires in SQL server it will fetch the data from database cache that called as logical read.
SET STATISTIC IO ON
SELECT E.Name, D.Name, E.Salary
FROM EMP E
INNER JOIN Dep D
ON E.ID = D.EmpID
When above queries fire inside the execution pane one tab will display called as results. Inside this pane it will display the Physical and Logical read number.
If physical read is more than 0 means it has fetching data from the data page. This case physical read is happening.
If physical read is 0 and logical read is near to 0 that means it has fetching data from the data cache. This case logical read is happening but for the query performance need to reduce the logical read up to what extended it can possible.
4. Execution plan
Execution plan need to check the estimation cost based on the four things
- Table scan
- Table seek
- Index scan
- Index seek
Without index a table called as Heap.
On unique key / primary key table scan improve the performance.
On table scan SQL server search entire data page one by one.
On Index seek SQL server seek the index pages form page to page.
5. Use the table scan in the less no of records of table and use the index scan in the large number of records of table.
6. Use covering index for the increase the performance. But if the index page size is more then again it will give the performance problem.
Max size of the index page is 900 byte if any page goes beyond than 900 byte SQL server gives the warning message.
Two ways to create the covering index
n Composite Keys
n Included keyword
On varchar (max) and XML data type can’t create the composite keys index.
If the size of index increase which further impact the performance.
7. Keep index size small. Create index on the numeric as compared to text data type. Recommended to create the index on integer and numeric value to get the best performance.
If data pages or index pages are more in the database then performance will slow down.
8. Database tuning advisor tool help to create recommended indexes and statistics for the query.
9. Index view increase the performance of the view.
10. Avoid to more use the funcations inside the query.
11. Insted of subquery use the CET functionality.
12. Use CROSS Apply intsted of writing another select statement to get the particular column in a query.
13. Execute dynamic query based on the parameter.
14. Turn on execution plan and statistics
15. Avoid cursor
16. Archive old data from table
17. Partition data correctly
18. Remove user defined inline functions
19. Use Apply [Cross Apply]
20. Use computed column(Instead of calculating put one more column the calculated data)
21. Use correct transaction level
13. Execute dynamic query based on the parameter.
14. Turn on execution plan and statistics
15. Avoid cursor
16. Archive old data from table
17. Partition data correctly
18. Remove user defined inline functions
19. Use Apply [Cross Apply]
20. Use computed column(Instead of calculating put one more column the calculated data)
21. Use correct transaction level
Hi Sunil,
ReplyDeleteCan you provide some more info about this point "Insted of subquery use the CET functionality". Actually, i am not aware of CET functionality in SQL Server. Anyhow it's nice article.
HI Pavan,
ReplyDeleteWhenever we are using the co-related sub queries first inner query will execute and outer query will execute based on the result of inner query .it’s a recursive process to get the result.
From SQL server 2005 onwards Microsoft has given the functionality common table expression(CET) where we can capture the result set for the next execute query .So in above case inner query result set we can capture for the next query mean outer query .This process we can avoid the recursive process.