I've a pretty complex table valued function doing inner joins over various tables. I've to tune this function. SQL wan't showing actual execution plan executed inside the function. So I thought to convert this to stored procedure and see the query plan. When I ran the original function as well as this new stored procedure in single batch, 100% time was consumed by stored procedure.
我有一个非常复杂的表值函数在各种表上进行内连接。我要调整这个功能。 SQL不会显示在函数内执行的实际执行计划。所以我想将其转换为存储过程并查看查询计划。当我在单个批处理中运行原始函数以及此新存储过程时,存储过程消耗了100%的时间。
Is this expected? I'm using SQL Server 2008.
这是预期的吗?我正在使用SQL Server 2008。
1 个解决方案
#1
2
Yes, this is expected. A multi-statement table valued function (TVF) is a black box and the internal processing isn't visible.
是的,这是预料之中的。多语句表值函数(TVF)是一个黑盒子,内部处理不可见。
It isn't running slow (well it could be for other reasons, but this isn't the question). The optimiser can not judge the TVF vs the procedure correctly and so allocates 100% of the batch to the proc.
它运行速度不慢(好吧可能是出于其他原因,但这不是问题)。优化器无法正确判断TVF与过程,因此将100%的批次分配给proc。
One way is to run the code (separatley) and use SQL Profiler to note Duration, Reads, CPU.
一种方法是运行代码(separatley)并使用SQL事件探查器来记录持续时间,读取,CPU。
#1
2
Yes, this is expected. A multi-statement table valued function (TVF) is a black box and the internal processing isn't visible.
是的,这是预料之中的。多语句表值函数(TVF)是一个黑盒子,内部处理不可见。
It isn't running slow (well it could be for other reasons, but this isn't the question). The optimiser can not judge the TVF vs the procedure correctly and so allocates 100% of the batch to the proc.
它运行速度不慢(好吧可能是出于其他原因,但这不是问题)。优化器无法正确判断TVF与过程,因此将100%的批次分配给proc。
One way is to run the code (separatley) and use SQL Profiler to note Duration, Reads, CPU.
一种方法是运行代码(separatley)并使用SQL事件探查器来记录持续时间,读取,CPU。