SQL Server 高并发Insert数据解析,实践在现实的生产环境中,有可能遇到高并发insert的应用.在此应用时由于堆表(Heap)和聚集表的结构不同导致在高并发的情形下insert效率不尽相同.接下来我会简单的以测试用例来简要说明.并举例说明如果提高聚集表下高并发插入效率.
在测试前我们先简单了解下堆表和聚集表都是如何完成插入操作的.
关于堆表和聚集表的介绍:SQL Server 索引知识-结构,实现
堆表Insert
方式1 a获得第一个IAM页
b 获取与之相关的PFS页,从中找到第一个能容纳Insert数据行大小的数据页
c 如果没有找到相应数据页则转到下一个IAM页然后重复b操作
d 如果到最后的IAM页还是没有找到可容纳的数据页则分配新的扩展区(extent)
e Insert指定行
方式2 a 获取所有IAM页
b 获取与之相关的PFS页(s)找到能容纳数据行(s)的数据页(s)
c 如果没有相关数据页,或者没有足够的相关数据页,分配新扩展区(extent)
d 插入相应的行(s)
聚集表Insert
由于聚集表本身的特性,插入数据的时的数据行必须在叶子节点的特定位置.
a 获取root页
b 通过B-tree需找到指定插入行的数据页的位置
c 如果此时数据页中空间可以容纳此行,则insert,如果不能则分配新数据页或者页分裂.然后insert
注:页分裂相对于页内的DML操作(insert,update)消耗巨大,页分裂的频率上升会明显影响实例的性能
测试用例
测试工具: sqlquerystress
测试环境:sql2008R2,3台不同服务器上分别安装运行sqlquerystress,100 threads/server
2000 times/thread
注意：如要模拟高并发需多台机器共同执行,单台即便多threads测试,测试结果也不能合理反应高并发情况.
测试脚本
我们在相应的机器上sqlquerystress中分两次(堆表,聚集表)分别运行insert into tempdb.dbo.tx(str1) select 'aa'测试
然后比较堆表,聚集表相关的性能指标.(Batch requests/sec,elapsed time,wait stats)
堆表
create table t1(id int identity(1,1),str1 char(5))goDBCC SQLPERF ("sys.dm_os_wait_stats", CLEAR)with NO_INFOMSGS -----run the sqlquerystress at three servers-----100 threads /per server-----2000 times /threadselect * from sys.dm_os_wait_stats order by waiting_tasks_count desc
聚集表
checkpointdbcc dropcleanbufferscreate table t2(id int identity(1,1) PRimary key,---clustered index defaultstr1 char(5))DBCC SQLPERF ("sys.dm_os_wait_stats", CLEAR)with NO_INFOMSGS -----run the sqlquerystress at three servers-----100 threads /per server-----2000 times /threadselect * from sys.dm_os_wait_stats order by waiting_tasks_count desc
测试结果
执行时的吞吐量Batch requests/sec
可以看出在我们的测试环境下堆表的吞吐量是聚集表的2倍以上 图1-1
QQQq7zHVePAEIIof/THB1iX0RETX54Zukov56rb1KqK7Zj01uMzr79hDEAyo/MHBwRlrNucY/4RWeLrRSg+uzyqLBui/N5UPm65wPArFmzoqOjXTp1yO2kpaWNHTvW1WOB/m+7cQM6d3b1SKAGr+qQQAGYW+sTgvpufQwAwF1e6eE5eGuxAkDzlkYERM66ceVQsO+A7Ct2ALi+aaxf8Oi8SqoJLHYINQE7hFwPO+RmXtIhQjjClJ/ekpUwrOcH//WuV0z66r35FVzt8ZmefYLi0yeNDgyMnZX7kAJ3Zm4/74CRM1Piw/qPmLD/jmyIhMUOoaZgh5DrYYfczMs6xPHE/vDAwqRRsWMTksaPHR09c+2RR6yu1FxeljwqOiZ+0ZYrLGgCJ0qWW6unjh0ZM3rOqnwbaDzLsNgh1CTsEHI97JCbedV2OV6lzz3KUDiWYXnFcdOUOYZhWIblZMejqMIxDPPc8wE7hF4GO4RcDzvkZl7VodcF2CH0Mtgh5HrYITeDHUJOhR1CrocdcjPYIeRU2CHketghN4MdQk6FHUKuhx1yM9gh5FTYIeR62CE3gx1CToUdQq6HHXIz2CHkVNgh5HrYITeDHUJOhR1CrocdcjPYIeRU2CHketghN4MdQk6FHUKuhx1yM9gh5FTYIeR62CE3gx1CToUdQq6HHXIz2CHkVNgh5HrYITeDHUJOhR1CrocdcjPYIeRU2CHketghN4MdQk6FHUKuhx1yM9gh5FTYIeR62CE3gx1CToUdQq6HHXIz2CHkVNgh5HrYITeDHUJOhR1CrocdcjPYIeRU2CHketghN4MdQk6FHUKuhx1yM9gh5FTYIeR62CE3gx1CToUdQq6HHXIzX6NDhFccT6KqwDIMw7CsqDvu0gXCMNgh9FWwQ8j1sENuprkd4nhZeHI2Y2R4aNiglEX7H4uGJAiSUpIzfmRY2MCEOdtLRVMmDSUC7BB6GewQcj3skJtpVocIEXj+ZvagTt4DZ2TPnRDkH56w474JzNGswF5e0YvmTRvW0yc0/WStqQjYIdQk7BByPeyQm2lWhzhRYc7Na9M5clepAkDzV6aMTFhaVHUutrVf2omHAFCwMd7Pe/DBctBFVpZlVVUBICMjIyoqypUTh9wPdgi5HnbIzTS3Q/bjM/7uMfZQmQQA11YPDxqYkHtwXc/ozDPFVgpQlb88apDHknM8aMKNGzcuXLhQUFAQGxs7atQol04dcjvYIeR62CE307wO8RJXun9M17/2Grvm0I4tCf0++GRI6pEt8zqOSb9QYqcA1fmrIgf3WHyWgCkPHDiwXbt2HTp0eO+998aPH+/ayUPuBjuEXA875Gaad5wCYQVRsl5cG+Lj6R0YER40Ztac7DsXs9tHzD1ZbAWAJ6cWRoV4LL8ogcLxPM/zPACkpaXh/iH0AuwQcj3skJtp7vFyDMupjqeUZo8JHp95jbcfj/DqM/dUDQA9syK6z6Bp12xU4VlCCMdxADB79mzsEHoBdgi5HnbIzTTzeDme58v3Z8aEDAjz8/OLycy+VaNqhlC4OcqzY6/gfl6e3uOWXqvUNJ6tP3Ib8Hg59DLYIeR62CE308ztcoQjNdcPb165YsXKTXtvVWmmTFjCS6T85NY1K1au2p17j6cKx+D5Q+grYIeQ62GH3Ezzr6dAJM0EAKCG/OzaCZyoUwAAU5e4RpdTwA6hl8MOIdfDDrkZvL4ccirsEHI97JCbwQ4hp8IOIdfDDrkZ7BByKuwQcj3skJvBDiGnwg4h18MOuRnsEHIq7BByPeyQm8EOIafCDiHXww65GewQcirsEHI97JCbwQ4hp8IOIdfDDrkZ7BByKuwQcj3skJvBDiGnwg4h18MOuRnsEHIq7BByPeyQm8EOIafCDiHXww65GewQcirsEHI97JCbwQ4hp8IOIdfDDrkZ7BByKuwQcj3skJvBDiGnwg4h18MOuRnsEHIq7BByPeyQm8EOIafCDiHXww65GewQcirsEHI97JCbwQ4hp8IOIdfDDrkZ7BByKuwQcj3skJvBDiGnwg4h18MOuRnsEHIq7BByPeyQm/kaHSKCYtY92tRFjmEYliWi5rhHEwjDYIfQV8EOIdfDDrmZ5naI5yX20bEpA/v7+PgOS1l0rYbqkiiKhWtGDvT19Rsxef0dzpS5hhIBdgi9DHYIuR52yM00q0OEiDy5nNXvs8CYpdu3rxwX6DUoYcdjgzs806d3/8SNaxaM7usTMPVQtakK2CHUJOwQcj3skJtpXoc4kdhzx73fadphK4ByZNrQ4P6L71SfiWkTNO/UYwC4m5PU33fg3kfUEFlVVXVdB4DMzMyoqChXThxyP9gh5HrYITfTvO1yhAg8uboxoXtQ3Px5sf59QzYVauqFzHYjMs8W2yhAVf6KqEE9luRzoAlnz549cuTI2bNnIyMjR48e7drJQ+4GO4RcDzvkZpq5f4hwkuXu4c2TBnTp3rWDT/CMQ4VPqw5PazUy/UKJnQJU56+KHNxj8VkCpjxs2LDOnTt3797997//fVJSkmsnD7kb7BByPeyQm2nedjleZu+t9283ZONNFkA9t3Ckd+iE0weXdY6cm1tkAYCKvPkjgnuuuCyDwimKomka4HY59DLYIeR62CE306wOcaJiz89s23pozn0BAEq3xnr3Ct557nxSYO+0YxUASu7iEX2Gpt1mTZlnCSGEEMDjFNDLYIeQ62GH3Ewzj1MQBFvxxmipv7fp7uPT26OPd8aGm1adPtw9tk+7zl49u/X0SV53t1ZTebb+yG3ADqGXwQ4h18MOuZnmnj9EeJmtuH5w97acnK0Hztx4KlOF50Su6tLhXVu37si98EAwZI7B84fQV8AOIdfDDrmZr3E9BU5UDdM0TdPQFIEwDMOynFB3l66KXKPLKWCH0Mthh5DrYYfcDF5fDjkVdgi5HnbIzWCHkFNhh5DrYYfcDHYIORV2CLkedsjNYIeQU2GHkOthh9wMdgg5FXYIuR52yM1gh5BTYYeQ62GH3Ax2CDkVdgi5HnbIzWCHkFNhh5DrYYfcDHYIORV2CLkedsjNYIeQU2GHkOthh9wMdgg5FXYIuR52yM1gh5BTYYeQ62GH3Ax2CDkVdgi5HnbIzWCHkFNhh5DrYYfcDHYIORV2CLkedsjNYIeQU2GHkOthh9wMdgg5FXYIuR52yM1gh5BTYYeQ62GH3Ax2CDkVdgi5HnbIzWCHkFNhh5DrYYfcDHYIORV2CLkedsjNYIeQU2GHkOthh9wMdgg5FXYIuR52yM00u0OEcI0QUnd3/U3sEGoO7BByPeyQm/kG60OE43me51iW5XhB4DmWEI7nBZ7DDqGvhB1CrocdcjPN6RDhReXRkbn9erRp06ZN27ZtWn/SskP49FOPNZ3NS/Xs3KZ1K8+Bs/MqFUXADqGvgB1CrocdcjPNWh8ihLNVlt66fuXKtdvFtw5NDerba/C6MvXpphGdfcauvHHlxLzB/Tyi1j7QDal++xxgh9DLYIeQ62GH3Exzt8sRTpBlRQdQ7h+KHxSacrQKqncFd4hac/kJAJTuTw0PCPjirmFKLKW07jXnz58fFRXlsilDbgk7hFwPO+Rmvs7+ISKYem3eyiSv3jNu6yAcn94qJvN8iZ0CVOVnjwjvsfgsAV1cs2ZNRkbG4sWLvby8cImDXoAdQq6HHXIzX6NDnKTwZafnjeg1eNUdHcynh6a2iplzvthGAarzsyMH91h8loAhTZ48eciQISNGjGjVqlVCQoJrJw+5G+wQcj3skJtpfoeIrHL3D2UFtRt0yGpqqq6cy2o9YPrBexYAeHwsfXhQ37U3NCqTZy+9YMEC3C6HXoAdQq6HHXIzze0Qx4t8benacZ2Cp+XZNYHjJOnppdSQzkmbrom2x1tmDOsbv/qxbIh4nAJqEnYIuR52yM00s0NEEITKi2vCOvqtuGlTBZ6whFfkmvyswDYtW7f8xCMoI9fCq1LD2ayAHUIvgx1CrocdcjNfY/+Q3VL5+GGVlX0WGyLwTHnx3YKCwtIyiyALhGl4MGCH0Mtgh5DrYYfczNc6Xo4XRaHRFXwIJ0qSLEuiyL9wYR/ADqGXwQ4h18MOuRm8zilyKuwQcj3skJvBDiGnwg4h18MOuRnsEHIq7BByPeyQm8EOIafCDiHXww65GewQcirsEHI97JCbwQ4hp2p+hyjH6bduG3fu4eDmg367wHxS+W3POW8SdsjNYIeQUzWnQ1punnYslx8ZZ/nRL2zvvoeDmw/Wt3/JdvPUjp9UDx6hguCcGem1YIfcDHYIOVXTHVIPHJZmZVh/9q717V9af/Rzy1vvWP7lZzi4+/DWO5Yf/Kv1p7+yfO8n/Mg4ed0mZ85R3wR2yM1gh5BTvapDWt5pYfwk2+/+Yvnuj60/+TfLP/2Q+IfIK1fj8A8xCAkTLP/vbesv/sP6k19afvQLPmassmaD8+eu5sIOuRnsEHKql3ZIyztt/5+/Wb7zlvXtX9rf/0Q9cFjdve8fbJfD/21U1dR9B/ihUda33rH96reW7/7Y+rNfy8tXuXq8XgE75GbcrEOaRmWZysqzQUiYwHbtRfr0w8GZA+vhxYUOoQz73L+FTBXl9We4L3dIO33W/of3rT/8ue0/fm//y0faxcuv/1eQS1A7o1++Srz8bb/+nfWnv7K98xt5WbarR+plsENuxg06RKlZXmE+qTSfVJJ+wfa/t2RafuYYPm1v/em7ln/+oeV7P8HBqcN3f2R56x2mRVumZXvHv8VHrdn2XfWCO3X/UmZZORjGN5jhGnXIMLWz5+x/eN/61ju2//yDuveA+bT2G7wmciuUYfXCO0ybjta33rH962/kFZ9TWXb1SDWGHXIzruyQceeefvW6vGSF7Zf/Zfvvv9j++6/WX/6X5fvPLQ3/6Yds+66kbwDp64+DkwfWo6/lX35m+e6P6v85fmz5/tu23/7J9t9/tf33X2z/9p9S5nz96nW9oPBrzXDPd4hKsv2vH1t/8kv77/+u7N73jWZg5KaM+0Vsuy7Wf/1369u/0i9ecfXoNIYdcjMu6JDx6LG6fZeya6/9jx9a3/m19WfvWr7/tuX7b1u++2Pr27/ih4zgo0bzUaP5EaO5oSPMsnKnvh+oHpUVYUwCP3yk458jajQfEW396a8s//wjy/fftr79K9u771nf+bXtd39Wtu5Ut+82ikua87INHaJU2bXX9l9/tHznLWHClG93YpArqIePWv75R9Z3fi3NSKeEc/XoPAc75Gac2iFqZ6T5i4lXP8s//dD6419YfvCvlu/8gGnRVpydIc7OEKenSVmLXPAeoGaTFy8TU2eLszNYTz/Ld96y/OAdyw/esf74F5Z//hHbo480b6FZ87TpV3h+fcj+cVvL99+2/+lDLffktz/uyNmM4hK2V1/LW+9Y337XfOxOXyixQ27GeR2iHM+FDLJ854fWd35tfec3tl/8u7RoqZy9Wr9wyTWTjl6DUfpAXvG5smGzvHyV7d33rD971/qvv7F85y3iG2habU08MS0tbWx8PADIy1fZfv07+99a6OdxBvhfyywvJ338rD/5pRA7jvK8q0enHnbIzTipQ1SSuJBB1rfesb37nvWnvxJnZ2inz7psotEbpeWfk7IWWX/2a9u771neeof4BlKWedWDn3WI7dHX8p1/Ib19nTimyAWkGWmWf/qh7Te/a/oLilNhh9yMMzpEJYkLCbe89Y71hz/nxyYZd+5+s0OtkDsz7twTJk21/ugXlrd+RnwCKffyL79paWljx40DRSF9Ayz/76dcv2AnjydyMmlmuuWHP7f/4e+G+2yaww65GWd0iB85xvL/3rb+8Of86Hiqqq6cXPSt0jQhaaL1Rz+3/OAd4hf00oOw09LS4qdNhcwFlh/+nPngU7P6K/YnoX90VBRJyCDLv/yM7e3jLl9AsUNu5lvuUGwslFewHXtYf/xvfOw4l04pchJhfIrt3fcs33+bj4798v9NS0uLT50Gk2dY/vlHzCftqKY7fwyRk3HDRli++2OmVXvsEHqpb7dDMSmTY