SQL Performance Tuning: Scaling SQL Server

Scaling and optimizing SQL Server are complex exercises when you think about all the different variables that come into the equation because there is not a single checklist that you can apply to every SQL Server or Oracle database. But most companies have to go through the performance tuning exercise with their critical and highly transactional applications at least every year to keep pace with the business and transactional growth. There are also cases in which your system needs to scale 3X or 10X in transactional volumes in order to support growing business needs. But where do you start on Day 1 to scale your mission-critical SQL Server or Oracle instance that the business runs on? What is important for a DBA or a database developer to focus on Day 1 versus Day 30? How do you define and measure success?

Defining Success in SQL Performance Tuning

One important aspect of SQL performance tuning or scaling a system is defining what success looks like because there is a trade-off between the time spent on tuning and how fast you want the system to perform. Does it make a difference if an important query is 5ms or 25ms? Probably not, but  success needs to be defined by both the business and technology teams so there is alignment and a common understanding of the goals and objectives required to reduce the amount of resources used to process a query.

When Fortified starts a scalability project, defining success from the business and technology teams is the first step in the process but what does that mean? From the business perspective, we work with the business to identify the business goals and objectives around what they are trying to accomplish. Is that selling 5X more products or is that acquiring another 5 million new users to the platform? What is the timeline until the workload starts growing versus the business hitting the final milestone?

Once we define that information, then we can start to talk with the technology teams about what those business objectives map to on the technology side. From a database perspective, we need to define the service level agreements (SLAs), performance level agreements (PLAs), and how we will measure the capacity and performance of the system. If you remember the old saying, “If you cannot measure it, you cannot improve it.” And that is true for complex systems because one thing that we have learned in scaling systems is that there is a small margin of error but if you have the right instrumentation then you can determine early if there is an issue in the system that needs to be addressed.

Baselining SQL Server in SQL Performance Tuning

Before we can start optimizing code and process, we need to define where the system is today from a health, configuration, and performance perspective so we know what the capacity and performance are today. Only then, can we start to plan for what it will take to scale the system to meet the growth goals.

The SQL Server baseline needs to focus on capturing data from the following areas:

• Configuration – Hardware, Software, Application
• Metadata – Information about the systems like Tier of Service
• Runtime data – Execution information from the processes on the system
• Performance data – Perfmon data for the different aspects of the system
• The baselining process must be designed to be a repeatable process as the system will need to be re-baselined after each major change to the system. This will allow you to measure progress for the scalability project and determine what is the next priority to focus on. Another important design element is to design a dashboard with KPIs and other health scores that reflect the core aspects of the application and define target values for those KPIs that map to the success criteria. By defining high-level, easy-to-understand KPIs then it is easy to see when you have met your scalability goals.

Mapping the Priorities in SQL Performance Tuning

Once you have established a baseline and outlined your target values, you should begin mapping out the priorities for optimization. As you begin your SQL Server performance tuning, identify which queries are experiencing the high rates of slowdown, which is inhibiting maximum database efficiency. Poorly written queries often lead to a variety of problems, including both the query and thew overall database slowing down because of increased runtime and increased blocking and locking, and as the database grows, those problems become exponential and more costly to fix which would warrant tuning for an increase in database users and transaction requests. With that in mind, query tuning, for SQL Servers, should be a top priority.

Every query takes up database resources, so when optimizing your SQL Servers, you want to take into consideration query run time. A long query run time leads to more resources being used in the database to process the request. If you optimize your database to decrease query run time, you’ll be able to maintain the stability and reliability of your database for better database performance. The run time of a query is affected by table size, joins, aggregations, and whether there are multiple users running queries at the same time. For SQL performance tuning, improving query run time by reducing table size is one of the top priority tasks – as it takes less time, is simpler, and it will help to identify what other areas should be looked at to reduce the run time further. At times, it could be challenging to implement these changes very quickly without deep planning and communication with the user application development teams. One of the easiest things to look at in a query tuning is to see whether the size of the datasets being processed in the query can be reduced. If row counts, columns counts, and sized being processed in the query can be reduced then proceed with that plan initially so that you can have a more “light weight” query that can be reran multiple times regardless of how big the database is.

Identifying, optimizing, and tuning your SQL servers can be time-consuming, which then becomes costly because internal DBAs are spending hours to rectify the server performance problems. Fortified Data’s expert DBAs have aided many enterprises in optimizing their code and processes.

Book time with a Fortified team member today to discuss your needs for SQL Server Performance Tuning and be on your way to optimizing your databases today.