星空网 > 软件开发 > 数据库

深入解析SQL Server并行执行原理及实践(下)

谈完并行执行的原理,咱们再来谈谈优化,到底并行执行能给我们带来哪些好处,我们又应该注意什么呢,下面展开.

 

Amdahl’s  Law

再谈并行优化前我想有必要谈谈阿姆达尔定律,可惜老爷子去年已经驾鹤先去了.

 深入解析SQL Server并行执行原理及实践(下)

其中P:可以并行的百分比

N:算法并行计算使用的”CPU”

这里我们举个简单的例子,我们来做一份大餐,如图1-1所示

 深入解析SQL Server并行执行原理及实践(下)

                    图1-1

土豆泥,荷兰豆,鸡排还有整体组合各需十分钟.在这里前三个食材是可以共同执行的

也就是说4个步骤中3步可并行 P=3/4

其中3个食材可同时加工N=3
则根据公式S(N)=1/(1-0.75)+0.75/3 =2这个操作整体相比完全串行可提升200%即做这个大餐的时间由40分钟,缩短到20分钟.

这里实际引申出一个事实,整体的操作时间和整体事务中串行的比例多少有很大关联,这个引申到我们的并行调优中,并行可以改进查询,但受串行部分影响也是很大的,同时也告之我们没有必要过度串行优化,还是做大餐,即便并行可做的越来越多,但改进的效果却越发不明显,过度无谓的优化我们是应该避免的,如图1-2

 深入解析SQL Server并行执行原理及实践(下)

执行计划禁止并行

T-SQL scalar functions

更新表变量数据

访问系统表

动态游标

Branche不能并型

TOP(global)

2012以前窗口函数(Row_Number())

Multi -Statement  Function

Backward scan

CTE递归

 

并行执行的优点

说了一些限制,我们再来简单说下优点,要不开此文章的意义何在J.实际上在上半部分文章中大家可能已经感受到它的优点了,这里再简单总结下. 实际上在并行中

计算工作是均匀的分配在参与并行的threads中

所有的threads同时工作,无先后之分

某些操作中threads自身的工作完成后还会协助threads工作

虽然会有短暂的CXPACKET等待(数据分布,预估等问题)但基本可以解决或是缓解.

执行时分支(Branchs)间可以是无顺序的,更好的增加了并行.

针对CPU-Bound的操作,SQL Server可以说时随着CPU(并行度)的增加,性能也基本是线性增加的.

 

并行相关的设置

SQL Server中有并行相关的一些设置,主要有两个:并行阈值及最大并行度.

并行阈值:上篇已经提到过,查询子树大小触发并行的条件,此值的设定仁者见仁智者见智了,一般设定为实例执行计划编译的平均查询子树大小上下幅度不超过20%

最大并行度:查询中的操作符可同时采用的线程数.这个值便随着NUMA的诞生应尤为注意,早在SQL 2005研发阶段,NUMA开始出现,而SQL 2005也提供了支持,但程度有限,随着SQLOS的进一步演化在SQL2008时对其支持已经很不错了,这里有大家都知道的NUMA架构下的foreign memory问题,实际上SQL Server在采用并行时会试图将并行的线程都集中在某个NUMA节点下,所以我们在配置初始参数时并行度最好控制在某个NUMA节点的核数内,而且最好是偶数,这里面涉及到很多SQLOS的知识,限于篇幅就不深入了.

 

使用并行应注意的问题

强制使用并行

Trace flag 8649 在SQL Server中可以不触发并行而手动指定并行,注意这个标记是无官方文档记录,勿轻易使用.使用时只需在查询最好加上query hint:Option(querytraceon 8649)即可

数据分布不均,预估,碎片等问题

导致CXPACKET等待以及过多无谓IO,应对方式创建临时对象,更新统计信息,整理碎片等.

nested loop Join导致的随机IO,及nested loopjoin预读问题等

冷数据中使用并行nested join可能导致实例的IO稳定性受影响,面对具体场景应酌情使用.应对方式可以关闭nested loop预读,而nested loop预读时SQL Server也会试图将随机IO转化为连续IO,如具体应用合理应接受并行nested loop join.

线程饥饿问题(worker thread starvation)

前面我们说过,线程的授予是按照分支(branches)及并行度授予的,如果并行度高,此时复杂的查询下分支又很多,这个时候可能针对某个查询分配过多线程,加之这类查询又高并发,则这时出现线程饥饿的几率就大大增加了.具体生产中,这个应引起我们的注意.这里给大家举个简单的实例,感兴趣的同学可以自己测试下.这个查询有5个分支,分支所申请的线程就是5*16共80个!如图5-1

code

---- worker thread starvationselect a.productid,count_big(*) as rowsfrom dbo.bigproduct as ainner merge join dbo.bigtransactionhistory as bon a.productid=b.productidinner merge join dbo.bigproduct as con c.ProductID=b.ProductIDinner merge join dbo.bigTransactionHistory as don d.[ProductID]=c.ProductIDwhere a.ProductID between 1000 and 1020group by a.productidorder by a.productidoption(querytraceon 8649,maxdop 16)-------much join with many branches cost many threads

 

 深入解析SQL Server并行执行原理及实践(下)

并行死锁

并行执行提升查询响应时间,提高用户体验已经被我们所熟知了,但正如我一直强调的,任何事物均有利弊,我们要做的重点是权衡.并行死锁在并行执行中也会偶尔出现,官方给出的解释是SQL Server的”BUG”,你只需将查询的MAXDOP调整为1,死锁就会自动消失,但有时我们还应追溯其本质.这里用一个实例为大家说明下并行死锁的原因,以便我们更好的利用并行.

生成测试数据

code

CREATE TABLE Numbers(  Number INT NOT NULL,  CONSTRAINT PK_Numbers    PRIMARY KEY CLUSTERED (Number)    WITH FILLFACTOR = 100) INSERT INTO NumbersSELECT  (a.Number * 256) + b.Number AS NumberFROM  (    SELECT number    FROM master..spt_values    WHERE      type = 'P'      AND number <= 255  ) a (Number),  (    SELECT number    FROM master..spt_values    WHERE      type = 'P'      AND number <= 255) b (Number)

 

 

接下来我们执行如下语句,取30000下最大偶数,此时我将执行并行数maxdop随意调整为奇数,3,5,7我的执行都可以迅速返回结果.

code

set statistics time onselect   maxN=max(num.number)from dbo.numbers as numwhere   num.number<30000   and convert(integer,convert(varchar(max),num.number)) % 2=0option(Maxdop 3,-----5,7querytraceon 8649);

 

 

但当我将并行数调整为偶数时,执行时间居然长达数秒…打开trace profiler跟踪dead lock chain我们发现,当并行数为偶数时出现了死锁.

注我们用Trace profiler捕捉死锁

如图6-1,6-2,6-3

code
select    maxN=max(num.number)from dbo.numbers as numwhere    num.number<30000   and convert(integer,convert(varchar(max),num.number)) % 2=0option(Maxdop 4,-----2,6querytraceon 8649);

 

 深入解析SQL Server并行执行原理及实践(下)                             图6-2

 深入解析SQL Server并行执行原理及实践(下)ajaxeptPphP0KuUIsFqtV6nppvVQqNRqMvX65yH/D7fsR7hY9OTWQTFkVq2ujy6Wcxci2WgEAALA8hTOVVazWCQAAHjOZeJWMv0omppDxcXhQJOv+rxcseTDRhzgkjji01jN5IRF9hbwaA40Zj55PZWuzPBOnYvsOYSejOJONpx64Qlk/LR4CQrr9kh0AQK1SEwQBeaCqyqrmpmYhM7eoVlRbUysWiW9qdbtOuuPH7Dh8Z/QS5OlnQs4Betk5HI64mLiWlpYOW4esXuZ0OuukdTU1NSzLNjc1MwzT3tYuEUtQFMUxvL29nWGYkuISIVDJYDA4nU6GYUS1oprqGuF8jEajRCJxuVw6na6ttc1sMldVVmk1WgBAk7Kpo6NDIpEIy5IoikokEpfTlZ2VrdfplY1Ko9Go1+lFIhEMwwqFQsjuqlapK8orSktKTUZTdVV1TXWN2+3u9bJcJyTLPXD7fuRQ12/0QEiwFpm/HD9xEFq+g0qIgOevY4xOOtgf8T2ALlqCnkpg1e28x8O2t7NtWlYlgSeM9szdxWEPYi7z+4A+zJB43u2kAvfAG08wFenQnHWMshlbOcv9xVx09SrYewO6fiOZ1586Aj9jPNSEZDFbqquqzWZzVWVVZnpmS0uLslFZVlYmlUqrq6tra2q7WtE0XVpS2pfbZWdCHZYtKS7BMKxJ2VRbW3vrr64vGDghFRUWORwOs8mcn5fvdDrVKrXgy15eWu7xeEqKS5KTkqVSaXtbe3JScmtra31dvUFv8Hg8VxKu1FTXKJVKWb1MCF0CAFAk1dHR0djYqGxQSkQSqUQqEok6bB0AAMG5XCqVCj5rKIpKpVKP25OVmWXQG5qUTSajCUVRsUiMwIjVYpXVy3Ranc1mw3Hc6XTW1NQYjUaLxSIRS3rNdIp2I6QB2ZBUKhUMw315yniEW9EDIREQXZiBTJsEzVuH+R/CdviRSdfQbTvI5FRkzhx07wU6r5SDBKssT17YBc3dxiL/o2wE+rhk5zRiPmuIoka+MR9euoMnKbYyGztynooKQf0v4Lu2kNmPCOkGPNSEZLPZMtIzbDZbdVV1fl6+1WIV1YquJFxRqVSZGZndfypqlTozPdPpcN7ayU3grhfoq6muQWDE6XQaDAa9Xt9rw1sxQEISUpgr5AqX01VcVCzMBRVyBYIgZaVlBoOhualZKpE2NzfbO+w52TlSidTtdjcoGlAUraqqUiqVdoe9tqa2rKxMIIm2trbamtrmpmaPxyMRS0pLSrsW2Vwul1QiFZyoYRhmWVapVEqlUp1OJ5PJ5HK50WCsq6tTqVRtbW110jq1Wl1UWNSgaBBKNDU2NhoMhjppnVaj7ZUgumdqGJCXndFobGpqeuT23T/0aEPilOXQuP/Am46ToSfR3Wc5p4NtamIkFegCb/RoBNPYKsSEAtSIzltI1Wh/mlN/MNCXekh0ajSZWMoZW8lzp4nITAAAYAi2tY0pzSXCr9LVtTz6aMnuBjzUhARBUHFRsU6nU8gU2VnZHrdHVCtKT0tXNipTU1K7J4DW6/VJiUmNjb17snQt2VVXVkMeSKvVCutj/fgUA3T7FuKEBJ8CIWAIACDUMheSzoHr/gsAAIqiBOuR4FjIcZzgmS14MXR+NI7DMEzQp2maJEmWZWmKFty+hSF4nhfMSBzHCethXWN12WuEDZIkMRQTfByEc+ujQQdFIJv1XsQhAQAEn41+fDePIBBSTk7Oj7ucbcymLSAlhV7gTXw/B5S1AwAA4EFbA7N8JRtwDVi6Huhg5ofNQOUEAACnHuTW3OeTfxCQkpLSh4qxDAAAcAxP/I9a2u4WDzUhuVwuiUSi0Wja29tLiktk9TIhIalWqxWWsLrfrNpa2zTtml6HFjI1sCxbXVlttVoVcoVYJO4qrXRXGAgh3ZBxvDMO/boN7Prbm/dft3vd9BZ08dYt+gAAnuNZmr2hyS0d/rhxfdAfe+jmdghAH5wPOR6FOwlpoDOkRxgIlErlE088ER0d7XA4bDabzWaztsqNlyP0h4/oTl/UnzimXbfDkJxmCAvTXwxo+3Jy+6pd+oBgU02jtaFWf3BX28wVZrnG1mGzSor1x8KsZqutz7Db7Xa7ve/6DyAcDkdoaOgTTzxxO0IqLCz4Sb7WXsG3a7h2zU98ErcHx3EjR458SAmJIAgMwywWi16v7+joaGluYRhGq9UqlUpRrUihUPQlfPUm3PvyE/0iJIZieLYzlytL9+7jJ1Sj6PQnvF5L4mYFhuuhLcXQJN1V9EgYi6VZwAOe5Xsd966dDxkW9ni65bJ7REg/ERobGx977LHHHnvs8ccf/62AJ37/2//3i197ef32yb8+/tSTv/Hy+rWX16+9vP7fDRu/+M31Pb8VGj7x+8f/8n+/7TN+97vf/fnPf/7zn//8xz/+se+tHjQ8/vjjjz322K9+9avbEVJZWdlP8rX2Cu7gUXzUZ0xzy099IrfFqFGjHlBCGvMlQTAEAOT1R/ObpC+4SbnHfroLe2M5i1v76aMAAMir11yvD2GdbpPp7t2+eYAgiNVq7YsjBgAAQZAu9oVhWEgR1F0BgqAe43mFiFeCIGw2m91u7yL+jo4ODMP6do3vDjdmahiAl11fwLIs9L8Bj8dzV/kzPB5PbGxsaGhocDeEhIaFhIeHhIQEh4SEhIeHhIeHXLoUEhHxo4SGhoSGhkREhFwKDwkR2oQEd271jtDQ0JUrV3p5eXl5ee3cuTM6Ojqkz20fNISGhsbFxd2a1FIkEnl5eX3wwQdTpkz56kHDlClffjAswuuXon+PnDN37uTJk3/qE7oBkydPnjRp0q9+9Stvb+++/5LvD/C9B11DR2KZeXhBGZpbRBaVkUVlVEk5WVRGFJWRRWVEUSlVUk4WlwmHugtVXC5odqoVl1Ml5d3bdt++SYjCUqKws+c7qPUuFVXIzv2ul99iHa7+EBIA7e3tSYlJgtO2YNcRDDwCZ3TZkITLpZArmpubhW2bzZaRntHW1saybFfS0ZrqGrOpc+Gxu61IKJbhsDtSU1Lz8vJomhb6VCgUgnsex3H3NkFPV2Cs3WYZUBxSr+A4Ljg4+Pz58w/vXa/vCAkJ8ff372M+kp8QRUVFAiGNHj0a6kNGyIcOGo1m1qxZEyZMGD169Jj7g4kTx3z5ZZ80R48ePXHivu9nWgcN9v3V46PHjx8zdux/+eTuGlOmTAkLC/upv8YbIG9qEc1bRL402DVosP1frztfetM1aLBr0GDPK29Dr77jGjQYef1d6NW33S8Pdr88GHr1Hfi1IfBr77gGDYZfG+J+ebDjpTccL77hGjTYNehN16DBzpfecL/ylv1fr7kGDUZeH+Ia9KZr0JvQq++4Xh4MvzZEaIW88a5r0GD3y2+5X37LNWiw48XXnS+9aX/xdeegN6/3c7cy2PXP11zvjuBhuH9edhAEJV5J1Gl1EATJ6mUGg6GqokomkyEI0tzcLBKJ3G53TU2N0WC02+2lJaXdS/m1csAkAAAgAElEQVQ1KBpYlpVKpFVVVS6Xy2K2FOYXmoymBkWDQqFQq9W1NbVarRZcnyHRNJ2eni6VSmmaFvKa0zQtq5cBAMQisVgsJghCKpE2NTUN3IegW7bvuyOkduBeDtzLANPex5FIkjx37pzD4SBJkvi5gyTJM2fOGI29Ryb/tIiLi3v//fePHDni5eX17rvv9s+B9RFugNsDrmeS7iOogBDsuZdAXe/Zph8BAHDy5On63Lz2tAx3WUXs1u1IeQ1bJ4Mrqg7Pna9Jy+BlCk1qOlpde3X33sqgEFdJma2gqKOgiJMpTNm5vLyhLjK66Ow5TqZg6mScTCG+FBmxaUvyfn+2Xm7OycNqRMFr1plz82lpvSU3n5bUmXPy2lPTeZmCktR5yio5mSJh1x59ZnbCrj2u0jK2Tsb0T+rlrLIJsJzJqOsHIRmNxrLSMpZlm5qa6uvqJWJJfm6+3W5HUbSyotJsNre3tyckJCjkCplMVlNTI/AHAADDsOKiYpfLVVNdI5PJysvKZfWyivKKxobGlOSUosKitta2rMwswVNDICSPx1OQXyCkD8/MyExPS9fr9SXFJRRFNTc1FxUW2e32hPgE4XwG+OWiMNQPG5IGsHoA7wTwDsD29RZGkmRISAjxPxM8GxISYjLd3Y3pPoPn+cmTJy9cuBAAEB4e7uXl9c477whPRo/QbzB+h9CpM8DdGMx5DEe+nweN+A8YQJrk/x3Ex8Wn52R7r1oVdfXKpbh4ggMAAKVGs2nXriatprG9zf/EiaLq6pMXA5KzsyqkkvNhYWFxcY3t7RcjIhraWhWt6vyKH6vziRsb5i5dIlE2NrS1XYyIaGxv8zt+LDQ2xgbDJy9eRDkuMDJi/7FjcrW6Ri5X6fUAgJikJKPTEZOUBN2L78tk0Paj/IRep29saAQAEATR1NTkcrqkEqkQCarT6ZqbmimKam5uNpvNGIZJxJLmps4lO7PJXFlR2apubWhoaGtrE4JeRbUirUZrNBhbWlra29qblE3Cs2nnkp3DIcS0siyrUqlqqmskYklNdY1QHlBUK8IwTKvRtqpbBx6HivYr23crABzgCcATAPR1jkaSZHBwMIqiAzzjhwXBwcEPPiF99tlns2fPFt4KnPTmm2/2j5M4jlO16vQma//Myz8bmIPDOp4fRFdU3VUrtkHpemMoumYjT9zTulY/RzAMY7fbdTqdxWKhKEr4hTEMQ5Ikz/OCHbG7YUOIzhH2IwjCcVz3yKGut115cbqadKUuFY52mfFpmhZsJ/ckD0B/COl+lZ8QvO/uz1gCEBjuR2Bsaz9GekRIDyDGjBkzbdq0rrcCJ7311lv94CQUw8d8t/rI51PxBUs8M+Z6pv8o2JyFxDzv+yFzvaHv58Ez58OzBiYz58Mz58PzFhEhl+72OpzesFHxh6fY4rv266NzCxx/eY6Mib/bho/wUOOuCQkhOutB/PeFpVmGYu7PWILAEGSzmOx3mTroESH1jgefkFiWnTBhwu7rX7mAS5cuCWt3falX1h0ohn/49bKzz7+Fv/i6Z7a3Z+5izxxBFnXMmG/5bp5l2jzLd32QaTdqTrtlT/dDN+6xfjfPPWeR5x7IYs+cxY4X34DnLb7bq3olKqri9XfInfvvtiHgeXTTVvfr73LaR5a8/yH0j5C6mvd6i++75u0I6cf4VqG40fU+7zkb8SyPQJDN2klIj2ZI9xIPPiFJpdJnnnmmqurmxaWIiIh+2JNQDP/PtyvP/GMIO2Nu9/0YxS3aFvfWxENvjj/4zqTDQycfHTr5yNDJR9+ddOSdL4W3XXJk6FdH3xh34I1xB96bfHTo5KPvTT761sRDr431f3W039tfHH7vuuZ7k4++88Xh18f5C10Jr+98efjzuefdWA95XO42TEQQeMlKxHt5Py4svHAZNH0uuHsDL2c2u98fBX8/j4cepeD6X4HHZVc3yVtbGtvVytaWhka5pC9xSCaTSSKWqNXqXvs3GU0SscRut/fj3AQbksvlqpPWKRSKLm9yoeREPzrsFSgC96P8RH8JKSQERWBe086q1GxzC2uwcB1Ovu9/W47kzB1d66ecWcNqLLdVpmA6PYPt6OQ/traE1bk5tYRKS6crG3gMYsVVVFIqERlFxl8hI5I5igcAsPJqMjaeDAvDTwWQUXFUqRQAwDt0ZGQEXdfCUzwAgLfrqfRsnuAAz4PbLyI/+IRUVlb2u9/9rrKy8tZDkZGRXl5eb7zxRnt7X70oBUK68NK/m4cOP3P8VHBIWGBgcFhomMPu+Wz6Sa+/Lhr6xYEnXlvr9fxyr78v9np66S//teq3r6zxenap19NLvP6+xOuFFV5PL/F6fvm0FcGfzTjp9cyyX/xjhdezy4aM958w99zCTVH/GLHd66lFXk8v9XpqsdeT3i9+tHPs7DNeTy/tbPjsMq+/LnryHR+XB0dxPOHqlczsbB6AZlWLog85ynoEsnhF/wiJDAx1vfYO29SfcFemUel8bhC298BdtuNtNv21awn79+8LCwsODw8OCwsKCwuKiYkICwsKDr64a9fOs2dPdx0KCbkYGhoUFhYcEnIxIiI0OvpSSEhgSEhgeHhwaGhgaGjwwYMHduzYERQUsGvXTl9f382btxw9evjSpVCheXh4cHh4yIkTx3x9fQMDL3Rve32I4G47g0JCLoaHh+zYsWPTpk0nTx7fvHmzr++WwMCAkJBA4TxDQm5oKGwLDYV+fkZyMSQk8MyZk+fOnQkLC/Lx8dm1c9vhg/sOHdy3Z9e22KhLqiZ5X7zszCbz1YSrUqkUAOB2uxmGgSBI2MBxXKAfDMVQFK2srGxtbXU6nRRJddg6hJx1NE17PJ5eWUrwsoMgKPVaalFhEU3TCIKwLGs0GmtragEAHo/H4XAIqe0GUrm8C/2rh9RvQgpFPS7Sb5t75MeeT6dipy+g6/czpjs8DN5YsMQoQr5Zw8A0AICTFcPTJnnGzSKrb8n9zmJ0Xg4ZftEzfATqF0CmFLLqemTxMiI5j/D3QVZtRPcH0MVp0NTv0K374VU+ZEICPGslCzMAAB5ycWYz7usNrTzEWW2c08OpRciMqdCC9ZjveniRD2NEOGUh/OV3xOUU6loadS2Nkfd8NR58Qqqqqvr9739fXl7e41FhnvT666/3kZMEQgp8ZVj2X/4eFhSkVCqVSuUBf39tm27C7NO/emG53+mMSfPP/fKFFSMnHx782d4x35/cuO/Ks+9tGTJ2/wdfHvzTG+ve/HTP34ZsGjn58Cb/xF++sPx3L//w6xdXDf5sT0aB4vCF7PGzTr89et9bn+/95Jtj70888OKIbWdCCgd/tvfX/1r12se7XvjAd+g4vzc/2+Px4EplY25u7vnz5xUKha+vb3Jycv+uD7Joef8Iia4Vu159h5X3M3c4fua88/lX6NKev5cewbLszJnT//73v2zZslKnk7W2VqhUFTJZnrf3DJksv7W1MjLydF1drl4v1utFZnOdwSAxGiU6Xa3JJI+JObtjx9r29hqzuU6nqzWZpBZLQ3j4ieeff1qlKlu+fO4TT/z2t7/9dXJyqEYj1mpr9HqRwSAym+VlZcnPPPNUVla0xaIwmaQ6Xa3JJGlrq9JoqpubSzSaarNZqtFUm0xSrbYuKOjI8OFDTp3aX12d9vrrg1588bnduzfo9TKDQazXiwwGsU5XazCIdLparbZaq61paSlTq8u12pqfo4g2b15x9WpgZORpb+/pkZGnjUaFwVCfnRm/Y9uWNlVjXwjJ4/GkpqTabDaj0SgRS5qUTfl5+XKZHPJAErGkrq7OZDRlZWY1KZvsHfZWdSsEQQ2KhprqGo/HU1ZWJpVIqyqrKsoqdFrdHX5XAiFRFJWVldXc3EySpJDGm6ZpuUwOAKiuqq6srEQQJDc7t7am9h7EIf0YGHsfCCk4GEUxQJH4Dh80IBfQJnTRCuSHddjxWK5Vge/ZiZ6KZOVSzHcduvUoo7VQEaeQZWuIhBLeocG3+CKrlkOfLWZhBgCePLwJu5hBhfghByJuHonnOLOBra9BFn5PpFWz7QZWUQLPmI7uPIr5roWX7KCKyujyXGj8aHTXKfzIYWzHXuxEKOe5Tu+OJmjUMORAOGt2AADY8jTEe5Hni6/hxT/AC1eSJY2cshT5fiWRmEolXyPjk5l2W4+f98EnpLKysj/+8Y89zpAECPOkwYMH94WTBEK6+PIHJc/+K/pSRMLVq6mpqUGBgdo2/biZp/7y1sbEjLqdR1OHf3nohx2Xx88+/dWCc9sPJ7/2ya6tB6/tPJoyZOz+Q+ey1+yKf+odH2+fCK9nlj7x8prfvrT66aGbL8VXRVypWrcrYeXW2F3HU6ITq0Piysd8f2r7oWsb910Z8dWhsLiKFVtjdh1J9d4YYbV5aJquqqpqb2+vqalZtWpVYGBg/65PvwmJszs8n0/Ez1zo37iA56GJUzyfT+Ccrt6VhRE5buzYsX/60x8cjqbo6PPr1y+rry+oq8v/61//rFSWulzNW7eucbubr14NmTJlwoYNyyCoNTLyrI/PCrNZ5ufn+9FHwwCw1dRkLls2Ny0tsr29xsdnxccfjwDAkZYW+ctf/vLll19MTAxdtmzu5s0rAwOPTp06ceXKBSZT3RdfjF66dI5aXVlSkrRs2dzc3Phjx3bPmTMtOzsOALdKVbFly+qIiDMAeLZu/WHs2E9MJjkAyOzZ3w4a9K/8/ASathw+vOPgwW1Wq3zNmsVTp07cu3cTwxi3bl1z5MguvV4CgBsAx89OoOzsOKWyNCDg8IULh4qLkwCwA+BpVlb57dvd2tLQx/ITVZVVAACNRlMnrWtQNBTkFUAQRNO0qFZk0BtMJlNGeoZYJJbL5HKZXKvVymXy9rZ2FEVLikuSkpIK8wuVjcqS4pI7+AoKS3Z2u70gv4DlWBRFC/ILkpOS5TJ5TnYODMOtra35efkOhyMzPbOqsupeEZLdZrHfTeqggRESAITfduxSGQAOdOEK/GIg/I03lXkN/mYWXVWP+3h7vvJGvBfgEelURAC6eqlnynrivB+8ZB+dGg6NXszCLAAsvmMlkaNic4OQ3QG3jsU2iMmocGjKl9i

                                图6-4

 

而反观并行采用奇数并行数,这时当分发数据时就不会造成某个thread所持有的数据只是奇数或是偶数,也就不会造成后来的情形,死锁也就不会出现.如图6-4感兴趣的同学可以做实验调整并行数并阅读相应的执行计划.

 深入解析SQL Server并行执行原理及实践(下)

                                    7-1

 

可以看出我的这条语句由于对大量结果集进行排序,致使消耗了365MB的内存,并且由于分别对actualcost, quantity排序使得在进行第二个排序时内存不足并溢出,排序的操作只能在tempdb中进行.

Sort由于是典型的计算密集型运算符,此查询在我的机器上执行时间为5s

大量的内存被个别查询长时间独占,使得Buffer Pool的稳定性下降,进而可能影响整体吞吐.

关于SQL Server的Sort限于篇幅这里就不细说了.

在介绍”类MapReduce”之前,我想先接着上面Sort溢出的现象给大家简单介绍下通过Query hints 来影响优化器的资源分配

code

Declare@p1 int,@p2 nvarchar(56),@p3 smallint,@p4 int,@p5 bigint,@p6 bigint,@i intselect @i=3001; with p as(select productid,ProductNumber=convert(nvarchar(56),ProductNumber),reorderpointfrom bigproduct as bp)select@p1=p.productid,@p2=p.productnumber,@p3=p.reorderpoint,@p4=th.transactionid,@p5=rank()over (partition by p.productid        order by th.actualcost desc),@p6=rank()over (partition by p.productid        order by th.quantity desc)from bigproduct as pjoin bigtransactionhistory as th on th.productid=p.productidwhere p.productid between 1001 and @ioption(OPTIMIZE FOR (@i=5001))

 

 

通过查询可以看出由于我加了Query Hint,改变了优化器的资源评估标准,使得优化器认为productid本身需要资源从1001 and 3001分配变为了1001 and 5001分配,内存申请由365MB变为了685MB,接近一倍的增长,避免了溢出.并且执行时间也由5S变为了2S.提升了用户体验

如图7-2

 深入解析SQL Server并行执行原理及实践(下)执行中输出实际执行计划可以看出,此计划中消耗的内存15MB,和上述的执行计划相比有指数级的下降,同时执行时间为不到2s,保证执行时间的同时明显降低了资源消耗,从而避免了实例级的影响.

已经很美好了:)

如图7-3

 深入解析SQL Server并行执行原理及实践(下)code

select bp.productid,bp.productnumber,bp.reorderpointinto #pfrom bigproduct as bpwhere bp.productid between 1001 and 3001 alter table #p add primary key (productid) Declare@p1 int,@p2 nvarchar(56),@p3 smallint,@p4 int,@p5 bigint,@p6 bigintselect @p1=p.productid,@p2=p.productnumber,@p3=p.reorderpoint,@p4=ca.transactionid,@p5=ca.linetotalrank,@p6=ca.orderqtyrankfrom #p as pcross apply(select th.transactionid,linetotalrank=rank()over(order by th.actualcost desc),orderqtyrank=rank() over(order by th.quantity desc)from bigtransactionhistory as thwhere th.productid=p.productid) as ca drop table #p

 

通过查询时输出执行计划 如图7-4所示

我们可以看到通过将外表数据放入临时表中,使得内存消耗进一步降低,而数据较为平均的分布到多个threads中,你可能看到其中不少threads是没有数据的,其实有时需要我们根据查询管控并行度的.而在执行时间上有可能得到进一步的改善!

 深入解析SQL Server并行执行原理及实践(下)

                    图7-5

 

至此这个优化更合理的解决了面临的问题!

我们的并行原理及实践也到此为止吧.

说点体外话,不少朋友认为SQL Server是小儿科,没内容,技术含量不高,而且在国内的互联网公司中又显得格格不入.这种想法真心Too Naïve.这里我可以告诉大家,SQL Server,乃至关系型数据库的水很深. 如果你是相关的从业者,全身心的投入进来吧,其实很好玩.

更多Inside SQL Server请关注

微信公众号InsideSQLServer及我的新浪微博@shanksgao

认为有帮助的同学请点赞!




原标题:深入解析SQL Server并行执行原理及实践(下)

关键词:sql

sql
*特别声明:以上内容来自于网络收集,著作权属原作者所有,如有侵权,请联系我们: admin#shaoqun.com (#换成@)。

南乐的物流:https://www.goluckyvip.com/tag/98555.html
全南的物流:https://www.goluckyvip.com/tag/98556.html
南丰的物流:https://www.goluckyvip.com/tag/98557.html
越南专线出口物流:https://www.goluckyvip.com/tag/98558.html
印尼货运物流:https://www.goluckyvip.com/tag/98559.html
印尼物流货运:https://www.goluckyvip.com/tag/98560.html
长治婚庆女司仪和主持人:https://www.vstour.cn/a/366176.html
北京丰台区水上乐园哪家好玩?:https://www.vstour.cn/a/366177.html
相关文章
我的浏览记录
最新相关资讯
海外公司注册 | 跨境电商服务平台 | 深圳旅行社 | 东南亚物流