February 10, 2016 at 9:22 am
Hi everyone,
Hoping someone can offer some advice on the following issue. We have a (inline) table-valued function. This has run with no complaints for a while, although the function has always been slow (using a lot of joins, 2 x union all, pivot in each union etc). It usually takes 50 seconds to run and returns a couple of hundred records, max.
After adding a new field (the PK from an existing join) it now takes 2 mins +. I took the field out and it still runs slow. This is on a production server - the issue doesn't happen on the development server.
Since removing the added field I've tried the following to no avail:
1 - Creating a new function
2 - Running the query on its own
3 - Running query on its own with SELECT TOP 1
4 - Running first third of query (so no use of UNION ALL)
5 - Running query with OPTION(RECOMPILE)
6 - Removing the parameter (INT) and hardcoding value (both via function and running query solo)
My question is - what could cause performance of an ITVF to slow down like this? From my understanding, these types of functions don't cache a plan specific to the function - is this incorrect?
Are there any (safe) quick fixes I can try, or should I just get stuck into performance tuning?
Thankyou
Tindog
February 10, 2016 at 9:37 am
You are right that there is no plan for the iTVF itself. But there are cached plans for all queries using it.
Changing the iTVF invalidated those plans, so new plans were generated. They were obviously not good.
Since you tried a bunch of things and consistently got bad performance, even when changing back to the original iTVF, I suspect that one or more statistics are incorrect in such a way that all plans based on the iTVF become bad. The best way to investigate this is to look at an actual execution plan for one of your tests and try to find operators with a huge difference between estimated and actual row count, or between estimated and actual number of executions.
(If you have SQL Sentry Plan Explorer, use it - one of the tabs shows the esimates and actuals side by side and even color codes the ones with a significant difference)
If you do see a bad estimate, try to track it down to see from which table it originates. Then do an UPDATE STATISTICS on that table (preferably WITH FULLSCAN, but for a big table that may not be viable) and see if that works.
After that, start reading up on the "Ascending key problem".
February 10, 2016 at 9:42 am
Rather than being "stuck" on tuning, why not take this as an opportunity to possibly tune the query overall and post the code? Perhaps it can be reduced below 25 seconds for every run :-).
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
February 10, 2016 at 9:44 am
My first guess is that the original plan was "good enough" in that it ran consistently at 50 seconds. But when you altered things you got a new plan and now it sucks. This can happen. The issue is that you CANNOT get back to that old plan (unless you were on 2016 with it's awesome query store feature!!!). So now you are "stuck" with tuning but this could be a good thing. 🙂
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
February 10, 2016 at 11:40 am
I'd encourage Scott's suggestion. You might be missing something in the code that can be improved.
February 11, 2016 at 4:41 am
Would you mind posting the TVF definition?
I just want to be sure that it is not a multistatement TVF.
Is it defined as
CREATE FUNCTION DBO.iTVF (@Var INT)
RETURNS TABLE
AS
RETURN
SELECT blaadieblah
FROM dbo.mytable
WHERE MYColumn = @var
GO
Or is it defined as
CREATE FUNCTION DBO.MultiTVF (@Var INT)
RETURNS @Multi TABLE (Var INT)
AS
BEGIN
INSERT INTO @Multi (Var )
SELECT blaadieblah
FROM dbo.mytable
WHERE MYColumn = @var
RETURN;
END
GO
Let us have the TVF as well as the query which includes the TVF as the TVF by itself may not give a clue as to what is occuring.
An execution plan would be great.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply