Question

Imagine that you’ve just pulled off the lot of your local dealership with the car of your dreams. The car may be the right color and have all of the features that you could ever want. However, when you press your foot to the accelerator, the car drags and takes forever to reach your cruising speed of 70 mph. Your dream car can have all the bells and whistles but if it doesn’t perform, you may regret your purchase. Performance counts! This also applies to the world of database design. Database performance is important. If database performance isn’t optimal, then some tuning may be required. In this week’s assignment, you will write a one-page paper that defines what performance tuning is. Below are questions that you must answer in your paper. Hopefully, these questions will stimulate your thought processes and generate ideas in preparation for writing your paper. What is performance tuning? What are the key factors that a database professional must take into consideration to tune a database.What are some of the pitfalls or things to avoid when doing performance tuning? Where do you start when you tune a database? What do you review? What tools and/or resources are involved? What specific actions must be performed in tuning a database to ensure optimal performance and why? What are some of the pitfalls or things to avoid when doing performance tuning Etc.

Answer 1

Q1. What is performance tuning?
Ans: database performance tuning means to maximize the resource utilization.
so that result of SQL query execution can be obtain very fast with database consistency,integrity, and so on.
it is a broad term and include the optimization of both hardware and software, and importantly
1)Main memory
2)Secondary memory

1) optimization of Main memory/Secondary means to minimize the effect of memory fragmentation.
2)proper use of RAID(Redundant Array of inexpensive disk).

Q2.What are the key factors that a database professional must take into consideration to tune a database?
Ans:-
Key Factors
1)Creation of table should follow the normalization.
All the tables should be normalized as far as possible.
2)Use of Index:- index should be used(if there is a plenty of search operations),it makes the record retrieval fast.
3)Number to character conversion should be avoided as far as possible,because it degrades the performance.
4)Instead of fetching all the fields of record,fetch only required fields.
try to avoid the use of retrieval symbol * in SELECT queries(if possible).
5)Try to use procedures

Q3.What are some of the pitfalls or things to avoid when doing performance tuning?
Ans:
1)Do not install more other software on the machine which has database.
because other unnecessary software will take cpu cycles.
2)Avoid correlated sub queries.
3)Do not use count,use EXISTS() to check the existence of record.

Q4.Where do you start when you tune a database?
one should start with the normalization of tables.

Q5. What do you review?
1)if there is a need of a view,view is being used or not?
2)Tables are normalized or not?
3)in case of complex join queries, temporary tables should be used.
4)what is the frequency of execution of disk cleanup?

Q6,7. What specific actions must be performed in tuning a database to ensure optimal performance and why?
What tools and/or resources are involved?
Ans
1)installation of anti virus.
proper anti virus should be installed, because some malware slow down the speed .
2)time to time disk clean up should be executed to remove the temp files.