June 10, 2016 at 3:40 am
@jacob Wilkins,
No unfortunately I don't, but in the attached plans are the same, but execution times differ greatly.
Our hardware has been checked and all is good.
I restarted the service early this morning and since then, performance is fine. We are now regularly measuring the performance of the function I posted.
Once a decent baseline has been collected, we'll turn on one of two SQL agent jobs, that we, scientifically speaking "Have a bad feeling about." This'll gives us an idea if data increase is messing things up. I'll post back about the possible evil jobs.
Jeff, I've been reading up on parameter sniffing, but haven't found anything relating to scalar UDFs that do not access data. Have you written anything about it or do you know of a good article?
The UDFs are used in views, SSIS datasources, stored procedues, persisted computed columns and non persisted computed columns. Including in some WHERE clauses and JOINs. :crazy:
Locking has already been ruled out.
We'll be spending a lot of time rewriting our scalar functions to TVFs. We still need the reusable code. Or do TVFs have the same issues?
Parameter sniffing resources:
June 10, 2016 at 8:09 am
Well, the attached plans aren't for acceptably-performing query with UDF and unacceptably performing query with UDF.
They're for query with UDF and query with inline equivalent.
It's expected that on large data sets there will be a significant overhead for the version with the UDF, as you've seen, because the UDF is getting called for each row and each of those calls has overhead (incidentally, the plans are not exactly the same; they have the same shape but they differ in what is being done in the compute scalar, as expected).
The scope of my response was pretty narrow; I was just supplying a potential explanation for why a query using a UDF might suddenly become much slower.
Without the plans of the original problematic queries, though, it's difficult to provide much more.
Have you been able to recreate the slowness with a query referencing a persisted computed column? If so, that plan would certainly be helpful.
Cheers!
June 10, 2016 at 8:17 am
Jacob Wilkins (6/10/2016)
Well, the attached plans aren't for acceptably-performing query with UDF and unacceptably performing query with UDF.They're for query with UDF and query with inline equivalent.
It's expected that on large data sets there will be a significant overhead for the version with the UDF, as you've seen, because the UDF is getting called for each row and each of those calls has overhead (incidentally, the plans are not exactly the same; they have the same shape but they differ in what is being done in the compute scalar, as expected).
The scope of my response was pretty narrow; I was just supplying a potential explanation for why a query using a UDF might suddenly become much slower.
Without the plans of the original problematic queries, though, it's difficult to provide much more.
Have you been able to recreate the slowness with a query referencing a persisted computed column? If so, that plan would certainly be helpful.
Cheers!
What plans?
June 10, 2016 at 8:19 am
DennisPost (6/10/2016)
@Jeff,@jeff, I've been reading up on parameter sniffing, but haven't found anything relating to scalar UDFs that do not access data. Have you written anything about it or do you know of a good article?
What I've been trying to tell you and Sergiy has now joined in with the same line of thinking, it's probably NOT the UDF that's the problem. Think about it. You had perf problems and added persisted columns for the UDF. That sped things up. 15 minutes later, they were slow again so you removed the columns and that sped things up again for another "15" minutes.
You need to look at the larger picture. Seems like the "bad" parameter sniffing is occurring in the proc or view or whatever that's using the function, not the function itself.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 10, 2016 at 8:40 am
Lynn Pettis (6/10/2016)
Jacob Wilkins (6/10/2016)
Well, the attached plans aren't for acceptably-performing query with UDF and unacceptably performing query with UDF.They're for query with UDF and query with inline equivalent.
It's expected that on large data sets there will be a significant overhead for the version with the UDF, as you've seen, because the UDF is getting called for each row and each of those calls has overhead (incidentally, the plans are not exactly the same; they have the same shape but they differ in what is being done in the compute scalar, as expected).
The scope of my response was pretty narrow; I was just supplying a potential explanation for why a query using a UDF might suddenly become much slower.
Without the plans of the original problematic queries, though, it's difficult to provide much more.
Have you been able to recreate the slowness with a query referencing a persisted computed column? If so, that plan would certainly be helpful.
Cheers!
What plans?
The ones here: http://www.sqlservercentral.com/Forums/FindPost1793426.aspx
Cheers!
June 10, 2016 at 1:15 pm
Comment removed.
I missed the whole second page of this discussion...
-- Itzik Ben-Gan 2001
June 29, 2016 at 9:13 am
We haven't had any performance problems for a while now, though we still use the UDFs.
Some of the UDFs were rewritten and perform much better than their previous versions.
Following up on the bad parameter sniffing, I started rewriting queries with huge memory grants vs maximum memory used.
Running this every now and then, revealed some monsters.
SELECTDBName= DB_NAME(ER.database_id),
ProgramName= ISNULL(J.name, ES.[program_name]),
EQMG.session_id,
MemGrantMB= EQMG.granted_memory_kb / 1024,
MaxMemUsedMB= max_used_memory_kb / 1024,
ER.blocking_session_id,
ER.command,
ER.[status],
EST.[text],
EQP.query_plan
FROMsys.dm_exec_query_memory_grants EQMG
LEFT JOIN sys.dm_exec_sessions ES
ON ES.session_id = EQMG.session_id
LEFT JOIN sys.dm_exec_requests ER
ON ES.session_id = ER.session_id
OUTER APPLY sys.dm_exec_SQL_text(ER.[sql_handle]) EST
OUTER APPLY sys.dm_exec_query_plan(ER.[plan_handle]) EQP
CROSS APPLY
(
VALUES(
CASE
WHEN RTRIM(ES.[Program_Name]) LIKE 'SQLAgent - TSQL JobStep (Job %' THEN
CAST(CONVERT(VARBINARY(MAX),'0x' + SUBSTRING(RTRIM(ES.[Program_Name]), 32, 32),1) AS uniqueidentifier)
ELSE NULL
END
)
) Val1(UqID)
OUTER APPLY
(
SELECTDISTINCT Name
FROMMSDB.dbo.SysJobs
WHEREJob_ID = Val1.UqID
) J
WHEREEQMG.granted_memory_kb / NULLIF(EQMG.max_used_memory_kb, 0) > 2
I just read Paul Whites is-there-any-benefit-to-schemabinding-a-function-beyond-halloween-protection?, now I have more rewriting to do. :ermm:
This query returns list of all non-data accessing functions that need attention.
SELECTSchmName= SCHEMA_NAME(O.[schema_id]),
FuncName= O.name,
o.[type],
FuncType= O.type_desc,
IsDeterministic= OBJECTPROPERTYEX(O.[object_id], 'IsDeterministic'),
IsPrecise= OBJECTPROPERTYEX(O.[object_id], 'IsPrecise'),
IsSystemVerified= OBJECTPROPERTYEX(O.[object_id], 'IsSystemVerified'),
UserDataAccess= OBJECTPROPERTYEX(O.[object_id], 'UserDataAccess'),
SystemDataAccess= OBJECTPROPERTYEX(O.[object_id], 'SystemDataAccess')
FROMsys.objects O
LEFT JOIN sys.sql_expression_dependencies SEQ
ON O.[object_id] = SEQ.referencing_id
WHEREO.[type] IN ('FN', 'TF', 'IF')
AND SEQ.referencing_id IS NULL
AND (
OBJECTPROPERTYEX(O.[object_id], 'UserDataAccess') = 1
OR OBJECTPROPERTYEX(O.[object_id], 'SystemDataAccess') = 1
)
ORDERBY FuncName
Thanks for everyones help!
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply