A quick guide for SQL DATA FILE AUTOGROW performance troubleshooting

Question: An application owner  reported  slow SQL Server database response times . Using a query through the default trace and searching on the Trace event id  “Data File Auto Grow”  , I identified some long running DATA FILE AUTOGROWTH activity. The duration column displayed some extended time periods.  Why is it so slow?

SELECT te.name AS [trace_events_name] ,
t.DatabaseName ,
t.NTDomainName ,
t.ApplicationName ,
t.LoginName ,
t.Duration ,
t.StartTime ,
t.EndTime
FROM sys.fn_trace_gettable(CONVERT(VARCHAR(150), ( SELECT TOP 1
f.value FROM sys.fn_trace_getinfo(NULL) f WHERE f.property = 2 )), DEFAULT) T
JOIN sys.trace_events TE ON T.EventClass = TE.trace_event_id
WHERE te.trace_event_id = 92

Answer: There are many reasons for DATA FILE AUTOGROW slow performance . These are some notes

1)     When thinking about autogrow – it should be used as an exception. Proper capacity planning and understanding of the systems should result in accurate presizing.

If you’ve made the decision to use autogrow – then make sure it’s managed properly.

Consider factors such as  other files on the drive, workload and monitoring

2)   Autogrow and autoshrink  on the same SQL Server database can cause contention.

3)  Instant file initialization  can have a positive impact on data file growth. (It will not improve log file growth). If TDE is enabled than Instant File Initialization will be turned off , read more Tested TDE with Instant File Initialization on SQL Server databases

4)  Monitoring of data autogrow events , will supply information to DBAs. Use the information to grow the files in non-critical hrs. Make sure you maintain a disk space monitor – as autogrow will continue up to disk full – if MAXSIZE isn’t used

 5)       Speak to the storage guy . Before you speak to them gather Perfmon statistics – such as Logical Write Bytes \sec and Logical Read Bytes/sec

 Read More

 SQL Server - Top 10 DBA mistakes - SQL Server DBA

SQL Server faster restores with instant file initialisation - SQL Server ...

SQL Server – default trace FAQ - SQL Server DBA