SQL Server 优化器特性导致的内存授予相关BUG

9/1/2015来源:SQL技巧人气:1714

SQL Server 优化器特性导致的内存授予相关BUG

我们有时会遇到一些坑,要不填平,要不绕过.这里为大家介绍一个相关SQL Server优化器方面的特性导致内存授予的相关BUG,及相关解决方式,也顺便回答下邹建同学的相关疑问.

问题描述

一个简单的查询消耗了匪夷所思的内存.(邹建同学发现的)

原文链接

Code

create table test_mem(id int identity(1,1) PRimary key,itemid int not null,date datetime not null,str1 varchar(max) null)INSERT test_mem( itemid,date )    SELECT TOP(1000)        ABS(CHECKSUM(NEWID())) % 200,        DATEADD(day, CHECKSUM(NEWID()) % (3 * 360), GETDATE())    FROM sys.all_columns A, sys.all_columns B    go 100select * from  test_mem where itemid=28 order by date

执行代码后执行计划如图1-1

QQiorK0UiUVdXF8YYISQSierq6oRCIQCQxzHGDQ0NYrFYIBBwuVwej9fY2IgxJnc7Ozurqqq4XG5dXR1CiKIoiURSVVUlEAhqa2u5XG5tba1CoSCJ+wMhRH40NzdrXdECTdPV1dV1dXVcLpfL5YpEIrlcTrKtqampq6sj1ZBKpSQTcquhoYHL5VZXV5Mc2trahEJhfX19d3d3R0dHQ0MDn8+nKKp/ceRxkk99fT2XyxWLxeRPmqaZNAPWlhCHJMMYq1Qqhlzsb3ZBA4KpGE3TCCHyzb7L1ISpDE3TTM3Zt6RSaWVlZW1t7YCN1WqyUCisra2tq6vr6uoSiUQikaitrQ0AhEKhWCyuqanp6emprq4Wi8VSqVQmk4lEIjI2qqqq6urqAKCnp4fP59fU1JBOUalUpBV8Pp/0YGVlJZ/Pl8vlpGimv/qjra2turqay+VSFMU0CmPc1NQkk8nIs0yjCNmRBuxGsdtI0zQhHSmX6RRCQ6lU2tTUpFUxMneEQmF7ezvJjTSkvr6eyYF5hIC5qFKpQkNDP/zwww8//PDEiROk1exH2IN/sPFP0re1tXG53IqKCnJRqVSSiUborFQqSUqRSERmX319PbssiURSV1fH5/Orqqqqq6ulUilCqL29vaamRiAQVFVVVVVVDTY8SPUI36isrKyqqiJUIgQnOXO53O7ubsJDyEhg15+pBsZYqVSSGra2tmqRmoyu1tZW0h1Ml2EWtMjCdDE7AXNLKBTyeLympqYh5hpTAZKPTCarqalRKBTMhAIAwnkIC21vb2euCwSCysrK6upqMk36D4A/J9SiiKZVGNMA0NDQ4OjoaGBgYGBg4OHhMWHChMrKyubm5vHjx7/99tszZsyYPXv2m2++efnyZQCgadrIyOitt95av349sPr15MmT//jHP8aMGTNnzpwJEyaMHj2aUFkikezdu5dk/uqrr3K53MbGxtdee83AwOCLL7744IMPDAwM3njjjXv37g1WXYxxYWFhdHT0119/7e3tnZmZOVhKmUw2ZcqUt956a/r06bNnz37nnXfOnj1Lbq1evfrNN9+cPHmygYGBiYkJETwAUFZWtmrVKgMDA0NDw+TkZABISUkZOXLkq6++evfu3V27dr366quTJ09uaGjoXxxbmI0YMWLq1Kn79+9nRrlAIFAoFP0T989kiK5iGMoQxHn6W/3LYqe5fPmygYHBu+++m5KSMliGjOIye/bst956y8bGJiMj45NPPhkxYsSDBw8AYMuWLW+99dbSpUvLy8vHjBnz7rvvXr9+PTAw8IMPPvjiiy9mz5797bffvv/++yqVqri4+Isvvvjwww+/++47AwMDT0/P1tZWhUKxYMGCN998c9q0aXPmzPnwww/37t0LfeVBf6xdu3bUqFHTp08n3cQk8/b2vn37NvtKZWWlVtuJbB6aSv3J5evre+PGDejL1wCguLj4s88+s7S0JH/u379/6tSpH3/8cWNjI/RltVpISkoyNDQk8tLS0vLUqVOg0VGSk5Pd3NxmzZrVv7j+EAqF27ZtMzAwmDp16v379wGAz+ePHj363XffJXR2dnYm4mHVqlVvvvmmgYHBypUrS0tLmRxsbW3ffvvt8ePHz58/f8yYMfr6+gAQEhLy0UcfffbZZ2QiR0RE9PT0DEafNWvWvPXWW/r6+gYGBqampjwej9w9dOjQq6++On369Orq6oULF7766qv79+/XIixbQoSGhr7//vsGBgZ+fn5a1Kutrf3yyy83bdpE/tTqMiZZU1MTe+YOQbrNmzfr6+uPGjVqwHb1LwIAfHx83nnnnbCwMPJnd3d3eHg4oc/Ro0dXrVrl6OhIbqWkpMycOdPAwODzzz9fuHChViX/zGAcdAghCgCOHTu2bt06cun06dNz586tra0FgPj4+AULFrS3t5eUlLi4uPj5+RHy1dfXT5w4kShujIIJALNmzUpNTS0oKKioqNixYwexn3bv3n3p0iWS+e7duwUCQX19/bZt2wDg8uXLpNzjx49HRkYOUWMHB4fFixf/z//8z8yZM01MTGDwjkxPT589e3ZnZ2dRUZG7u7uXlxe53traOmHCBKIjX7t27eeffwaAwsLCdevWpaamAkBjY+PatWvT0tIA4MaNG6+88srdu3dNTU1feeWV4uLiActi6sDn8/X09Ii+SVSkvLy8b775Zvv27RwOJzAwkJFJjY2NHA7H19eXw+EwBAQAPz8/X19fX1/fkJAQYm4CwIMHD0aMGOHu7s7hcOLj4wcjTnNzs7+/f3V1tVAoLC8vB4Dk5OSOjg5y9969e3fu3OFwOITdYIwTExPDw8Npmk5ISCCUefz4MZPb3Llzw8PDByuLmcwVFRV6enrk4qFDh7Zv305+UxSlp6dHVPj4+Pg5c+aQ66tWrfLz8ysvL6+trd26dSvRcz09PVeuXEkSzJkzh7DO6urq7777Ti6XP3ny5O7du66urhhjtrnTHz/99BN5lgAhVFtby+FwQkJCampqGFPmxo0bH3/8sZeXF4fDycjIYPdgXFwcoX9cXBwA8Hi8wMDAtra2nJwcYkOT6wAgkUh8fX0fPHggEonYrC04ONjX1zcnJ+f27dsbN25kMu/u7p4wYYJIJGIIWFZWxuFwmGFw//59mqaTkpKmTZsGAPX19dOnT3dxcSGJu7q6li9fPmHChBkzZsB/EkVcLtfCwuLOnTskpampaUBAAACEhobOnz+fpFmxYgXRKZubmydMmKBUKrOystatW1dQUMDk88MPP+Tn5z958qSoqGj37t3k4u7du48ePUpynjhxIhlp/YcHALS3t+vr65OB7ePjQ4Qowd69e2/cuCGVSjkczs6dO6Gf0cn+08vLa+nSpezrABAeHs7hcFJSUsLCwpi7AJCenk6oGhERQS4KBIL58+cvXLiQw+HcuXOHzH0AoCiKob9AIGDq1tDQMH78eGbi9AdTN4KysjJzc/MzZ86QEVJQUDBy5EiSMiUlZdGiRYSnpaam/vLLL8T0TEtLs7W1ZRqrk0ZqUYQQBYAAICoqysLCws3Nzc3NjQxTgvT09IkTJy5fvvzXX38lV4jWJpFIJk2aRPqMTc1FixaZmJh88cUXEomEXHFxcSHGExsMT7l06RKj18B/sg+SkpLMzc39/f1hyF7Mz88fO3bsqlWr1qxZw77e0tIyefJk8tvHx4fMzGXLll29epX9LGGvly5dGjdu3IULFw4dOvTJJ5/k5OQMVitSDaFQOGnSJCJvyHi9efPmhx9+OHnyZCMjow0bNnR1dQEAl8s9deqUmQaurq6NjY09PT3nz583MTExNzc3MzP717/+RewAANizZ8+bb745b948IyMjwgUGBJ/PJ7qYra2tnZ3dlStXNm3a5ObmRtixqakpKc7a2jorKwsAjh079uqrr+7YscPMzOzWrVv29vYmJiZEBgPA3LlzB1ML2ByhoqLihx9+EAgE6enpjo6OhK0AAE3TEyZMsLe3d3FxMTExWbJkCbm+efPm9evXjxw5Mjc3l6Gbp6cnMwBmzJjB4XAAoKam5ptvvtm4ceO8efPILbYbbUAsWrQoMDAQWJw6OzvbyMjoX//61/Hjx0kaiqLWrVv31ltvLV682MjI6OLFi0xbYmNjt27dSqi0c+fOmJiYhISEr776atWqVXv27HFycrp48eKaNWs4HA5FUfn5+UZGRp988omDgwOTs7+///Lly42MjCwtLdetW0cEMyOKJk2aVFNTw1QvJibGzMyMdLeZmdmuXbuUSmV6evr8+fO5XO6xY8dGjx5NpiHT5KysrB9//HFoIgCAjY0NY5ABQHt7++jRo2majoiIWLZsGbm4fPlyNzc3AGhubp40aRJRGq5fv07YOoGhoaGZmdmoUaM6OzuZi7t27Tp37hz5PWrUqJKSksGq0dXVNWbMmBMnTri5udnb2xPDlJndX331VXx8/OjRo8mfjJ+KcYcy397e3qTazHwPCwtbvXq1kZGRhYXF5s2b165dSzIJDQ21sLAg9NyyZQvRpZKSkkaOHPnFF18YGRktW7aM+BVlMtmVK1dISnNzc3t7e6Yh9fX1enp67e3tAzaKUWiYyhgbG3t6ev7yyy+hoaEAUFdXt337dhcXFzc3N3d3d0ah1NfXZ4t5YI1SnTTqddARUQQAaWlpJho4OjoSf0JGRsa///3vSZMmEXHCrEMQUUScnmwsXrx49uzZ7733HjGqAMDd3Z3wGrZCwaT38PBgvHzwn0TRl19+GR0d/cUXXxAdZDA8efLk008/nTJlyooVK8gVRlP78ssvt2/ffvDgQTs7O6I5rlu3jlhspOjs7Ozvv/8eABwdHR0dHY2Nja9evbpmzZqkpKQBy2KaIxAI9PX1ibxhbMSVK1dqmRccDufTTz89ffq0k5PT6dOn//KXvyQmJsrl8r179544ceL06dOnT58+dOhQQkICQ6WpU6ey2cGAdQCAxMTEjz76KC4uzt7efty4cSKRyNDQMDIy8rPPPjt69KiTk5OTk9OECRO2bNlCnjIwMFizZo1QKPzf//3fK1eu3Lp1i2FV8+bNG0wUsdlEeXn53LlzORzOihUr3NzcGA5I0/S4ceNWrlxpYmIye/ZsIyMjct3MzGzGjBlvvfUWscBItTkczujRo62srE6cOGFra0usz9ra2pEjR06bNm369OlapQ82QhYtWkTUf7awBAA7Ozs7OzumuNbW1m+//ZZ5ivHLTZ061djYmHTKmjVrCJc8e/bs2LFjq6qq5s2bN2/ePKFQOHbsWKYvDh06xDiX5HL5qFGjCAsTCARTp07dsWMHU0pXVxcjikgrHj9+fJoFV1dXiqJSU1NXrVrl6uq6a9cuFxeXkydPsmmenJxMRBEMKY2OHDlCOoKkkUqlEydOpCgqISHhiy++sLKyOn78uLW1NXFxy2SyiRMnEmedp6cnW3UzNDScNWvWhx9+SIxXkputre20adNIZx0+fJhZKusPQpC1a9eamJhYWloSg57pO1dXVzs7u7Nnz1IUxbSFLOYxC5nkure39/Lly5kKYIy//vprYsdipdLp06cTUYQx9vHxsbOzIzNr586djJw7cuTI4cOH2XUrKSl55513GOJ/+umnDKnr6uqGEEVs4pPvLVu2eHt7b968mZnm7e3tDBfdtWsX0fx+/PFHLV1WJ4EY9IYtAAywgDFmzBhCu7i4OCMjIz6fr8WbiPpApmVKSgrx5ALAvHnzUlNTg4KCmKXmc+fOMa4/BgyzuHz58oYNG7QuDgbig/Lz8yPO3MESp6enL1y4UCQShYaGsoVfZ2fnyJEjra2tFy5cyPC4JUuWsK2ikpKScePGAcCpU6f27NmTkJBQXFw8b968wdZOmDoTUdTd3U2uk4uLFy9mKEMQEBAwatQoa2trGxsbGxubU6dOEbMdAOzs7Kw1SExMJDnI5fKJEycyXEwL7NZxOBzi6TI1Nb1y5QoA/Pjjj9HR0ePHj9+zZ4+tra2tre2xY8eys7PJs99//71EIqmpqfnhhx+ApRMAwPz58wdz0LE1hoqKipkzZ96/f9/Y2NjNzY2xilQq1ZQpU1paWgAgISGBsWzWrl0bEBAQGRkpFouZDIkosra2fvvtt4OCgsjF6urqGTNmSCQS5grTzP8oirSwb98+xnYBAKFQqK+v338xYNasWWvXrrWxsbG2tnZwcCDlHjhw4MSJEwAwY8aMkpKSjo4OPT09ZmDb2NgcOHCA/JbL5fr6+ky7fH19GfWL3GVbRQCQmppqzcLhw4cpikPPT1+wYEFRUZGDg8OXX37J2B8EycnJWoJ5QFhZWbGtop6enpEjRyKEEhMTP//8c2tr6xEjRjDecplMpqenRyTKrVu3Fi9ezNRwxowZ2dnZ9+/fZ2IcAMDe3v6HH36wtrZ+5ZVXtMaklo7f2to6efJkIuk5HA4zBpg5+9VXX5HJwubswBpghHQ+Pj6MhkQwdepUxltOuBN5Kjg4eN++fdbW1nZ2drt27WIUDkJhdhGVlZUfffSRlZUVmYbHjx9nMhzaKtJqAgDs379fT0/vww8/JCJHC1u2bCFj75tvvtGyINkG1hBl/RnAjqCjASAtLW316tUMA5owYUJeXh4ApKamEg81ANy+fZtJQEx7EvLk4uLCDJe5c+cmJiYCQEtLy6FDhzDGFRUVW7du9fHxIQns7e2rqqqYIcv4Z/5AWzU/P59YNpo2qrPt6uqaMmUKAMjlcmtr6wsXLgBASkrKhg0b8vPzAaCzs9PY2Ji00dnZmVn8mDZt2oBDjZ25QCCYNGkSsYqYVjCLH3K5nGjQN2/eJGtUbEilUrbz7cCBA2ZmZuR3Z2fnV199RazArKwssgo6IK2CgoKI/mhiYkKaNmPGjNDQUNLk/vjuu++qq6tFIhGZtO7u7ozGMGfOHOJtHxBMsFBpaemMGTOCg4M3bNhw/vx5c3NzJs3kyZMJq3r48CGzSLtu3bpbt26R3w4ODsS7e+vWLeL7zc7OXrt27ZMnTwBALBYzrtSoqKibN29CP56lhcGsIhsbm2PHjjHJ+Hz+mDFjyO+IiAiGKU+ZMqWoqEgrT3t7+0OHDgHA9OnTc3NzOzo6JkyYwPApGxsbe3t78lsul3/99dekm8rKysaMGUPWV5iBx7aKtEIkGCQlJRFhw+Vyb9++TdIzbcnKymJE0RDTpKCgwNTUNCQkhPy5a9cuElvx8OFDZnF0w4YNJDyns7OTuKOLi4s3bNiQkpLC5Dxt2jQyKSQSCUPA3bt3E89eXFzcxo0biWePrSUwIkSpVOrr6xNV1cvLa9GiRexqKxQKfX39/j4Vhj5Mq7WsIgAYP348CTypqanR09Nj9CcDAwOm1StWrJg0aRL5vXfvXsZ4tbW1FYvFZWVlJBCDDawJsNLT0xtsrUhLTAYHB9vZ2Xl6evr4+OzevVsmk8nl8l9++YUxwrZs2UJ+h4eHb968mWSbk5ND9Bst4f2nRW/YAhFFZ86cmT59+qFDhwwNDQ0NDb29vUkQs56e3htvvDFr1ixDQ8PXX3/d1dUVABBCK1aSEO2113744QdDQ8PPP/+cxCCcPXv273//+zfffGNoaDhx4sRPPvmElMHj8bZu3UpydnNzYxYP8/PzP//883fffZcIKjxI0POw0Nzc/N13373++utkELDzXL9+/WuvvUa8EE1NTRYWFmRlODMzc+XKlYaGhnPnziXBMOnp6Z9++ukHH3wQFxd34cKF119/fcqUKcRjqQVmGBF1m8h