در چه شرایطی Auto Growth می تواند بر Performance تاثیر منفی بگذارد

 -- Create a new database with 10 GB Auto Growth for the Transaction Log
CREATEDATABASE AutoGrowthTransactionLog ONPRIMARY
(
NAME =N'AutoGrowthTransactionLog',
FILENAME=N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\AutoGrowthTransactionLog.mdf',
SIZE = 5120KB,
FILEGROWTH = 1024KB
)
LOGON
(
NAME =N'AutoGrowthTransactionLog_log',
FILENAME=N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\AutoGrowthTransactionLog_log.ldf',
SIZE = 1024KB,
FILEGROWTH = 10240000KB -- 10 GB Auto Growth!
)
GO

 -- Create a new table, every records needs a page of 8kb
CREATETABLE Chunk
(
Col1 INTIDENTITYPRIMARYKEY,
Col2 CHAR(8000)
)
GO
-- Another simple table
CREATETABLE Foo
(
Bar INTNOTNULL
)
GO

 -- Begin a new transaction, that blocks the 1st VLF in the Transaction Log
BEGINTRANSACTION
INSERTINTO Chunk VALUES (REPLICATE('x', 8000))
GO

INSERTINTO AutoGrowthTransactionLog.dbo.Chunk VALUES (REPLICATE('x', 8000))
GO 66
-- Commit the ongoing transaction from the different session
-- Execute this code in the 1st session
COMMIT

 -- This statement will trigger the Auto Growth mechanism!
INSERTINTO Chunk VALUES (REPLICATE('x', 8000))
GO

-- This statement is now blocked by the Auto Growth mechanism.
INSERTINTO Foo VALUES (1)
GO

 -- Analyze the blocking situation
SELECT wait_type,*FROMsys.dm_exec_requests
WHERE session_id > 50
SELECT wait_type,*FROMsys.dm_os_waiting_tasks
WHERE session_id > 50
GO