Posts Tagged ‘disk space’

Should you turn off Autogrowth for tempdb data files in a production system?

July 6, 2011 Leave a comment

Yes, if you want consistent and optimal performance.

And, note that we are talking about the production system (and any other test system where you test system performance).

According to Microsoft tempdb optimization recommendations, you should have “one tempdb data file for each CPU on the server” (cores count as CPUs), and you should “make each data file the same size”.  This recommendation is repeated by experts in the field.

So to ensure the multiple tempdb data files are always the same size, you must disable any Autogrowth settings.  Preallocate the files to meet the demands of your largest workload and then monitor usage to make sure you have plenty of reserve space.

The danger of leaving Autogrowth on is you rely on that as a crutch to ensure your files are large enough.  Then when they autogrow you are not alerted and your system performance is compromised.

Granted while the Instant File Initialization feature of Windows Server 2003 and SQL Server 2005 make for much faster file growth, there is still a window where operations pause.

Note this quote from Microsoft in Working with tempdb in SQL Server 2005:

By default, tempdb is set to auto grow; once the space in the file is full,
the file is allowed to grow 10 % of the initial file size. However,
relying on auto grow to manage file growth causes all applications or 
internal operations that use tempdb to pause. Instant data file
initialization (see Improvements in SQL Server 2005) shortens this pause,
improves performance, and allows files to grow much faster as compared to
SQL Server 2000. It is important to note that auto grow can lead to data 
fragmentation and should be used as a last resort.


How to save disk space on dev SQL Servers

May 23, 2011 Leave a comment

One of the first things I do on any development installation of SQL Server is change the Recovery Model on the “model” System Database from “Full” to “Simple”.  This ensures that new databases created carry the Simple model which means the transaction log will be truncated automatically.  Just checked a new SQL Server 2008 R2 Developer install and it was Full – I would have thought that Microsoft would default to Simple on dev installs but I guess not.

One other tip: If you restore a database – say you bring back a live or test copy onto your dev environment for analysis / debugging – it will keep the Recovery Model of the original – so be sure to change to Simple if you don’t want a large transaction log building up during development!