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.
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
