Cost of using OPTION (RECOMPILE) vs Joining to tables

Question

Using: SQL Server 2016+

I've been looking to see if there is any way to evaluate how long it takes SQL Server to recompile the execution plan for a query. We have several stored procedures where we select from table 1 based on a nullable parameter in table 2. E.g. If customer is not null return their sales else return all sales.

Sample data:

DROP TABLE IF EXISTS dbo.TestTable1;
DROP TABLE IF EXISTS dbo.TestTable2;

CREATE TABLE dbo.TestTable1 (ID INT NOT NULL PRIMARY KEY CLUSTERED , TextValue NVARCHAR(255) NULL);
CREATE TABLE dbo.TestTable2 (ID INT NOT NULL PRIMARY KEY CLUSTERED , TextValue NVARCHAR(255) NULL);

INSERT INTO TestTable1 (ID, TextValue)
VALUES (1, N'Table 1 - Text 1'),
       (2, N'Table 1 - Text 2'),
       (3, N'Table 1 - Text 3'),
       (4, N'Table 1 - Text 4'),
       (5, N'Table 1 - Text 5'),
       (6, N'Table 1 - Text 6'),
       (7, N'Table 1 - Text 7'),
       (8, N'Table 1 - Text 8'),
       (9, N'Table 1 - Text 9'),
       (10, N'Table 1 - Text 10');

INSERT INTO TestTable2 (ID, TextValue)
VALUES (1, N'Table 2 - Text 1'),
       (2, N'Table 2 - Text 2'),
       (3, N'Table 2 - Text 3'),
       (4, N'Table 2 - Text 4'),
       (5, N'Table 2 - Text 5'),
       (6, N'Table 2 - Text 6'),
       (7, N'Table 2 - Text 7'),
       (8, N'Table 2 - Text 8'),
       (9, N'Table 2 - Text 9'),
       (10, N'Table 2 - Text 10');

This is greatly over simplified as we would have multiple possible conditions, linking to multiple tables. We are currently looking at recompiling the query so that the joins to the secondary table are only made when required.

DECLARE @LookupValue NVARCHAR(50);

SET @LookupValue = NULL;

SELECT  *
  FROM  dbo.TestTable1 T1
 WHERE  @LookupValue IS NULL
    OR  EXISTS ( SELECT TOP (1) 1 A FROM dbo.TestTable2 T2 WHERE T1.ID = T2.ID AND T2.TextValue = @LookupValue)
OPTION (RECOMPILE)

SET @LookupValue = N'Table 2 - Text 1';

SELECT  *
  FROM  dbo.TestTable1 T1
 WHERE  @LookupValue IS NULL
    OR  EXISTS ( SELECT TOP (1) 1 A FROM dbo.TestTable2 T2 WHERE T1.ID = T2.ID AND T2.TextValue = @LookupValue)
OPTION (RECOMPILE);

As you can see from the query plan below with the recompile table 2 is effectively removed from the execution.

There is however a cost to recompiling, and I'm looking to trace this overhead so I can make sensible decisions as to which of our queries to format in this way. In general I am seeing that recompiling is consistently faster but there are lots of post stating that this means the execution plans can come out as being far less than optimal.

Any guidance on measuring these overheads or any issues I should investigate before implementing this more widely would be gratefully received.

Many thanks.

Answer 1

You can do what you are asking without the option recompile all that does it force it to generate a new plan every time. You want to do whatever you can to not put your branching logic inside of the where clause that just beats up the engine trying to figure out how to possibly create a suitable plan for both of your scenarios which it cannot do and will just create performance issues. What you want to do is create a branching logic such that there are two distinct queries each which can have their own execution plan. You can perform this with something along these lines.

Declare @customerid int
Set @customerid = (select customerid from dbo.table2)

If @customerid is null
BEGIN
Select datadesired from table1
END

ELSE
BEGIN
Select datadesired from table1
INNER JOIN table2 ON PKey = FKey
WHERE customerid = @customerID
END

This should work fantastic if you want to either pull all the data or a specific set of data. If you go more complex like you suggested you might be dynamic SQL is probably the better option. Can you still use this method to create a query for each scenario? Sure, if you want. But I can pretty much guarantee that if you try to use branching where clause logic to create two completely different queries and then give it any significant amount of data you will have problems, but making two distinct queries works as well as if both of those queries lived in different stored procedures and you figured out which procedure to call at the web layer (also possible).

Answer 2

To get an insight on compile times, please take a look at:

https://ericblinn.com/quantifying-compile-time

Basically, use SET STATISTICS TIME ON before your query to get console messages on time spent in compilation and execution.

As a side note, not your question, worrying about compilation times might not be the most productive course of action. Pretty low-level under-the-hood stuff and unpredictable, IMHO.

If you have two very distinct query patterns, maybe it would be better creating two (or more) distinct stored procs, gated by an entry one based on conditions, each one with its own pattern (inclusion or removal of the nullable parameter) and letting the optimizer do its work in peace.

Forcing reparsing and rebuilding plans while being hit by continuous transactions might not be the most sensible choice.

Also, check the following blog:

https://blogs.msdn.microsoft.com/robinlester/2016/08/10/improving-query-performance-with-option-recompile-constant-folding-and-avoiding-parameter-sniffing-issues/

It has some insights on OPTION(OPTIMIZE FOR (@string = '')) that might be usefull.

But, as stated before, the conclusion I take from this is not using recompile but design data access procedures that avoid the necessity of it, when possible.