• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 656
  • Last Modified:

Does SQL server respond slow during Autogrowth event ?

Does SQL server respond slow during Auto growth  event  ?

Auto growth  value in my production database.

File Name       Initial Size            Auto Growth
 MDF                  115MB                By 1 MB, Unlimited
 LDF                   24MB                   By 10 percent,  Limited to  2,097,152MB

When i verified my production database  using the following query every day or every two days this auto growth event is happening. This is a peak hour and lot of transaction are happening this hour. Does SQL server slow down the application during this time ?

DECLARE @path NVARCHAR(260);

SELECT 
   @path = REVERSE(SUBSTRING(REVERSE([path]), 
   CHARINDEX('\', REVERSE([path])), 260)) + N'log.trc'
FROM    sys.traces
WHERE   is_default = 1;

SELECT 
   DatabaseName,
   [FileName],
   SPID,
   Duration,
   StartTime,
   EndTime,
   FileType = CASE EventClass 
       WHEN 92 THEN 'Data'
       WHEN 93 THEN 'Log'
   END
FROM sys.fn_trace_gettable(@path, DEFAULT)
WHERE
   EventClass IN (92,93)
ORDER BY
   StartTime DESC;

Open in new window


See attached image for auto growth event happened on 08-Aug-2014 between 09:40:25.087 to  11:03:45.987.
Should i need to change the auto growth  value of the production database ?
0
Varshini S
Asked:
Varshini S
  • 7
  • 6
  • 3
  • +2
4 Solutions
 
Carl TawnSystems and Integration DeveloperCommented:
It will depend on

a) how much it is growing by
b) the type of storage being used
c) the speed of the storage being used

Ordinarily an AutoGrow of the datafile will take less than a second, so shouldn't normally impact performance. However depending on the answers to a,b and c above it might.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
115 mb as initial size seems really small.
So you shouldgrow the file manually once to 1gb, for example, and monitor the usage to plan growth for off-peak hours. Auto-growth should be avoided.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
115 mb as initial size seems really small.
So you shouldgrow the file manually once to 1gb, for example, and monitor the usage to plan growth for off-peak hours. Auto-growth should be avoided.
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
Scott PletcherSenior DBACommented:
>> Does SQL server respond slow during Auto growth event? <<

It depends.  If IFI is on, data file growth will be virtually instant.

Log file growth will always cause a delay because log file space must be pre-formatted prior to use.


I don't see an attachment.  And, since I don't know your data, I have no idea if 115MB should be 300MB or 500MB.  If it grows constantly, it should be increased up front.  

But, I do know that 1MB is far too small a data growth amount.  Bump it to at least 10MB.

And never use % growth.  I've found % growth to always be slower, on either data or log files.  Change log growth to 2MB.

Also, change the model database to a fixed amount instead of a %.  Then future dbs won't have a % growth either.

Finally, I don't necessarily agree with the common notion that a data file should "never" autogrow.  In the large shops I've been in, it just wastes too much disk to dramatically overallocate all data files enough to prevent any growth.
0
 
Varshini SAuthor Commented:
ScottPletcher, Here is  the auto growth event happened on 08-Aug-2014 between 09:40:25.087 to  11:03:45.987.


DatabaseName      FileName      SPID      Duration      StartTime      EndTime      FileType
tempdb      tempdev      82      330000      2014-08-08 11:03:45.657      2014-08-08 11:03:45.987      Data
tempdb      tempdev      82      576000      2014-08-08 11:03:44.840      2014-08-08 11:03:45.417      Data
tempdb      tempdev      82      400000      2014-08-08 11:03:44.430      2014-08-08 11:03:44.830      Data
tempdb      tempdev      82      330000      2014-08-08 11:03:44.090      2014-08-08 11:03:44.420      Data
tempdb      tempdev      82      413000      2014-08-08 11:03:43.667      2014-08-08 11:03:44.080      Data
tempdb      tempdev      82      583000      2014-08-08 11:03:43.073      2014-08-08 11:03:43.657      Data
tempdb      tempdev      82      266000      2014-08-08 11:03:42.797      2014-08-08 11:03:43.063      Data
tempdb      tempdev      82      316000      2014-08-08 11:03:42.473      2014-08-08 11:03:42.790      Data
tempdb      tempdev      82      223000      2014-08-08 11:03:42.243      2014-08-08 11:03:42.467      Data
tempdb      tempdev      82      203000      2014-08-08 11:03:42.033      2014-08-08 11:03:42.237      Data
tempdb      tempdev      82      263000      2014-08-08 11:03:41.763      2014-08-08 11:03:42.027      Data
tempdb      tempdev      82      200000      2014-08-08 11:03:41.557      2014-08-08 11:03:41.757      Data
tempdb      tempdev      82      766000      2014-08-08 11:03:40.783      2014-08-08 11:03:41.550      Data
tempdb      tempdev      78      250000      2014-08-08 09:40:26.160      2014-08-08 09:40:26.410      Data
tempdb      tempdev      78      213000      2014-08-08 09:40:25.940      2014-08-08 09:40:26.153      Data
tempdb      tempdev      78      150000      2014-08-08 09:40:25.783      2014-08-08 09:40:25.933      Data
tempdb      tempdev      78      113000      2014-08-08 09:40:25.667      2014-08-08 09:40:25.780      Data
tempdb      tempdev      78      420000      2014-08-08 09:40:25.087      2014-08-08 09:40:25.507      Data
0
 
David ToddSenior DBACommented:
Hi,

This appears to be a duplicate question
http://www.experts-exchange.com/Database/MS-SQL-Server/SQL_Server_2008/Q_28495833.html

The short answer is now that we know you are talking about tempdb is that its size should be pre-allocated other wise you can get this really slow startup while tempdb grows from 10MB say to a more representative size.

HTH
  David
0
 
Scott PletcherSenior DBACommented:
Whoa, tempdb is different.

Tempdb data should never autogrow unless something extremely unusual and unexpected happens, especially since tempdb often has multiple data files.  Tempdb log should also autogrow only if something extremely unexpected happens.

You need to pre-allocate more space for tempdb, and keep allocating it more until it never grows during any daily processing, including during heavy activity.
0
 
Varshini SAuthor Commented:
ScottPletcher ,

Is it possible to find auto growth  event timestamp of my production DB using SQL query ?
0
 
David ToddSenior DBACommented:
Hi,

Easiest way is to use the inbuilt report for Disk usage, and there should be an expand button on the autogrow events.

HTH
  David
0
 
Scott PletcherSenior DBACommented:
Yes.  Your original query has the start and end times, since they are in the default trace.

Run that query every 1-2 hours and dump the results into a table.  Then you'll have a permanent history.
0
 
Varshini SAuthor Commented:
ScottPletcher ,

Does Event class 92 and 93  common for the all the databases ?


CASE EventClass
       WHEN 92 THEN 'Data'
       WHEN 93 THEN 'Log'
0
 
Varshini SAuthor Commented:
ScottPletcheR,

I have run the following query but I was not able to find the records.



DECLARE @path NVARCHAR(260);

SELECT
   @path = REVERSE(SUBSTRING(REVERSE([path]),
   CHARINDEX(CHAR(92), REVERSE([path])), 260)) + N'log.trc'
FROM    sys.traces
WHERE   is_default = 1;

SELECT
   DatabaseName,
   [FileName],
   SPID,
   Duration,
   StartTime,
   EndTime,
   FileType = CASE EventClass WHEN 92 THEN 'Data' ELSE 'Log' END
FROM sys.fn_trace_gettable(@path, DEFAULT)
WHERE EventClass IN (92,93)
AND DatabaseName = N' MYDB'
ORDER BY StartTime DESC;
0
 
Scott PletcherSenior DBACommented:
See if this returns a value:

 DECLARE @path NVARCHAR(260);

 SELECT
    @path = REVERSE(SUBSTRING(REVERSE([path]),
    CHARINDEX(CHAR(92), REVERSE([path])), 260)) + N'log.trc'
 FROM    sys.traces
 WHERE   is_default = 1;
 
 SELECT @path

If not, someone has disabled the default trace, and you won't be able to use that.
0
 
Varshini SAuthor Commented:
yes, I got the following output

C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log\log.trc
0
 
Varshini SAuthor Commented:
yes, I got the following output

 C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log\log.trc
0
 
David ToddSenior DBACommented:
Hi,

To go back to the original question for a moment
>> Does SQL server respond slow during Auto growth  event?

That is yes.
Yes to growth on a data file as the inserts and some updates are blocked waiting for the datafile to grow. Depending on what exactly is locked, these inserts and updates could be blocking selects on the table(s) involved. As there is some disk activity, potentially other users of the same disk are now slower.

For growth on the log files, all data modifications are blocked pending the increase in the logs, which in turn could be blocking other transactions.

HTH
  David
0
 
Scott PletcherSenior DBACommented:
Then the trace is running.

If you get no results from the query, then no autogrowth has occurred during the last however-many-minutes-your-default-trace-file-holds.
0
 
Varshini SAuthor Commented:
ScottPletcher ,

This is my Auto growth  value in the production database.

 File Name       Initial Size            Auto Growth
  MDF                  115MB                By 1 MB, Unlimited
  LDF                   24MB                   By 10 percent,  Limited to  2,097,152MB

Every day  the database size is increasing up to 2 MB. So there should be a auto growth every day. But I run the query I did not see anything.
0
 
Scott PletcherSenior DBACommented:
The default trace only stores data for a limited period of time.  Exactly how long depends on the level of activity in your server.

You certainly don't want autogrow occurring all the time.  So preallocate a chunk of additional space for the data file, change the log file to a fixed amount ... then stop worrying about this, as you have vastly more important things overall to tune, such as indexes, remove ISNULL() and other functions from WHERE clauses and JOINs, etc..
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 7
  • 6
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now