How to know when and whether SQL query optimization is required

SQL database performance

It’s simple to get into the nuts and bolts of SQL query optimization and SQL server performance. You launch SQL Server Management Studio (SSMS), track wait times, look through the execution plan, gather object data, and start optimizing SQL until you have a well-tuned system.

You gain a swift triumph and return to your regularly scheduled stir if you’re good enough at it. But if you make the incorrect adjustment, or the proper adjustment in the wrong way, your Wednesday is ruined.

​

SQL query optimization? What makes you think you need it?

The most common cause is an increase in trouble tickets or user complaints. Your users’ gripe, “Why is the system so slow?” “This week, it’s taking us an eternity to do our regular reports.”

Of course, that’s a reasonably hazy definition. It would be wonderful if they could notify you, “Things are slow because line 62 of CurrentOrderQuery5.sql contains an implicit conversion.” You’re handing in an integer to a varchar column.” However, it’s unlikely that your users will be able to see that level of detail.

At the very least, trouble tickets and phone calls provide an operational metric that is easy to notice and measure. When they begin to arrive, you may be relatively sure that SQL optimization is required.

Other, more passive measurements, on the other hand, obscure the necessity. A variety of circumstances could cause things like revenue decline. Is it because your online store’s dreadfully slow queries lead shoppers to abandon their shopping carts? Is it because of the state of the economy?

It could even be anything as simple as slow SQL Server performance. Is it because a poorly constructed query causes logical reads to skyrocket? Is it because the server’s physical resources, such as memory and storage, are running low?

SQL query improvement can assist with the first option in both cases but not with the second.

Why apply the right solution to the wrong problem?

Make sure that tuning is the appropriate solution for the correct problem before you go down the path of optimization.

SQL tuning is a technical process, yet each technical step is based on sound business principles. You could spend days attempting to lower execution time by a few milliseconds or the number of logical reads by 5%, but is the time investment worthwhile? Meeting user needs is critical, yet every effort eventually reaches a point of diminishing returns.

Consider the following SQL query performance issues and the business context:

• Acceptable performance – A query takes 10 minutes to process, while the user wants it to run in one minute; this appears to be a tolerable discrepancy and a reasonable objective for optimization. However, if the query takes an entire day to perform and the user expects it to take one minute, it may be more than a tuning issue. For one thing, you may need to inform the user about how much work the query does. Another possibility is an issue with the database design or the client application code. 
• Usefulness — Assume you’re in charge of maintaining a manufacturing company’s financial database. Users complain about the lousy performance after each month. You track the issue down to a series of month-end reports generated by Accounting that take hours to complete and then go straight into a file cabinet without being checked. Rather than adjusting, you inform the business managers of the problem and ask for authorization to delete the reports. 
• Changing the time frame – Those identical reports are critical for governance but not for the business. By pre-caching the data set and delivering the findings to a file, they can be scheduled for off-peak hours if run once a week or once a month. This relieves the other business users of the bottleneck and frees the Accounting user from waiting for reports. 

You may set priorities and buy yourself time when considering the business context when optimizing.

When you do optimize SQL queries, try SQL diagramming

Most of what you need for good SQL query optimization is available in SSMS and the tools integrated into SQL Server. As described in the e-book “The core guide to SQL query optimization,” combine the devices with a systematic approach centered on the following steps: 

1. Keep an eye on the waiting time. 
2. Examine the Plan of Execution 
3. Collect data on the object 
4. Look for the Driving Table. 
5. To identify performance inhibitors, you must first identify them. 

In step 4, your goal is to use the table that returns the minor data to run the query. You can limit the number of logical reads by studying joins and predicates and filtering sooner in the question rather than later. That’s a significant advancement in SQL query optimization.

Server performance tuning

SQL diagramming is a graphical method of mapping the quantity of data in tables and determining which filter returns the fewest entries. You must first decide which tables contain extensive data and master or lookup tables.

Registration is the detail table. It includes two lookup tables, one for each student and one for each class. To diagram these tables, make an upside-down tree with arrows (or connections) connecting the detail table (at the top) to the lookup tables, as shown below:

Calculate the total number of records needed to meet the join criterion. At either end of the arrow, write the numbers. There are around five records in the registration table for each student and approximately 30 documents in registration for each class to get a result for a single student or style; JOINing more than 150 (530) entries should never be necessary.

If your join columns aren’t indexed, or if you’re not sure if they are, this exercise can help.

Next, examine the filtering predicates to determine which table should be used to drive the query. There were two criteria in this query: one for registration canceled = ‘N’ and another for signup date between two dates. Run the following query on registration to see how selective the filter is:

where cancelled = ‘N’, select count(1) from registration

AND r.signup date r.signup date r.signup date BETWEEN:beg date AND:end date AND:start date +1;

It generates 4,344 papers out of 79,800 total entries in registration. With that filter, it will read 5.43 percent of the records.

The second filter is based on class:

where name =’ENGLISH 101′ select count(1) from class

It accepts entries from 0.2 percent of 1,000, suggesting that the filter is far more granular. As a result, the most crucial table to tweak is class, and it’s the one you should begin with.

The voice of the user

If you’re sure, you need SQL optimization, “The fundamental guide to SQL query optimization” can help. It leads you through five performance tweaking tips, including those stated above, using copy-and-paste queries and case studies.

The user’s voice is likely to be the most critical SQL query optimization technique. Why? Because that voice informs you when to start optimizing and when you’ve done enough optimizing. It can help you start fiddling with the gears when you need to and quit while you’re still moving forward.

About Enteros

Enteros offers a patented database performance management SaaS platform. It proactively identifies root causes of complex business-impacting database scalability and performance issues across a growing number of RDBMS, NoSQL, and machine learning database platforms.