The SQL Swiss Army Knife #5 - Checking Autogrow times

Blog do Ezequiel
Portuguese PFE SQL Server Team


Latest Updates
10/05/2014 - Latest update on the AdaptiveIndexDefrag procedure v1.5.8. Look for it in the "Ezequiel shortcuts" section on the right or just click HERE.

10/05/2014 - Updates to Maintenance plan deployment scripts available HERE

04/06/2014 - The SQL Swiss Army Knife Series Index

The SQL Swiss Army Knife #5 - Checking Autogrow times

  • Comments 5

Hello all,
Here is another one focusing on SQL scripts that may help DBAs, following the series "SQL Swiss Army Knife". This time we are exploring an alternative way of verifying autogrow times besides checking the ErrorLog for any recorded information, and that is when an error 5144 or 5145 occurs.

When these happen, SQL Server will report messages to the Application Event Log and ERRORLOG on a failed autogrow of a database and/or transaction log file that has timed out or has taken a long period of time to complete.

When you see this message, most likely your performance as already taken a hit. This is because operations that require the autogrowth of a file will hold on to resources such as locks or latches for the duration of the grow operation, so everything that needs to act on that file sits there.
Not a nice thing. Also, long waits on latches for allocation pages are likely to be observed and the operation that requires the long autogrow will show a wait type of  PREEMPTIVE_OS_WRITEFILEGATHER. As for data files, this will not be an issue if using the “Instant File Initialization” feature, which is a best practice, but will certainly have an impact on log files, as these must be zeroed out.

So if the recommended best practice of preemptively growing files off-hours just isn’t something you are doing in your organization (please consider it), it can be a good idea to at least check how much time these operations are taking from time to time. We can rely on the default trace for this information, and the following script will extract that information for you. The caveat here is that it this information is limited to the number of events the default trace can hold for a given amount of time.

The output will resemble this:

image

Hope you find it useful.

Download code here: view_AutoGrow_Times.sql
Social Media Sharing
|
Leave a Comment
  • Please add 6 and 3 and type the answer here:
  • Post
  • I visited your blog first time and found it very interesting and useful. Keep up the good work.

    --------------------

    <b><a href="www.swissarmyknives.com.au/">swiss army knife</a></b>

  • Great script!

    A minor tweak:  

    EventClass = 92 OR EventClass = 93

    AND DATEDIFF(hh,StartTime,GETDATE())<24 -- Last 24h

    Should be  

    (EventClass = 92 OR EventClass = 93)

    AND (DATEDIFF(hh,StartTime,GETDATE())<24) -- Last 24h

  • Thank you for the feedback.

    I will actually do without the OR, changing it to:

    WHERE EventClass >=  92 AND EventClass <=  95

    AND DATEDIFF(hh,StartTime,GETDATE())<24 -- Last 24h

  • I get:

    Msg 156, Level 15, State 1, Line 11

    Incorrect syntax near the keyword 'FROM'.

    on this bit of the code:

    SELECT databaseid, filename, SUM(IntegerData*8) AS Growth, Duration, StartTime, EndTime, CASE WHEN EventClass =

    FROM ::fn_trace_gettable(@base_tracefilename, default)

    WHERE EventClass >= 92 AND EventClass <= 95

    What is the CASE statement for?

    Best regards,

    Henrik

  • Hello Henrik Staun Poulsen,

    Nothing whatsoever at the moment. I just uploaded again with that CASE commented out.

    Thanks for spotting this!

    Cheers

    PL

Page 1 of 1 (5 items)