SQL Server 2005:select好,插入不好

我们在数据库应用程序中遇到了一个令人困惑的问题。 我们正在运行3个不同的(但类似spec'd)环境中的应用程序。

Select Insert Env1 0.81ms 0.94ms Env2 0.69ms 5.79ms Env3 0.52ms 15.61ms 

我们在3个环境中对磁盘和内存性能进行了基准testing。 Env3稍快于Env2,稍快于Env1(不超过10%)。正如你所预料的那样,Env3中的select比Env2中的select略快于Env1中的select。 但是,Env1中的插入比Env2 快5倍,比Env3 快15倍 。 在Env2和Env3中写入的性能可能很差吗?

一些相关的背景资料。

  1. 上面的数据是通过我们编写的testing应用程序来收集的,以便尝试和诊断问题。 select和插入是基本的,并在一张小桌子上执行。

  2. 我们的testing应用…

    a)是用C#编写的,并使用基于System.Data.SqlClient.SqlConnection的类。
    b)通过执行1000次的动作并将所花费的时间除以1000来计算平均次数。
    c)在两种模式下工作…
    一世。 我们的代码称为插入/select/更新1000次
    II。 我们的代码称为单个存储过程,称为insert / select / update 1000次
    d)与数据库在同一台机器上运行

  3. 除非存储过程称为更新,否则更新会受到类似于插入的影响。 当存储过程在Env3中调用更新时,它比Env1慢了4倍,但是当客户端调用1000更新时,速度慢了17倍。 下面的扩展表格说明了不同之处。 环境。

      Select SP Select Insert SP Insert Update SP Update Env1 0.81ms 0.015ms 0.94ms 0.72ms 0.83ms 0.04ms Env2 0.69ms 0.019ms 5.79ms 5.32ms 7.42ms 0.09ms Env3 0.52ms 0.020ms 15.61ms 14.37ms 15.27ms 0.15ms 
  4. 我们build立了“环境1”。 我们的客户build立了“环境2和3”。 环境2是VMWare映像。 当我们第一次注意到插入和更新速度有多慢时,我们就把图像的副本带到了我们的托pipe中心。 性能问题消失了。

  5. 每个环境在Windows Server 2008(64位)上运行SQL Server 2005标准版(64位)

  6. 环境之间的主要差异是

    环境1 :在带有2个四核英特尔®至强®E5520处理器的戴尔T610的虚拟机pipe理程序中运行。 (VM有3个专用内核和6Gig专用RAM。)
    环境2 :在配备有4个双核2.8Ghz AMD Opteron 8220处理器的HP Proliant DL585 G2上运行在VMWare VSphere上。 (VM有3个专用内核和6Gig专用RAM。)
    Env3 :在物理盒子上运行 – HP Proliant DL380 G5,2 *双核Intel Xeon 3Ghz处理器,6GB RAM。
    Env1使用RAID0, Env2和3使用RAID5
    当我们将Env2图像复制到我们的服务器时,我们使用VMPlayer来运行它。 我们无法复制性能问题。
    Env1运行在我们的networking上。 Env2&3运行在我们的客户networking上。

  7. 我们尝试了共享内存,命名pipe道和TCP作为通信机制,没有明显的区别。

  8. 我们已经尝试了重build索引,删除和重新创build表等。所有表上的所有插入/更新都发生了糟糕的性能。

我们有两个问题

1)在Env2和Env3中写入的性能可能很差吗? 2)我们可以使用什么工具来看看所有这些额外的毫秒是用在哪里?

感谢您给我们提供的任何帮助。

这是我会调查:“Env1使用RAID0,Env2和3使用RAID5”

RAID 5写入速度较慢。

记住数据库是磁盘驱动器的接口,磁盘要重​​要得多,特别是在一个小桌子上,然后是RAM或CPU。

尝试更简单的设置,并改变RAID设置,甚至可能不尝试完整的一组数据的RAID。

快速编辑对于一个更随机的testing,所以确保你正在testing磁盘,而不是RAM或CPU,尝试有更低的内存和CPU,并有一百万甚至一千万行的表,在那里你随机select行,以增加服务器的机会必须到每一行的磁盘。

思考的食物:SELECTs是从内存中提供的。 INSERT必须刷新日志磁盘。 检查3个环境中的sys.dm_io_virtual_file_stats 。 我希望你会发现在io_stall_write_ms列中的巨大差异。

使用RAID 5的两台服务器无疑是一个问题(RAID 5是快速读取/慢速写入)。 几乎任何其他RAIDconfiguration都可以获得更好的性能(尽pipeRAID 0没有容错function)。

改变RAID,你可能会得到你想要的performance,但我想我会添加一些我不认为已经说过的东西:

索引虽然对于select非常有用,但却会以负面的方式影响写入操作的性能(写入数据,索引也必须更新)。 所以,即使交换RAID不会给你所期望的性能,我会接下来看看索引。

运行Glenn Berry的查询来了解每个数据库的读/写IO失速情况:

 -- Calculates average stalls per read, per write, and per total input/output for each -- database file. -- Helps determine which database files on the entire instance have the most I/O -- bottlenecks SELECT DB_NAME(fs.database_id) AS [Database Name], mf.physical_name, io_stall_read_ms, num_of_reads, CAST(io_stall_read_ms/(1.0 + num_of_reads) AS NUMERIC(10,1)) AS [avg_read_stall_ms],io_stall_write_ms, num_of_writes,CAST(io_stall_write_ms/(1.0+num_of_writes) AS NUMERIC(10,1)) AS [avg_write_stall_ms], io_stall_read_ms + io_stall_write_ms AS [io_stalls], num_of_reads + num_of_writes AS [total_io], CAST((io_stall_read_ms + io_stall_write_ms)/(1.0 + num_of_reads + num_of_writes) AS NUMERIC(10,1)) AS [avg_io_stall_ms] FROM sys.dm_io_virtual_file_stats(null,null) AS fs INNER JOIN sys.master_files AS mf ON fs.database_id = mf.database_id AND fs.[file_id] = mf.[file_id] ORDER BY avg_io_stall_ms DESC OPTION (RECOMPILE); 

我要做的第一件事是确保索引在每个环境中完全相同,并查看每个数据库服务器的填充因子是多less。 另外,每个环境中的数据量是相同还是相近?

我看到很多关于磁盘和时间的问题。 我想知道磁盘实际上在做什么。 他们的平均访问时间是多less? I / O请求需要等待多长时间才能得到服务? 有多less个I / O请求排队? 当然,也有一些问题,关于你的内存子系统有多忙, 所有这些问题都可以通过在执行testing时在另一个窗口中运行collectl来轻松解决。 我要求改变一些设置,并重新运行testing,寻找什么设置有所帮助,但你真的需要find根本原因。

例如,有人提到raid0 va raid5。 这确实是一个巨大的。 在没有工作量的情况下,这两个子系统如何比较? 尝试使用像dd或dt甚至iozone这样的工具对每个工具进行简单的读/写,然后观察像collectl甚至iostat这样的工具发生了什么。 如果这两个performance之间存在很大差异(我知道raid0在写入时会更快,raid5会在读取时获胜),您可以对数据库进行所有调整,而且永远不会超过磁盘可以做。

-标记