创建 SQL Server 服务器性能基准查询

发布于 2024-12-26 18:00:53 字数 729 浏览 1 评论 0原文

我正在寻找一个简单的查询来说明数据库性能

我们创建了一个工具来找出客户端网络/服务器/软件性能，以便为我们创建一个基线来说明他们的服务器或者客户确实能力不足，或者我们的软件在某些情况下表现不佳。

我们为什么要这样做？

在我们的一个客户那里，我们的软件执行速度非常慢，他们指责我们。在搜索了可能的软件缺陷后，我们发现问题实际上是他们的网络速度，速度非常慢。（网络流量通过 100 英里外的 VPN 进行路由）

我需要什么

我正在寻找一个“简单”查询来为 Microsoft SQL Server 数据库设置基准性能。

最好是一个查询来说明数据库是否真的很慢/很快或者只是工作正常。

我创建了一个简单的查询，它的作用很简单：

select top 50000 * from BigTable 
where YEAR(startdate) = 2011 or YEAR(startdate) = 2010 
order by table.startdate desc

这里的 BigTable 实际上是我们的软件会大量查询以进行一些复杂的更新计算的东西。

调用此查询（100 次）并从客户端计时，可以让我们对服务器性能有一个不错的估计。但我宁愿执行一个更加面向计算的查询，其中包含一些复杂的连接，这些连接实际上说明了服务器性能，而不是简单的选择。

原文

I'm looking for a simple query to say something about database performance

We've created a tool to find out the clients network/server/software performance to create a baseline for us to say that either their server or clients are really underpowered or our software is under performing in some situations.

Why are we doing this?

At one of our clients our software was performing really slowly and they blamed us. After searching around for possible software defects we found that the problem was actually their network speed, it was just terribly slow. (network traffic was routed over a VPN 100 miles away)

What do I need

I'm looking for a 'simple' query to set a baseline performance for a Microsoft sql server database.

Preferably a query that would say if a database is really slow/fast or just working ok.

I've created a simply query's that simply does:

select top 50000 * from BigTable 
where YEAR(startdate) = 2011 or YEAR(startdate) = 2010 
order by table.startdate desc

The BigTable here is actually something that our software would query a lot to do some complex update calculations.

Calling this query(100 times) and timing it from the client gives us a decent guesstimate of the server performance. But I'd rather execute a more computational oriented query with some complex joins that actually said something about the server performance other then a simple select.

分享到QQ

分享到微博

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

发布评论

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

从﹋此江山别 2025-01-02 18:00:53

我可以建议使用相同的查询，但在查询之前设置以下内容。

Set Statistics IO ON    
go
Set Statistics Profile ON
go
Set Statistics Time ON
go

您还可以运行 SQL Profiler 来跟踪会话，该会话由会话给出
CPU、READS、WRITES、DURATION 将提供有限的服务器级别信息，而
会话正在您的工作站上运行。

May I suggest using the same query but set the following before the Query.

Set Statistics IO ON    
go
Set Statistics Profile ON
go
Set Statistics Time ON
go

You could also run SQL Profiler to trace the session which gives by session
the CPU,READS,WRITES,DURATION which would provide limited server level information while the
session is running on your workstation.

回复收藏 0 原文

~没有更多了~