SQL DATA FILE AUTOGROW performance troubleshooting

18 September,2013 by Jack Vamvas

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

 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


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on SQL DATA FILE AUTOGROW performance troubleshooting


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer