I work with SQL Server 2005 and wonder, if not CPU, disk or network, what are users waiting for when SQL Server is working. The strange thing is that system monitor shows that the 4 processors are at an average of 5%, the disk (demonstrated 50MB/s write) works with about 5-8 MB/s, but the execution (inserts and selects) take up to 10 minutes. I'd be happy to install additional hardware, but I don't see what device is the bottleneck and how do I measure its capacity and current workload.
我使用SQL Server 2005并且想知道,如果不是CPU,磁盘或网络,那么用户在SQL Server工作时会等待什么。奇怪的是系统监视器显示4个处理器平均为5%,磁盘(显示50MB / s写入)的工作速度约为5-8 MB / s,但执行(插入和选择)占用了10分钟。我很乐意安装其他硬件,但我不知道什么设备是瓶颈,我如何衡量其容量和当前工作负载。
Any advice would be appreciated.
任何意见,将不胜感激。
Thanks
additional info: RAM is constantly at about 70% capacity and I am running windows xp.
附加信息:RAM持续大约70%的容量,我正在运行Windows XP。
3 个解决方案
#1
1
check your disk read and write 'wait' time. a heavy load database may just make a lot of read and write request with very small piece of data that saturates the IO.
检查你的磁盘读写'等待'时间。重负载数据库可能只会通过使IO饱和的非常小的数据产生大量的读写请求。
#2
1
As others mention, disks are rarely the bottleneck when it comes to bandwidth, but rather in the number of IO operations they can perform per second - commonly called IOPS.
正如其他人所提到的,磁盘在带宽方面很少成为瓶颈,而是每秒可以执行的IO操作数量 - 通常称为IOPS。
The IOPS capabilities of your disks will vary according to disk type, cache and the RAID setup you have.
磁盘的IOPS功能将根据磁盘类型,缓存和您拥有的RAID设置而有所不同。
Another thing you may run into is locking. If you have a lot of concurrent access to the same data, especially inside of large transactions, you may see other transactions being blocked - causing no network, CPU nor disk usage while being blocked, just wasted time.
你可能遇到的另一件事是锁定。如果您有大量并发访问相同数据的内容,尤其是在大型事务中,您可能会看到其他事务被阻止 - 在被阻止时不会导致网络,CPU或磁盘使用,只会浪费时间。
#3
0
Probably the disks. If you are seeking all over the place, the throughput (MB/s) will be low even though the disks are running as fast as they can.
可能是磁盘。如果您正在寻找所有地方,即使磁盘运行速度尽可能快,吞吐量(MB / s)也会很低。
Generic advice: try increasing SQLServer's cache, tune your queries, check that the appropriate indexes exist and are used (and that you don't have too many).
通用建议:尝试增加SQLServer的缓存,调整查询,检查是否存在适当的索引并使用(并且您没有太多)。
#1
1
check your disk read and write 'wait' time. a heavy load database may just make a lot of read and write request with very small piece of data that saturates the IO.
检查你的磁盘读写'等待'时间。重负载数据库可能只会通过使IO饱和的非常小的数据产生大量的读写请求。
#2
1
As others mention, disks are rarely the bottleneck when it comes to bandwidth, but rather in the number of IO operations they can perform per second - commonly called IOPS.
正如其他人所提到的,磁盘在带宽方面很少成为瓶颈,而是每秒可以执行的IO操作数量 - 通常称为IOPS。
The IOPS capabilities of your disks will vary according to disk type, cache and the RAID setup you have.
磁盘的IOPS功能将根据磁盘类型,缓存和您拥有的RAID设置而有所不同。
Another thing you may run into is locking. If you have a lot of concurrent access to the same data, especially inside of large transactions, you may see other transactions being blocked - causing no network, CPU nor disk usage while being blocked, just wasted time.
你可能遇到的另一件事是锁定。如果您有大量并发访问相同数据的内容,尤其是在大型事务中,您可能会看到其他事务被阻止 - 在被阻止时不会导致网络,CPU或磁盘使用,只会浪费时间。
#3
0
Probably the disks. If you are seeking all over the place, the throughput (MB/s) will be low even though the disks are running as fast as they can.
可能是磁盘。如果您正在寻找所有地方,即使磁盘运行速度尽可能快,吞吐量(MB / s)也会很低。
Generic advice: try increasing SQLServer's cache, tune your queries, check that the appropriate indexes exist and are used (and that you don't have too many).
通用建议:尝试增加SQLServer的缓存,调整查询,检查是否存在适当的索引并使用(并且您没有太多)。