Sunday, May 13, 2012

Auto Grow or Auto Slow

I have recently taken a position with a new company as the SQL Server production DBA.  My second weekend with the company I received the call - "users are reporting slowness", and then asked if I can take a look?

Anyone that has worked as a production DBA understands that this is all part of the job.  So I jumped onto the vpn and started taking a look. Now, there are multiple production database servers in my new environment, and I am still getting a handle on them.  This particular server I have not reviewed yet.  I checked for locking, blocking, long running process. I checked that I could run basic queries against the database in question.  All of these came back clean.

I checked the OS for disk space, fine.  Then I started looking at the data file size and amount of free space in the tempDB and database files themselves.  The PRIMARY data file was at 0 free space.  Now comes the fun part.  Autogrow was turned on, but at 1MB growth.  I asked - how often has it grown? Could this be what is causing the users to complain?

So - here is what I ran:

--Section to report autogrow data
DECLARE @trcfilename VARCHAR (1000);
SELECT @trcfilename = path
FROM   sys .traces
WHERE  is_default = 1

SELECT StartTime,
       DB_NAME(databaseid ) AS DatabaseName,
       SUM (( IntegerData*8 )/1024) AS [Growth in MB] ,
       (Duration/ 1000 ) AS [Duration in seconds]
FROM   :: fn_trace_gettable(@trcfilename , DEFAULT )
WHERE  (EventClass = 92 OR   EventClass = 93 )
       StartTime ,
       Databaseid ,
       IntegerData ,

The code I used it not the important take away here, if you are interested in the code and reading from the default trace check out


The take away from the results, the database was auto growing during the time of reported slowness. Now this was a small database, and in the past users more than likely were not using the system the same way. Systems change, and databse usage changes with it. 

I have added some head room in space to the data file, as well as set the database to auto grow but changed from 1 MB to 150 MB at a time.

The point I am making, even if your system has been working, always check your settings and plan for growth. While I was pleased that I found root cause, you can bet that I will be giving this database server a complete "work-up" to see what other pitfalls we can avoid. There are enough things that can go wrong on a good day that it is my job as a DBA to try and avoid the easy ones.