Database Optimization

Send request to get interview tips in PDF format Personal Interview - Answers to frequently asked questions Tips to earn promotions and perks You can also get your answers published How to write a good CV? How to write a good Cover Letter? Tips to become boss's favorite CareerRide is hiring

Database Optimization Interview questions

---

<<Previous  Next>> 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.  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.  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. 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. 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. <<Previous  Next>> Database Optimization questions What are B-trees? Explain Table Scan and Index Scan. Describe FillFactor concept in indexes. What are Index statistics? Describe Fragmentation. Explain Nested Join, Hash Join, and Merge Join in SQL Query Plan.