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,

       Filename,

       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 )

GROUP BY

       StartTime ,

       Databaseid ,

       Filename,

       IntegerData ,

       Duration

ORDER BY

       StartTime

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 http://bit.ly/JS99kw.

Results:

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.