Just hearing the words, “We need to change the code” out-loud can make any DBA feel a massive migraine coming on.
The database is not performing optimally, but DBAs know that changing the code is not the way to go. The client ends up frustrated at the push back. The DBA is frustrated that changing code is focused on as the solution. Lines get drawn. Wagons are circled. Tempers rise. You are at an impasse. But at the end of the day, the database performance has to improve.
Databases are often designed and implemented with inefficiencies in the coding; it happens. Issues due to the inefficient code also tend to come up later rather than sooner as usage increases exponentially. While these inefficiencies can turn into problems for the apps running on the databases, and therefore on the profitability of the entire business, redoing code can be even more damaging and time-consuming. So, what can you do to improve the performance of databases without actually redoing code?
Experienced DBAs will have different techniques they’ve picked up along the way that improve database performance. Small changes or systemizing some database management behaviors can create upticks in database performance, and even the slightest improvements in productivity can have meaningful impacts on the business as a whole. Companies rely entirely on apps to run their business, so enhancing the performance of the databases that host these apps will keep clients happy and productivity high.
Here are five ways that a DBA can improve the performance of a database without changing any code, and can keep your operations smooth and efficient:
#1 Examine your plan cache
Besides examining the cache’s contents to investigate performance problems, take a proactive approach, instead of reacting to problems, by regularly looking for information that may act as signs of performance inefficiencies.
For example, the plan cache can tell you about the plans that were used only once, that have adjustment opportunities, and that are similar to other plans—all of which are ultimately superfluous. Increased visibility into these plans and acting proactively with them can help keep your system operating like a spinning top. Examine your plan cache, gain insight into plan usage, and proactively manage your plan cache to improve database performance.
#2 Utilize a new indexing strategy
Indexes aren’t code. They are schema, so unless you have code that utilizes index hints you shouldn’t worry about changing code to avoid using an index. Adding indexes is not always the correct solution to improve performance. Nor is eliminating them the right move, if you are not completely sure the index is not being used.
An investigation of unused indexes should span at least a month, if not a quarter, to make sure the indexes are unused and not just timed differently. A proper indexing strategy should involve the periodic investigation into finding indexes that are missing, those that are duplicates, and those that are unused. Create an indexing strategy that optimizes database performance by using the necessary indexes and removing any redundancies.
#3 Review your archiving strategy
Most DBAs have an understanding of their file server storage issues: too much space is used by too many old files, too many duplicates, or too many users that keep too much data.
Simple. And the solution can be simple too.
Start small and resist the temptation to over-analyze. Begin by setting up WatchPoints on folder paths by department or project. Collect some data and review the reports with stakeholders with suggested changes. Creating simple policies to delete or archive nonessential or unused files is easy to get behind on. You can also suggest to archive and shortcut files older than an approved date to implement a system that maintains proper storage practices at all times. Simple and easy to follow policies will set up an environment that assures long-term performance stability, so get started on yours.
#4 Enable page or row compression
Compression has the advantage of reducing storage requirements but at the cost of increasing processor consumption. It’s a trade off. By using both row and page compression, you will reduce the storage required for tables while also increasing the density of data in the cache.
At the same time, this also depends on the type of data you are working with. Row compression takes all numeric types and renders the physical storage a variable length. Storing the value of 1 takes less space than storing the value 34543986.
For a large numerical table, this is a huge win. For purely text tables, there is no plus side. If this is your situation, explore page compression and assess any tweaks that can create improvements in your database performance.
#5 Reconfigure VM allocations
Storage can get used up quickly, and a seasoned DBA will tell you that resources are extremely limited. Even machines with plentiful resources can have their supplies depleted by VMs running services that use far less of the resources given to them. Planning ahead lessens challenges with resource allocation.
Nothing is ever set in stone when you work with VM. Changing the size of a VM is very common. If you are always monitoring your VMs, you know which resources VMs are using at any given moment. The increased visibility of watching workloads over time and identifying when demand peaks allows a DBA to appropriately and optimally distribute resources when needed. It’s not wise to assume that an application will run as it always has. Changing service packs, the addition of more users, and inevitable changes in the overall environment will cause applications to require more storage, and perhaps additional processors.
Database stability and maintaining optimal performance levels is crucial for businesses these days. Databases host all sorts of applications that directly impact the operations of companies, and, therefore, profitability. It’s more important than ever to maintain optimal performance over your databases. At the same time, redoing code that is causing inefficiencies is risky and time-consuming. This won’t be a problem for an experienced DBA, though. They’ll know that exploring different policies, procedures, and best practices in your database management will allow your company to optimize the performance of databases without having to go in and change any code.