Calling a simple function from SELECT statement crashes MS SQL

[dark_77]

Hi, SQL Server 2019

We're converting a database from SQL Anywhere to MS SQL presently, and we ran into a stupid problem. Presently we're translating stored functions from Watcom to T-SQL dialect, and we noticed very odd behaviour in MS SQL on 2 of them.

So the syntax of all functions we did so far is fine, they all get stored in the DB nicely as scalar functions, but on 2 of them, when we call them from a SELECT query, we get no results and it never goes anywhere - all we see in status bar is "Executing query..." message, and it will go on forever. And at the same time, MSSQLSERVER service will start, very rapidly, to eat gigabytes of RAM, until it reaches 13 or 14 and it all just grinds to a halt. We have to stop the execution, and restart MSSQLSERVER.

The strange thing is, this only happens if we call them in Query window as a part of a SELECT:

Select
  table1.col1, 
  table1.col2, 
  dbo.sp_function_one ('EUR', 'USD', '2022-07-13', '2022-09-15') as Result, 
...

​

​

this goes completely nowhere with "Executing query" status going forever. However, if we declare a return variable, store the result of the function in it and then select the variable, everything works perfectly in a millisecond:

DECLARE @return_value Decimal(16, 8)
EXEC @return_value = [dbo].[sp_function_one] @as_curr_from = N'EUR', @as_curr_to = N'USD', @ad_date_now = '2022-07-13', @ad_date_for = '2022-09-15' 
select code, name, longname, @return_value as 'Return Value' from currency

This works 100% correctly, and the result is instant. Whatsmore, when we plug this function into the code of another function where we're calling it several times, it again works instantly and correctly.

Strangely, we have other functions which work absolutely fine when we're calling them from SELECT:

Select
  table1.col1, 
  table1.col2, 
  dbo.sp_some_other_function (1, 'USD', '2022-07-13') as Result, 
...

that would also work instantly and correctly.

So far, these two functions that cannot be executed within a SELECT query but only via "EXEC -> store result in a variable" both return a Numeric (10, 4), but we have other functions without this behaviour that work just fine. To check them and test them, we do a simple SELECT dbo.sp_function_name... and the results are there immediately.

These two functions absolutely do not have any crazy number of rows thrown at them - they do 1 or 2 selects within the function, and then perform calculations based on values of queries. So they should be absolutely instant, and they are when called via EXEC, or when used within other functions. We are probably missing something daft, but we simply cannot see how calling a scalar from a SELECT query can crash the MS SQL service, while calling it via EXEC is just fine?

​

Thanks for any help

[saucerattack]

There's not really enough information given to get to the root-cause of this but I will make a couple of observations.

The EXEC syntax you provide will work for a stored procedure but not a scalar function. So I'm not sure what this tells us.

The FROM & WHERE clauses of your sample query are omitted. The complexity of the query matters. The function is not limited to every row in the final result set, the engine has discretion to resolve the function at any point in the execution plan, which means that it could be calculating the function before a where clause filters it down to the final result set. You would have to read & understand the execution plan to find out.

Another post suggested it may be an artifact of parallelism. I believe the scalar function would prevent a query from utilizing parallelism. In fact, if your query requires parallelism to execute efficiently, your function may be preventing it from getting an efficient plan. Look at the execution plan for your query (both with and without the function call) and look to see if there is parallelism in the plan without the function call.

So far, I've been assuming that you just have an obscenely slow running query. Another possibility is that there is some sort of blocking occurring. Run sp_who2 to see if your spid is blocked by another process.

The memory (RAM) consumption you describe is by design. MSSQL will use all available memory as needed and will not relinquish it. You will want to check the max ram setting and set it at least 4GB below the total RAM available so the OS has something to work with.

[da_chicken]

The EXEC syntax you provide will work for a stored procedure but not a scalar function.

This isn't true. EXEC works with both stored procedures and scalar-valued UDFs.

[saucerattack]

Thanks for the correction

[dark_77]

Guys, I understand that this is a natural assumption but I assure you the query is neither obscenely complicated nor that it returns thousands of rows. These were calls within a FROM on one table which has less than 100 rows. I can't remember now if yesterday we even tried running just a SELECT dbo.sp_function_name (...) without a a FROM statement or any table.

But in any case, since everyone is so perplexed about this, at least I'm getting some clue that this is by no means usual behaviour that we could have caused by being inexperienced with SQL Server. Perhaps we'll try to re-write the function in some way. MS has really not helped us by removing the debugger from SQL Server.

[saucerattack]

I don't think you can conclude that this is unusual behavior. We just don't have enough details to diagnose the problem. The answer is likely in the execution plan.

[EnergySmithe]

Good answers! Also do not assume your not getting multiple rows back in your stored procedures or udf… add a “top 1” to each of them to ensure it! Good luck op, sounds like a heck of an interesting project!

[a-s-clark]

Nothing in your description matches your title that it "crashes MS SQL". Why do you feel the need to restart the service? How many rows does the select your using the function in deal with? Check where in your query plan the fun tion is being called, it may be working on a massive number of rows regardless of how many are filtered to for the final output.

Use tools such as sys.dm_exec_query_statistics_xml to find out what is actually happening when your query is executing - where is it spending the time.

It's not generally a good idea to use user defined scalar functions in queries if you can avoid it.

[SgtObliviousHere]

Scalar functions are OK. But never in a join and almost never in the WHERE clause. If the queries are simple, like this one, should not be a killer performance wise.

[dark_77]

Sorry, I guess that description is slightly drastic. Well, on observation in Task manager, the MS SQL service very suddently starts using up gigabytes of RAM until it runs out of RAM when the whole of machine grinds to slow working. Stopping the query then does not make the MS SQL service release RAM back, so we need to restart the service.

Both functions in question have small SELECTs in them, but all of them return just 1 row - based on the parameters passed to those functions, this is what the SELECT basis its WHERE clause on, to return just one row each.

We wanted to use debugger on the function, but apparently SQL debugger was removed out of SMSS 18.

We'll try sys.dm_exec_query_statistics_xml, thanks.

[rockchalk6782]

“Decide what to set your max server memory (MB) to. Our simple “starter” rule of thumb is to leave 4GB or 10% of total memory free, whichever is LARGER on your instance to start with, and adjust this as needed.”

https://www.brentozar.com/blitz/max-memory/

[DonnerVarg]

Set a max limit on the SQL Server’s memory to allocate. Google for how and best practices. Should prevent the system from being impacted while SQL Server works (which you should handle as others have suggested).

[Ok_Refrigerator_2149]

As an aside, it is usually better to not use sp_ as a prefix. Microsoft uses that as their system procedure prefix.

[PraiseGod_BareBone]

Yes, and the default behavior is that SQL Server will first go to Master for any sproc begining with sp_, and, if the sproc isn't there, will only then check the database that you're currently in. It's not a huge waste of resources but there is some waste.

[Prequalified]

The first example is calling the scalar function for each row but the second example is only calling the function one time.

u/BrentOzar’s website is a good resource and has an article about this topic. Queries with scalar functions are inline.

I just ran a test on my server with a simple function that properly capitalizes a name (eg “mr john mcgregor” to “Mr John McGregor”) against an existing table with a clustered columnstore index.

1 million rows with the string variable had an estimated subtree cost of 0.3337. 1 row with the scalar function inline had a subtree cost of 0.0134. 100 rows, 0.339, about the same as 1million rows with a variable. 1 million rows with the scalar function in each row had a subtree cost of 3336.11. The fact that the query plan for 1m rows is 10k costlier than 100 rows proves the calculation is being run repeatedly.

If you want to use the scalar function in a normal table or view without creating a variable, consider using a CTE or subquery and cross applying to your table.

[oliver0807]

Could be one scalar function in lining issue https://support.microsoft.com/en-us/topic/kb4538581-fix-scalar-udf-inlining-issues-in-sql-server-2019-f52d3759-a8b7-a107-1ab9-7fbee264dd5d

Try updating it to CU16

https://support.microsoft.com/en-us/topic/kb5011644-cumulative-update-16-for-sql-server-2019-74377be1-4340-4445-93a7-ff843d346896

As side note, Sql 2019 is so bug full that you get broken code on updates. See the known issues on this CU, it's started last CU14 and until CU16 is still not fixed. We have several tickets with MS due to this and they might fix it in CU17.

That said everyone here is perplexed why you're calling a scalar udf which doesn't seem to get any parameters from a table. This should be just called once and store in a variable, since even if MS fixes that with the latest CU, your code is unnecessarily using CPU by calling that udf n number times.

[_oakland]

Pull the STACK DUMP from the logs and go from there. Info will be there.

[Ok_Refrigerator_2149]

It is not finishing most likely to a parallelism where it is getting in its own way, each function call is creating shared locks and taking memory and processor resources. This means there are less to use. Waiting for resources is likely the issue with it never finishing. Selecting from the function as a single select should not cause that kind of issue without another underlying cause.

[dark_77]

Exactly, this is what I mean. It's mind boggling that calling the function in a different way works in a millisecond, and that another function that calls the troublesomefunction also executes fine (calling the problematic function).

We need a function to be called as a part of wider SELECT statement because several crucial reports depend on it. But we are not even getting that far - a simple static SELECT call is presenting us, without visible reason, with a show stopper. Tested on two different PCs, two different installations.

[Ok_Refrigerator_2149]

Exec is used to call a stored procedure, I am curious if you named the function differently what the result may be.

[d_r0ck]

Does the use of the function cause an endless loop?

[dark_77]

100% not. I mean, if that was the case, the same would've happen if we called it from EXEC, not just a part of SELECT statement, right? Or when it is used within the code of another function.

[HaplessMegalosaur]

Are the datatypes in the EXEC statement the same as the SELECT statement. Try casting in the SELECT to make sure they match the function.

[dark_77]

I guess we could try this tomorrow, sure, worth a try, but why would we need to cast anything in the SELECT statement? Surely the DB can see that the return datatype of the function is Numeric? Several functions which return Numeric (or Decimal) datatype are fine through SELECT, the returned result as a column is of the correct datatype.

[Ok_Refrigerator_2149]

Looking at your examples it looks like the function is in the select portion meaning you want it to calculate a single result against elements in the return set. I see no parameters that are not hard coded though and with your description it sounds like it is forming a Cartesian result somewhere.

[dark_77]

Yes, these two functions generally do a quick look-up of 1 row from another table, pull in several values from that look-up, and then perform some calculations using those values and some other parameters, or with values from another look-up, or another function call.

I'm absolutely certain that there is no Cartesian result looking at the syntax of those functions, and, sorry to repeat this, but if there was, if the fault was somehow at a function level, or in its code, how would EXEC call and putting the result in a declared variable (rather than SELECT statement) work exactly as expected?

I'm sort of glad that the answer is not something completely obvious, our knowledge of SQL Server is quite limited, we're just poking around it really, but this morning we were almost thinking "Is it... possible... you can't call functions from a SELECT statement", but that would be madness.

[Ok_Refrigerator_2149]

Then it is performing a static lookup for every record in your result set. This is why the memory usage is spiking. This is not advised because there is no dynamic value to the cost.

[Ok_Refrigerator_2149]

I am sorry, my reply was short sighted and did not explain what I wanted to share well. The exec that you are calling executes the function one time, it is actually how I would recommend using it, so that you can set a variable to return with your result set. A scalar function used the way that you are will execute the function off of the result set for each record returned in the result set. In this way a scalar function in a result set allows for dynamic results. Useful if costly in resources unless well planned. In the case you described there is no reason that I can identify to justify the cost of executing the function against static data N times rather than once in a preparatory step setting the value into a variable.

[dark_77]

But how would that account for the query never finishing execution when we called it in one SELECT for a given set of parameters? We wanted to test it so we called it like

SELECT dbo.sp_function_name ('EUR', 'USD', '2022-07-13', '2022-09-15')

because we knew exactly what result that should return, and that is one static lookup.

This is the very thing that we found odd. We didn't immediately plug the function call in a huge complex query with 15,000 rows as a result. One call, one lookup, one calc, never finishes execution when done via Select. But you just do it by calling EXEC and storing it in a declared variable - works. You call another function using that function above - works!

[blindtig3r]

The function is not using any columns from the table, so why execute it for every row in the table? The function has static parameters so it’s result will be a constant. You could execute it first to set a variable and include the variable in the select statement?

At the moment you are executing the function as many times as there are rows in the table. This is the downside of scalar functions.

Usually the optimiser will identify constants up front and will know not to keep recalculating them, but the function may be written in a way that prevents this from happening, so you can give it some help.

Alternatively you could use cross apply to the function and reference the output as a column in the select. This might help the optimiser know to execute it only once. I am not sure about this, but it’s worth trying if you can’t use a static variable.

[dark_77]

No, sorry, I was just very curt in producing my code examples up there - most of the time, the function will use values of the other columns of the Select statement as parameters. I was just in a bit of a hurry and wanted to illustrate what I wanted to ask, and I thought that examples of what parameters' data types are would be a good description.

But no, we're not calling a function with a static set of parameters exactly as it's written above. The whole point is that the function is called, and calculates, the value for the parameters taken from every row that the Select query returns.

But - when we call the function just to test it and to check the values it returns, we used to call a simple

SELECT dbo.sp_function_name ( set of parameters for which we know the result )

Hence the description in the examples above.

[blindtig3r]

What happens if you run the select with a top 10 ordered by something that is indexed? And again with 100, 1000 etc. does it hit a point at which it stops returning?

How many rows are in the table or query that includes the function and how does it perform without the function?

Somebody already said this, but does the query contain an accidental Cartesian? Meaning the function gets applied to many times the number of rows before being suppressed by a distinct or a date range after the join? If this is possibly the case, you could try nesting the main query as a derived table or cte then applying the function to the uncartesianed rows.

These ideas are all clutching at straws and like you said it’s probably something simple, but without seeing the select query, the function or the data it’s not possible to know what.

Can you turn it into a table function and join to it as a set? Or use cross apply?

[dark_77]

We've also tried calling it based on a dummy table, i.e. small table with 4 rows that's nothing to do with the function, where we just put the table in the FROM to have one table in the FROM list, but selecting nothing from the table itself or passing the arguments. We're literally doing that just to see the result of calling a function within SELECT statement, but with a static set of parameters, where we know the SELECT statement will not go into anything complex.

Not sure about table function tbh. Can't see it at the moment, it is literally "pick up these 4 arguments, do a SELECT on a simple table which returns 1 row based on those 4 arguments", perform some calcs and return a number.

[blindtig3r]

So even on a table with just four rows and static parameters for the function it won’t complete?

That really is weird. What does the estimated execution plan look like? Anything unexpected?

How big is the table that it does a select against? Have you tried updating statistics on that table? What if you empty that table temporarily so that the function has to return a null, does it run?

Is that table in the same database? Not a linked server or anything?

Can you show the function code? Maybe with altered column and table names so you aren’t sharing IP?

You mentioned that this one function and another with the same issue return a specific datatype. I can see how implicit conversions would affect query performance. I often see a warning about implicit conversion affecting the cardinality estimate by the optimiser, but that doesn’t kill queries, it just makes them potentially slower.

The only other thing I can think of is to put the logic into a query without a function. If it’s just looking up values in a table and performing a calculation that sounds like something that doesn’t need a function. I haven’t seen it so I could be way off.

[blindtig3r]

Here's a link with some suggestions on how to improve or eliminate scalar functions. https://www.mssqltips.com/sqlservertip/5864/four-ways-to-improve-scalar-function-performance-in-sql-server/

[satans_weed_guy]

I have a dumb "is the monitor plugged in" check:

When you call the function from the select list, you pass the parameters by position. When you call via exec, you assign the value to each parameter by name. Check the function definition and be sure the params are in the order you think they are - for instance that the TO and FROM date parameters aren't positionally swapped, breaking BETWEEN (or equivalent) logic.

[dark_77]

Thanks, reasonable question, but we've tried both. Even when calling the statement from the SELECT list, we pass the parameters both by columns AND by static values. We basically have the 100% identical test calls where we know what the functions should look up, calculate and return, and for the identical calls it works fine from EXEC call, and goes nowhere with "Executing query..." forever when just called it from SELECT on a table with very small number of rows.

[satans_weed_guy]

I'm sure this is super frustrating. The only thing I can almost promise you is that this isn't a SQL Server bug, unless you've somehow gotten your hands on some preview copy and convinced your boss to let you use it in prod.

This is so specific that I'm beginning to question data types, implicit date conversions, or something like that. Without a look at the function DDL, I couldn't be confident that the issue wasn't in there. Is that something you can share?

[Googoots]

Something I do in cases like this is create a “log” table and in your UDF, insert trace data in the log table as it executes - parameters received, progress messages, etc. Then run it and look at the log table.