Sql Server Tempdb原理-日志机制解析实践

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

Sql Server Tempdb原理-日志机制解析实践

笔者曾经在面试DBA时的一句”tempdb为什么比其他数据库快?”使得95%以上的应试者都一脸茫然.Tempdb作为Sqlserver的重要特征,一直以来大家对它可能即熟悉又陌生.熟悉是我们时时刻刻都在用,陌生可能是很少有人关注它的运行机制.这次我将通过实例给大家介绍下tempdb的日志机制.

测试用例

我们分别在用户数据库(testpage),tempdb中创建相似对象t1,#t1,并在tempdb中创建创建非临时表,然后执行相应的insert脚本(用以产生日志),并记录执行时间用以比较用以比较说明tempdb”快”

Code

用户数据库testpage

use testpagegocreate table t1(id int identity(1,1) not null,str1 char(8000))declare @t datetime2=sysutcdatetime()declare @i intset @i=1while (@i<100000)begininsert into t1 select @i,'aa'select @i=@i+1endselect [extime]=DATEDIFF(S,@t,sysutcdatetime())

tempdb

use tempdbgocreate table #t1(id int not null,str1 char(8000))declare @t datetime2=sysutcdatetime()declare @i intset @i=1while (@i<100000)begininsert into #t1 select @i,'aa'select @i=@i+1endselect [extime]=DATEDIFF(S,@t,sysutcdatetime())

非临时表在tempdb中执行

use tempdbgocreate table t1(id int not null,str1 char(8000))declare @t datetime2=sysutcdatetime()declare @i intset @i=1while (@i<100000)begininsert into t1 select @i,'aa'select @i=@i+1endselect [extime]=DATEDIFF(S,@t,sysutcdatetime())

由图1-1中我们可以看出,在普通表中执行一分钟的脚本,tempdb只需执行22s.而普通表在tempdb中也只需27s均大大优于普通表中执行情况.

感兴趣的朋友亦可在执行过程中观察日志相关的性能技术器的运行情况如(Log Bytes Flusged \sec 等)

QQmxTkSRisSqPWC1EkogkEclCLBZ57UEskrK+hovdYrlMpJLuTS+td/KbVuuifr3IXrK0s1ovk+V6tdvk9ZL/sS8ql4ud7JXs0+Lg3aZ81d6fSRLadYfw9Hd1jUUmDRh52ae5VVmtkUkAAABRc/RUDqujTBJCiG0pkw6r8u9tWiqi1UKsDuUx9UrJ4ovIvsJhty6UyWG3LlTNdqMoFks7y2SZ11/sFsXf+/1WOqaQZ+XfF7uFpKBs/Xn03PNFqf+qERPaJ4eo8XuxnS6T4orgZDXrdbpcJsvN9hiC2WzdkRqpnXSvyqR98RKSCQAAIDpqZJIQIi18jkQcD5EdEe0lOwZ5U/FRSqlTjvbIlZZ2VAkkhDjs1rIK0n2gi91C0l2lJLP0R9ZIxlL31cMT0FeGSBheMnkZTYorgnOxW2RmZL/JjENhaiyRmn1aSqBy0p0olNWFdjwAAADEQr1MKtmWPkfawPnQMPkiZWzVxX7TYDRJCcfmvovso6jaSf7c/SYfTTL3p1YmRaudUERQS38XSa8ySQSJ4BSmY79JNnuhyiRDpKa0LXmzkkwyR3YAAAAgDkqZdPQ/DmIhzWyRQ7NiK5I0/zO1SCk7pZLJA7FCiO1GWzJUOViUHomjndXaNDFPlkNFGLjS/mq9lCf4Gfsj0001DaydkEbQAb+XTd8yqWSwCI5LJpkiNS6ZZIzsAAAAQCzknooUhV2tyjUA5ez/SlzW8ZKZMpuCNonOMOnOWOloZ7Vep5t1NVvDYVdUHg+oTsZbrNeyK+P6aAtRCSekEXjh9AvJl0yKKILjHE0yDXrvU8mGyMssleOZdAcAniG1JoAHJuJAn5I2Sk4X4ZEg2gmBBH455YryipMiiuAUsZXNPnvLYneRBWLSvTVSIyfbTDabzHHJvBbpeDwYgLlAak2AETEFN1pKHd4ihXe3d51Ir9oJgQQ90e3S8iOTRoIvLySqxY0As4LUmgCgMSlPZYx4FE4IJOgPZJIDXzGXSFY2jgLjpFAYntAXgmdIrQkAMlOzcdMgqsVOAAWtfKP5yCRfcLMbCagBoBuhL5nukFoTAGRGbM7mBqoJgtPKDUImnQI3e0AvHzwS+jpqB6k1AUBmZCYMZHx4Tq89rW3k1r1r0zuv777w6PZr1mazdz24d/ncnWfZH1nN/TumBt+5uvX80/tCCPHstq0nL1w9MH7WO1e3bC9VeHDv0tqO1Pnnnr+8+05Zcf9O5dPLDosH9y5tp85xfrLTYkZqXAiRnRNrU3kfbt27tp49x2e1o5Xrg0zyy0yE0ySd7wkzMb1Eak0AkBmH5YJaikdRe5kk+9C6g25RNcV7VTkh8ez280/vFzJJUUFK+5XPfXbb2OZrTx3yxiBjLDhkkkPtGNWa1tT9O6UauX9H0UhtWzbJJNt5Lto3nVX/IJOiYkrCaQIeNmSM+6cktSYASERvs6AZXR9CrWRS/dCT5M2/9vT2a0eZdP9OXl9p4fZr13dfkMc9ntbKpEyE1Bbj8Euj0SQhZGVSDIhVuX9H/pTyi7hHfo5fp91okk0mXd99oalE9AEyKWY6qKZIhNMo/WloAL9sT5yYWjOeex8gcjBYE2EomeTyyK/vvpB586ryycute9eSrlDn7LWZdKcOcF3ffcE+3vXO1a0GnqLpCxpkUjFv0HAOa1q2fyldYapHvnB116QJi/NWnoo2Mw9PAJk0LuJXTfjQM4Ef2iMnptaM4cYHGAuYqolwgkzqoiKMFDIp49nt5y9v35FGb157KrWmHtxm0t39O49u3XtmVGJ6nyuqrzqapM0qzEVRRSYpejIXM1lTUt/koaea+YodR5OM58e5TswnyKSxE5twwm+eFSilSIgnUAIQOdipiRDVaFKmIvK1SfIwyJ3847QPyv772tPbrykywDjhTepSXW6DFjKpyYCPOphTnI360aQKXmVSvizKPIhX05N2IJMmRgfV5NGRwmOeJ/zukRDVwDJAnJRGKjUtWxTq8kRSo0RLx6eOrkM8rk1Ssxro7SuKK/fvj8M4Nb5+0SUta4IzgZ70fZ2jSS2QZZJrNKm9ACvmCt65uvvCo9v35G+hy6RMiBq1ouH8nwoyafIMpPRwlecMP31UDBYZARgdRyO1WuR7BVSSYJbJLreNdpiGIAwlk1xutzaapJWs2Wx6mLyg6Nnt5y/vvmaedOcaTXKuO5Im3dX7edpKp2ZHdh5Nyr77lTnjX3nqjqfrwTvl0Fx5uuTz8/zlLcuMu6nIJCI4YelJOOEozxl+/WhBLwHIJEIIcRALSf8o+wakytHbVNo6AGLiBJnUWEU4cr5VUFbLKG88igpVJBTy7J2rW88/vWvslTRKU1UFnkaTtOVVzo9oOpqk9SevVNJFPFIG4tRu579RIUHlCXhP79sXJk1CJhHBiRAvwmlsjvI7V7fKYdtnt9tMZG2QYNNuMFtQt0Yxou3axvbrzxH0EoAwyqRy52lk0njo9tSppIlzjiYZ9YkZWQu9c3XreS21g3UyXuZP3K94CZLfYFYyDefO1fkrXUeTamVSOa6ljQXJA0f62RBClM7KsfLoCaknZLIyiQjOGOigmh499/zYHOXhZJJ1AwAtqlXpQEVrNfkhwmzXNrZff9Z4GT0GGCnlpLvCyZDDtMrO00KkhGxjpdtTpxIvdMgkXe00bf855SMyjfRUdzIkDXDr3nU1mFrWZEc2mE3Xad+kFqNJ5Z62uky6yrWWPAVRHwuSNt61r+AydPjoCUmdmY9MIoIzFk4caKrw2tOhNlC205NMOo7wlHexTd6owRFjYMj7aFJ/27Uhk0YKegnmRm6kpJ2nF/JsFqk+SfLZLxAfXZ46hoe3XSa19bmPm8AWGdg0SSBPPxOy+DHkx5OncNhGjQYeTco00v3W8dRnt5+/vHtPTowuhChXN0kpHIrzYMhrd+veM0nOTVkmEcGZCqd5V+OSSa2WROoy6dlx1eI711UzVZFJV7b0MI2Tx4Targ2ZNHYYYqqSwMgx/6zVqnJmC4wH989sxOReG4RK8Rxt7qY8u/18Jdb42tPKCE82llWmuSuevvbRJGs3/MmkJqNJUjfqsv9VBuvc0iU/Gy9cPbAN32WNqHkCpU9RlJ7vzZRaXWZ+UjgQwZkizT0qLXJRnbOqxC90q3J8SWpE3f3sOHZtuV/uS2rh9j2DTDK/t9WSSFUmKTaw0p9wo0net2vr8MCCOJmnXhrUeYewFL96mTaKuOwIqf6gAN5pdZn5kUkSRHCmRJtrqTKaVE55FdrQtJIQpZAx5V5nqtR5TktRo46ZqBkmH9W9NxcS3WXSs9vKYebs/4o4rOysMJhMOm27tpgfWKTW7My09dIwPjlER+gLD/zADwoD0Ooy8yWTiOBMkjbXkiaTXDpEmw6nJYyRX63mksmnBx8/URUGlUl31veeMJrkxjSaJL9RywNRP/FP0ZCDbtcW7QOL1JpemMwQE+70rOB3nTL8oDAAQWQSTJLuMsmS5KDYeE3PGaNtjCbLJG24oxikMqRxq1ubVA5wnbo2yXT8nWfCNumuVGUOrRXbdm2RPrBIremb8eolFBFk8GNPBO5eGABkEvjiRJlkWyoZiUzqPJpUCpLKMda1SflWDRaZFON2bZE+sEit2Sdj0UtII5Dhh58I3MkwAMgk8EV3mWSYEVfSTiapDr10cPUjquuabO+t0CYheBeZJH87Q84Yc+qLsNu1RfvAIrXmAEQ7xIQ6gipcAROBWxoGAJkEvmgrk2TRkqUNUIY+ZAHTQia5czaU2sacwsHyXh2fMsmVIKG6Nina7drifWCRWnNAotJLqCMwwqUwEbi3YQCQSeCLdiYrz82tbBdmEgxtJ909k9rRdI68TOi4pbU66c7xXoWWMqnl2qQSm3SJcLu2UTywSK05JGH1EhoJbHA1TARubxgAZBL4IrjJcm4R65W67dqEcTSpxJ3IW5Nz1ddj3K4t+K/vgNSaYQkyxBTzBQlhmfI1sU1FkpjXWR5W+ah6b7Gi7WaZLJeL3UVfH6DCTQ4DgEwaExe7xWYv9pvBrFArgpus4WQSVAj+68MoGEYvRXw11mWOcdAgOiNMyyyP5OPVyvi2/OmmAe0u6OPP+Sh3fcylJn5kGtlWNprLkXKTmlLdDHdZrBZ+dmRr1Y627FLDy5D6ar2p9uewWyfDOigR3+cwHZBJXhgognOxW2z2h9063Tfr1cwiO8ikgAT/9WFc9KqXIr4ae5ZJ9+8c90krjizEj66C8nHmfP6zWur1mBF1jnHWSClRlCFryz4Qjp5UR+YduUCf3teWg5pkUvbkzgaaC3WRjT4vVuXD+ygJ5OWMagLNYo2jbe2jsvzR1o4RRzvGz81k0rZ8l+aXGGRSq/5c7BbLZSIV3R1BJsG08CWTlnb6/gpVphrBEftNYpJJRHYykEkBCf7rw0jpY0qe5WrUEmwGoU+ZJB9w/85x5adFJsmrOo+YREVbJCWTN9IkneYR+2hSRVNd3n1HH6cqNZUik56p84dLmbRNRZJvC5ApovIRvpU0w/aoTORn/GElKYpt6SiUu1nn/60+m63t2DH7IpbPzWTe8SMOYpEo7636Il36Y/I5jszJ54A54FEmGd9irCeC07o/+dBQWdY7IYjsQCzw68Pp+NJLk5BJrXa1Nm4N99rT5164umuSSaWgqioNy5S8Zjy7/fzl7TuXt+5dH2WSvMmbLpMs6W2qckjNdpOLMS27j6Tumo0muTYB2Dof2IndR0nqZJKjHTtmmWT5XE2qHVb6f5Wv1q0/yCSYDcPLJCI4nfuT2Z/tZr1SjRAmC4LDrw8eOVEvVa9GJSWJadsxTRVkLnj5rlv3rpVGFJf90a1713I72oCJPKvt9j2DTDK/t82u1o4dug2jSRa56G806dnt55/ef+3pcy88va2NWWnZODXco0mqTLr1gn2dUrO1Se1kkv34NFGe5bW+SIc92sztWD5Xm9lSI5O69WfEPkfNxVE367Ru/ZydyliqdB+aZ77Khkb9XF/rCKEBw8skIjid+3PYrZFJECf8+tAH3abkNR1Nun9HcoeUcQ95m7JSSh2dnOrO1PqojnP3tpr35hKuhUw6juEYXayqTJJdslv3rn2vTdIn3bWgjUwyZO80jyZdKWNWd57Kk+7kp3KqThqpyiTt+CMHsVhIf1d8kaNi2ZbPeHM7Tgzt2D93tZDCrg1Ctl36s869kP0mWar+R+w+R81QYz6bs71MarMNohBCPHjn2rDtvf2zZGvllkmVW1q2I9cNtz2BHL8yqboq6VSZRATnyD5VJ9fJhgiZBMHh14e+aa6Xmskklw7RpsNp6ePkV6uZ5a7vFgMdVS+oMunO+t42o0lCFxLlf60pHCo1Bter2cS/3C18pg7ZPXruhav7hjwNxR7ZTVu+/ZplbZJ2igqv0ugJH0+OksKh3CtAkhmpbcK9enwhe4p1Akki0tQ6R19+SBvbcWFqx/i52ZKGNC1fKvyS1B6abd2f/Sb3RUrnw7wwoE9Ol0nVhWuyTLrWL2hrubz7TsttEJtEESqS7P6drAXDykLtMEvjisVRQzhgI8ikOyI43fqTJW+oiqIxR3ZgIvDrD8msNkepUquXGskkS6a17IC2u1rr8qPMXqD5IXVrk0oPp8PaJFUmVQeRiqmDha8lTyw0iZm2VEeTGjqH2Sm9um1x26yjSZIQLY/R51Vd333h8u5rV7deuHowbEJw6JXBR5PyC8soUSoy6coWCSiX7h0/1BAYOHbMPHJVkwvFKpP08XTSXjVheJkkiOB07U823c4wdjTiyA5MhLC/Pqk1Naa0OYqDWhWhHm6QSbZYaiQyqfNoUtnD6mjS8chK+z7WO1RSOCjdduTvtq0TcaRwyP+X/6yKTHrh6kHxjd4pBFK2bopH1EToLJPUGENTmfTs9vOXd+89fe7O1d0XDFGErqNJ2hRS6bBcJrXNnW+TSUYLoix/UmJFkBFEJkE3tpv16mKf9i94uoFMmjOR/Pqk1jy2N6HNURrSQC9psVTXuoB2MkmN+UoHVz+iuq7J9t4K7oTgmZMmOVTl4vDKpLt8LdOdZ65Mdx2WJx2lV5HCoVRHx0VQWnC8dFmrSTWy/l8/qJVJ6hkQonRB83lV2cGZ/4lMmg6njyZVF67ZZFKuXjLpkgtxGcPaJKWd7jKpQiXJY0WkVcbHix5W1ikZ5gc/u8sQU4lHmRTPvkkQhEgcZQhCJL8+qTWLT5zM5ihtcYglTbRk880UL0IWMC1kkjtng7JB6iNdJlnfq9NEJsk1mixRAtwmiehhNEl2C+W/MzlUEYTSOiV11KhyfEOZpAyX1aVwgLHjb9JduXDNKJOkG744uCKlTKNJsjRyyKSWk+5MqSoNQYj8Q3V7lB921HXlgBWDSEZ8yaS8wHyJxFGGIETy65Nas/jvZDZH6UB2KTr0UuFkKytzKgnBhfxf56Q7eSm45mzIXtDT+4ZJd473KrSUSfoMN3U0yTBkdLpM0p