????????????????????????????

????????????????????????????????????????????Transaction Log??????????????????Auto Growth operations??????????е?????????????????????????????????????????????С????????????á????????????????????????????????????????????????????????????????????????????????????????????????????????
??????????????????????????????????????????????????????????????????Log Fragmentation??????SQL Server???????????????????б????????Crash Recovery??????к????????????????????????д?????????????????????????????????????????
????????????????????????????????SQL Server???????????飬???????????β???????????SQL Server???????ü????????????Instant File Initialization?????——?????????????????????????????????????SQL Server????????????????????????????wrap-around ?????????????Crash Recovery?????????????????
??????????????????????????????????????????????????????洢??????????????б?????????д?????????????????????????????????????????????????????????????д???????????????????????????????????????????????????????????????????????????????ɡ??????????????????????????
?????????????????????????μ????????????????????????????????????????????????????????10GB?????????????????????????????????????????????????????????????á???????????????????????????????????????
????-- Create a new database with 10 GB Auto Growth for the Transaction Log
????CREATE DATABASE AutoGrowthTransactionLog ON PRIMARY
????(
????NAME = N'AutoGrowthTransactionLog'??
????FILENAME = N'C:Program FilesMicrosoft SQL ServerMSSQL10.MSSQLSERVERMSSQLDATAAutoGrowthTransactionLog.mdf'??
????SIZE = 5120KB??
????FILEGROWTH = 1024KB
????)
????LOG ON
????(
????NAME = N'AutoGrowthTransactionLog_log'??
????FILENAME = N'C:Program FilesMicrosoft SQL ServerMSSQL10.MSSQLSERVERMSSQLDATAAutoGrowthTransactionLog_log.ldf'??
????SIZE = 1024KB??
????FILEGROWTH = 10240000KB -- 10 GB Auto Growth!
????)
????GO
????????????????????????2???????1??????????????Щ??????????????????????????????????????????Σ????????2??????????μ?????????????????????????????????
????-- Create a new table?? every records needs a page of 8kb
????CREATE TABLE Chunk
????(
????Col1 INT IDENTITY PRIMARY KEY??
????Col2 CHAR(8000)
????)
????GO
????-- Another simple table
????CREATE TABLE Foo
????(   
????Bar INT NOT NULL
????)
????GO
?????????????????????????????????????????????μ??????????????????????????????
????-- Begin a new transaction?? that blocks the 1st VLF in the Transaction Log
????BEGIN TRANSACTION
????INSERT INTO Chunk VALUES (REPLICATE('x'?? 8000))
????GO
???????????????????????У??????????SQL Server???????????????????????????????洢???????????????????????????????????????????????????66???????????????????????
????INSERT INTO AutoGrowthTransactionLog.dbo.Chunk VALUES (REPLICATE('x'?? 8000))
????GO 66
???????????????????????????
????COMMIT
????????ζ????????????????????????????????????????????DBCC LOGINFO???????
????DBCC LOGINFO

??????????????????????????????????????????п????????SQL Server??????????????????????
????-- This statement will trigger the Auto Growth mechanism!
????INSERT INTO Chunk VALUES (REPLICATE('x'?? 8000))
????GO
????????????????????????????????????????????SSMS????μ??????????????????2??????????????——??Foo??
????-- This statement is now blocked by the Auto Growth mechanism.
????INSERT INTO Foo VALUES (1)
????GO
???????SQL ??????????????????д???????????????????????????????á??????????????????????Σ?????????3????????????????2??SQL???
????-- Analyze the blocking situation
????SELECT wait_type?? * FROM sys.dm_exec_requests
????WHERE session_id IN (54?? 55)
????SELECT wait_type?? * FROM sys.dm_os_waiting_tasks
????WHERE session_id IN (54?? 55)
????GO
?????????????????????………………??
???????????????????????2??DMV sys.dm_exec_requests ?? sys.dm_os_waiting_tasks??2???????????????——?????????????????????????????????????????????????????????????????ν?????????????Preemptive Wait Type??——PREEMPTIVE_OS_WRITEFILEGATHER????????????????SQL Server??????????????SQL Server ??????WIN32 API??????????????????????????????????WriteFileGather??WIN32 API?????????
????INSERT???????Foo????????μ???????LATCH_EX?????????????DMV sys.dm_os_waiting_tasks ???resource_description??????????SQL Server????????????????????????????????????DMV sys.dm_os_latch_stats ????lactch class?LOG_MANAGER???????????????????????????Щ????????????????????????????????????????????????????????????????????д??????????????????????д??????????????????????????????????????????????????
????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????д??????????????????????????????????????????????????????к????????????????????????????????????