0

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.

  • If at all there are any Autogrowth events logged in , you can check them in Database standard reports . Right click on Database , look for Reports , Standard Reports and check Disk Usage , only if there are any auto growth events logged only then you can see the data over there else it doesn't show up . – DBOn Dec 26 '17 at 12:24
  • 1
    You can use my script to get autogrowth and how much time it took for that event. This will explain the slowness. – Kin Shah Dec 26 '17 at 22:02
  • @Kin, thanks for the script. I will try to study it before firing it in the production. I tested it in the local and on the test environment. Results are quite good. – Edgar Allan Bayron Dec 27 '17 at 7:33
1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.