Is my SQL Server 2000 optimized?
My server is x366 (02 CPU 3.16GB, 4GB memory, 6x73GB HDD/RAID5-C, D, E, F, CD-ROM) with MS Windows 2003 Enterprise and SQL Server 2000 Standard (SP4) for Database Server purpose (client/server app). I have two questions for you:
1. Here's my SQL Server 2000:
C: (OS + SQL program install )
D: (50GB for Database data files .MDF)
E: (50GB for Database log files .LDF)
F: (other data)
Would you consider this to be optimized?
2. It is my understanding SQL 2000 Enterprise supports a maximum of 2GB memory. Can I use TEMPDB in RAM to enhance the performance? If so, how do I go about configurating my system for optimal performance?
1. This is a good configuration to split the different processing types. Without performing any capacity planning, it is difficult to determine if this storage will be sufficient though. In addition, based on the controller configurations, it may be better to have independent physical disks as opposed to a single RAID 5 set. Evaluate the performance of the server under load in a test environment and see the results. If the issue is disk drive (queue) related consider additional drives and RAID sets to support the load.
2. SQL Server 2000 Standard edition can support up to 2 GB of memory with Windows 2003 Enterprise Edition. If you upgraded to the Enterprise Edition of SQL Server 2000 a total of 32 GB could be leveraged.
TempDB in RAM was an option with SQL Server 6.5 and is no longer an option with SQL Server 2000.
This was first published in December 2005