SQL Server 最小化日志操作解析,应用

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

SQL Server 最小化日志操作解析,应用

Sql Server 中数据库在BULK_LOGGED/SIMPLE模式下的一些操作会采用最小化日志的记录方式,以减小tran log落盘日志量从而提高整体性能.

这里我简单介绍下哪些操作在什么样的情况下会最小化日志记录.以及现实生产环境中如何应用最小化日志.

概念:SQL Server在满足相应条件的基础上时进行一些特定的操作如Rebuild Index时会进行最小化Tran Log记录操作,从而改善系统性能.

注意:含最小化操作日志操作段日志无法按时间点恢复(point in time)

需要还原模式为简单或大容量日志

最小化日志的操作

Create Index,Alter Index Rebulid

Bulk import操作(BCP,Bulk insert)

Select into

Blob数据操作(使用Write等)

Insert select(sql 2008后特定条件下可以)

Merge(特定条件)

应用:实际应用过程中我们实际使用insert select的时候居多,就此介绍

关于insert select操作的最小化日志

聚集表

当聚集表为空时,使用TABLOCK 锁提示将会最小化日志

当聚集表非空时,无论如何将不会最小化日志

非聚集表

当堆表为空时,使用TABLOCK锁提示,表中行数据,索引数据(非聚集索引)都会最小化日志

当堆表非空时,使用TABLOCK锁提示,表中存在非聚集索引,则行数据,索引数据均非最小化日志

注:最小化日志中表非复制表

一些文档中在堆表有索引非空的情况认为堆行数据会最小化日志,实际是错误的.见图b-2中说明

聚集表实例

聚集空最小化日志 图a-1

create database testbulkgouse masterALTER DATABASE [testbulk] SET RECOVERY BULK_LOGGED WITH NO_WAITgouse testbulkgocreate table t1(id int not null identity (1,1),dystr varchar(200),fixstr char(500));goset nocount ondeclare @i intset @i=0while(@i<20000)begin  insert into t1(dystr,fixstr)values('aaa'+str(RAND()*100000000),'bbb'+str(RAND()*100000000))  set @[email protected]+1endcreate table tcls(id int ,dystr varchar(200),fixstr char(500))goCREATE UNIQUE CLUSTERED INDEX inx_id ON dbo.tcls (id)insert into dbo.tcls with(tablockx)select * from dbo.t1 ----cluster table emptyselect Operation,CONTEXT,[Log Record Length],AllocUnitName from fn_dblog(null,null)where AllocUnitName like '%tcls%'

PRoI2EqTCJQCITyTAV3nLq6SFMhalkKplI+RJGwlSYRCATyonEChJMgUtBKhmikCgVZQQqmUolUQnlBAqJQiKQCOVEQDLxsyCxggRRIGIFiVhBopAohHIChbg18hbbYCpMJpIJ5QQqmQpTYWIFiVhBhCgQTPkCRYApMEyBiRXrGYGkfvvKpJAoW4wBNQMyAnG+fPoPkwJ1Aj/sY49JGdQViUACZx8f/E1tTSKWEykkCqGcuL1Xg/KSiWT4UXVSUUagkChUMkQoJ/yWdwTI6Ms1+ucm9WRNA24WYgXxEa283vQk0K+epKtQyVTKE9x3WzrVI/vYk5b3MUlRHzbfNnsqyiqMeuMOjME4NuiQtFjcVSyiIyPLnVc6N8aIRWLECiICoQiMoghGx+g0lIZAKAqjNJQGJkkGnU4hk6PwI86CIIZgGEIDMrbx7BZuuRaFUQyhbUlq+9lH5ovCKAIhG41k0plyqRR7VBG2ZLSFMBXRqNQep8Oo19FQGkyFNWq1x+lgMVhSscTlsPM4XJFA4HLY5VKZQat12KxWs8nlsMmlMr1WY7dYHFaLUa+jY3Sz0WAy6CUiMYis/yyyXS6VgoKUiCGYSqFwOx18Lg9DMJNebzWbWAxmqTZQGEUeUc8YKONGGnU6u9Xistt4HC6GYI+5dnvKKIxiCIbCKAqj4CxEgUV8odlo3N58Rp3OZjGzmaxNRkJbk3qkzSiMgSbDEBoNpZsMeplEAlFgDKHRUJpRr7NbLCwG65FthMKYSqHQaTTASBTG1EqlTqMBJUJhTK1Q6h8Gt1i12QyaXqOxmk1SscRhs6qVSpgKb7GZhtIUMlkpXwRCEBhFIAQ0q1go4rA4TrutUqkClQYyApmCyi810EYztpUIRSBEr9VaTSYWg7ledQgGCrjx2s0p07anhsIYTEV0Go3VbGIxWKCwKLx+Sz6qw8McFttsNMgkUrPRQENpMBV52IKYXCoFN8JGM7ANwVJrgj7M43BlEunGVgbFL/U3FEZpKB1DMJlEYtLrSzesTCItBbdwpskEwxCMQduSFA1DMKlYYjIYSpGlYonZYADNx2GxFVJZqRpLpJKpVrN1JwZhHA91aG6G19L840hI4/NG3E53LBpLViUfPHhQLBYjoQiVTFUpFEq5XMgXoDDKojNVCoVCJgf3AAIhLoc9GgpQyRAY7ktnQWSNWs3j8NhMFobQ7BaLw2qVS6XbBxRwrVqpVMoV4JRULGHRmeCeR2EUpKyUycFZmVjCpDMVMjno5RvPIjBqs5gdNqtcKmUzWRiCcVgcvVarUavBfQIyUikUIGupWMKkM+VSGYNG326VRqV2Ox1mo7EqHqGhNI1K5XY6TAaDw2bVqNXpoT6bxczncjvamtRKZSwc9LicjXXVXrfLYbP43M5kImo1m8C1fo+7OhGTS2XtLY0qhaIU2ed2up0OiAJvGQ6qYhG/x8Vlc2AqHAr4qqtipeGGQWOoFAqVQsGgMVAYZdAYlSoVn8vnc3hCvmDjEINAaNDnrU0mdBpN0Odh0Bg0lKaUy1Xy9WvpGL1SqRLw+GwmC1yiUijUCiWDxgD3MIvBkkmkCpkMVI5SrlApFA21SS6bu7G6EAjxe9111VVgNFyPLJMDI0tJySVSFoMF8lUrlUKegMfmCnh8vVbjcTnlUhkor9/j9rldQp6ARWehMKpSKJobauVS6fYRBJTRoNPV1yQZNCboeBq1ur42WRqeKlXqhtokqD1QfLVSCcZ3GkpTK5QigZDNYGEI5nLY62uTHBa7rblBr9UK+QI2kyWTSNhMFgqjLAarUqUy6HQ0lIbCmK6y0ud2KaQyDovN43AH+7oioQAdo7e1NBh0OgRCFDKZSqFk0hkcFkfEF3BZHAzBuGwOGC4VUplaoWQxWFseHWgoTcgXeF3O6qpYyO9lMVhioYjNZEtEItBqGIIpZPJKpZJFZ3JYbJFAyGVxMITGYXG2PxwIeAKPy1FTFQ8HfGwmSyQQcZhsqVjMZXG2VOa6VTKZXCqtSca1lZV11QmxUKRWqthMNpBVmUTS1FDDpDNKkSuVKg6TDe4spVwuEQFTxWBwMOr1bqeDSoYkQhGHxZGIxFw2F4EQsVDE5/BYDJZMIgH9UCoSNzXUgictDMEkIlFTQ832gYKG0oDYS4RiHudhUlwei8EsJSUWipobasHzEIZgIgEIslEYFfIEsXAQpIPr0FOCdR2anmLEIsfX1taPfvLJJ8FA8KOPPioWi/FYwmw0REOBRCycjEfpGN3rckbDwXgk5LBZwKOiTCINeD1UCSSkMzwuZywcjEdCdquFjtHdDnvQ7w0H/bXJOApjddWJpvoan9sFnvE39i0GjeFy2IM+byjgs5iMGIJVV8Xqa6r8XrfFZKKjdLfTEQsHY5GQw2ZFYbQ6EauvSfo9bpvFTEPPTps1Fg7GwiGb2YxAaE1VvLmh1ud2CXh8mAqLBaKaZBwYyaAxnHZb0O8N+b1WsxlDsKp4pKE26fe67Rbzxt5JQ2k8DjcRDYPVSPBwl4iGURgjEchadWXQ73M7HZGgn8/lB/1eJp2BQAifyw/4vBQSBabCGpW6o63J73FDFAhIi1atDvq8PDYXPPvzOTwQ+VFjKxIJ+nlsDgqjMBXRabQ2i4VKhlAYpWM0h9US9PtCfq/DaqFjdLvFHPJ7/V735fZmnaYSgZANeoZoKytddhuJQI5HQlw2x24xx8LBWDjocTnoGN1mMYf83oDP01hXzWKwrGZTyO8L+r1OmxWFsUjQ39xQ6/O43Q77w8g+n8cFBustOlSpUjtsVmAkhmB2qyUWDsXCQa/bhSFYKOBrbqj1eVwuu42O0a0mUyjg87ldHW1Neo0m4PW0tzT6vR6pWEwlU+VSWXNDrd/jdtntDBqDRCD53G6F7BFPsnSMjkCww2YN+rwWoxGiwDSUxmayo+FAaRLDYrCjoQAYlSwmUyjgC/m9oERmozEU8Pk9bjBUySQyt9NZUUYI+rwcFsfndrU21fvcLrfTwcDoHBYnEgpUxSMYgiEQEvB5OtqaA16PXCqDKLDP446GghKhyOty8jg8g04bi4Si4aDLblfI5JfamkJ+r8/tCvl92spKg04XiwSjoWDA5ynNINdnFQhm1Ok62poSsbDNYqahNLfT0dbc4HO7fG4Xg8Y06nXhgC/o9zrtNoVM1tHWBGoy5PcadLqNE2sURg1abUdbU1UsYreY6Rjd5bC1t6wnxWZuakEURo16fTjo87qcjfXVPA43EgrEIqGAz+N1u9hMNoZgdIwWCQeBhBt1unDAF/R5fW4Xk87UazXhgN/rdra1NHicDgRCGDRGPBJiMVgwFXbYrO2tjT4PKALD5bADPWhpqpNJJGBuFwkFS1WBIVgk5N9iIehmdov5UmuTz+Pye1xMGsNhs8YjIRpKa2msk0ulYMIXCfpL/RN0Yw6LA2bzTpvVpNfD1E2PfbgO7SA+06H6+qOzs9dTqVQqlVpYWKiprgE6FAlHJSJR0OcN+jwGnZbH4V1qbfS6nKGALxIKgDm7XCr3e9wkIlnA47e3NnrdzlDAFw74hHxBLBwsLyNw2RyxUEQhQVaTyaDVVpRVbBwoQZ8T8gXxSKiinIhASDIRRSDEbDD43E4ykZKIhoU8fltLg8/jCvq90XCQSoaMOl3A6yFUkCAKxKAx3U5HwOsJ+ryN9TVUMmQy6I16XdnFCnAzQBSEz+UHfR4yiSLg8ROxmlGCTCVDyUQU3KgBn4dCosSjYfD8VVrT43N5iWgYosAYgsFUmM/lVUXDEAWBKJBKroxHw26nXSVX+DzugNfNZrLA01ko4AML0HqttjYZNxuNZBKFjtEhCqSQyuLRMFhOxBBMLBCCyKV7rDTOAh0SC4QYgsFUxKDTOWxWCokq5At4HG5VLEImUkHliIWiRDRMIpIZNEZ1IsakMzfeujAVqVSpW5rqGuuqjTodCqOtTfVBn9fvdSeiIR6Hm4iFySQKk84UC0UsBqsqHqWSIRKBXBWLsBgslUIRCwVJBAqVTGUz2VXxKIVEpaG06qoYuLE3DhDaykqXw04mUoV8AZPObG6sC/l9fo+7Kh6loTSFTB4LBwkVxEgoIBIIq2JhMIeuTsToGF2tUDrstrKLFTAVRiCkUqmKR0LEClI0FBDw+FQyFPR5N+oQAqGlXkQmUrwuZ3tLo1GvB+ZxWOxoOFBaS2Qz2dFQAMRPJqIojFSUEWLhoEQkTsTCMBWhYXSpWIJAqFqp9HnchHJiJOjncXgSkTgRixDKiZFQQCIUIRCCIVg0FEBhGgIhSrnc43QAmzGEFg745FKZz+0KeD1CvsBsNAT9Xo/L2dZcz2FxouGAQiqrjscUMnk44K9NxqOhgNftqk7E+BzelikRoZzksttkEimhgohAiEQkqopHqBSISqZiCA08EZZdqIiEAkK+IBoKKGSyZCKqlMmj4eAWqSaUkxxWq0IqJ5QTEQgRCYTJRJRYQQr6PHKpbKNoYQhWFY+C+qyKR4Q8QTQclIolF86VhQI+pVwOnoGi4SAQrapYhMVgXjxfHgr45FJZLBxg0hlkIqUqFpGIRAiEAh1i0pkojAp4/OpEjEQg+z1uuVTK5/IioUD5xQq3y6GUy4EORcNBMNMCxkTDAdCZGTQ6eBoDx/kcbnVVjEyk+NxOpUzOZXPCIX/5xQqXw65SKIAORUOB0iI2CoMgC9ehpxOf6VBDw7FsdqG9rb29rX0iN1FbUwt0qCZZrVWr+VyeVCwJB/0CHj8aDvA5PJFACJYXGDS6yWCoikXoNLqAx4+Gg+Asm8nic3nRcJDP5Yn4Ar1WC1Fgi8notNn4XB5YvaVtuAH4XF4sHAR3fiwchKmw1WRy2Kx8Li8aCgi4/Gg4yOfyQb4QBfa6nR6Xg81i0zEGl82NhYNcNsdkMHS0NYPXCS6HncfmlpaJ9VptMh5l0pk8DjcWCfG5fLFAGIuEUBj1OB0uh52B0etrk3wuj0qm2q0Wi8kIhi2nzaqtrBRw+SaDgUFjOKxWbaWGx+G67Daz0QDG/ZDf29HWxKAxEAhRKRQ1yTgNwWAq7HLYfW4Xi8Fy2q00lKZVq8MBv1qpjEdDHBYHgRCVXF6TjNNQjI7RIQpsM5tsFjMYR7bokMNmDfm9HBa7NhkX8PjRUEDIF4IxCFQRn8vTqNXND1c2NuqQ3WoNeN1CnsDjdIBrxUIRqEkWgxmLhEQCIZ/L02s0HBYnGg6KBSIBjx8LBxk0htVsDvm9HCabSWcy6cxYJCTgCdRKZUd7s5Av3Dh6IhBiNZsioQCLwapLJoR8QTQYkIjEQDgRCLGajCG/F4aQ2uqETCKJBP1CvkClULQ01jHpTLVCGfB52Ew2WMo3Gw3hgA+mInXVCalYQiFRN+oQRIGNOp3LYUdhDIVRhUwej4a8blcsHBTw+OAVTl1Ngs/l0zE6g0aXS2V11Qkeh0fH6NFwUCoW8zhcoHDhoF8qlvA4XL1Wy6AxFDJ50O9j0pnRUAAs50ZCAYgC11YnFFIZeG9RV50QcAUojCrl8pDfx2ayTQa9gMevq07wOFy7xdJ9pZ3P4YWDfqlIopDJLre3qBSK6kRUIhInomGZRBILB4N+r1wqFfIFfC5vy/SOhtKYdGY8ErKYjFw2B4VRnUYTj4bYLDabyQajqkIm47I4wPiaqhgQVJlEAp5yNq80MGPhoM1sBklp1JWJWBiiQNWJaKVKjUCfRcYQLBIKyKVSiVB8qbVRrVA21CYdNiuLzgwH/VKxGEMwsVBYV1MlFoowBAsHfEq5nMPkhIN+sVAU9HkUMplYKGpprAOizqAxqmIROsZAYVStVCXjEYgMVcUiGpUK1LyQL7jc3qxRqRAIFQmEdTVVUrEYGCPkCepqElKxhEGjw1RYKVcEfB6w6KqSK5KJKJUMJaJhXWUlj8MFSXW0NWsrKxEIFfAEddUJmUQCkhJwQVDKoDEoJKrDarEYjbgOPT0o6RC9KnHs/v3PTsSiMaBD1VVJn8cVDQUioYBIIIKpSKVKFY+E4pGQ02aFqLBcKq2KR6oTUYfNCkOIWql8eNaGQIhaqUxEw7FwUP7w3aDLbkvEwnKpbMsDIApjKoUyHg2Dp1QKiWIxGVub68NBv0