Performance tuning SQL Server 2005 64bit 1

[davidchardonnet]

Hi,

I have a server on which I have installed this version of SQL Server:
--------------
Microsoft SQL Server 2005 - 9.00.3152.00 (X64) Mar 3 2007 05:22:48 Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)
--------------
on Windows 2003 Server R2 Enterprise x64 Edition SP2.

I want to tune Windows Server 2003 so that this machine will give the best performance for any database installed on it.

Do you have some tuning recipes to boost that server?

Thank you very much

David

 

[ptheriault]

There is really not much you do with the OS.

how much memory is in the server?
Have enabled Lock pages in memory?
What is your disk configuration?
Do you have tempdb installed on it's own array?
Do you have an array for data and another one for Logs?
Have you created your database yet?
If so, How many datafiles?
Have you separated you indexes into their own data file on another array?


- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[ptheriault]

Also,
How many processors?
Create a tempdb file for each processor (including multi core)

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[davidchardonnet]

Memory: 5 Gb
Lock pages in memory: I don't know how to do it
Disks:
C:\ ADAPTEC SCSI RAID 2120S controller with RAID 1 (System + SQL Server files)

D:\ 30 Gb Fibre channel IBM Totalstorage (MDF files)

E:\ 30 Gb Fibre Channel IBM Totalstorage (LDF files)

tempdb is on the c:\ at its default installation place

I have created my database, but i can delete it to recreate it after tuning.

The database has only 1 MDF file and 1 LDF File

The whole database is in the same file

 

[ptheriault]

Here is a link on how to lock your pages in memory.

http://msdn2.microsoft.com/en-us/library/ms190730(SQL.90).aspx

Move your tempdb and create a tempdb file for each processor
It should have it's own array but if you don't have any more available then put it on the log array. What is the RAID type for D and E?

How large to you think your db will get?

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[davidchardonnet]

2 x Processors:Intel Xeon 2.80 Ghz

-----

I have locked the pages in memory with LOCAL_SYSTEM AND NETWORK SYTEM Accounts (LOCAL_SYSTEM runs the sqlservr.exe)

-----

The D:\ and E:\ are on the same SAN (IBM Totalstorage DS4300) and are both 30 Gb and are enough for the database.

I don't know how I can move the tempdb database, and how to make 2 tempdb, one for each processor, I have no Idea

I can create F:\ and G:\ on the same SAN to host the tempdb databases if it's better than keeping them on the Log array.

 

[davidchardonnet]

I don't know RAID Type, I think it's RAID 5 of something like that

 

[ptheriault]

I don't know how I can move the tempdb database, and how to make 2 tempdb, one for each processor, I have no Idea

I can create F:\ and G:\ on the same SAN to host the tempdb databases if it's better than keeping them on the Log array.

Yes create an F:
Then use the following example to move the tempdb.

use master
go
Alter database tempdb modify file (name = tempdev, filename = 'E:\Sqldata\tempdb.mdf')
go
Alter database tempdb modify file (name = templog, filename = 'E:\Sqldata\templog.ldf')
Go

After you move the tempdb add a data file for each CPU.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[ptheriault]

You may want to consider moving the other system databases to your d: drive. You wouldn't want to crash the server because your msdb db grew to large and used up all the OS disk space.

- Paul
- If at first you don't succeed, find out if the loser gets anything.