New query hints
The SQL Server Query Optimizer does an amazing job of execution plan generation. Most of the time, for most queries, it generates an optimal execution plan. And this is not easy at all. There is a lot of potential to get a suboptimal plan: wrong server configuration, poorly designed databases, missing and suboptimal indexes, suboptimal written queries, nonscalable solutions, and so on. And the query optimizer should work for all those workloads, all over the world, all the time.
Depending on data constellation, it generates suboptimal execution plans sometimes. If the execution of the queries is very important from a business point of view, you have to do something to try to achieve at least an acceptable execution plan. One of the weapons you have for this is hints to the query optimizer. With hints, which are actually instructions, you instruct the query optimizer how to generate the execution plan. You take responsibility or you take part of the responsibility for the execution plan generation.
There are three types of hints: table, join, and query hints. You use them if you cannot enforce the required execution plan with other actions. Hints should be considered as a last resort in query tuning. You should use them if you don't know another way to get a desired plan, when you have tried all that you know, or when you are under time pressure and have to fix a query as soon as possible. For instance, if you suddenly encounter a problem on the production system on the weekend or during the night, you can use the hint as a temporary solution or a workaround and then look for a definitive solution without time or operative pressures. Literally said, you can use query hints if you know what you are doing. However, you should not forget that the hint and the plan remain forever and you need to periodically evaluate whether the plan is still adequate. In most cases, developers forget about plans with hints as soon as the performance problem is gone.
SQL Server 2016 brings three new query hints to address problems related to memory grants and performance spools:
NO_PERFORMANCE_SPOOLMAX_GRANT_PERCENTMIN_GRANT_PERCENT
NO_PERFORMANCE_SPOOL
The new query hint NO_PERFORMANCE_SPOOL has been added to SQL Server 2016 to allow users to enforce an execution plan, which does not contain a spool operator.
A spool operator in an execution plan does not mean that the plan is suboptimal; it is usually a good choice of query optimizer. However, in some cases, it can reduce the overall performance. This happens, for instance, when a query or a stored procedure whose execution plan contains a spool operator is executed by numerous parallel connections. Since the Spool operator uses tempdb, this can lead to tempdb contention when many queries are running at the same time. Using this hint, you can avoid this issue.
To demonstrate the use of this query hint, you will once again use the new WideWorldImporters sample database. Assume that you need to display order details for orders picked up by the sales persons provided in an input list. To ensure that the execution plan contains a spool operator, this example uses a user-defined function and not the new SQL Server 2016 function STRING_SPLIT. Use the following code to create the function and filter orders with the sales persons from the list.
USE WideWorldImporters;
GO
CREATE OR ALTER FUNCTION dbo.ParseInt
(
@List VARCHAR(MAX),
@Delimiter CHAR(1)
)
RETURNS @Items TABLE
(
Item INT
)
AS
BEGIN
DECLARE @Item VARCHAR(30), @Pos INT;
WHILE LEN(@List)>0
BEGIN
SET @Pos = CHARINDEX(@Delimiter, @List);
IF @Pos = 0 SET @Pos = LEN(@List)+1;
SET @Item = LEFT(@List, @Pos-1);
INSERT @Items SELECT CONVERT(INT, LTRIM(RTRIM(@Item)));
SET @List = SUBSTRING(@List, @Pos + LEN(@Delimiter), LEN(@List));
IF LEN(@List) = 0 BREAK;
END
RETURN;
END
GO
DECLARE @SalesPersonList VARCHAR(MAX) = '3,6,8';
SELECT o.*
FROM Sales.Orders o
INNER JOIN dbo.ParseInt(@SalesPersonList,',') a ON a.Item = o.SalespersonPersonID
ORDER BY o.OrderID;
When you observe the execution plan for this query, you can see the Table Spool operator in it, as Figure 4.4 shows:
Figure 4.4: Execution plan with the Table Spool operator
When you execute exactly the same query, but with the query hint OPTION (NO_PERFORMANCE_SPOOL), you get a different execution plan, without the Spool operator, as shown in Figure 4.5:
Figure 4.5: Execution plan without the Table Spool operator
By observing the execution plan, you can see that the spool operator has disappeared from it. You can also see that the Estimated Subtree Cost is about 10% higher for the plan without the hint (by comparing the yellow SELECT property boxes); therefore, the query optimizer has chosen the original plan with the Spool operator. Here, you used the hint just for demonstration purposes to show that you can enforce another plan, without the spool operator.
Note
You have to create a user-defined function in this example because this query with a new STRING_SPLIT function has an execution plan without the Spool operator.
However, if the Spool operator is required in an execution plan to enforce the validity and correctness, the hint will be ignored. To demonstrate this behavior, you will use the next example. First, you need to create a sample table and insert two rows into it:
USE WideWorldImporters; DROP TABLE IF EXISTS dbo.T1 CREATE TABLE dbo.T1( id INT NOT NULL, c1 INT NOT NULL, ); GO INSERT INTO dbo.T1(id, c1) VALUES(1, 5),(1, 10);
Now, assume that you want to add some of the existing rows into the table, say the rows where ID has value less than 10. At this point, only one row qualifies for this insert. The following query implements this requirement:
INSERT INTO dbo.T1(id, c1) SELECT id, c1 FROM dbo.T1 WHERE id < 10;
The execution plan for this query is shown in Figure 4.6:
Figure 4.6: Execution plan for INSERT statement with the Table Spool operator
When you observe it, you can see the Table Spool operator proudly staying in the middle of the execution plan. However, when you execute the same statement with the NO_PERFORMANCE_SPOOL hint, you get an identical execution plan; the query hint is simply ignored. The reason for this decision by the query optimizer is that the spool operator in this plan is used not for optimization, but to guarantee the correctness of the result. To demonstrate this, execute these two statements:
INSERT INTO dbo.T1(id, c1) SELECT id, c1 FROM dbo.T1 WHERE id < 10; INSERT INTO dbo.T1(id, c1) SELECT id, c1 FROM dbo.T1 WHERE id < 10 OPTION (NO_PERFORMANCE_SPOOL);
Figure 4.7 shows both plans and it is obvious that this is the same execution plan:
Figure 4.7: Execution plans showing that the hint NO_PERFORMANCE_SPOOL is ignored
Use the query hint NO_SPOOL_OPERATOR when:
- You want to avoid the spool operator in the execution object
- You know that this is a good idea (performance issue is caused by the Spool operator)
- You cannot achieve this, with a reasonable effort otherwise
MAX_GRANT_PERCENT
The MIN_GRANT_PERCENT and MAX_GRANT_PERCENT hints were first introduced in the SQL Server 2012 SP3 and now in SQL Server 2016 RTM (they are still not available in SQL Server 2014). They address the problem of inappropriate memory grant for query execution.
Memory grant is a memory associated for the execution of queries whose execution plan contains operators that need to store temporary row data while sorting and joining rows (Sort, Hash Join, and so on). The value for memory grant depends on SQL Server's Estimated Number of Rows that should be processed by memory operators. If the estimated number of rows significantly differs from the actual, the memory grant is overestimated or underestimated.
To demonstrate an overestimated memory grant, use the following code that generates a 10M row large table:
USE WideWorldImporters; DROP TABLE IF EXISTS dbo.T1; CREATE TABLE dbo.T1( id INT NOT NULL, c1 INT NOT NULL, c2 TINYINT NOT NULL DEFAULT 1, c3 CHAR(100) NOT NULL DEFAULT 'test', CONSTRAINT PK_T1 PRIMARY KEY CLUSTERED (id ASC) ); GO INSERT INTO dbo.T1(id, c1) SELECT n AS id, 1 + ABS(CHECKSUM(NEWID())) % 10000 AS c1 FROM dbo.GetNums(10000000); GO CREATE INDEX ix1 ON dbo.T1(c2); GO
Note that the c2 column has a value 1 for all the rows in the table. Also, note that the code sample uses the GetNums function introduced at the beginning of this chapter. Assume that your task is to return rows from the T1 table, where the c2 column has value the 0 or 2. The query is very simple:
SELECT * FROM dbo.T1 WHERE c2 IN (0, 2) ORDER BY c1;
The query returns no rows (as mentioned, c2 = 1 for all rows in the table). The execution plan is simple too: Clustered Index Scan followed by the Sort operator. The plan is shown in Figure 4.8:
Figure 4.8: Execution plan with an overestimated memory grant
You can see that the query optimizer has significantly overestimated the number of rows for the Sort operator. Therefore, it is the most expensive part of the execution plan, even in concurrency with the Clustered Index Scan of a 10M row table. Actually, the result of the query is an empty set, but SQL Server thinks that all rows from the table will be returned. This is a bug with new cardinality in SQL Server 2014 and it is not fixed in SQL Server 2016. Since the Sort operator requires memory, this query needs a memory grant. With the mouse over the Select operator, you can see that the memory granted for this query is 1.9 GB. More details about memory grant are available in the XML representation of the execution plan, as shown in Figure 4.9:
Figure 4.9: Memory Grant information in the XML representation of the execution plan
For the execution of a single query that returns no rows, about 2 GB memory is granted! If this query was executed by 100 concurrent sessions, almost 200 GB of memory would be granted for its execution.
As mentioned earlier, query hints should be used as a last resort for performance tuning. For demonstration purposes, you will use the MAX_ GRANT_PERCENT query hint to limit the amount of memory granted.
The MAX_GRANT_PERCENT query hint defines the maximum memory grant size as a percentage of available memory. It accepts float values between 0.0 and 100.0. Granted memory cannot exceed this limit, but can be lower if the resource governor setting is lower than this. Since you know that you have no rows in the output, you can use a very low value in the query hint:
SELECT * FROM dbo.T1 WHERE c2 IN (0, 2) ORDER BY c1 OPTION (MAX_GRANT_PERCENT=0.001);
When you observe the execution plan shown in Figure 4.10, you can see that the plan remains the same, only the memory grant value has dropped to 512 KB!
Figure 4.10: Execution plan with the query hint MAX_GRANT_PERCENT
With the memory grant hint, you cannot change the execution plan, but you can significantly reduce the amount of memory granted.
Note
All execution plans shown in this section were generated by a database engine with the SQL Server 2016 RTM installed on it. If you have installed SQL Server 2016 Service Pack 1, you will see a new "Excessive Grant" warning in the SELECT property box, indicating a discrepancy between granted and used memory for the query execution. You can find more details about this warning at https://support.microsoft.com/en-us/help/3172997.
You can see that the applied query hint saved 2 GB of memory. The query hint MAX_GRANT_PERCENT should be used when:
- You don't know how to trick the optimizer into coming up with the appropriate memory grant
- You want to fix the problem immediately and to buy time to search for a final solution
Here, you have used the query hint just for demonstration purposes, the correct way to fix a query is to understand why the estimated and actual number of rows are so discrepant and to then try to rewrite the query. As mentioned earlier, in this case, the problem is caused by the change in the Cardinality Estimator (CE) in the SQL Server 2014 and the query works well under the old CE. Use the following query to force SQL Server to use the old CE using the trace flag 9481, as shown in the following query:
SELECT * FROM dbo.T1 WHERE c2 IN (0, 2) ORDER BY c1 OPTION (QUERYTRACEON 9481);
This generates the execution plan displayed in Figure 4.11:
Figure 4.11: Execution plan where the old cardinality estimator is enforced
You can see the expected Nested Loop Join operator and a symbolic memory grant of 1 MB. The old CE estimates one row to be returned, while the new CE estimates the whole table. These very different assumptions lead to completely different execution plans and memory grants.
Another option to fix this problem is to rewrite the query to use UNION ALL instead of the IN operator. Let's rewrite our original query:
SELECT * FROM dbo.T1 WHERE c2 = 0 UNION ALL SELECT * FROM dbo.T1 WHERE c2 = 2 ORDER BY c1;
Figure 4.12 shows the execution plan for the preceding query:
Figure 4.12: Execution plan for the rewritten query
You can see that the plan is a double of the old CE but significantly better than the initial one. With the MAX_GRANT_PERCENT query hint, you have successfully reduced the amount of granted memory. However, as demonstrated, you could even improve the initial execution plan by using other techniques. Therefore, use the query hint only when you don't know how to instruct the query optimizer to generate a better estimation.
MIN_GRANT_PERCENT
Similar to the previous example, in the event of a memory underestimation, you can force the optimizer to guarantee a minimum memory grant for a query execution using the MIN_GRANT_PERCENT query hint. It also accepts float values between 0.0 and 100.0. Be very careful with this hint, since if you specify 100 percent as the minimum grant, the whole resource governor memory will be granted to a single query execution. If you specify more memory than is available, the query will simply wait for memory grant.