Total Page Preview:   000000000394

Top 16 performance query optimization techniques in SQL Database?

 
1. Build indexes. Using indexes on a table, It will dramatically increase the performance of your read operation because it will allow you to perform index scan or index seek depending on your search predicates and select predicates instead of table scan.
Building non-clustered indexes, you could also increase the performance further.
 
2. You could also use an appropriate filtered index for your non clustered index because it could avoid performing
a key lookup.
 
3. You could also use a filtered index for your non-clustered index since it allows you to create an index on a particular part of a table that is accessed more frequently than other parts.
 
4. Try to use JOIN instead of SET operators or SUB-QUERIES because set operators and sub-queries are slower than joins and you can implement the features of sets and sub-queries using joins.
 
5. Avoid using LIKE operators, which is a string matching operator but it is mighty slow.
 
6. Avoid using blocking operations such as order by or derived columns.
 
7. You could also use an indexed view, which is a way to create one or more clustered indexes on the same table.
In that way, the query optimizer will consider even the clustered keys on the indexed views so there might be a possible faster option to execute your query.
 
8. Do table partitioning. When a particular table as a billion of records, it would be practical to partition a table so that it can increase the read operation performance. Every partitioned
table will be considered as physical smaller tables internally.
 
9. Update statistics for TSQL so that the query optimizer will choose the most optimal path in getting the data
from the underlying table. Statistics are histograms of maximum 200 sample values from columns separated by
intervals.
 
10. Use stored procedures because when you first execute a stored procedure, its execution plan is stored and the
same execution plan will be used for the subsequent executions rather than generating an execution plan every
time.
 
11. Use the 3 or 4 naming conventions. If you use the 2 naming convention, table name and column name, the SQL engine will take some time to find its schema. By specifying the schema name or
even server name, you will be able to save some time for the SQL server.
 
12. Avoid using SELECT *. Because you are selecting everything, it will decrease the performance. Try to select columns you need.
 
13. Avoid using CURSOR because it is an object that goes over a table on a row-by-row basis, which is similar to the table scan. It is not really an effective way.
 
14. Avoid using unnecessary TRIGGER. If you have unnecessary triggers, they will be triggered needlessly. Not only slowing the performance down, it might mess up your whole program as well.
 
15. For the last resort, use the SQL Server Profiler. It generates a trace file, which is a really detailed version of execution plan. Then DTA (Database Engine Tuning Advisor) will take a trace file as its input and analyzes it and gives you the recommendation on how to improve your query further.
 
16. Manage Indexes using RECOMPILE or REBUILD.
The internal fragmentation happens when there are a lot of data bubbles on the leaf nodes of the b-tree and the leaf nodes are not used to its fullest capacity. By recompiling, you can push the actual data on the b-tree to the left side of the leaf level and push the memory bubble to the right side. But it is still a temporary solution because the memory bubbles will still exist and won’t be still accessed much.
 
The external fragmentation occurs when the logical ordering of the b-tree pages does not match the physical ordering on the hard disk. By rebuilding, you can cluster them all together, which will solve not only the internal but also the external fragmentation issues. You can check the status of the fragmentation by using Data Management Function, sys.dm_db_index_physical_stats(db_id, table_id, index_id, partition_num, flag), and looking at the columns, avg_page_space_used_in_percent for the internal fragmentation and avg_fragmentation_in_percent for the external fragmentation.
 

Thank You

About Author

Brijesh Kumar

Database Developer

I have more then 6 years Experience in Microsoft Technologies - SQL Server Database, ETL Azure Cloud - Azure SQL Database, CosmosDB, Azure Data Factory, PowerBI, Web Job, Azure Function, Azure Storage, Web Apps, Powershall and Database Migration On-Premise to Azure Cloud.
LinkedIn : https://www.linkedin.com



Comments

Peak
04-Nov-2021
None of matter how long I had been playing, even assuming it had only been a few months, I has my doubts that this game was a scam of your beginning. The fact that I had won and imagine on the withdrawal meant that I anticipated them to successfully lose, which they did not. After just 20 hours, the first money was received, and the second repayment was received within 48 hours after the first. What i'm overjoyed, and I sincerely apologize for my tardiness. This website comes highly recommended by me to all patterns of gamblers.

                           
                           

                           

                           

Recent Posts

How to get the alternate rows or records from table in sql server
07-Jun-2017
Data Warehouse Three Tier Architecture
20-Aug-2017
SQL Server - Fragmentation on azure sql database
07-Oct-2018
Limitations of Azure SQL Database
19-Sep-2019
PIVOT and UNPIVOT in SQL Server with Example
29-Jun-2017
SQL Server - Find unused indexes on azure SQL database scripts
02-Oct-2018
SQL Server - Find all index with key columns on Azure SQL Database
02-Oct-2018
Find Resource usage in Azure SQL Database using script or T-SQL
07-Oct-2018
How to Set client IP Address using the firewall security in Azure SQL Using Portal
29-Jun-2017
What is a Trigger and type of trigger in SQL server?
10-Sep-2021
Top 60 SQL Server Interview Questions Answers for freshers and experienced
17-Sep-2021
What is Table Variable? Explain its advantages and disadvantages?
10-Sep-2021
Top 16 performance query optimization techniques in SQL Database?
16-Sep-2021
Cloud Data Engineer SQL Server Interview Questions Answers for freshers and experienced
31-Aug-2021
What are the types of User Define Function and difference between a nested UDF and recursive UDF in SQL server?
10-Sep-2021
Data warehouse Interview Questions Answers for freshers and experienced
16-Sep-2021
How many types of dimension tables available in data warehouse?
16-Sep-2021
What are the different types of Error Handling in SQL Server?
11-Sep-2021
What are the differences between OLTP and OLAP?
07-Sep-2021
What are characteristics and advantages of stored procedure?
10-Sep-2021

Facebook User: