You’ve probably heard about parameter sniffing
But there’s an even more insidious menace out there: Parameter Snorting.
It goes beyond ordinary parameter sniffing, where SQL at least tried to come up with a good plan for something once upon a compile. In these cases, it just plain gives up and throws a garbage number at you. You’ve seen it happen countless times with Table Variables, Local Variables, non-SARGable queries, catch-all queries, and many more poorly thunked query patterns.
While Scalar and Multi-Statement Table Valued Functions get lot of abuse around here (and rightly so), Inline Table Valued Functions aren’t perfect either. In fact, they can snort your parameters just as hard as all the rest.
Heck, they may even huff them.
First, let’s get Jon Skeet and his impersonators
In the Stack Overflow database export, there are four people in the Users table that have a DisplayName like Jon Skeet. Note that this query is most definitely not SARGable, but it gets the job done:
|
1 2 3 |
SELECT u.Id, u.DisplayName, u.Reputation FROM dbo.Users AS u WHERE u.DisplayName LIKE '%Jon%Skeet%'; |
The results:
If we run a query like that, it turns out pretty alright. No problems here; at least none that couldn’t be solved if I could be bothered to create a covering index that starts with DisplayName. The real Jon Skeet is obvious enough. He’s the one that has a Reputation that looks like a PowerBall jackpot.
Put that query in an inline function
Let’s look at a function I use in a few demos. Awkwardly, I use it to demonstrate how much better Inline Table Valued Functions are. I never said perfect! Call my lawyer. Whatever.
|
1 2 3 4 5 6 7 8 9 10 |
CREATE FUNCTION dbo.BC_ITVF ( @uid INT ) RETURNS TABLE WITH SCHEMABINDING AS RETURN SELECT COUNT_BIG(*) AS BadgeCount FROM dbo.Badges AS b WHERE b.UserId = @uid GROUP BY b.UserId; GO |
Simple enough, right? Return a count from the Badges table based on UserId. There’s only one statement here, so this function goes inline – the best kind of function.
Let’s go on a date, just me and Jon Skeet. Let’s feed the function literal values because parameters are lovingly tended to, and they get their own special fancy plan.
|
1 2 3 4 5 |
SELECT * --Jon Skeet FROM dbo.BC_ITVF(22656) AS bi SELECT * -- His Mentor FROM dbo.BC_ITVF(4338144) AS bi |
The first plan (the real Jon Skeet) has a plan that includes a stream aggregate because he has a boatload of badges.
Jon Skeet’s Mentor has, uh, two. Which is still probably more than you have, so stop snickering. He gets a slightly different plan that doesn’t include a stream aggregate.
Uh oh – that sounds like parameter sniffing
One query, two plans depending on parameters – ah, it’s our old friend, parameter sniffing. When you see that, you should also try running the query with local variables to see another potential problem:
|
1 2 3 4 5 6 7 8 9 |
DECLARE @Id INT = 22656 SELECT * FROM dbo.BC_ITVF(@Id) AS bi GO DECLARE @Id INT = 4338144 SELECT * FROM dbo.BC_ITVF(@Id) AS bi GO |
Then our plans look like this:
We’ve been snorted. Snorted real hard. Both Skeets – the big one and the little one – are getting the local variable treatment. SQL Server’s optimizing for a relatively small number of badges, and neither plan includes the stream aggregate.
That means we can use a RECOMPILE hint to go back to the original plans with literals. We can also use unsafe dynamic SQL.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
DECLARE @Id INT = 22656 DECLARE @SQL NVARCHAR(1000) SELECT @SQL = N' SELECT * FROM dbo.BC_ITVF( ' + CONVERT(NVARCHAR(10), @Id) + ') AS bi' EXEC sp_executesql @SQL GO DECLARE @Id INT = 4338144 DECLARE @SQL NVARCHAR(1000) SELECT @SQL = N' SELECT * FROM dbo.BC_ITVF( ' + CONVERT(NVARCHAR(10), @Id) + ') AS bi' EXEC sp_executesql @SQL GO |
If we use parameterized SQL, we used the a cached plan for whichever value goes in first. This is a lot like what happens with dynamic SQL and filtered indexes.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
DECLARE @Id INT = 22656 DECLARE @SQL NVARCHAR(1000) SELECT @SQL = N' SELECT * FROM dbo.BC_ITVF(@i_Id) AS bi' EXEC sp_executesql @SQL, N'@i_Id INT', @i_Id = @Id GO DECLARE @Id INT = 4338144 DECLARE @SQL NVARCHAR(1000) SELECT @SQL = N' SELECT * FROM dbo.BC_ITVF(@i_Id) AS bi' EXEC sp_executesql @SQL, N'@i_Id INT', @i_Id = @Id GO |
Icky
While I’d much rather see you using Inline Table Valued Functions, because they are better than the alternatives somewhere in the neighborhood of 99% of the time, you should be aware of this potential performance hit.
Thanks for reading!
21 Comments. Leave new
The whole time I read this, all I could think is ‘Is this real life?!’
Great article, Erik. Thanks!
It is indeed! Thanks!
“One query, two plans depending on parameters – ah, it’s our old friend, parameter sniffing. ” Isn’t parameter sniffing the exact opposite – a single plan using the first value in?
Regan – not exactly. If I run several different parameters and they all produce the same plan when they’re used as literals (and I’m simplifying a lot here), then parameter sniffing isn’t a problem. But you’re on the right track!
We have seriously skewed data, plus an ORM ( life can be cruel ). So you update statistics like crazy add some missing indexes ( sp_blitzIndex ) and eventually filtered statistics and find……without option(recompile) the new indexes and filtered stats aren’t used ( sql gets a “good enough” plan from cache ). I don’t think I’ve see use of inline functions much here, just some odd ORM generated stuff like that below. Our current efforts to keep the application usable include: minor, very slow addition of query hints in the ORM ( dev doesn’t understand it well enough to move very quickly on that ), putting the aspstate DB in memory on a separate sql 2016 box, going to all SSD drives ( netapp ), filtered stats, targeted plan guides to add option(recompile) until the ORM can be tweaked to do more of that, automated FreeProcCache 3 times a week and wholesale move to Sql 2016 in the near future.
FROM (SELECT MIN(ReminderWorkItem02.REMIND_DATE) AS DerivedTable01_8 FROM WORK_QUEUE_ITEM AS ReminderWorkItem02 WHERE ((ReminderWorkItem02.ACCOUNT_ID = Account13.ACCOUNT_ID AND ReminderWorkItem02.BUSINESS_PROCESS_STATUS = @DerivedTable01_BUSINESS_PROCESS_STATUS47 AND ReminderWorkItem02.REMIND_DATE IS NOT NULL ) AND ((ReminderWorkItem02.CONCRETE_TYPE IN ( @DerivedTable01_68))))) AS ScalarQueryTable) AS AccountReminderDate
SELECT DISTINCT TOP 2000 AccountOwnershipDocSummary02.ACCOUNT_MODIFIED_MANUFACTURER_ID AS ManufacturerId,AccountOwnershipDocSummary02.CUSTOM_ATTRIBUTE_1 AS AccountNumber,AccountOwnershipDocSummary02.CUSTOM_ATTRIBUTE_2 AS LoanNumber,AccountOwnershipDocSummary02.CUSTOM_ATTRIBUTE_3 AS LoanSuffix,AccountOwnershipDocSummary02.CUSTOM_ATTRIBUTE_4 AS Branch,AccountOwnershipDocSummary02.BORROWER_FULL_NAMES AS Borrowers,AccountOwnershipDocSummary02.EXPECTED_TITLING_STATE_ABBR AS ExpectedTitlingState,AccountOwnershipDocSummary02.ACCOUNT_ID AS AccountPropertyId,Account13.FINANCED_DATE AS FinancedDate,Account13.CREATED_DATE_TIME AS CreatedDate,Account13.CATEGORY AS Category,Account13.SUB_CATEGORY AS SubCategory,FollowupEntity16.ENTITY_CODE AS DealerId,AccountOwnershipDocSummary02.STATUS AS Status,AccountOwnershipDocSummary02.BUSINESS_UNIT_LONG_NAME AS BusinessUnit,AccountOwnershipDocSummary02.BUSINESS_UNIT_SHORT_NAME AS BusinessUnitShortName,Account13.BOOKED_DATE AS BookedDate,AccountOwnershipDocSummary02.USER_DEFINED_1 AS UserDefined1,AccountOwnershipDocSummary02.USER_DEFINED_2 AS UserDefined2,AccountOwnershipDocSummary02.USER_DEFINED_3 AS UserDefined3,Account13.RECOVERY_STATUS AS RecoveryType,LienholderStatusCode14.STATUS_CODE AS LienholderStatusCode,(SELECT DerivedTable01_8 FROM (SELECT MIN(ReminderWorkItem02.REMIND_DATE) AS DerivedTable01_8 FROM WORK_QUEUE_ITEM AS ReminderWorkItem02 WHERE ((ReminderWorkItem02.ACCOUNT_ID = Account13.ACCOUNT_ID AND ReminderWorkItem02.BUSINESS_PROCESS_STATUS = @DerivedTable01_BUSINESS_PROCESS_STATUS47 AND ReminderWorkItem02.REMIND_DATE IS NOT NULL ) AND ((ReminderWorkItem02.CONCRETE_TYPE IN ( @DerivedTable01_68))))) AS ScalarQueryTable) AS AccountReminderDate,Account13.PERFECTED_DATE_TIME AS PerfectedDate,AccountProperty17.VEHICLE_TYPE AS CollateralType,Account13.AMOUNT_FINANCED AS AmountFinanced,AccountOwnershipDocSummary02.ID AS AccountOwnershipDocSummary02_ID1 FROM ACCOUNT_OWNERSHIP_DOC_SUMMARY AS AccountOwnershipDocSummary02 INNER JOIN (ACCOUNT AS Account13 LEFT OUTER JOIN LIENHOLDER_STATUS_CODE AS LienholderStatusCode14 ON Account13.LIENHOLDER_STATUS_CODE_ID=LienholderStatusCode14.LIENHOLDER_STATUS_CODE_ID LEFT OUTER JOIN LEGAL_ENTITY AS FollowupEntity16 ON Account13.FOLLOWUP_ENTITYLEGAL_ENTITY_ID=FollowupEntity16.LEGAL_ENTITY_ID INNER JOIN PROPERTY AS AccountProperty17 ON Account13.ACCOUNT_ID=AccountProperty17.ACCOUNT_ID) ON AccountOwnershipDocSummary02.ACCOUNT_ID=Account13.ACCOUNT_ID WHERE ((AccountOwnershipDocSummary02.CLIENT_ID = @DerivedTable01_CLIENT_ID30 AND Account13.CLIENT_ID = @DerivedTable01_CLIENT_ID51 AND ((AccountOwnershipDocSummary02.CUSTOM_ATTRIBUTE_1 LIKE @DerivedTable01_CUSTOM_ATTRIBUTE_172)) AND (LienholderStatusCode14.STATUS_CODE IN ( @DerivedTable01_STATUS_CODE93, @DerivedTable01_STATUS_CODE104)) AND ((AccountOwnershipDocSummary02.STATUS = @DerivedTable01_STATUS125) OR (AccountOwnershipDocSummary02.STATUS = @DerivedTable01_STATUS146)))) ORDER BY 9,27
Oh wow, that’s some ugly stuff you got there. ORDER BY 9, 27! How do you even pick that?
The query would be sorted by the 9th and 27 column as selected. Something I found recently was that a slow query was ordering by an “id” column that meant nothing to customers and wasn’t even displayed in the application. Removing the sort dropped run-time from minutes to 5 seconds — but again, getting dev to change things like that is hard and slow.
Heh, yeah, I know what it does. It’s just so odd for someone to sit down and be like “yeah, if we order by columns 9 and 27, everything will be perfect”.
Funny thing about ordering: most people don’t care how data comes back to them. They’d rather get it and be able to sort it their own ways.
And the best part, when someone removes or adds some columns to the query, and 9 and 27 point to completely different columns. There’s really no valid reason to treat column names like array indexes or rely on positional properties. Even if this were code-generated you’d use the names.
I like this site normally, but this article is a bit confused and incorrect; nothing is happening there that would not be happening if you removed the function and executed the SQL literally. Because inline TVFs have no execution plan, so it can’t sniff parameters; the statement calling it has the plan. And the statement calling is specifying literals, not parameters when they get “sniffed,” and the statement calling it is specifying a variable, not a literal, when it gets the “unknown” execution plan. Exactly like any other SQL statement even if you didn’t use an ITVF.
In fact, the author is really confused about parameter sniffing even is. If you do this:
exec something 1
exec something 2
and see two different plans that is literally the OPPOSITE of parameter sniffing; parameter sniffing means the first time it gets compiled it gets compiled with a plan optimal for the parameters passed in, and then subsequent calls (with different parameters) then use that same plan. If two calls with two parameters get different plans then the plan is being generated each time. This could be due to something like OPTION RECOMPILE, but here it’s because its an ITVF where the execution plan comes from the caller, it does not have its own.
“inline TVFs have no execution plan” – they do when called by themselves, as shown in the post here.
You may want to reread the post – you’re missing a few vital points. Thanks for stopping by, though.
What am I missing? I think I explained pretty clearly?
The plan you see is the caller sql’s plan, not the function itself. Because again the function doesn’t have a plan any more than a view does. It’s just providing SQL that the caller incorporates into its SQL.
Hi, author here!
I promise I’m not confused, and we agree on most of your points.
1. Yes, no ‘plan’, just like views and CTEs. Except when you call them. They’re inlined to the query.
2. This isn’t parameter sniffing, that’s why I didn’t call it that, but it’s a similar concept. That’s how I’m choosing to categorize it and relate it to people reading. Hence ‘parameter snorting’ instead.
3. Expected behavior to you is a surprise to some other people. We’ve written a lot about the local variable effect on adhoc queries. This is an extension of that, but specifically about iTVF behavior. If I remove it, it’s not a post about iTVF behavior anymore, is it?
This is specifically about how iTVFs behave differently under different calling circumstances.
So let me know what needs correcting, and I’ll test it out.
Thanks!
First, sorry about the snarky tone of my comment. Not sure what kind of mood I was in when I wrote that (too tired maybe?) but re-reading it again it definitely is uncalled for.
I think the main take-away is that ITVFs parameters are not “traditional” parameters for the reasons we mentioned; they are essentially “pass-throughs” for whatever is provided as input. So, the sniffing (or optimizing for a literal or for unknown) never happens within the UDF, it occurs at the caller’s level, as we’ve discussed here and both agree upon.
I just don’t see this as a “drawback” of inline UDFs — they are behaving exactly the same as writing the SQL inline in every respect, so I suppose my issue is with presenting it that way.
Well, fine, but I’ll be expecting something extra nice for Valentine’s Day.
I don’t think it’s presented as a drawback, necessarily, just something to be aware of. Though it could be a drawback if it resulted in a really poor plan choice.
Another minor note – it looks to me like both query plans have a scalar Stream Aggregate that follows the Gather Streams. It’s just that one of them also has an additional parallelized Stream Aggregate that precedes the Gather Streams.
Thanks for the post, but I find myself agreeing with Jeff’s comments — I was reading through trying to understand what’s different with iTVFs, but it’s just regular query behavior. It was a little confusing.
Jeez, that’s no good. I thought I presented things pretty clearly.
Let me know which parts were confusing, and I’ll re-work the post when I have some time.
Thanks!
I’m not who you asked, and I’m 5 years late, but I was also a bit confused by this post for the same reasons. Some fragments that I think confused me personally:
> One query, two plans depending on parameters – ah, it’s our old friend, parameter sniffing.
My understanding was that this was a requirement for parameter sniffing when run as standalone queries, and the parameter sniffing then manifested in the SP calls. But here the example already has the iTVF calls and shows the different query plans, so the mention of parameter sniffing had me questioning my understanding of what “parameter sniffing” means, and I spent the rest of the article waiting for the other shoe to drop, which didn’t really happen.
> We’ve been snorted. Snorted real hard. Both Skeets – the big one and the little one – are getting the local variable treatment. SQL Server’s optimizing for a relatively small number of badges, and neither plan includes the stream aggregate.
At this point I was wondering how this was different to a regular query, or why this was worth pointing out. My assumption when reading posts like this is that if I am confused like that, I’m likely missing some vital piece of information so I kept trying to find a difference. I think the term “snorted” being added without much explanation of what it means didn’t help, but that term seems to be important to the article (And introducing it without a dictionary definition is probably best for this kind of blog format imho)
Finally, the last statement
> While I’d much rather see you using Inline Table Valued Functions, because they are better than the alternatives somewhere in the neighborhood of 99% of the time, you should be aware of this potential performance hit.
Left me somewhat confused: What’s the alternative that iTVFs are being weighted against here? How is this different from a regular query?
Maybe some clarification on the sniffing part (Maybe something like “We have the potential for parameter sniffing”, if I understand correctly?), and in the last section a clarification of what alternatives there are. How are iTVFs the problem here (And not local variables, or parameterized SQL), and what would be a better alternative to iTVFs (While keeping the local variables, or parameterized SQL).
Thanks for the article, I enjoyed reading it!
If you have significant data value skew you just cannot code “business as usual” stuff (which I note is often horrible with perfectly distributed data at virtually all of the clients I come across). You simply MUST do some things differently or you may well not be able to buy big enough hardware to run your database on!