TSQL动态确定SP/功能的参数列表 [英] TSQL Dynamically determine parameter list for SP/Function

问题描述

我想将一个通用的日志记录片段写入存储过程的集合中.我写这篇文章是为了量化衡量我们的前端用户体验，因为我知道前端软件使用了哪些SP以及如何使用它们.在开始性能调整之前，然后在显示性能调整结果之前，我想以此为基准.

I want to write a generic logging snip-it into a collection of stored procedures. I'm writing this to have a quantitative measure of our front-user user experience as I know which SP's are used by the front-end software and how they are used. I'd like to use this to gather a base-line before we commence performance tunning and afterward to show the outcome of tunning.

我可以从@@ PROCID动态提取对象名称，但是我无法确定所有传递的参数及其值.有人知道这是否可能吗?

将我的回答标记为结束此问题的答案.似乎扩展事件是对性能影响最小的项目，但是我不确定最小分析和扩展事件之间是否有实质性区别.也许在下雨天.

marking my response as the answer to close this question. Appears extended events are the least intrusive item to performance, however i'm not sure if there is any substantial difference between minimal profiling and extended events. Perhaps something for a rainy day.

推荐答案

针对我的情况，最好的解决方案是仅对SP:starting和SP:completed进行概要分析，并编写一些TSQL以遍历数据并填充跟踪表.

Appears the best solution to my situation is to do profiling gathering only SP:starting and SP:completed and writing some TSQL to iterate through data and populate a tracking table.

为此，我个人更喜欢代码生成，但是从政治上讲，在我工作的地方，他们更喜欢这种解决方案.我们在日志记录中失去了一些粒度，但这已经足够解决我的问题.

I personally preferred code-generation for this, but politically where i'm working they preferred this solution. We lost some granularity in logging, but this is a sufficient solution to my problem.

到此为止，这是一个好的解决方案.即使仅对这两项进行概要分析，也会在一定程度上降低性能. :(我希望我们提供了一种MSFT提供的方法来描述不会降低生产性能的工作负载.Oracle对此有很好的解决方案，但也有其折衷的选择.我很乐意看到MSFT实现类似的功能. DMV和扩展事件有助于关联项目.再次感谢Martin的链接.

This ended being an OK solutions. Even profiling just these two items degrades performance to a noticeable degree. :( I wish we had a MSFT provided way to profile a workload that didn't degrade production performance. Oracle has nice solution to this, but it's has its tradeoff's as well. I'd love to see MSFT implement something similar. The new DMV's and extended events help to correlate items. Thanks again for the link Martin.

这篇关于TSQL动态确定SP/功能的参数列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！