We have lots of SQL sessions containing the tsf_user procedure taking up lots of CPU time. This only happens when tsf_user is called via Indicium. We can't really put the finger on why this is happening. What could be the cause? Today this even caused SQL server to lock up entirely.
CPU time total and per session:
Best answer by Anne Buit
First of all, it is worth noting that the column command text in this query is not showing the command initiated by Indicium, but is constructed based on the object currently being executed.
It can be difficult to trace this problem to the actual command. There is, to my knowledge, not something like a 'stack trace’ that you can retrieve in which context the function is being called using dynamic management views.
The only way to find the query that's repeatedly calling tsf_user() and consuming all this CPU is by using the Query Store (I'm sure they'll show up in the Top Resource Consuming Queries) or really dive deep with the SQL Profiler / Extended Events.
Alternatively, you could perhaps consider altering the tsf_user() function to be inlineable. I'm pretty sure this will cause the ‘outer’ request not to make a separate query plan and request for the tsf_user() function. This requires at least SQL Server version 2019. Depending on the version of the Software Factory you are using, this would mean replacing with schemabinding with with inline = on in the function header code.
+5Hi Roland,
What query are you using to obtain the command text from these sessions?
Indicium has no reason to call tsf_user() by itself on an end product database. This should always occur in context of another select query (prefilter/expression) or logic execution like a task or a process procedure.
Can you verify that the tsf_user() function on your database is schemabound?
Hi Anne,
I've attached the query.
The tsf_user() function is called in various prefilters, default procedures and tasks throughout the application.
I'm not sure about the function being schemabound or how to accomplish that. We're seeing major performance hits with this function when running the application in the Windows GUI via Indicium which doesn't happen when we use the Windows GUI directly on SQL Server. Unfortunately it's beyond my expertise to thoroughly debug Indicium sessions.
+5First of all, it is worth noting that the column command text in this query is not showing the command initiated by Indicium, but is constructed based on the object currently being executed.
It can be difficult to trace this problem to the actual command. There is, to my knowledge, not something like a 'stack trace’ that you can retrieve in which context the function is being called using dynamic management views.
The only way to find the query that's repeatedly calling tsf_user() and consuming all this CPU is by using the Query Store (I'm sure they'll show up in the Top Resource Consuming Queries) or really dive deep with the SQL Profiler / Extended Events.
Alternatively, you could perhaps consider altering the tsf_user() function to be inlineable. I'm pretty sure this will cause the ‘outer’ request not to make a separate query plan and request for the tsf_user() function. This requires at least SQL Server version 2019. Depending on the version of the Software Factory you are using, this would mean replacing with schemabinding with with inline = on in the function header code.
Those are definitely some valuable tips. Since we're running SQL Server 2019 I started with the last one and altered the function with the “inline = on” option. The function still works. I'll keep monitoring the performance and indeed also use the SQL Server profiler in SSMS. I had just completely forgotten about that tool's existence.
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.
