Sql Server tempdb原理-缓存机制解析实践

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

Sql Server tempdb原理-缓存机制解析实践

Tempdb就像Sqlserver的临时仓库,各式各样的对象,数据在里面进行频繁计算,操作.大量的操作使得tempdb可能面临很大压力,tempdb中缓存的设计就是为了缓解这些压力.这次就为大家介绍下tempdb的缓存机制.

在介绍缓存机制前,先简单了解一下TempDB对象

一般我们把tempdb对象分为两种类型用户对象和内部对象.用户对象指通过显式T-sql来创造的对象(如临时表),内部对象指通过隐式T-sql创建的对象(Worktables)

注:在引入版本控制后,也可以此单独分类(DMV sys.dm_db_file_space_usage中单独列出)

用户对象

临时表

表变量(包含表值函数返回值及表值参数)

临时存储过程

用户自定义对象

用户在线(Online)创建索引空间

内部对象

Sorts(排序溢出)

Worktables(checkdb,游标,Merge joins,假脱机,并行查询交换溢出,LOB对象等)

Workfiles(hash join 溢出)

Version store(版本行控制)

查看对象使用情况

当tempdb数据文件很大或者有异常时,我们可以查看相应的使用情况.如何查看,沄剑的Blog中如何查看某个查询用了多少TempDB空间有详细脚本.

Tempdb缓存机制

Tempdb中的众多对象缓存机制不尽相同,其中一些操作(如Sort)采用内部的机制,对用户是不可控的,这里我们主要介绍常用对象临时表/表变量(也是经常引发问题)的缓存机制.

临时表缓存机制(#t)

只有使用存储过程,触发器,Functions才能缓存

以plan cache的形式缓存一个IAM页和一个DatePage页

禁止Create后使用DDL操作

禁止命名约束

可以看出临时表的缓存是以PRoc执行计划缓存的形式实现的.所以batch,动态sql是无法缓存的.值得注意的是既然是执行计划缓存,我们就不能在proc中加WITH RECOMPILE关键字.

注意:缓存对象时局部临时表,不包括全局临时表.

我们通过一个简单的实例来分析下缓存实现.

首先我们来看下一般batch的操作(非缓存)执行两次,观察日志情况如图1-1

batch create code

use tempdbgocheckpointgocreate table #t(id int)insert into #t select 1drop table #tselect Operation,CONTEXT,[Transaction ID],AllocUnitId,AllocUnitName,[Page ID],[Transaction Name],Description from fn_dblog(null,null)

jspo2TnWv2TZ9XVlPgn/bFPdfWm3wuLj4oLsw6/97GHil8Eav0+qrLery2ddl7I/RJQgbf39P+98+V/9+7v/es3P/fbr/3Gr7Y/Mbv+8w/+4X/7X2++8p29g/0+7EF99fGz5UfosYdyXJgWH029oq8+frafX/+52llV0PsbXykkOyw+evZ00s541ibDtPholD1ZtMoIkln+2EPpGkhyWcqxB22eFC5nIGEg487a7dlvtZ+98plUFVz5jPalnm89fm3p9ENLi48ylpfKKb/e1epWHi6xfi3jktVOt3rH0T7s9aFvgbPPysNkDvf5OlDv+sJ1L3/oz/zMMY7W+TBUYWDfU32/jNRWb3q9tS/MSvzeKNAJVjTU931UYb3JRS8z9EtABV7nj7+y86V/+86T/+eb/+rca7/xqxufmP32Rz+8/ON/99//2qd6/+k/5VVQ6e3Ah86mU5PehjHmK2MpOT3dM4kmqH6PJ8vExpoJR+Wxhz76oY+cfezxzzA39oT2hJ0l93rjq8+VpJCPKtJCaKdF65Pbq1HhkQR69DNavK2/7OoS7PBs6IE9/pn0lQQQzBhB01dbeTjZdzLa4L4Y7CvNLCfXuNjtyc9ndnzzz896x91iN/WCK7lojoU/fKX97JXPmFlyXRnN/or2FBSCxT68nbOuG/br0sNXnJTJWNpHvD6Y6yIs5KGzD4c1RgmIdyt/L+SYz6qCSvfYxXWabA9GF3ylteXbU936sqTHdU8K/ZmfBRUUzSiXz9tfWO/SuLN2Fv2cQY6XUu+HyB1Yfj6b9drHMU+9Rb5nhe+Fxx46+9iV5CNdTeW5zgvXK5JLGyyz/fqtc27np4yC6lO9zoq3XwIq8Ha+/Ptb/+4Lbzc/94PPPPr9hf/9u/7Hv/ULP/f//v0fvzj5wP0gyKWgaJ8XH6UrIZ095p6PaSkpvbGXQg9ylHIow5jHHtKuntHftvaHazU+qxZ/c6/QpU6/53Ld86Z7OM9e+UxS71cfP0snaMb85rw3ux1CDyzyw9wUlOh2RJc2ZtLXp6DyjotkT2k+S+UUm591jbt1oBVBa3X47CHuFyMn3K+MUn8le0p7LHb7CN9Y7uOy8nA6oC6n+MbWPuL1gV0XX1s6HX6DxnffE4Vj/16QQo49KGGdam1LSqusPdWtL0t6XPek0J/5WVhBJf+Vypfsb7EzP+6ynVk/Z1DjFTWDXW5cv/h65XHMW2/O9ovfC5FYTQVqxl60ZE/pekUaoJcsrevUIIZbWH4Pqi/1UmvbQn/kUxAE3juXnrj7xPk3f/e3Xnv0X3zvkU+8Ont6/SMPtn7ix85N/O33giDnHhQ5+s/eyOFkvW4pMb3tpm9VCkq79Dx8Jav9rD5RsxRUUMbeUeIY2WekHooqqOjiUmoPKjEF64fVq6Dcx0W2JzefxXIKzs+6xt060NV4eCSjPCsK91e0p0Uh5L1S5xgXrUCXm2Fjah/x+sCuiyRBbA2n66ocBAWVlpN+am+PVlqF7RmogsJ1rz/zs6yCspXP2t+66S2bhfXUGT9ncOOljEJmv6R6pXHMX28p/0p9ikGycw57StcrmtGYmWz7+6Cg+lCvZlUp9Ec+BUHgvXXxs29+9tzr//LX7vzq2c3/9ZdfOf0/ffPB/+HpH/s7v/3jP5FfQSkTK9bQGcddmHu9fPpqFJRFEmjnVuOBz3vlUlSytmwGcIWSrk32U3xJRY+7KyhuyJK3TRht6LuCEsfFyZ50PvPlDJ0nIdqtolNG9jcu9EkhkGk8bApqXO0jXB+EdSF6NgUf0MrcgyKR1vbodqiuPQM6xccO6wm87vVnfhZWULYbjnb7V6agWD9nYOOljEJmv0ZJQYl2zmFP8XpFMkJBGaE/8ikIAu+N/+OTr/36/PfmP7H5v82++vF/8tLP/+Nn/rt/cPm/+ltf+pV/fi/I9xyUMmPU+6+WMTAtJaVX46NnbPTJ6vCkpvQGhcce0h5zisqxtZ+d0OpNL1VBie3MutK53UsOd731pcjbzfImCctppUwFldhNOukhlSO1X7KPvRz3cZHsyc9nuZxi87OacZdDlofHnAFgSmbGRf9G17593a+MUn8le36VPFan/Z1fIbiPi2Io5XTTCbMPf32Q1oXs2divq3muA5yCktcptaf2tptc2kZsTzXrK2Oe4LonhX7Mz1zjKMwHqXw3P0qxc949KNbPGdR46aNjn89SvYNSUPL3gmJnp1N8vD2l6xXbAEv70xq504D5lAw3z/tR7xCe4rvzzz/x17/y0Kb/S6/+0j+59gvTX/+Zf3j1733gsz/0N29/+1r38CCnghK3vLmPlKMX8pOCYryyPK7kefJPfSKTzuCHH03fFauqguz2q+fLw8ioQDqxrFv2tgOQqXuU/DcujToB7Ds0JbtJbzNPy9TPN+uNVCPTj+IayYOe8ZJjy7G3n11RQjn5xsViT2kSWsY3z/ysctwzJjndAynwBgVuXNQHZMmjrl+T+pWvv3nnrWwf23WGvW6Ie0fkSfQPPfoZenPh5NjH7foQr4vHycK/Ej0D/VXybLG0xHJdB0gh+k1ffp0Se55+6GzWdX4w6wvXvWLXvf7MzxzjaJ0PbPmOflS6B8iXL9rZ4ucM5HtK+zWC+Horzmem3q9ljKN7vQXaz34vhDOB2FlV9c72tF+vko6Yi51pf1oOfSNF/u8jbp73p95hfJNEe/b0X/3SP739z2Ze/vkH//xnGn/y93/8Cz/0N578nz/+fv2/BzVsYZhfC+vchQH8uFZ/2p/39AtCSSDsRYz2vKo7wD59s2e114H+X1Xyri9c94YzjO51cgz8nBMbxB2h0m8zzxUGNc+H8W3m3/rI/7j+c//oG//ov3/mp3/qT/7eB77wN//G4z83s3983Ol0TpSCIu8PXRp4YwoGY6d4xIK1/fFNDlz96wvCL0WO+ryqO8A+fbRnJdeBAV3ti6wvXPeGL4zwdXIc/JyTFzJHra+qeFDz3HkD6tl+KqjWT3yg9RMfePrH/s4f/Jd/67P/2Q89/gv/bP+99/a63d3d3ROloBAQEBAQEBAQEBAQRjf0S0AF3m/+3f/mX/zn/8Wv/+0f/be//L/c+ta194Ogs9fZjYGCQkBAQEBAQEBAOJnBePV89IjOwBuGwIZ+CajAey8IjoPgOAiOgqB7eLCrAgWFgICAgICAgICAgDD8oV8CKvB2rbgrKAAAAAAAAAAYe6CgAAAAAAAAAMAVKCgAAAAAAAAAcAUKCgAAAAAAAABcgYICAAAAAAAAAFegoAAAAAAAAADAFSgoAAAAAAAAAHAFCgoAAAAAAAAAXMmtoDQG23oAAAAAAAAA6Cf5FBQAAAAAAAAAnGSgoAAAAAAAAADAFSgoAAAAAAAAAHAFCgoAAAAAAAAAXIGCAgAAAAAAAABXoKAAAAAAAAAAwBUoKAAAAAAAAABwBQoKAAAAAAAAAFyBggIAAAAAAAAAV6CgAAAAAAAAAMAV7x4AAAAAAAAAADe8TY6NmDt37nz961+/cuXKF77whS984QtfJJgxAAAAAAAAAEe+oHLlypWvf/3rd8Aw8X0Oj41NeOONN65cufL666/v7+93AQAAAAAAAPXw/e9//8qVK2+AYeIHKm+99dbdu3e9NzmSRHfv3n3yyScPDg663f1ut7u3l4ZOZ6/T2aMxCAgICAgICAgICAiOIXSnO5293d3O9vb29vbOpUuX7oKB8g7HuzFbW1vb29ve22+/vb293el0tre33zbY2tr6vd/7vf39g2632+l07K+dAAAAMED29rr7+wf7+wfsH4NuHQAAAJHt7Z27d999992tpaWlLTBMbG9v7+3t7e/v7+3tbW9v7+zs7O7ueqF8+tjHPtbpdCQF1e3u7+52trd33n13CwEBAQFhOMPeXvcfNhr+Qw9pf/z2b/3W3l534M1DQEBAQJDC3bvvvvnW23fv3oWCGja2t7f39/d/8id/MhRRkYLqdDo/8iM/8slPftKqoLrbO7vvvrt19+47CAgICAjDGTqdvbnZ2T+4fFn7Y/nppzudvYE3DwEBAQFBCm+9ffeNH7wJBTWEhArqR3/0Rz/2sY+FImp3d9f74R/+4Z/92Z/9/d//fYuC2tvrbm1t3737zltvvY2AgICAMJxhd3f3U5/85MrKivbHM888s7u7O/DmISAgICBI4Qc/ePP11994++23oaCGjURBffzjH/+1X/u1g4ODbrfr/dRP/dSnP/3pp556yqKg3n777ve+d+fVV797+/Z3EBAQEBCGM7z++huhcNL+eOaZZ15//Y2BNw8BAQEBQQo3b95+6eXrf/Xqq1BQw0aooD7wgQ98+tOf/qM/+qM//uM/Pjg48H7xF3/x4sWLf/qnf2pRUG+99fbGxl+/8spfXb9+CwEBAQFhOMOdO6+Fwkn745lnnrlz57WBNw8BAQEBQQovv3xjff3br7zyV1BQw0aooH76p3/6ypUr6+vr0R5Up9P5nd/5nWeffdaioN58861XX/3ujRu3v/3tawgICAgIwxk2N7/3eLO5tram/fGNb3xjc/N7A28eAgICAoIUXlx/6ZvffPH27VegoIaNUEH5vv/ss8+mz0GF7+JrtVoWBfWDH7z5yiuvvvTS9Rde+BYCAgICwnCG7353c/npp1944QXtj2vXrn33u5sDbx4CAgICghS++c0X/+IvvnHz5m0oqGEjVFAXL15U3sX3tsPvQX3/tde//e1r//E//n9/8RffQEBAQEAYzvDSS9e/8532d77TZv8YePMQEBAQEKTw53/+l88++/XrN25CQQ0b/O9BmarJVFDf/e7mc8/9xTPPPP8f1p5DQEBAQEBAQEBAQKgs/IfnVlef/bM/e+batetQUMPGtkqkoN7leCdmZ2fnq1/96gsvvPjyyze+9a2X19e/jYCAgICAgICAgIBQefjLv/zml770pR0wxOzu7nY6HW/byu7u7ksvvfTUU0/9XwAAAAAAAIDa+PKXv/y1r31tFwyUThZ7e3veXQAAAAAAAMDQYH/KBtSKywB5lwEAAAAAAAAAuOENeqMMAAAAAAAAAEYGKCgAAAAAAAAAcAUKCgAAAAAAAABcgYICAAAAAAAAAFe8z1384pCHQZsIAAAAAAAAACK8YLiBggIAAAAAAAAMDyOgoIY/DHoQAQAAAAAAAH1iBBTU8XADBQUAAAAAAMDJwQuCwL+0zoZJXw8DUVB7Ks8999xzzz13VeXy5S/vDQIoKAAAAAAAAE4UkYK6tB5E4VLgC8GuoDzPs/y3QgV16ZLejLt37/qXLkFBAQAAAAAAAOqG34Myd58m/fXJyVIKypOxK6iOSqKgOp3O+rvB+rvBV17Y9f1LnUHw3nvvQUEBAAAAAABwcvCCIKC7Oi+++GLy93PPPRf+ce/evUuX1icn/Uz5ZBFIVClJf7MKSmtx2NpXXnllfX39xRdf/MoLu5curfv+pVJW8MTfxbJ8tLu7CwUFAAAAAADAiUJXUIlqCoLg6tWroXwKd34++MEPZsoniy4qvAe1o3Lp0vql9fWrV69eurR++fKXw+D7l3ac8TzP5Q8JWhQUFAAAAAAAACcKLwiCy5e//Morr7z44ovPqYQqJXxPg11BmUKIVVCZf7MKakvFfA7qKy/sRptR6+uX1tc3Nja2svA8j/5B/9USSBlD7t+/DwUFAAAAAADAySFSUIkaoXtQly6th0/7rL8bXL78ZUlBaUJI2l8qvAf1rsqlS+uvvfba3bt37969u74