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