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,
       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.