是否有相当于“mysqladmin processlist”的东西？对于 SQL Server？

发布于 2024-09-09 05:10:57 字数 684 浏览 7 评论 0原文

我一直在尝试制定一个查询来帮助自己识别使用 SQL Server 的资源密集型查询/数据库/用户，但还没有完全解决。我想构建一个查询，该查询将执行“mysqladmin processlist”为 MySQL 执行的操作。

我提到了此相关问题但还没有从中得到我真正需要的东西。我正在使用 sp_who、sp_who2 和这样的查询：

select master.dbo.sysprocesses.loginame, 
count(master.dbo.sysprocesses.loginame)
from master.dbo.sysprocesses
group by master.dbo.sysprocesses.loginame

问题始终是这些工具之一无法为我提供所需的一切。我的目标是有一个采用这种格式的查询：

LOGIN, DATABASE, QUERY, CPU, MEM, etc.

如果有人知道如何做到这一点，我将不胜感激。如果有人有任何 SQL Server DBA 备忘单，那就太好了。

原文

I've been trying to formulate a query to help myself identify resource-heavy queries/database/users using SQL Server and haven't gotten it down quite yet. I want to build a query that will do what 'mysqladmin processlist' would do for MySQL.

I've referred to this related question but haven't gotten what I really need from it. I'm using sp_who, sp_who2 and queries like this:

select master.dbo.sysprocesses.loginame, 
count(master.dbo.sysprocesses.loginame)
from master.dbo.sysprocesses
group by master.dbo.sysprocesses.loginame

The problem always is that one of these tools doesn't give me everything I need. My goal would be to have a query that would be of this format:

LOGIN, DATABASE, QUERY, CPU, MEM, etc.

If anyone knows how to do this, I would appreciate the help. If anyone has any SQL Server DBA cheatsheets that would be great, too.

分享到QQ

分享到微博

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

发布评论

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

霞映澄塘 2024-09-16 05:10:58

是否必须通过存储过程调用来完成？ SQL Server Management Studio（该链接适用于 Express 版本，但 SQL Server 的完整安装已包含该链接）具有“活动监视器”功能，可准确列出您想要的内容。

除此之外，

EXECUTE sp_who2

为您提供了您所要求的内容：登录、DBName、命令、CPUTime、DiskIO，都在那里...

如果您想要 SPID 正在执行的准确命令，您可以使用命令

DBCC INPUTBUFFER(spid)

（sp_who2 只是告诉您它是否是 DELETE、SELECT 等）

Does it have to be done with a sproc call? SQL Server Management Studio (the link is for the express edition, but a full install of SQL Server already has it) has an "Activity Monitor" feature which lists exactly what you want.

Other than that,

EXECUTE sp_who2

Gives you exactly what you asked for: Login, DBName, Command, CPUTime, DiskIO, are all there...

If you want the exact command that a SPID is executing, you can use the

DBCC INPUTBUFFER(spid)

command (sp_who2 just tells you whether it's a DELETE, SELECT, etc)

回复收藏 0 原文

归途 2024-09-16 05:10:58

如果您找出 sp_who2，您可以提取您感兴趣的字段：

select    
  spid
 ,status 
 ,hostname
 ,program_name
 ,cmd
 ,cpu
 ,physical_io
 ,blocked
 ,dbid
 ,convert(sysname, rtrim(loginame))
        as loginname

      from sys.sysprocesses with (nolock)
      order by cpu desc

If you bust out sp_who2, you could extract the fields that you're interested in:

select    
  spid
 ,status 
 ,hostname
 ,program_name
 ,cmd
 ,cpu
 ,physical_io
 ,blocked
 ,dbid
 ,convert(sysname, rtrim(loginame))
        as loginname

      from sys.sysprocesses with (nolock)
      order by cpu desc

回复收藏 0 原文

~没有更多了~