+91 90691 39140 | +1 253 214 3115 | info@hub4tech.com | hub4tech

Top 10 Most Asked Database Optimization Interview Questions and Answers

Mon, 2017-07-17 16:39
Database Optimization Interview Questions

Are you preparing for Database Optimization job interview and wondering what questions you might be asked? In this blog we have hand-picked the questions you are likely to be asked in Database Optimization job interview and provided to-the-point answers to each one of them so as to help you prepare better for Database Optimization job interviews.

Always keep in the mind that, only academic knowledge is not enough to crack an interview. Employers expects from the candidate to have practical knowledge and hands-on experience as well. This Database Optimization Interview Questions designed by industry expert will help you to gain practical knowledge of Database.

Database Optimization Interview Questions and Answers

  1. Reasons of poor performance of query.
  2. 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.
  1. Tell me in brief the cursor optimization tips?
  2. 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
  1. How is index tuning used to improve query performance?
  2. 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.
  1. Tell me what is Index tuning?
  2. 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.

  1. What are the ways to code efficient transactions?
  2. 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.
  1. Do you know what are the ways to code efficient transactions?
  2. 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.
  1. Explain some disadvantages of the Indexes?
  2. Answer:
    Indexes improve query performance but it slows down data modification operations.
    Indexes consume disk space.

  1. Explain Execution Plan.
  2. 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.
  1. Explain in brief the cursor optimization tips.
  2. 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.
  1. Tell me what are the types of Indexes?
  2. 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.

Use Full Link for Training and Certification

Hitesh Patel
 
Copyright ©2015 Hub4Tech.com, All Rights Reserved. Hub4Tech™ is registered trademark of Hub4tech Portal Services Pvt. Ltd.
All trademarks and logos appearing on this website are the property of their respective owners.
FOLLOW US