12

I'm trying to see if there's a way to trick SQL Server to use a certain plan for the query.

1. Environment

Imagine you have some data which is shared between different processes. So, suppose we have some experiment results which take a lot of space. Then, for each process we know which year/month of experiment result we want to use.

if object_id('dbo.SharedData') is not null
    drop table SharedData

create table dbo.SharedData (
    experiment_year int,
    experiment_month int,
    rn int,
    calculated_number int,
    primary key (experiment_year, experiment_month, rn)
)
go

Now, for every process we have parameters saved in the table

if object_id('dbo.Params') is not null
    drop table dbo.Params

create table dbo.Params (
    session_id int,
    experiment_year int,
    experiment_month int,
    primary key (session_id)
)
go

2. Test data

Let's add some test data:

insert into dbo.Params (session_id, experiment_year, experiment_month)
select 1, 2014, 3 union all
select 2, 2014, 4 
go

insert into dbo.SharedData (experiment_year, experiment_month, rn, calculated_number)
select
    2014, 3, row_number() over(order by v1.name), abs(Checksum(newid())) % 10
from master.dbo.spt_values as v1
    cross join master.dbo.spt_values as v2
go

insert into dbo.SharedData (experiment_year, experiment_month, rn, calculated_number)
select
    2014, 4, row_number() over(order by v1.name), abs(Checksum(newid())) % 10
from master.dbo.spt_values as v1
    cross join master.dbo.spt_values as v2
go

3. Fetching results

Now, it's very easy to get experiment results by @experiment_year/@experiment_month:

create or alter function dbo.f_GetSharedData(@experiment_year int, @experiment_month int)
returns table
as
return (
    select
        d.rn,
        d.calculated_number
    from dbo.SharedData as d
    where
        d.experiment_year = @experiment_year and
        d.experiment_month = @experiment_month
)
go

The plan is nice and parallel:

select
    calculated_number,
    count(*)
from dbo.f_GetSharedData(2014, 4)
group by
    calculated_number

query 0 plan

enter image description here

4. Problem

But, to make usage of the data a bit more generic, I want to have another function - dbo.f_GetSharedDataBySession(@session_id int). So, straightforward way would be to create scalar functions, translating @session_id -> @experiment_year/@experiment_month:

create or alter function dbo.fn_GetExperimentYear(@session_id int)
returns int
as
begin
    return (
        select
            p.experiment_year
        from dbo.Params as p
        where
            p.session_id = @session_id
    )
end
go

create or alter function dbo.fn_GetExperimentMonth(@session_id int)
returns int
as
begin
    return (
        select
            p.experiment_month
        from dbo.Params as p
        where
            p.session_id = @session_id
    )
end
go

And now we can create our function:

create or alter function dbo.f_GetSharedDataBySession1(@session_id int)
returns table
as
return (
    select
        d.rn,
        d.calculated_number
    from dbo.f_GetSharedData(
        dbo.fn_GetExperimentYear(@session_id),
        dbo.fn_GetExperimentMonth(@session_id)
    ) as d
)
go

query 1 plan

enter image description here

The plan is the same except it's, of course, not parallel, because scalar functions performing data access make the whole plan serial.

So I've tried a several different approaches, like, using subqueries instead of scalar functions:

create or alter function dbo.f_GetSharedDataBySession2(@session_id int)
returns table
as
return (
    select
        d.rn,
        d.calculated_number
    from dbo.f_GetSharedData(
       (select p.experiment_year from dbo.Params as p where p.session_id = @session_id),
       (select p.experiment_month from dbo.Params as p where p.session_id = @session_id)
    ) as d
)
go

query 2 plan

enter image description here

Or using cross apply

create or alter function dbo.f_GetSharedDataBySession3(@session_id int)
returns table
as
return (
    select
        d.rn,
        d.calculated_number
    from dbo.Params as p
        cross apply dbo.f_GetSharedData(
            p.experiment_year,
            p.experiment_month
        ) as d
    where
        p.session_id = @session_id
)
go

query 3 plan

enter image description here

But I can't find a way to write this query to be as good as the one using scalar functions.

Couple of thoughts:

  1. Basically what I'd want is to being able to somehow tell SQL Server to pre-calculate certain values and then pass them further as constants.
  2. What could be helpful is if we had some intermediate materialization hint. I've checked a couple of variants (multi-statement TVF or cte with top), but no plan is as good as the one with scalar functions so far
  3. I know about coming improvement of SQL Server 2017 - Froid: Optimization of Imperative Programs in a Relational Database.I'm not sure it will help, though. It would've been nice to be proven wrong here, though.

Additional information

I am using a function (rather than selecting data directly from the tables) because it is much easier to use in many different queries, which usually have @session_id as a parameter.

I was asked to compare actual execution times. In this particular case

  • query 0 runs for ~500ms
  • query 1 runs for ~1500ms
  • query 2 runs for ~1500ms
  • query 3 runs for ~2000ms.

Plan #2 has an index scan instead of a seek, which is then filtered by predicates on nested loops. Plan #3 is not that bad, but still does more work and works slower that plan #0.

Let's assume that dbo.Params is changed rarely, and usually have around 1-200 rows, not more than, let's say 2000 is ever expected. It's around 10 columns now and I don't expect to add column too often.

The number of rows in Params is not fixed, so for every @session_id there'll be a row. Number of columns there is not fixed, it's one of the reasons I don't want to call dbo.f_GetSharedData(@experiment_year int, @experiment_month int) from everywhere, so I can add new column to this query internally. I'd be glad to hear any opinions/suggestions on this, even if it has some restrictions.

migrated from stackoverflow.com Mar 25 '18 at 12:41

This question came from our site for professional and enthusiast programmers.

  • The query plan with Froid would be similar to that of query2 above, so yes, it will not take you to the solution that you want to achieve in this case. – Karthik Apr 9 '18 at 23:03
12

You cannot really safely achieve exactly what you want in SQL Server today, i.e. in a single statement and with parallel execution, within the restrictions laid out in the question (as I perceive them).

So my simple answer is no. The rest of this answer is mostly a discussion of why that is, in case it is of interest.

It is possible to get a parallel plan, as noted in the question, but there are two main varieties, neither of which are suitable for your needs:

  1. A correlated nested loops join, with a round-robin distribute streams on the top level. Given that a single row is guaranteed to come from Params for a specific session_id value, the inner side will run on a single thread, even though it is marked with the parallelism icon. This is why the apparently-parallel plan 3 does not perform as well; it is in fact serial.

  2. The other alternative is for independent parallelism on the inner side of the nested loops join. Independent here means that threads are started up on the inner side, and not merely the same thread(s) as are executing the outer side of the nested loops join. SQL Server only supports independent inner-side nested loops parallelism when there is guaranteed to be one outer-side row and there are no correlated join parameters (plan 2).

So, we have a choice of a parallel plan that is serial (due to one thread) with the desired correlated values; or an inner-side parallel plan that has to scan because it has no parameters to seek with. (Aside: It really ought to be allowed to drive inner-side parallelism using exactly one set of correlated parameters, but it has never been implemented, probably for good reason).

A natural question then is: why do we need correlated parameters at all? Why can SQL Server not simply seek directly to the scalar values provided by e.g. a subquery?

Well, SQL Server can only 'index seek' using simple scalar references, e.g. a constant, variable, column, or expression reference (so a scalar function result can also qualify). A subquery (or other similar construction) is simply too complex (and potentially unsafe) to push into the storage engine whole. So, separate query plan operators are required. This is turn requires correlation, which means no parallelism of the sort you want.

All in all, there really is no better solution currently than methods like assigning the lookup values to variables and then using those in the function parameters in a separate statement.

Now you may have specific local considerations that means caching the current values of the year and month in SESSION_CONTEXT is worthwhile i.e.:

SELECT FGSD.calculated_number, COUNT_BIG(*)
FROM dbo.f_GetSharedData
(
    CONVERT(integer, SESSION_CONTEXT(N'experiment_year')), 
    CONVERT(integer, SESSION_CONTEXT(N'experiment_month'))
) AS FGSD
GROUP BY FGSD.calculated_number;

But this falls into the category of workaround.

On the other hand, if aggregation performance is of primary importance, you could consider sticking with inline functions and creating a columnstore index (primary or secondary) on the table. You may find the benefits of columnstore storage, batch mode processing, and aggregate pushdown provide greater benefits than a row-mode parallel seek anyway.

But beware of scalar T-SQL functions, especially with columnstore storage, since it is easy to end up with the function being evaluated per-row in a separate row-mode Filter. It is generally quite tricky to guarantee the number of times SQL Server will choose to evaluate scalars, and better not to try.

  • Thanks, Paul, great answer! I thought about using session_context but I decide that it's a bit too crazy an idea for me and I'm not sure how it's going to fit my current architecture. What would be useful though is, may be, some hint which I could use to let optimizer know that it should treat result of the subquery like a simple scalar reference. – Roman Pekar Mar 25 '18 at 18:08
7

As far as I know the plan shape that you want isn't possible with just T-SQL. It seems like you want the original plan shape (query 0 plan) with the subqueries from your functions being applied as filters directly against the clustered index scan. You'll never get a query plan like that if you don't use local variables to hold the return values of the scalar functions. The filtering will instead be implemented as a nested loop join. There are three different ways (from a parallelism point of view) that the loop join can be implemented:

  1. The entire plan is serial. This isn't acceptable to you. This is the plan that you get for query 1.
  2. The loop join runs in serial. I believe in this case the inner side can run in parallel, but it's not possible to pass any predicates down to it. So most of the work will be done in parallel, but you're scanning the whole table and the partial aggregate is much more expensive than before. This is the plan that you get for query 2.
  3. The loop join runs in parallel. With parallel nested loop joins the inner side of the loop runs in serial but you can have up to DOP threads running on the inner side at once. Your outer result set will just have a single row, so your parallel plan will effectively be serial. This is the plan that you get for query 3.

Those are the only possible plan shapes that I'm aware of. You can get some others if you use a temp table but none of them solve your fundamental problem if you want query performance to be just as good as it was for query 0.

You can achieve equivalent query performance by using the scalar UDFs to assign return values to local variables and using those local variables in your query. You can wrap that code in a stored procedure or a multi-statement UDF to avoid maintainability issues. For example:

DECLARE @experiment_year int = dbo.fn_GetExperimentYear(@session_id);
DECLARE @experiment_month int = dbo.fn_GetExperimentMonth(@session_id);

select
    calculated_number,
    count(*)
from dbo.f_GetSharedData(@experiment_year, @experiment_month)
group by
    calculated_number;

The scalar UDFs have been moved outside of the query that you wish to be eligible for parallelism. The query plan that I get appears to be the one that you want:

parallel query plan

Both approaches have disadvantages if you need to use this result set in other queries. You cannot directly join to a stored procedure. You'd have to save the results to a temp table which has its own set of problems. You can join to a MS-TVF, but in SQL Server 2016 you may see cardinality estimate issues. SQL Server 2017 offers interleaved execution for MS-TVF which could solve the problem entirely.

Just to clear up few things: T-SQL Scalar UDFs always prohibit parallelism and Microsoft has not said that FROID will be available in SQL Server 2017.

3

This can most likely be done using SQLCLR. One benefit of SQLCLR Scalar UDFs is that they do not prevent parallelism if they do not do any data access (and sometimes need to also be marked as "deterministic"). So how do you make use of something that requires no data access when the operation itself requires data access?

Well, because the dbo.Params table is expected to:

  1. generally never have more than 2000 rows in it,
  2. rarely change structure,
  3. only (currently) needs to have two INT columns

it is feasible to cache the three columns — session_id, experiment_year int, experiment_month — into a static collection (e.g. a Dictionary, perhaps) that is populated out-of-process and read by the Scalar UDFs that get the experiment_year int and experiment_month values. What I mean by "out-of-process" is: you can have a completely separate SQLCLR Scalar UDF or Stored Procedure that can do data access and reads from the dbo.Params table to populate the static collection. That UDF or Stored Procedure would be executed prior to using the UDFs that get the "year" and "month" values, that way the UDFs that get the "year" and "month" values aren't doing any DB data access.

The UDF or Stored Procedure that reads the data can check first to see if the collection has 0 entries and if so, then populate, else skip. You can even keep track of the time that it was populated and if it has been over X minutes (or something like that), then clear and re-populate even if there are entries in the collection. But skipping the population will help since it will need to be executed frequently to ensure that it is always populated for the two main UDFs to get the values from.

The main concern is when SQL Server decides to unload the App Domain for whatever reason (or it is triggered by something using DBCC FREESYSTEMCACHE('ALL');). You don't want to risk that collection being cleared out between the execution of the "populate" UDF or Stored Procedure and the UDFs to get the "year" and "month" values. In which case you can have a check at the very beginning of those two UDFs to throw an exception if the collection is empty, since it is better to error than to successfully provide false results.

Of course, the concern noted above assumes that the desire is to have the Assembly marked as SAFE. If the Assembly can be marked as EXTERNAL_ACCESS, then it is possible to have a static constructor execute the method that reads the data and populates the collection, so that you only ever need to manually execute that to refresh the rows, but they would always be populated (because the static class constructor always runs when the class is loaded, which happens whenever a method in this class is executed after a restart or the App Domain is unloaded). This requires using a regular connection and not the in-process Context Connection (which is not available to static constructors, hence the need for EXTERNAL_ACCESS).

Please note: in order to not be required to mark the Assembly as UNSAFE, you need to mark any static class variables as readonly. This means, at the very least, the collection. This is not a problem since read-only collections can have items added or removed from them, they just can't be initialized outside of the constructor or initial load. Tracking the time that the collection was loaded for the purpose of expiring it after X minutes is trickier since a static readonly DateTime class variable cannot be changed outside of the constructor or initial load. To get around this restriction, you need to use a static, read-only collection that contains a single item that is the DateTime value so that it can be removed and re-added upon a refresh.

  • Don’t know why somebody downvoted this. While not very generic, I think it could be applicable in my current case. I’d prefer to have pure SQL solution, but I’ll definitely take closer look at this and try to see if works – Roman Pekar Mar 25 '18 at 15:04
  • @RomanPekar Not sure, but there are plenty of folks out there that are anti-SQLCLR. And maybe a few that are anti-me ;-). Either way, I can't think of why this solution wouldn't work. I understand the preference for pure T-SQL, but I don't know how to make that happen, and if there is no competing answer, then maybe nobody else does either. I don't know if memory optimized tables and natively compiled UDFs would do any better here. Also, I just added a paragraph with some implementation notes to keep in mind. – Solomon Rutzky Mar 25 '18 at 15:31
  • 1
    I have never been fully convinced that using readonly statics is safe or wise in the SQLCLR. Much less am I convinced about then going on to fool the system by making that readonly a reference type, which you then go and change. Gives me the absolute willies tbh. – Paul White Mar 25 '18 at 16:33
  • @PaulWhite Understood, and I recall this coming up in private conversation years ago. Given the shared nature of App Domains (and hence static objects) in SQL Server, yes, there is risk for race conditions. That is why I first determined from the O.P. that this data is minimal and stable, and why I qualified this approach as requiring "rarely changing", and gave a means of refreshing when needed. In this use case I don't see much if any risk. I found a post years ago about the ability to update readonly collections as being by design (in C#, no discussion re: SQLCLR). Will try to find it. – Solomon Rutzky Mar 25 '18 at 17:00
  • 2
    No need, there's no way you're going to get me comfortable with this aside from official SQL Server documentation saying it's fine, which I'm pretty sure does not exist. – Paul White Mar 25 '18 at 17:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.