What does Tuning mean?

Database tuning is the process of optimizing the performance of a database. In the enterprise, this usually means the maintenance of a large database management system (DBMS ) and Avoiding transactions that create bottlenecks or avoiding queries that run for minutes to hours unnecessarily. This includes optimizing the performance of the database as well as the underlying hardware.

How to Tune Database Performance?

• While using the SELECT statement use column names so that it will fetch whatever information is required and avoid using * in your SELECT queries because it would put a load on the system unnecessarily.
• Use column names instead of the DISTINCT keyword to get unique results. 
• Avoid typecasting as it will lead to performance downgrade.
• Create joins with INNER JOIN instead of using  WHERE condition.
• Use WHERE instead of HAVING to define filters
• Do Your Constraints in Application Code Rather Than the Database
• Create indexes carefully on all the tables where you have frequent search operations. Avoid indexing the tables where you have less number of search operations and more number of insert and update operations.
• Use wildcard search wisely. If you want records starting with string neo you should use “neo%” instead of “%neo%” it will effectively prevent the entire filter from being used.
• To make a database fast and reliable you can use the RAID method in the creation of the database. RAID stands for Redundant Array of Independent Disks. If data are stored on one disk, the entire database is completely reliant on that one disk; if it were to fail, the database would not exist anymore. Another drawback to having it on a single disk is the read/write time. One hard disk can only be so fast. If a lot of I/O data is processed, it can be a time-consuming process. What RAID does is it partitions and replicates the data onto several autonomous disks. It makes reading and writing faster because instead of one disk trying to find all the data, the task is separated into parts, and each hard disk does part of the job.
• Avoid the use of logical operators such as OR in a query if not required as it slows down the query.
• Defragmenting the database can help to increase the performance of the databases.
• For queries that are executed on a regular basis, try to use procedures. A procedure is a potentially large group of SQL statements. Procedures are compiled by the database engine and then executed. Unlike an SQL statement, the database engine need not optimize the procedure before it is executed.
• When performing batch transactions, perform COMMIT after a reasonable number of records creation as opposed to making them after each record creation.
• Use response time analysis: With response time analysis, we can optimize database tuning by identifying root causes and prioritizing actions based on the impact of poor database.

Tuning Tools:

Oracle: tkprof

MySQL : Solarwinds DB Performance Analyzer, QL Power Tools, Idera SQL, Diagnostic Manager.

SQL Server: SQL Server Management Studio, SQL Server.

Conclusion:

While DBAs must consider various territories for performance tuning, execution tuning begins from the database and application configuration from the design stage. Databases and applications that are structured in light of execution tuning point of view are considerably more scalable than applications planned without performance tuning thought.