Database Optimization Interview Questions and Answers
Reasons of poor performance of query.
Excess recompilations of stored procedures.
Procedures and triggers without SET NOCOUNT ON.
Poorly written query with unnecessarily complicated joins
Highly normalized database design.
Excess usage of cursors and temporary tables.
Tell me in brief the cursor optimization tips?
The following are few tips for cursor optimization:
When the cursor is not needed, close the cursor
Deallocate the cursor after closing it.
Fetch less number of rows.
Avoid triggers – because trigger executes whenever data gets updated, leads to overload of the system.
When the rows are not need to update, use the option FORWARD ONLY
Use where instead of having clause unless it is essential
How is index tuning used to improve query performance?
The Index tuning wizard can be used to improve the performance of queries and databases. It uses the following measures to do so:
It uses the query optimizer to perform the analysis of queries with respect to the workload and based on this knowledge, it recommends the best usage of indexes.
The changes in the usage of index, query distribution and their performance are analysed for checking the effect.
It also recommends ways of tuning the database for a small set of problem queries.
Tell me what is Index tuning?
Index tuning is part of database tuning for selecting and creating indexes. The index tuning goal is to reduce the query processing time. Potential use of indexes in dynamic environments with several ad-hoc queries in advance is a difficult task. Index tuning involves the queries based on indexes and the indexes are created automatically on-the-fly. No explicit actions are needed by the database users for index tuning.
What are the ways to code efficient transactions?
We shouldn't allow input from users during a transaction.
We shouldn't open transactions while browsing through data.
We should keep the transaction as short as possible.
We should try to use lower transaction isolation levels.
We should access the least amount of data possible while in a transaction.