用户定义函数中的分析语句 [英] Profiling statements inside a User-Defined Function

问题描述

我正在尝试使用 SQL Server Profiler (2005) 来追踪一些应用程序性能问题.正在进行的调用之一是对表值用户定义函数.该函数包装了一个将多个表连接在一起的选择.

I'm trying to use SQL Server Profiler (2005) to track down some application performance problems. One of the calls being made is to a table-valued user-defined function. This function wraps a select that joins several tables together.

在 SQL Server Profiler 中，会记录对 UDF 的调用.但是，根本没有记录作为 UDF 基础的选择.正因为如此，我没有得到关于哪些表的有用数据索引被击中.我想将此信息提供给数据库优化顾问以获得一些索引建议.

In SQL Server Profiler, the call to the UDF is logged. However, the select that underlies the UDF isn't being logged at all. Because of this, I'm not getting useful data on which tables & indexes are being hit. I'd like to feed this info into the Database Tuning Advisor for some indexing advice.

有没有办法(除了自己解包查询)在 Profiler 中记录 UDF 调用的表?

Is there any way (short of unwrapping the queries themselves) to log the tables called by UDFs in Profiler?

推荐答案

你不能:多语句 TVF 是一个黑盒子，你只能获得 CPU、读取、写入等.

You can't: a multi-statement TVF is a black box and you can only get CPU, Read, Writes etc.

所谓黑匣子"，我的意思是它是另一个查询中完全封装且不透明的一系列语句，并且没有像您通过存储过程逐行获取那样的流程".

by "black box" I mean it's a fully encapsulated and opaque series of statements inside another query, and there is no "flow" like you'd get line by line through a stored proc.

内嵌 TVF 像视图或宏一样扩展到主查询中并且可以看到.

An in-line TVF is expanded like a view or macro into the main query and can be seen.

相关:表值函数我的查询计划去哪儿了?

这篇关于用户定义函数中的分析语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！