How to retrive table valued function from cache

Question

Post reply

How to retrive table valued function from cache

learningsqlserver

SSC Veteran

Points: 238
More actions
November 10, 2010 at 5:26 am

#388200

Hi,
I am new to sql server and want to know how can we retrive all the table valued function from cache.
Thanks in advance
VDT

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

nigelrivett SSCertifiable Points: 5362 More actions · Answer 1

Not really sure what you want but maybe

select specific_schema, specific_name, ROUTINE_DEFINITION

from INFORMATION_SCHEMA.ROUTINES

where ROUTINE_TYPE = 'FUNCTION'

and data_type = 'TABLE'

Cursors never.
DTS - only when needed and never to control.

learningsqlserver SSC Veteran Points: 238 More actions · Answer 2

I need to retive the table valued function from sys.dm_exec_query_stats i.e from cache (if i am correct). The requirement is to analyze all the table valued function from performance point and conver to scalar or use temp table if required

wbrianwhite SSC Eights! Points: 954 More actions · Answer 3

By definition it is impossible to change a TABLE valued function to a SCALAR function. One returns a table. One returns an int or a string.

You may in some circumstances have better performance with a physical temp table (more disk i/o in tempdb, less server CPU) but generally a table valued function will perform better a temp table.

At our shop we're going to start caching the results of a table valued function that is called hundreds of times a second in a physical table. If you have a similar situation you may want to investigate that. I'm taking the parameters to the function and forming a synthesized key, which becomes the key to finding rows in the physical table.

learningsqlserver SSC Veteran Points: 238 More actions · Answer 4

Thanks for the information,I liked the idea of caching the output of table valued function.Will try the same. Thanks again.

chrisfradenburg SSCrazy Eights Points: 9592 More actions · Answer 5

It actually is possible to change a scalar function to a inline table valued function in some cases. I've done it before to improve performance and it requires not just changing the function but any code that references the function so it handles the new output. It's not always easy (or possible) but it can result in a drastic performance improvement.

Start with this query to get a list of the functions to look into. It returns both scalar functions and multi statement table value functions since they have the same performance issues as scalar functions. You can see if you can find these by cross applying the sql_handle in sys.dm_exec_requests to sys.dm_exec_sql_text but a big factor is going to be how you're using them in your code.

select * from sys.objects where type in ('FN','TF')