Sql-server – Auto-Growth Events

auto-growthsql server 2014

Do we have a way to prove to somebody that auto-growth events may hurt performance of SQL Server?

Currently I can only know when it occurred through sys.trace and sys.fn_trace_gettable. But of course it will only show the start and end date.

I want to know if there is like where you'll show it that whenever there are auto growth events, one may show occurring slowness in SQL Server.

Best Answer

You may not have a dramatic slow down if you have Instant File Initialization turned on for data growths, but the log will still suspend activity while it grows. You can insert rows into a demo table via a while loop and track the number of seconds required for the insert.

USE [master]
GO
--Create DemoAutoGrow database with autogrowth of data and log to 1gb
CREATE DATABASE [DemoAutogrow]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'DemoAutogrow', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL13.SQLEXPRESS2016\MSSQL\DATA\DemoAutogrow.mdf' , SIZE = 8192KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024000KB )
 LOG ON 
( NAME = N'DemoAutogrow_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL13.SQLEXPRESS2016\MSSQL\DATA\DemoAutogrow_log.ldf' , SIZE = 8192KB , MAXSIZE = 2048GB , FILEGROWTH = 1024000KB )

GO
---------------------------
use DemoAutogrow
go
--Create a table to track the number of seconds required to insert a large row
CREATE TABLE dbo.Demo_TrackInsertTime
 (InsertSeconds int NULL)  ON [PRIMARY]
GO
----------------------------
--Create a table with a large column 
CREATE TABLE dbo.Demo_Table_1
 (NCHAR_Big NCHAR(4000) NULL)  ON [PRIMARY]
GO
-----------------------------
--set up a while loop to insert 2000 rows and track the number of seconds
--required to insert the data
set nocount on
Declare @BeginTime DateTime
Declare @Counter int = 1
Declare @InsertSeconds int
set @BeginTime = getdate()

while @counter < 2000
begin
set @BeginTime = getdate()

INSERT INTO dbo.Demo_Table_1 (NCHAR_Big) SELECT REPLICATE(N'W', 4000)

set @InsertSeconds = DATEDIFF(second,@beginTime,getdate())

insert into dbo.Demo_TrackInsertTime values(@InsertSeconds)
set @Counter += 1
end

--Select rows from the TrackInsertTime table where number of seconds > 0
--Users would've waited for that event to occur
select * from dbo.Demo_TrackInsertTime where InsertSeconds > 0
Related Question