Michael J. Swart

April 27, 2016

You Can’t Force Query Plans If They Use TVPs With PKs

Filed under: Miscelleaneous SQL,SQLServerPedia Syndication,Technical Articles — Michael J. Swart @ 12:24 pm

Have you ever played “Fortunately/Unfortunately”? It’s a game where players alternately give good news and bad news. It goes something like this:

Databases such as SQL Server make it easy to retrieve sets of data.
Unfortunately, it’s kind of awkward to send sets of data to SQL Server.
Fortunately, table-valued parameters (TVPs) make this easier.
Unfortunately, queries that use TVPs often suffer from non-optimal query plans.
Fortunately, creating primary key or unique key constraints gives us a way to index table types.
Unfortunately, those constraints prevent any kind of plan forcing.
Fortunately, SQL Server 2014 lets us create named indexes for table types which lets us force plans if we need to.

otherhand

Let’s break this down:

Sending Sets of Data to SQL Server is Awkward

It always has been. Originally, developers were forced to send a CSV string to SQL Server and write a do-it-yourself function to split the string into a set of values.

  • In 2005, Microsoft introduced XML and CLR which let developers shred or split strings in new ways,
  • In 2008, Microsoft introduced table-valued parameters,
  • In 2014, they introduced In-Memory TVPs,
  • In 2016, there’s a new SPLIT_STRING() function

So there are more options now then there ever have been and they each have their own issues.

Aaron Bertrand explores some of those performance issues in STRING_SPLIT() in SQL Server 2016. It’s a specific use-case where he focuses on duration. In our case, we focus on aggregated system load like worker time or reads so we don’t necessarily value parallel query plans. But I love his methods. He gives us tools that let us evaluate our own situation based on our own criteria.

I’m going to focus on TVPs which is the most natural method of sending sets of data to SQL Server from a syntax point of view.

Indexes on Table Types

Table-valued parameters are implemented using table types. Before SQL Server 2014, the only way to index a table type was to define a primary key or a unique key on it like this:

create type dbo.TypeWithPK 
    as table ( id int primary key );

The syntax for CREATE TYPEprevents us from naming our primary key and this turns out to be important. Every time I define and use a table variable, SQL Server will dynamically generate a name for the primary key. So when I look at the plan for

declare @ids dbo.TypeWithPK;
select * from @ids

I see that it has a primary key named [@ids].[PK__#A079849__3213E83FDB6D7A43]:
PKName

As I’ll show later, this dynamically generated name prevents any kind of query plan forcing. But as of SQL Server 2014, we can include indexes in our table type definitions. More importantly, we can name those indexes:

create type dbo.TypeWithIndex 
    as table ( id int index IX_TypeWithIndex );
go
declare @ids dbo.TypeWithIndex;
select * from @ids;

This has a primary key named [@ids].[IX_TypeWithIndex] which is what we expect.

Plan Forcing is Not Allowed For TVPs with PKs

Where does plan forcing fit in your tool belt? For me, I’ve never used plan forcing as a permanent solution to a problem, but when I see a query that often suffers from suboptimal query plan choices, I look to plan guides to give me some stability while I work at fixing and deploying a permanent solution.

Plan forcing in SQL Server involves specifying a plan for a particular query. But the primary key name for a table variable is always different so the specified query plan is never going to match. In other words SQL Server is never going to use your query plan because your plan includes index [@ids].[PK__#A079849__3213E83FDB6D7A43], but the query it’s compiling has a differently named index like [@ids].[PK__#AA02EED__3213E83FAF123E51].

If you try, this is what that failure looks like:

USE PLAN
If you try to use the USE PLAN query hint, you’ll get error 8712:

Msg 8712, Level 16, State 0, Line 15
Index '@ids.PK__#B305046__3213E83F57A32F24', specified in the USE PLAN hint, does not exist. Specify an existing index, or create an index with the specified name.

Plan Guides
If you try to force the plan by creating a plan guide, you’ll also see message 8712:

select 
from sys.plan_guides
cross apply fn_validate_plan_guide(plan_guide_id)
-- Index '@ids.PK__#BA711C0__3213E83F44A3F2C8', specified in the USE PLAN hint, does not exist. Specify an existing index, or create an index with the specified name.

Query Store
And if you try to force a plan using SQL Server 2016’s Query Store, you’ll see this:

select plan_id, last_force_failure_reason_desc
from sys.query_store_plan
where is_forced_plan = 1
-- last_force_failure_reason_desc = 'NO_INDEX'

Summary

When defining table variables, avoid primary key or unique key constraints. Opt instead for named indexes if you’re using SQL Server 2014 or later. Otherwise, be aware that plan forcing is limited to queries that don’t use these table variables.

5 Comments »

  1. Unfortunately, I’ve discovered that using named indexes on a table parameter doubles the tempdb GAM/PFS allocations because tempdb storage has to be allocated for the both the heap and for the nonclustered index.

    It’s a good reason to consider using Memory-Optimized Table Variables

    Comment by Michael J. Swart — April 27, 2016 @ 4:26 pm

  2. Regarding your comment above you can just add CLUSTERED

    create type dbo.TypeWithIndex
    as table ( id int index IX_TypeWithIndex CLUSTERED);

    Comment by Martin Smith — May 2, 2016 @ 11:49 am

  3. Sweet! I don’t know how I missed that.
    You should have started that comment with “fortunately”.

    Thanks Martin

    Comment by Michael J. Swart — May 2, 2016 @ 11:51 am

  4. A few years back I did a series of articles (see below). When passing long parameter list instead of trying to use tables or other tricks to get bulk data into a procedure. I actually stole the idea from DB2 and Oracle, DB2 uses this method internally to pass up to two million rows in their procedures at the system level.

    The advantage of using actual parameters is that you spent all that time writing in SQL compiler that does all kinds of validations, verifications, checking, and optimizations, so why try to rewrite it inside a stored procedure body? Oh wait! People who do not use the long parameter list never bother with any of that stuff. They simply take what they get and go with it.

    https://www.simple-talk.com/sql/learn-sql-server/values()-and-long-parameter-lists/
    https://www.simple-talk.com/sql/learn-sql-server/values()-and-long-parameter-lists—part-ii/

    Comment by Joe Celko — May 2, 2016 @ 12:08 pm

  5. I’m not sure what you’re trying to say Joe.

    When comparing a long parameter list to table-valued parameters, it seems to me that the table variable is the more natural thing.
    I want to pass a set of data to a procedure the same way I receive a result set from a procedure.

    As far as I can tell, SQL Server TVPs have the same checks/validations/checking as any other solution. What TVPs don’t have is portability across multiple RDBMSs. I know you value that. You and I value different things I think.

    Comment by Michael J. Swart — May 2, 2016 @ 12:48 pm

RSS feed for comments on this post. TrackBack URL

Leave a comment

Powered by WordPress