Running queries with the optimal plan
AimBetter Diagnostics in Action
May 2018
Introduction
One of our customers recently reported that one important function that needed to run efficiently was displaying sharp differences in execution time, varying without any visible reasons between a few seconds up to several minutes. All other processes running at the same time were performing normally, so an investigation of the actual query that was performed in this function was called for.
Background information
SQL Server load is created when queries are reading data from the database, and resultant insert/update/delete actions happen. Query Optimizer is a basic SQL Server component that creates optimal query execution plans based on the database objects used, indexes available, joins, number of columns and other factors that were relevant at the instant when the query began to execute. For this reason, plans may vary every time the query executes. An estimated plan is built before the query runs. The actual plan is what actually runs, and can be different if server conditions have changed.
For this reason, identifying which plan was being executed by a specific query that is running at a specific time is an essential requirement in database administration. AimBetter’s monitor gives this advanced functionality to users and our Expert Support team alike.
Once the plan is identified, the strongest method available for performance monitoring and tuning is to make use of detailed analysis of the plan which SQL Server executed when performing a query.
Summary of this event
- Many users in our customer complained of extended wait times in one specific action
- Execution times varied for no visible reason from a few seconds to several minutes
- Visible from the Queries console is the identification of the probable cause – the plan that is being executed now includes a SORT call
- Using AimBetter query monitor, the actual query was identified and the plan it was executing was examined
- Analysis of the plan showed that it was executing a SORT function even though the query was supposed to utilize existing indexes
- AimBetter Expert Support personnel identified the reason for this behavior and suggested a change to the index structure
- This new index reduced execution times by at least 80% compared to previous performance
- A further suggested index change was implemented that solidified this gain
- Most importantly, this substantial improvement was achieved without the expense of query modification or schema change
- The whole process, from start to completion, took less than one hour!!!
Important to note is that all of this happens in real-time on a single console, with AimBetter support and customer’s administration able to see and respond to exactly the same console information, without need for over-the-shoulder or VPN access into the customer’s site.
The symptoms
There is a problem that one process is unstable in normal mode and runs for extended times and in poor working condition. Our first snapshot shows how this is indicated on the AimBetter monitor.
The first picture shows the main query monitor view, with the indicator on the query detail line showing the presence of a plan problem. Opening this query up with a single click then shows the exact detail, of time taken to execute, as well as the detected plan’s problem.
We identify in this illustration the dynamic new feature that detects the anomalies in the executing plan. Shown clearly along with the extended execution times, is the indicator on the query line itself indicating that attention to the plan is required. Once the query itself is opened, the specifics of the plan anomaly are visible – in this case, a SORT.
The new AimBetter feature
We have now introduced an advanced new feature in AimBetter monitor’s range of tools. Directly visible from the first display of a query, we now bring up in real-time any instance where the plan that is executing has some basic condition requiring your attention. In the past, our monitor detected and reported on missing indexes. In the new version, we also identify instances of the following cases inside the executing plan:
TABLE SCAN
INDEX SCAN
INDEX SPOOL
TABLE SPOOL
HASH MATCH
SORT
INDEX SCAN/SEEK WITH CONVERT_IMPLICIT
Using the AimBetter console, we were able to bring up the actual plan that is running in this case:
The obvious need is to understand why this plan was selected by the SQL Optimizer when experience had shown that at other times the query ran significantly faster. To understand why the first plan is consuming so much time basically running a SORT, when indexes are available, it is necessary to look at the columns in the index, relative to the query’s requirements.
In the first case, the index has 3 columns: [CUST] [IVALL] [CURDATE]. In this case, CURDATE is not the primary field in the index so SORT has to be performed to complete the required information. This takes time because the small range of possible values in the primary index may result in optimizer selecting to perform an TABLE SCAN instead of INDEX SEEK. This may happen for a variety of reasons, for example when statistics indicate that a significant number of matches will be found, in which case Optimizer chooses SCAN as more efficient than SEEK, and this then requires a sort to organize the resultant set.
AimBetter support team’s first recommendation was to change index order in index 2 so that CURDATE will appear first
([CURDATE] [TYPE]) and monitor performance: CREATE NONCLUSTERED INDEX [DOCUMENTS#7#TYPE#CURDATE] ON [dbo].[DOCUMENTS] ( [TYPE] ASC, [CURDATE] ASC ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF,ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY] GO
Query monitor now shows a substantial improvement in performance, with no more plan warnings:
And the reason can be seen by examining the plan used by optimizer in this case:
Since CURDATE contained 1800 different values, performance may be improved significantly but still, the Optimizer may also sometimes chose SCAN in other circumstance and this came up with a need for more understanding. The team’s recommendation was to move the CURDATE field:
CREATE NONCLUSTERED INDEX [DOCUMENTS#6#CUST#IVALL#CURDATE] ON [dbo].[DOCUMENTS]
( [CUST] ASC, [IVALL] ASC)
INCLUDE ( [CURDATE]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING =OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [DOCUMENTS#7#TYPE#CURDATE] ON [dbo].[DOCUMENTS]
( [CURDATE] ASC, [TYPE] ASC) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF,ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO
There will be no reason to choose the INCLUDE index that is not included in the statistics of the index. This finally solved the problem. Finally, we can illustrate a further case where running the same query, with the same plan, can still produce different performance:
There are times when the information is in memory and it runs faster than where information is required from the disk. In the picture above, the Disk I/O column testifies to this.
The AimBetter Solution
Good database administration needs good tools, so that minimum time and effort are needed to identify and correct performance bottlenecks. With the new functions included now in AimBetter, this becomes much esier. Without having to search at random through piles of extraneous information, wasting time and money, DBAs can now see immediately where and when the running query has been allocated a faulty plan.
In this case, with the insight gained from the ability in AimBetter to view the actual plan that was running in real-time, instead of having to try to reconstruct the problem in a different configuration, we were able to determine that a change in the index was the best solution, and this was immediately applied.
Why AimBetter does it better.
AimBetter brings a host of special features and benefits that are specially designed to make the management of SQL databases more efficient and to simplify the work of database administrators. The particular features that worked in favor of our customers, in this case, can be summarized as follows:
- Identifies all current queries elements
- Allows query selection on specific criteria
- Displays all live Queries statistics
- Historical display of queries
- Retrieves full query syntax
- Retrieves query plan
- NEW – identifies serious deficiencies in this plan
- Enables wide-scope correlation of operational data across the whole environment
- Monitors core SQL database performance
- Reports on SQL Exceptions
- Alerts of any unexpected behavior
- Enables comparison of current with historical metrics
- Displays in a single comprehensive dashboard
- Displays all operational metrics on one screen
Putting all these features into a single package and then having the results at the fingertips of both our experts and the customers’ own database managers was the key element that allowed for this happy ending.
