Database Optimization Interview Questions & Answers

Question 1. Reasons Of Poor Performance Of Query?

Answer :

• No indexes
• 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.

Question 2. Tell Me In Brief The Cursor Optimization Tips?

Answer :

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

Question 3. How Is Index Tuning Used To Improve Query Performance?

Answer :

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.

Question 4. Tell Me What Is Index Tuning?

Answer :

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.

Question 5. What Are The Ways To Code Efficient Transactions?

Answer :

• 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.

Question 6. Do You Know What Are The Ways To Code Efficient Transactions?

Answer :

• 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.

Question 7. Explain Some Disadvantages Of The Indexes?

Answer :

Indexes improve query performance but it slows down data modification operations.

Indexes consume disk space.

Question 8. Explain Execution Plan?

Answer :

• SQL Server caches the plan of execution of query or stored procedure which it uses in subsequent call.
• This is a very important feature with regards to performance enhancement.
• You can view execution plan of data retrieval graphically or textually.

Question 9. Explain In Brief The Cursor Optimization Tips?

Answer :

• Close cursor when it is not required.
• You shouldn’t forget to deallocate cursor after closing it.
• You should fetch least number of records.
• You should use FORWARD ONLY option when there is no need to update rows.

Question 10. Tell Me What Are The Types Of Indexes?

Answer :

• Indexes can be clustered and non-clustered indexes.
• Clustered index keeps all the records in the database in the order of clustered index key.
• There can be only one clustered index per table.
• Non-clustered indexes are stored as B-tree structure in their own storage separate from data storage in the table.

Question 11. What Are Indexes?

Answer :

• Index can be thought as index of the book that is used for fast retrieval of information.
• Index uses one or more column index keys and pointers to the record to locate record.
• Index is used to speed up query performance.
• Both exist as B-tree structure.
• Kind of the indexes are clustered and non-clustered.