Transcript

Optimize the performance, cost, and value of databases

Today’s businesses run on data, making it essential for them to access data quickly and easily. This requirement means that database must run efficiently at all times but keeping a database performing at its best remains a challenging task. Fortunately, database administrators (DBAs) can adopt many practices to achieve this goal, thus saving time and money.

Key factors to optimization

• Organizations need to access data fast

• Keep databases running smoothly at all times

• Keeping databases healthy remains challenging

• Database administrators (DBAs) ensure database performance

Database audits

• Databases lose performance without proper maintenance

• Database designs no longer meet current needs

• Audit databases regularly with schema evaluations

• Review logs for functional activities (table views, updates, deletions)

• Review database accounts so that users have minimum rights

Monitor virtual machines

• Transitioning from on premises to cloud-based is challenging

• Databases are resource-intensive applications

• Databases can perform poorly on virtual versus machines

• Use monitoring tools to monitor virtual machines, databases, and queries

Delete unnecessary data

Deleting data that’s not longer needed improves the performance of queries because they no longer need to return irrelevant results. However, simply deleting rows isn’t always the best way to accomplish this task, especially if you want to delete large portions of a table. In these cases, it may be better to move the rows you want to keep into a new table. You can then drop the old table and rename the new one with the old name.

Automate administrative tasks

• DBAs already have tools to automate many of their tasks, but DBAs need to always maximize automation

• Ensure that automation saves time in long run

• Only automate tasks that do not involve lots of manual data entry

Optimize SQL queries

• Poorly structured queries cause sluggish database performance

• Narrow result set to minimum needed to reduce execution times

• Change queries to reduce deadlocking to improving performance

• Deadlocking prevents queries from obtaining needed resources

Schedule long SQL queries

• Query execution times can be prohibitive even after optimization

• Need to schedule long-running queries that are not time sensitive

• Schedule queries to run during low demand on database resources

• Run queries outside of business hours

Manage database growth

DBAs must routinely manage the growth of their databases to prevent applications from failing simply because a database ran out of space. Setting a database’s maximum size to a particular value is an easy way to prevent this occurrence, but it also means that users will get an error when they try to add more rows to a table. DBAs should carefully review a database before setting a maximum size limit to ensure that this action is the appropriate solution. It’s usually better to allow a database to grow automatically provided storage capacity isn’t an issue.