Slow query

Users who are responsible for running queries, like the happily swimming frog, may not notice that their queries are running slower until someone comes by while one is running and remarks on how long it takes for a result to come back. At that point, the users call you. Now you get the chance to do a little detective work. Somewhere — whether it be in the application, the database management system (DBMS), the network link, the database server, or the storage subsystem — something has maxed out. Your job is to figure out what it is and restore performance to acceptable levels as soon as possible without replacing or upgrading the parts of the system that are not part of the problem. Your job is to find the bottleneck.

Slow update

Perhaps the problem is not with queries, but with updates. For a person making additions, changes, or deletions in a database, long waits between entering a change and having the system being ready to accept the next one can be frustrating at best and intolerable at worst. The causes for delays in updating tend to be different from the causes of slow responses to queries. Although the bottleneck may be different, your job is still the same: Find the source of the problem and then fix it. In the next section, I look at some of the likely causes of bottlenecks.

Problems with indexes

Probably the number-one cause of less-than-optimal performance is improper indexing. Improper indexing may mean the lack of one or more indexes that should be present, but it could also mean the presence of indexes that should not be there.

Pitfalls in communication

One area where performance may be lost or gained is in the communication between a database server and the client computers running the database applications. If the communication channel is too narrow for the traffic, or if the channel is just not used efficiently, performance can suffer.

Determining whether hardware is robust enough and configured properly

Perhaps your queries are running slowly because your hardware isn’t up to the challenge. It could be a matter of a slow processor, bus clock, or hard disk subsystem, or it could be insufficient memory. Alternatively, your hardware may be good enough but isn’t configured correctly. Your database page buffer may not be big enough, or you may be running in a less-than-optimal mode, such as flushing the page buffer to disk more often than necessary. Perhaps the system is creating checkpoints or database dumps too frequently. All these configuration issues, if recognized and addressed, can improve performance dramatically without touching your equipment budget.

You may well decide that you need to update some aspect of your hardware environment, but before you do, make sure that the hardware that you already have is configured in such a way that you have a proper balance of performance and reliability.

Avoid direct user interaction

Among all the components of a database system, the human being sitting at the keyboard is the slowest by far — almost a thousand times slower than a hard disk and more than a billion times slower than semiconductor RAM. Nothing brings a system to its knees as fast as putting a human in the loop. Transactions that lock database resources should never require any action by a human. If your application does require such action, changing your application to eliminate it will do more for overall system performance than anything else you can do.

Examine the application/database interaction

One important performance bottleneck is the communication channel between the server and a client machine. This channel has a design capacity that imposes a speed limit on the packets of information that travel back and forth. In addition to the data that gets transmitted, a significant amount of overhead is associated with each packet. Thus, one large packet is transmitted significantly faster than numerous small packets containing the same amount of information. In practice, this means that it’s better to retrieve the entire set in one shot than to retrieve a set of rows one row at a time. Following that logic, it would be a mistake to put an SQL retrieval statement within a loop in your application program. If you do, you’ll end up sending a request and receiving a response every time through the loop. Instead, grab an entire result set at the same time, and do your processing on the client machine.

Another thing you can do to reduce back-and-forth traffic is to use SQL’s flow of control constructs to execute multiple SQL statements in a single transaction. In this case, the number-crunching takes place on the server rather than on the client. The result is, however, the same as in the preceding paragraph — fewer message packets traveling over the communication channel.

User-defined functions (UDFs) can also reduce client/server traffic. When you include a UDF in a SELECT statement’s WHERE clause, processing is localized in the server, and less data needs to be transmitted to the client. You can create a UDF with a CREATE FUNCTION statement, and then use it as an extension to the SQL language.

Don’t ask for columns that you don’t need

It may seem like a no-brainer to not retrieve columns that you don’t need. After all, doing so shuttles unneeded information across the communications channel, slowing operations. It’s really easy, however, to type the following:

SELECT * FROM CUSTOMER ;

This query retrieves the data you want, along with a lot of unwanted baggage. So work a little harder and list the columns you want — only the columns you want. If it turns out that all the columns you want are indexed, you can save a lot of time, as the DBMS makes an index-only retrieval. Adding just one unindexed column forces the query to access the data table.

Don’t use cursors unless you absolutely have to

Cursors are glacially slow in almost all implementations. If you have a slow-running query that uses cursors, try to find a way to get the same result without cursors. Whatever you come up with is likely to run significantly faster.

Precompiled queries

Compiling a query takes time — often, more than the time it takes to execute the query. Rather than suffer that extra time every time you execute a query, it’s better to suffer it once and then reap the benefit every time you execute the query after the first time. You can do this by putting the query in a stored procedure, which is precompiled by definition.

Precompilation helps most of the time, but it also has pitfalls:

• If an index is added to a column that’s important to a query, you should recompile the query so that it takes advantage of the new index.
• If a table grows from having relatively few rows to having many rows, you should recompile the query. When the query is compiled with few rows, the optimizer probably will choose a full table scan over using an index because for small tables, indexes offer no advantage. When a table grows large, however, an index greatly reduces the execution time of the query.

Isolating performance problems

As long as a wide variety of system elements could be involved in a problem, it’s hard to make progress. The first step is narrowing down the possibilities. Do this by finding which parts of the system are performing as they should, thus eliminating them as potential sources of the problem. To paraphrase Sherlock Holmes, “When you eliminate all the explanations but one as being not possible, then whatever is left, however unlikely it may seem, must be true.”

Performing a top-down analysis

A query is a multilevel operation, and whatever is slowing it could be at any one of those levels. At the highest level is the query code as implemented in SQL. If the query is written inefficiently, you probably need to look no further for the source of the problem. Rewrite the query more efficiently, and see whether that solves the problem. If it does, great! You don’t have to look any further. If you don’t find an inefficient query, however, or the rewrite doesn’t seem to help, you must dig deeper.

Partitioning

Suppose that you’ve done the top-down analysis advocated in the preceding section and have isolated your performance problem to one of the primary hardware subsystems: the hard disk drive, disk controller, processor, or network. Suppose further that you’ve done everything you can think of: defragmented your hard drives, optimized paging in your disk controller, optimized paging in your database page buffer, and analyzed the traffic on your network. Despite all these remedies, the problem persists. Partitioning offers another approach that may break the bottleneck.

Partitioning can be helpful if your performance problem is caused by exceeding the capacity of a critical system resource. Partitioning is essentially spreading out the work so that the overstretched resource doesn’t get overloaded.

You can spread the work spatially, temporally, or both. Spatial partitioning means doing more things in parallel, which could entail moving to a multicore processor, adding hard disk drives to your RAID array, installing a bigger database page buffer, and so on. You get the idea. Wherever the bottleneck is, widen the neck to increase the flow rate through it.

The other thing to try is to increase temporal partitioning. In temporal partitioning, you don’t make the neck of the bottle any wider; you just schedule workflow so that it’s more evenly distributed in time. Don’t run large update transactions at the same time that online query activity is high. Give users incentives to use the system across a broader range of times, rather than allow everybody to try to access the same resources at the same time.

Locating hotspots

Spreading the work away from an overloaded resource presupposes that you’re able to determine which of the many components of your system is causing the bottleneck. Such overloaded resources are called hotspots. When a resource is hot, it’s continually in use. If the resource is too hot, operations have to wait in line to use it. If the wait takes too long and results in aborts and rollbacks, performance is greatly affected. Fortunately, several tools are available, both at the operating-system level and at the database level, which you can use to monitor the performance of various aspects of your system and locate the hotspots. These performance monitoring tools, of course, vary from one operating system to another and from one DBMS to another. Check the documentation of your specific systems to determine what is available to you. After you locate a hotspot, you are well on your way to solving your performance problem. When you know what the overloaded system component is, you can apply the remedies discussed in this chapter to restore performance to an acceptable level. In the next section, I discuss several performance monitoring tools available on popular systems.

Using query analyzers

All the major DBMSs offer tools that give the database administrator (or other person responsible for the efficient operation of the database) a way of analyzing how well queries on the database are performing. In versions of Microsoft SQL Server before SQL Server 2005, the tool for this job was even named Query Analyzer. SQL Server 2005 represented a major break from the past in a number of ways. The functions that had been the province of Query Analyzer were incorporated into the new Microsoft SQL Server Management Studio, along with additional functionality useful for tuning query performance. This functionality is retained, along with a lot of other functionality in the version of SQL Server Management Studio included with SQL Server 2017.

Here’s a brief introduction to SQL Server 2017’s tuning tools (the Database Engine Tuning Advisor and the SQL Server Profiler) to give you an idea of what such tools look like and what they do. The operation of the tuning tools in SQL Server Management Studio differs in detail from the operation of similar tools for other DBMSs, but the overall functions are the same.

Figure 3-1 shows the main screen of Microsoft SQL Server Management Studio.

If you take a peek at the Object Explorer in the left pane of the Management Studio window (refer to Figure 3-1), you can see that I’ve connected to a database named AdventureWorks2017. This database is a sample SQL Server database provided by Microsoft. If you don’t have it already, you can download it from www.msdn.microsoft.com. It contains sample data for a fictitious company.

Suppose that you’re a manager at AdventureWorks, and you want to know what customers you have in the United States. You can find out with a simple SQL query. To draft a query in Management Studio, follow these steps:

1. Click the New Query button at the left end of the Standard toolbar.

   An SQL editor pane opens in the middle of the Management Studio window, as shown in Figure 3-2.

   To remind yourself of the names of the tables in the AdventureWorks2017 database, you can expand the Databases node and then, from the items that appear below it, expand the Tables node in the tree in the Object Explorer in the left pane of the Management Studio window.

2. Type your query in the editor pane, as shown in Figure 3-3.

   The query

   SELECT FirstName, LastName, Phone FROM Person.Contact

   WHERE LastName = 'Taylor' ;

   retrieves the names and phone numbers of everybody in the Contact table of the Person schema whose last name is Taylor.

3. Execute the query by clicking the Execute button in the toolbar.

   The result of the query shows up in the Results tab, as shown in Figure 3-4. The first several of the 83 people in the table whose last name is Taylor appear on the tab. You can see the rest by scrolling down.

   The tree at the left shows that the Phone column is a nullable VARCHAR field and that the primary key of the Contact table is ContactID. The Phone column isn’t indexed. There are indexes on rowguid, EmailAddress, the primary key PK_Customers, and AddContact. None of these indexes is of any use for this query — an early clue that performance of this query could be improved by tuning.

4. Name and save your query by choosing File ⇒ Save As.

   My example query is named SQLQuery2.sql. You can name yours whatever you want.

Now you have a sample query that’s ready to be taken through its paces. The next sections spell out how SQL Server Management Studio lets you do that.

The Database Engine Tuning Advisor

The tool that SQL Server Management Studio provides for tuning queries is the Database Engine Tuning Advisor. To use this tool with the sample query created in the preceding section, follow these steps:

1. In SQL Server Management Studio, choose Tools ⇒ Database Engine Tuning Advisor.

   The Tuning Advisor window opens to the General tab, as shown in Figure 3-5.

2. When you’re asked to connect to the server you’re using, do so.
3. (Optional) The system has assigned a default session name, based on your login and the date and time; change this session name if you want to.
4. In the Workload section, choose the File radio button and then click the Browse for a Workload File button to the right of the long text box.

5. Find and select the query file that you just created.

   For this example, I select SQLQuery2.sql.

6. Choose your database from the Database for workload analysis drop-down menu.

   For this example, I choose AdventureWorks2017.

7. In the list of databases at the bottom of the Tuning Advisor window, select the check box next to the name of your database.

8. Make sure that the Save Tuning Log check box (at the bottom of the list of databases) is selected.

   This option creates a permanent record of the tuning operation that’s about to take place. Figure 3-6 shows what the Tuning Advisor window should look like at this point.

9. Click the Tuning Options tab to see what the default tuning options are — and to change them, if so desired.

   Figure 3-7 shows the contents of the Tuning Advisor’s Tuning Options tab.

   The Limit Tuning Time check box in the top left corner is selected by default: Tuning can be so time-consuming that it severely affects normal production operation. To prevent this effect, you can set the maximum amount of time that a tuning session can take. When that maximum is reached, whatever tuning recommendations have been arrived at so far are shown as the result. If the tuning run had been allowed to run to completion, different recommendations might have been made. If your server is idle or lightly loaded, you may want to clear this check box to make sure that you get the best recommendation.

   The three tuning options you can change are

   • Physical Design Structures (PDS) to use in database: The Indexes radio button is selected, and other options are either not selected or not available. For the simple query you’re considering, indexes are the only PDS that it makes sense to use.
   • Partitioning Strategy to employ: No partitioning is selected. Partitioning means breaking up tables physically across multiple disk drives, which enables multiple read/write heads to be brought into play in a query, thereby speeding access. Depending on the query and the clustering of data in tables, partitioning may enhance performance. Partitioning isn’t applicable if the entire database is contained on a single disk drive, of course.
   • Physical Design Structures (PDS) to keep in database: Here, you can specify which PDSs to keep. The Tuning Advisor may recommend dropping other structures (such as indexes or partitioning) that aren’t contributing to performance.

10. Click the Advanced Options button to open the Advanced Tuning Options dialog box.

    The Advanced Tuning Options dialog box (see Figure 3-8) enables you to specify a maximum amount of memory use for the recommendations. In the process of coming up with a recommendation, the Tuning Advisor can consume considerable memory. If you want to set a limit on the amount it can commandeer, this dialog box is the place to do it. (The Online Index Recommendations check boxes allow you to specify where you want to see any recommendations for indexes that are generated.)

11. Click OK to return to the General tab (refer to Figure 3-6), confirm that your query file is specified in the Workload area, and then click the Start Analysis button to commence tuning.

    The Start Analysis button is in the icon bar, just below the Actions option on the main menu.

    Depending on the sizes of the tables involved in the query, this process could take a significant amount of time. The Tuning Advisor keeps you apprised on progress as the session runs.

Figure 3-9 shows the Progress tab at the end of a successful run. It shows up after a tuning run starts.

Figure 3-10 shows the Recommendations tab, which appears when a tuning run is complete.

In Figure 3-10, the Tuning Advisor has concluded that tuning would not improve performance at all. The query is already as efficient as it can be.

When you complete the tuning run, you’ll probably want to look at the Reports tab, which is shown in Figure 3-11.

In this report, you see the details of the tuning run. The report in Figure 3-11, for example, shows that 1 minute of tuning time was used and that the Tuning Advisor expects 0.00 percent improvement if you perform any tuning. The Tuning Advisor’s recommendation allocates a maximum 279MB of space. Currently, the table uses 180MB of space. If the memory allocated originally was too small, it’s a simple matter to raise the limit. Finally, note that 100 percent of the SELECT statements were in the tuned set — a confirmation that the tuning session ran to completion.

SQL Server Profiler

The Database Engine Tuning Advisor is just one tool that SQL Server Management Studio provides to help you optimize your queries. The SQL Server Profiler is another tool available under the Tools tab. Instead of operating on SQL scripts, it traces the internal operation of the database engine on a query, showing exactly what SQL statements are submitted to the server — which may differ from the statements written by the SQL programmer — and how the server accesses the database.

After you start a trace in the Profiler by choosing File ⇒ New Trace, it traces all DBMS activity until you tell it to stop. Somewhere among all the things that are going on, actions relevant to your query are recorded. Figure 3-12 shows the General tab of the Trace Properties dialog box, which appears when you select New Trace.

Figure 3-13 shows the Events Selection tab of the Trace Properties dialog box. In this example, the default selections are shown, selecting almost everything to be recorded. In many cases, this selection is overkill, so you should deselect the things that don’t interest you. Click the Run button to start the trace, and then click the Execute button in Management Studio to give the trace a query to operate upon.

A trace of SQLQuery2.sql dumps information about the EventClasses that you’ve specified for monitoring. Figure 3-14 is a view of the trace, showing the events that I selected. Data listed on the right side of the trace window include CPU usage, number of reads, number of writes, and time consumed by every event represented by a row in the trace file.

You can include many more event classes in a trace beyond the few that I display in this section. Check the Show All Events box in the Events Selection pane of the Trace Properties dialog box to display a host of them. You can break down a query to its constituent parts and see which parts are consuming the most time and resources. For the trace shown in Figure 3-14, I chose to monitor the acquisition and release of locks (and there were a lot of them). In cases in which you have little or no chance of deadlocks or lock contention, the trace will run faster if you choose not to monitor lock behavior.

Queries aren’t the only things that consume system resources. If you’re experiencing performance problems, the source of the problem may be somewhere other than in a poorly performing query. Performance monitors are tools that give you a broader view of how a system is performing. Just remember that performance monitors aren’t specifically related to database systems but are maintained by the operating system.

Finding problem queries

In a poorly performing multiuser, multitasking environment in which multiple queries are being run at any given moment, tracking the source of the problem may be difficult. Is the problem systemic? Are weaknesses in the server’s processor, the server’s memory, or the network slowing everything? Is one problem query gumming up the works for everyone? This last question is particularly important. If you can restore performance to a satisfactory level by tuning a query, doing so is a lot cheaper than making a major hardware upgrade that may not solve the problem.

A useful approach is the divide-and-conquer strategy. Find all the jobs that typically run together when performance is slow, and run them individually. Use your system’s performance-monitoring tools to check for jobs that saturate one or more system resources. Then use your event-monitoring tool to find a query that seems to be consuming more time and resources than it should.

When you find a suspicious query, use your query-analyzer tool to look inside the query to see exactly where time and resources are being consumed. When you find a bad actor, you can try several things to make matters better. In the next sections, I discuss a few of those techniques.

The disk subsystem

The way that data is distributed across the disks in a disk subsystem affects performance. The ideal case is to store contiguous pages on a single cylinder of a disk to support sequential prefetching — grabbing pages from disk before you need them. Spreading subsequent pages to similarly configured disks enables related reads and writes to be made in parallel. A major cause of performance degradation is disk fragmentation caused by deletions opening free space in the middle of data files that are then filled with unrelated file segments. This situation causes excessive head seeks, which slow performance dramatically. Disk fragmentation can accumulate rapidly in transaction-oriented environments and can become an issue over time even in relatively static systems.

Tools for measuring fragmentation are available at both the operating-system and database levels. Operating-system defragmentation tools work on the entire disk, whereas database tools measure the fragmentation of individual tables. An example of an operating-system defragmentation tool is the tool for Microsoft Windows, Optimize Drives utility, which you can access from the Search field. Figure 3-15 shows the result of an analysis of a slightly fragmented C: drive, an unfragmented backup drive, and a 28% fragmented System drive. Optimizing the System drive will improve performance.

In addition to being badly fragmented, this drive has very little free space, and none of that space is in large blocks, which makes it almost impossible to store a new database in a fragment-free manner.

With Optimize Drives, you can not only analyze the fragmentation of a disk drive, but also defragment it. Alas, this defragmentation usually is incomplete. Files that Optimize Drives can’t relocate continue to impair performance.

DBMSs also have fragmentation analysis tools, but they concentrate on the tables in a database rather than look at the entire hard disk. SQL Server, for example, offers the sys.dm_db_index_physical_stats command, which returns size and fragmentation data for the data and indexes of a specified table or view. It doesn’t fix the fragmentation; it only tells you about it. If you decide that fragmentation is excessive and is impairing performance, you must use other tools — such as the operating-system defragmentation utility — to remedy the situation.

The database buffer manager

The job of the buffer manager is to minimize the number of disk accesses made by a query. It does this by keeping hot pages — pages which have been used recently and are likely to be used again soon — in the database buffer while maintaining a good supply of free pages in the buffer. The free pages provide a place for pages that come up from disk without the need to write a dirty page back to disk before the new page can be brought in.

You can see how good a job the buffer manager is doing by looking at the cache–hit ratio — the number of times a requested page is found in the buffer divided by the total number of page requests. A well-tuned system should have a cache–hit ratio of more than 90 percent.

In the specific case of Microsoft SQL Server running under Microsoft Windows, you can monitor the cache–hit ratio along with many other performance parameters from the Windows System Monitor. You can access the System Monitor from the Windows command prompt by typing perfmon. This launches the System Monitor in a window titled Reliability and Performance Monitor. From the menu tree on the left edge of the window, under Monitoring Tools, select Performance Monitor. This displays the Performance Monitor Properties dialog box. Open the Data tab and then click the Add button. This enables you to add counters that monitor many performance-related quantities, including cache–hit ratio.

Activating the System Monitor consumes resources, which affects performance. Use it when you are tracking down a bottleneck, but turn it off when you are finished before returning to normal operation.

Another useful metric is the number of free pages. If you check the cache–hit ratio and the number of free pages frequently under a variety of load conditions, you can keep an eye out for a trend that could lead to poor performance. Addressing such problems sooner rather than later is wise. With the knowledge you gain from regular monitoring of system health, you can act in a timely manner and maintain a satisfactory level of service.

The logging subsystem

Every transaction that makes an insertion, alteration, or deletion in the database is recorded in the log before the change is actually made in the database. This recovery feature permits reconstruction of what occurred before a transaction abort or system failure. Because the log must be written to before every action that’s taken on the database, it’s a potential source of slowdown if log writes can’t keep up with the transaction traffic. Use the performance-monitoring tools available to you to confirm that there are no holdups due to delays in making log entries.

The locking subsystem

The locking subsystem can affect performance if multiple transactions are competing to acquire locks on the same object. If a transaction holds a lock too long, other transactions may time out, necessitating an expensive rollback.

You can track down the source of locking problems by checking statistics that are normally kept by the DBMS. Some helpful statistics are

• Average lock wait time (the average amount of time a transaction must wait to obtain a lock)
• Number of transactions waiting for locks
• Number of timeouts
• Number of deadlocks

Time spent waiting for locks should be low compared with total transaction time. The number of transactions waiting for locks should be low compared with the number of active transactions.

If the metrics cited here point to a problem with locks, you may be able to trace the source of the problem with a Microsoft Windows System Monitor or the equivalent event monitor in a different operating environment. Things such as timeouts and deadlocks appear in the event log and indicate what was happening when the event occurred.