找出sql server硬件或速度测试

I use an sql server regularly and have recently been getting frustrated by the performance. It would be difficult for me to get direct access to find out the hardware so:

我经常使用sql服务器,最近对性能感到沮丧。我很难直接访问以找出硬件,所以:

Is there a direct way in management studio to assess performance or find out the exact hardware.

管理工作室是否有直接的方法来评估性能或找出确切的硬件。

Alternatively does someone have a set of test sql procedures I could try and ideally compare to other results to get an idea of it's performance.

或者,有人有一套测试sql程序,我可以尝试,理想情况下与其他结果进行比较,以了解它的性能。

So far I have setup a few quick queries on my local machines sql express server just as test these seem to run quicker than the sql server on the network which is meant to be high performance although no one knows when it was last upgraded I have a feeling it hasn't been for 6 or 7 years. Obviously these test don't account for the possibility of others querying at the same time or network transfers of results... Hopefully someone has a better solution.

到目前为止,我已经在我的本地机器sql express服务器上设置了几个快速查询,就像测试它们似乎比网络上的sql server运行得更快,这意味着高性能虽然没有人知道它何时上次升级我有一个感觉它已经没过6或7年了。显然,这些测试没有考虑到其他人同时查询或网络传输结果的可能性......希望有人有更好的解决方案。

5 个解决方案

#1

IF you are on 2005:

如果您在2005年:

SELECT * FROM sys.dm_os_performance_counters

That will give you some sql only stats. You will not find much info about the machine without at least terminal access. In the sql startup log you can see some info on processors as well.

这将给你一些仅限sql的统计数据。如果没有至少终端访问权限,您将找不到有关该机器的更多信息。在sql启动日志中,您也可以看到处理器上的一些信息。

#2

You can't just ask your server guys? Seems like there's a fair bit of mistrust if you can't get hardware metrics. Count of CPUs, total memory, etc.

你不能只问你的服务员吗?如果您无法获得硬件指标,似乎有一些不信任。 CPU数量,总内存等

If there's that amount of mistrust, even if you found the answer from the database server, rectifying it would be impossible. If you can't get the current parameters, how could you get a change of hardware passed the server guys?

如果有这么多的不信任,即使你从数据库服务器找到答案,也不可能纠正它。如果你无法获得当前参数,你怎么能通过服务器人员改变硬件?

Start building rapport. The best line in the world to get someone on your side is, "I'm in trouble and I need your help..." You've elevated them and subjugated yourself, you've put them in a position to save you. You'd be amazed at how much you can get out of people that way.

开始建立融洽关系。让一个人站在你这边的世界最好的路线是,“我遇到麻烦,我需要你的帮助......”你提升了他们并征服了自己,你已经把他们放在了能够拯救你的位置。你会惊讶于你可以通过这种方式离开人们。

As far as standard queries. You could look at TPC queries.

至于标准查询。您可以查看TPC查询。

#3

You also might try updating your references in your server. I had an issue a while back that 1 query returned in 100ms and an identical query in 5+ minutes and the only difference between the 2 was a Capital letter in the table name in my query (whih obviously shouldn't matter).

您也可以尝试更新服务器中的引用。我有一个问题,一个问题,1个查询在100毫秒内返回,相同的查询在5分钟以上,2之间的唯一区别是我的查询中的表名中的大写字母(显然应该无关紧要)。

After some searching and SO-Questioning, I found that I needed to update my statistics. Could it be something like this is needed for your database / SQL Server too?

经过一些搜索和SO-Questioning,我发现我需要更新我的统计数据。您的数据库/ SQL Server是否也需要这样的东西?

#4

This sort of thing can be very political, especially in a firm with an endemic CYA culture (which describes most financial services companies). If there's no reasonable expectation of a good working relationship with the production staff, A few approaches are:

这种事情可能非常政治化,特别是在具有地方性CYA文化的公司(描述大多数金融服务公司)中。如果没有合理的期望与生产人员建立良好的工作关系,有几种方法是:

Look at the query plans of the queries. Check that they are sensible (using indexes when they should etc.)

查看查询的查询计划。检查它们是否合理(当它们应该等时使用索引)
Make it formal. Ask their manager to get the specifications of the machine, the disk layout and server configuration and the last time statistics were updated on all tables and indexes. Make it clear that the machine appears to be under-performing.

使它正式。请他们的经理获取机器的规格,磁盘布局和服务器配置以及上次更新所有表和索引的统计信息。清楚地表明机器似乎表现不佳。
If the statistics are out of date, get them updated.

如果统计信息已过期,请将其更新。

#5

and one more

还有一个

SELECT * FROM sys.dm_os_sys_info

#1