Interview Questions.

Question 1. Reasons Of Poor Performance Of Query?

Answer :

No indexes
Excess recompilations of stored methods.
Procedures and triggers with out SET NOCOUNT ON.
Poorly written question with unnecessarily complex joins
Highly normalized database design.
Excess utilization of cursors and brief tables.
Question 2. Tell Me In Brief The Cursor Optimization Tips?

Answer :

The following are few guidelines for cursor optimization:

When the cursor is not wanted, close the cursor
Deallocate the cursor after final it.
Fetch less quantity of rows.
Avoid triggers – due to the fact cause executes every time records gets updated, leads to overload of the device.
When the rows aren't want to update, use the choice FORWARD ONLY
Use where as a substitute of having clause unless it's far critical
RDBMS Interview Questions
Question three. How Is Index Tuning Used To Improve Query Performance?

Answer :

The Index tuning wizard may be used to improve the overall performance of queries and databases. It makes use of the following measures to do so:

It makes use of the question optimizer to carry out the evaluation of queries with respect to the workload and based totally on this expertise, it recommends the best usage of indexes.
The adjustments within the utilization of index, question distribution and their overall performance are analysed for checking the impact.
It also recommends methods of tuning the database for a small set of hassle 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 earlier is a difficult assignment. Index tuning entails the queries based on indexes and the indexes are created mechanically on-the-fly. No specific movements are wished by means of the database users for index tuning.

Software Development Lifecycle (SDLC) Tutorial
Question 5. What Are The Ways To Code Efficient Transactions?

Answer :

We should not allow input from users in the course of a transaction.
We should not open transactions even as browsing via information.
We must preserve the transaction as quick as viable.
We should try and use lower transaction isolation tiers.
We should access the least amount of facts feasible even as in a transaction.
Database Interview Questions
Question 6. Do You Know What Are The Ways To Code Efficient Transactions?

Answer :

We should not permit enter from customers during a transaction.
We shouldn't open transactions even as browsing thru records.
We ought to maintain the transaction as quick as viable.
We should try and use lower transaction isolation degrees.
We must get right of entry to the least amount of records possible even as in a transaction.
Question 7. Explain Some Disadvantages Of The Indexes?

Answer :

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

Indexes devour disk area.

Database Design Interview Questions
Question eight. Explain Execution Plan?

Answer :

SQL Server caches the plan of execution of query or stored manner which it makes use of in next name.
This is a completely crucial characteristic on the subject of performance enhancement.
You can view execution plan of information retrieval graphically or textually.
Question 9. Explain In Brief The Cursor Optimization Tips?

Answer :

Close cursor when it isn't always required.
You shouldn’t overlook to deallocate cursor after last it.
You should fetch least number of statistics.
You should use FORWARD ONLY choice whilst there's no want to replace rows.
Database Administration Interview Questions
Question 10. Tell Me What Are The Types Of Indexes?

Answer :

Indexes may be clustered and non-clustered indexes.
Clustered index maintains all the information within the database inside the order of clustered index key.
There can be handiest one clustered index in step with desk.
Non-clustered indexes are stored as B-tree structure in their very own storage break free facts storage inside the desk.
Question eleven. What Are Indexes?

Answer :

Index can be notion as index of the e-book that is used for fast retrieval of facts.
Index uses one or greater column index keys and pointers to the record to discover record.
Index is used to speed up question performance.
Both exist as B-tree structure.
Kind of the indexes are clustered and non-clustered.
Oracle Performance Tuning Interview Questions