Approaching Performance Tuning Methodically

Performance tuning is usually focused on the database because many problems will manifest there, although some of the problems are not caused there. We will begin by analyzing the presentation of these problems in the database, and direct our focus outward as needed.

DATABASED FOCUSED ISSUES

QUERY/PROCEDURE/VIEW

1. Begin by looking at an execution plan. Are there are obvious bottlenecks like tables that are getting a significantly greater amount of reads that the other tables in the plan? Are the plan operators using indexes, and are they using indexes efficiently (SCAN vs SEEK, estimated rows read vs actual rows read). Can indexes be added or modified to benefit the plan? Sometimes the performance issue can be resolved purely by indexes alone. If not, proceed to step 2.
2. Are there anti-patterns in the code that indexes don’t solve? Problems to look for include OOP (Object Oriented Programming) techniques that application developers often implement in database code when they should not. This usually presents as an obsession with code reuse. Many developers would rather call an existing object such as a view instead of running a precise query directly, but the existing object has a great deal of overhead that is not necessary for the task at hand. With databases, simplicity is key. If you are not going to use every field in the view, do not use that view. Write another view for your specific task, or run a query directly. I personally prefer that application developers not rely on views at all, and use parameterized based approaches instead.
3. If you are using a parameterized based approach such as a stored procedure or parameterized query, you should look for signs of parameter-sniffing. Usually this presents as “sometimes it is fast, other times it’s incredibly slow“. Without getting too deep into the details in this blog post, parameter-sniffing affects how the SQL engine comes up with the plan after looking at the statistics about the data in the plan. The parameter selected for this process may not be ideal for the particular execution. For example, if you have an online store and your biggest customer has 10 million line items of transactions in your database, but your other customers only have around 100 transactions each, the execution plan to optimize for your biggest customer’s query would be very different than the other customers. You may be able to recognize this is occurring inside the plan just from looking at the row counts if you know the data well enough. What to do in this situation is nuanced and depends on your situation.

DATABASE IN GENERAL

1. Does the database server have enough CPU/memory devoted to it? Does it share resources with other processes? From what we’ve seen in big SAAS companies, if your CPU is routinely throttled above 80% the answer is NO.
2. Are there missing index requests that suggest the database itself is requesting assistance? We advise people not to implement every request that comes along, but to look at them holistically and over time strategically implement indexes that would eliminate the missing index request from being made. There are horror stories out there about some rambunctious automation focused developer writing code that automatically creates every request that the database is making, not taking into account that indexes are meant to be used by multiple queries. Implementing an approach like this can quickly use all the available space you have.
3. Analyze the WAIT STATS about the database. Many of them may indicate a problem that can be solved with focused performance tuning approaches in certain areas.
4. Keep in mind that some problems are difficult or impossible to analyze after the fact. Sometimes tooling can help capture evidence of these problems to be analyzed at a later time. This tooling can include paid software solutions that exist purely for these purposes, many of them we have worked with in the past. However, there are built in features in SQL Server that can be turned on that can also add value, but usually require more technical knowledge to make full use of.