4

I've just finished debugging a problematic SQL Server 2012 table-valued function. It was declared to use a unique column in the table:

CREATE FUNCTION [dbo].[myApp_getUserIdsFromXml]
    (
    @searchXml xml
    )
RETURNS @tbl_found TABLE
    (
    userId int UNIQUE
    )
AS ...

The function returns some ID values based on XML data. It was being OUTER APPLIED by various procedures without issue, until in one situation it returned about 60,000 rows, in which case the procedure execution time took 18 minutes instead of a fraction of a second.

The function was debugged and redeclared as:

RETURNS @tbl_found TABLE
    (
    userId int PRIMARY KEY /* <-- PK fixed the issue */
    )
AS ...

The execution time then dropped to 0.68 seconds.

Before debugging and altering it, even after the query had completed (18 minutes' worth), SQL Server remained extremely unresponsive. Simple queries took minutes rather than milliseconds, and the SSMS GUI continuously froze and showed Windows' not responding message. This lasted for about an hour each time before SQL seemed to assume normal speeds.

Can anyone suggest a reason why this might happen (e.g. memory not being freed, or something). I've been using SQL for over ten years and never seen behaviour like this (the behaviour was replicated on two servers).

Or, can anyone suggest TSQL code to help investigate different areas where the problem might lie? I've found code to test for locks, but there was nothing 'locked' here, the transaction had completed and nothing was being executed (by my apps) at the time.

It's obviously fixed now, but I've lost too much hair in the process...

  • How much memory has the server got? What is SQL Server capped at? – wBob Sep 3 '15 at 14:42
  • 16Gb RAM and 1TB hard drive. I'm not sure if there's a cap; I've never applied one but am happy to check if you can tell me how? – EvilDr Sep 3 '15 at 14:51
  • If you run EXEC sp_configure and then scroll down to max server memory (MB), what do you see as the config_value? If you don't see that then you need to run EXEC sp_configure 'show advanced options', 1 GO RECONFIGURE and then EXEC sp_configure again (it should be there this time). – mskinner Sep 3 '15 at 15:01
  • 2
    Could simply be that your function pulled enough data into the buffer pool to evict everything else. Subsequent queries would be slow as they have to wait for SQL Server to read that data back from the disk into memory. – Aaron Bertrand Sep 3 '15 at 15:18
  • 2
    Assuming the server is 64-bit and a dedicated SQL Server machine, I would probably cap that at 12GB eg EXEC sp_configure 'max server memory (MB)', 12288 -- 12GB RECONFIGURE – wBob Sep 3 '15 at 16:08
1

Multi statement table valued functions are well known performance killers. I think the change you introduced had the beneficial side effect of turning your table variable from heap to clustered index. Other than that, I see no other difference between the two implementations.

I have no precise explanation of what could have "freezed" SQL Server, but that unresponsiveness is usually associated with high CPU. Did you take figures of what the CPU looked like during that period?

Another thing maybe worth investigating is the number of cache objects created in tempdb. Paul White has a great post on the subject on his blog. I would expect the numbers to be unchanged between heaps and CIs to be honest.

Another thing worth looking into is the preminent wait class during the "freeze". That could tell a lot about what's going on behind the covers. If you want to collect it over time you can use the Data Collector or a custom script.

BTW, if you managed to turn that function into an Inline Table Valued Function, that would probably help with performance.

  • I took a look for Inline Table Valued Functions, but can't quite figure how its different to what I have now. Can you shed any light for the layman? – EvilDr Sep 4 '15 at 13:21
  • Don't worry I've found something that helps... stackoverflow.com/questions/2554333/… – EvilDr Sep 4 '15 at 13:22
1

If you were profiling at the statement level to understand the performance of the function, then the profiler would have had severely affected the process of the system. SQL Server applies the function to every row. If you have 60,000 rows, then you will have 60,000 invocations to run through. That is a lot of profiling data.

Our application uses functions heavily. We have learned through experience to exclude functions from profiler traces. Any time we profile functions, the performance of the entire system slows to a crawl.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.