Monday, 26 October 2009

SQL 2005 tunning

Almost any administrator can install Microsoft SQL Server after all doesn't take a great deal of knowledge to click next, but how many of us really have optimized systems?

Here are some useful pointers.

Do you have optimised drives for SQL currently the best setup is 1024K partition alignment this formatting must be done from diskpart in windows version 2000 and 2003 by default window 2008 uses 1024K alignment, you should have ideally have a minimum of three drives for your databases
one dive dedicated to tempdb
one or more drives dedicated to .mdf and .ndf data files
one or more drives dedicated to .ldf log files
and these drives should ideally be on two or more RAID controllers.

After the SQL server has been installed the first thing you should do is correct the default database locations so that new database are created on the correct drives.

USE [master]
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer',


REG_SZ, N'E:\Microsoft SQL Server\MSSQL.1\MSSQL\Data’
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer',


REG_SZ, N'E:\Microsoft SQL Server\MSSQL.1\MSSQL\Data’

Move the tempdb database this will have been located in the same directory as the system databases and this should be on its own drive this is done by running SQL query then restarting the SQL service after.

USE [master]
(NAME = tempdev, FILENAME = 'D:\Microsoft SQL Server\MSSQL.1\MSSQL\Data\tempdb.mdf')
(NAME = templog, FILENAME = 'D:\Microsoft SQL Server\MSSQL.1\MSSQL\Data\templog.ldf')

Move the Model database stop sql server and start the instance from command line using
NET START MSSQLSERVER /c /m /T3608 then run the following SQL query to detach the model database.

USE [master]


sp_detach_db 'model'


Move the Model.mdf and Modellog.ldf files from the origanle location C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data folder to the new location example:
E:\Microsoft SQL Server\MSSQL.1\MSSQL\Data.
Reattach the model database by using the following commands:

use [master]


sp_attach_db 'model',

'E:\Microsoft SQL Server\MSSQL.1\MSSQL\Data\model.mdf',

'E:\Microsoft SQL Server\MSSQL.1\MSSQL\Data\modellog.ldf'


Now stop the SQL server and start it normally from windows services.

Note: Make sure the directory structre exists before moving the Database

For best performance tempdb should have one data file per physical CPU assigned to SQL server, due core counts as two CPU however hyperthread does not, to find the correct number you can use the following script.

strComputer = "."

Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\CIMV2")

Set colCSes = objWMIService.ExecQuery("SELECT * FROM Win32_ComputerSystem")

For Each objCS In colCSes

WScript.Echo "Computer Name: " & objCS.Name

WScript.Echo "System Type: " & objCS.SystemType

WScript.Echo "Number Of Physical Processors: " & objCS.NumberOfProcessors



Set colItems = objWMIService.ExecQuery("SELECT * FROM Win32_Processor")

For Each objItem in colItems


Wscript.Echo "==================="

Wscript.Echo "== Processor " & i & " =="

Wscript.Echo "==================="

Wscript.Echo "Processor: " & objItem.Name

Wscript.Echo "NumberOfCores: " & objItem.NumberOfCores

Wscript.Echo "NumberOfLogicalProcessors: " & objItem.NumberOfLogicalProcessors



The same can be done on Windows 2008 by WMIC

wmic cpu get NumberOfCores, NumberOfLogicalProcessors

For best performance you need to grant the SQL service account rights to “lock pages in memory” and “Perform volume maintenance tasks" this can be done by editing the local policy on the server using gpedit.msc or by domain policy assigned to these servers.

Note: Windows server restart is needed for policy to take affect.

Changing the SQL startup parameters to opermise the system here are some example ones to use on x64 servers parameters: -c -E -T834 -T2301

-E Increase the number of consecutive extents allocated per file to 4

-T2301 trace flag to enable more accurate query run-time behavior modeling in the SQL Server query optimizer typically only needed for large data set decision support processing.

- T834 On systems with 8GB or more, this traceflag causes the buffer pool to use large pages. These are allocated at startup and are kept throughout the lifetime of the process. This trace flag can only be set on 64-bit

Note: If the system is not x64 and has less than 8GB of RAM do not use these switches

This concludes my notes for Microsoft SQL 2005 installation, please remember that these are some of the things that I have found to be of some use and I just didn't want to go into all of them in detail because I've not enough time in this posting.