当前位置：文江博客话题详情

如何确定 sp_cursorfetch 正在使用的查询

发布于 2024-10-20 04:15:51 字数 67 浏览 1 评论 0原文

Profiler 显示我的服务器因大量调用 sp_cursorfetch 而过载，但我想知道哪些查询导致了所有这些流量。

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

放我走吧 2024-10-27 04:15:51

在这种情况下，探查器将不起作用。

我运行了一个来测试它，并使用以下命令查询我从中创建的表：

select CPU, TextData FROM cpu where LoginName = 'db_name_here' order by CPU desc

// 确保替换 db_name_here

结果我显示了如下内容：

CPU----TextData-----------------------------

0------exec sp_cursorfetch 180150000, 16, 7415, 1

*注意：上面的“-”只是为了格式化它，以便它在本网站上实际上可读。

========

我对此找到的唯一答案是：

Select 语句是这些游标获取的唯一原因，检查最常用表的索引是解决问题的良好第一步< /p>
您也许可以过滤对cursorfetch 调用的 SPID 的跟踪看看 sp_cursorfetch 运行之前和之后它在做什么。
仅获取当前记录集的一个子集。假设您现在抓取 100 行。只获取 10 个，因为 10 是用户在任何给定时间最多可以看到的。

Profiler won't work in this case.

I ran one to test it out, and queried the table I created from it with this:

select CPU, TextData FROM cpu where LoginName = 'db_name_here' order by CPU desc

// Be sure to replace db_name_here

Result I got showed stuff like this:

CPU----TextData-----------------------------

0------exec sp_cursorfetch 180150000, 16, 7415, 1

*Note: The "-" above are just to format it so it's actually readable on this site.

========

The only answers I found on this are:

Select statements are the only cause of these cursor fetches, and examining your indexes of most commonly used tables is a good 1st start to resolving the problem
You maybe able to filter a trace on the SPID of the cursorfetch call to see what it's doing before and after the sp_cursorfetch is ran.
Only fetch a subset of the total RecordSet you are currently. Say you grab 100 rows now. Only grab 10,because 10 is the most the user can see at any given time.

回复收藏 0 原文

冷弦 2024-10-27 04:15:51

回应评论：

Thanks for your suggestions, all of which are helpful. Unfortunately the queries in question are from a third party application, of which I do not have direct access to view or modify the queries. If I find a query that is a particuar problem, I can submit a support request to have it reviewed. I just need to know what the query is, first. – Shandy Apr 21 at 8:17

您不需要访问该应用程序即可尝试我上述的大部分建议。让我们回顾一下它们：

Select 语句是这些游标获取的唯一原因，检查最常用表的索引是解决问题的良好第一步
- 这是在数据库服务器上完成的。您只需在数据库上运行调整配置文件，并使用生成的配置文件运行 SQL Tuning Advisor。这将有助于改善索引
您也许可以过滤cursorfetch调用的SPID上的跟踪，以查看它在运行sp_cursorfetch之前和之后正在做什么。
- 您也可以使用 SQL Profiler 执行此操作
仅获取当前总 RecordSet 的子集。假设您现在抓取 100 行。只抓取 10 个，因为 10 是用户在任何给定时间最多可以看到的。
- 这是在应用程序级别完成的

您运行的 SQL Server 版本是什么？在我的例子中，这个问题的解决方案最终是升级到 SQL Server 2008。我会尝试一下，看看它会走向何方。

由于您无权访问该应用程序，因此绕过光标使用很可能会成为一个问题。如果您查看http://sqlpractices。 wordpress.com/2008/01/11/performance-tuning-sql-server-cursors/ 您可以看到大多数替代方案都涉及编辑运行的应用程序查询。

真正的问题是什么？为什么要分析数据库？

In response to the comment:

Thanks for your suggestions, all of which are helpful. Unfortunately the queries in question are from a third party application, of which I do not have direct access to view or modify the queries. If I find a query that is a particuar problem, I can submit a support request to have it reviewed. I just need to know what the query is, first. – Shandy Apr 21 at 8:17

You don't need access to the application to try out most of my aforementioned recommendations. Lets go over them:

Select statements are the only cause of these cursor fetches, and examining your indexes of most commonly used tables is a good 1st start to resolving the problem
- This is done on the database server. You need to just run a tuning profile on the database, and to run the SQL Tuning Advisor using the profile generated. This will assist with improving indexes
You maybe able to filter a trace on the SPID of the cursorfetch call to see what it's doing before and after the sp_cursorfetch is ran.
- This is something you do using SQL profiler as well
Only fetch a subset of the total RecordSet you are currently. Say you grab 100 rows now. Only grab 10,because 10 is the most the user can see at any given time.
- This is done at the application level

What SQL server version are you running on? The resolution for this ended up being an upgrade to SQL Server 2008 in my case. I would try this out to see where it goes.

Since you don't have access to the application, getting around cursor use is going to be a problem most likely. If you take a look at http://sqlpractices.wordpress.com/2008/01/11/performance-tuning-sql-server-cursors/ you can see that most alternatives involve editing the application queries ran.

What is the real problem? Why are you profiling the database?

回复收藏 0 原文