Do you have some database performance tuning suggestions?

The following are some database performance tuning strategies:

Check that all columns where searches or sorts take place are indexed unless tables are very small or have high levels of updates. One place where indexes are overlooked (and very necessary) is on the parent column of a foreign key relationship.

Consider adding to an index all the columns needed by frequent queries so those queries don't have to look up both index and table data.

Tables' physical order on disk should reflect the order needed by queries that retrieve the largest number of rows (this could result in a table that's not in primary key order).

Column order matters when creating indexes. The column that does the most to narrow down the size of the final result set should be first in the index.

Be sure that table data distribution statistics are calculated and kept up-to-date. Missing or incorrect statistics can result in disastrously slow queries.

Once the database is in final shape, inspect query execution plans and use provided query and index analysis tools to be sure nothing major was missed.

For better performance, rewrite SQL queries to remove subselects.

Continue to monitor performance issues (such as tracking the most resource-intensive queries) to see how tuning might change to support current usage.

This article has been provided by Codero Hosting, the leading provider of reliable dedicated, managed and cloud hosting services. Need more information on this topic or to learn more about Codero’s hosting services please visit www.codero.comchat with us online or give us a call at 866-2-CODERO.  

Posted in
Last update:
2015-12-03 20:39
Average rating:0 (0 Votes)