Predictability is the key to scalability

DBAs are focused on getting the highest queries per second. They will use measures such as average response times. Is this always the best approach?

Consider this scenario:

A high traffic volume database server – launched with capacity to absorb high queries per second, yet users start to complain some queries are randomly taking longer. The deviation from one request to another is growing and the database server response variance is growing.  At the same time , the average response times are still acceptable, but there are some responses taking longer.

In this case DBAs need to focus on the high impact queries . Identifying the worst queries, diagnosing and fixing is a constant cycle of improvement. It’s an evidence based approach i.e identifying specific cases and applying fixes based on these cases.

Performance Tuning experts have tools enabling them to monitor at fractions of a second. Finding problems and trying to understand the cause of the delays. 

This approach is not at the exclusion of the queries per second method. In fact, benchmarking a system either before it goes live or comparing architectures requires a method such as queries per second. But once the system is live – performance can vary . Identifying the outliers is critical to maintaining good service levels. We often talk about a service level agreement for the server, but a similar principle can be applied per user request.

Sacrificing some performance but increasing the level of predictability can be a solid basis for scaling. As workload increases , greater demand is placed on system resources. Methods are applied to scale the service. Ensuring an approach is in place to deal with  extremes in query response , assists in preserving a predictable service level.

This approach does not work for every environment. Context is important , some environments can absorb deviations from the norm , typical examples , may include reporting and modelling applications. But for OLTP based applications , it’s an approach which can yield positive results

Read More

SQL Server scaling techniques

Top 10 DBA Tips to develop your competitive edge - SQL Server DBA

DBA supertech . Specialise or generalise? - SQL Server DBA

SQL Server – DBA Skills Growth Areas

Professional Development and Soft Skills Training Books for the SQL Server DBA