2 replaced http://sqlblog.com/blogs/paul_white/archive/2012/08/17/temporary-object-caching-explained.aspx with http://web.archive.org/web/20180422150925/http://sqlblog.com:80/blogs/paul_white/archive/2012/08/17/temporary-object-caching-explained.aspx
source | link

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 bloggreat 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.

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.

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.

1
source | link

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.