SQL Server DBA advice, handy hints and tips

020 8465 9100

Sales Enquiries

Other Enquiries

SQL Server services

SQL Server support

Upgrades & Migrations

Tuning

Disaster Recovery

Business Intelligence

SQL Server tips

Welcome to SQL-DBA.com - SQL Server Database Specialists

SQL Server Hints & Advice:
SQL Server Tip: How to unlock you server’s memory

	Servers running Windows Server 2000 or 2003 limit applications to only half of the installed memory by default. Servers with 4GB or more may take advantage of boot settings that allow applications to access more memory.
Windows Server 2000/2003 Standard If you have 4GB of memory installed (the maximum for Standard edition), you can add the /3GB switch to your boot.ini to increase the memory limit to 3GB. Ensure that you reboot the server for the change to take effect.
Windows Server 2000 Advanced, Windows Server 2003 Enterprise and Windows Server 2003 Datacenter These editions of Windows Server can access far more than 4GB of memory, but only SQL Server 2000/2005 Enterprise edition may take advantage of memory beyond 4GB. If your server has 8GB or more installed, you may enable PAE (Physical Address Extensions) for Windows Server and AWE (Advanced Windows Extensions) for SQL Server Enterprise to get the most out of server’s memory.

SQL Server Tips: Adequately Sizing your Databases

	When creating new databases on SQL Server, the initial size is that of the model database (1MB by default). This is hardly adequate for most practical uses and will result in excessive growth and file fragmentation, impacting server performance.
Before you create a new database, estimate the amount of data you expect to store and set the size appropriately. This advice applies to log files as well. Databases that undergo frequent updates will require a larger log file, half the size of the database file is a good start. Better to allocate too much space than too little!

SQL Server Tip: Planning placement of database and log files


By default, SQL Server will place all database and log files in the same location on the server. If your server does not utilise RAID, you can obtain increased performance by placing data and log files on separate hard drives. As all database transactions are written to log files prior to being committed, dedicating a fast hard drive for log storage will give database transactions a performance boost.
	To get the most out of your database file placement, placing the tempdb on a dedicated hard drive will also increase performance.
Note: As a typical RAID system consists of two or more hard drives working in parallel, placing all database and log files on the RAID will offer similar benefits to using dedicated hard drives.


SQL Server Tips: Disable AutoShrink

	Autoshrink is a database option that allows SQL Server to reclaim unused space, but results in greater file fragmentation and unnecessary use of server resources.
Note: Only enable this option if drive space is tight and you don’t mind the performance hit.