Hi All,

You probably see this question alot, but I need some advise about new hardware for our SQL Server which is getting slower and slower.

Our Current configuration:

• DELL 2950 with VMWare Infrastructure 3
• 4 core's (1 CPU)
• 3,6 GB RAM (our current VMware limitation)
• SQL Server 2000 std (so only 2GB in use)
• system drive RAID 1 - 2xHDD
• data drive RAID 5 - 7xHDD
• log drive and some indexes RAID 1 - 2xHDD
• FB-HDD are in a EMC SAN setup.

Current PerfMon logs (average between 10am and 8pm):

• CPU: 25% usage
• RAM pages per sec < 20
• Physical Disk usage
• system drive 1%
• data drive 50%
• log/index drive 10%
• Buffer Cache Hit 99%
• 30 Transaction/sec

There are alot of big queries running on this machine, and the main goal is to speed them up alot. And in general improve system performance. What will give me the most benifit? RAM, HDD?

 So I was thinking 2 new dedicated server (failover clustering) for SQL Server which would contain this:

• 2xQuad Core Xeon
• Windows Server 2003 Enterprise x64
• SQL Server 2005 x64
• 32GB Ram
• 2xSAS HDD Raid 1 (system drive)
• 9xSAS HDD Raid 5 (data drive)
• 4xSAS HDD Raid 10 (log/index drive)
• ...

Any other good idea's? More drive array's? Different config?

Thanks,

Koen

On what areas the current SQL Server performance is hampered?

Is it compulsory for you to replace the current hardware and SQL Serve?

Current performance issues is the increased time it takes to run queries.

The problem is that it is a mixed production system with lots of user databases, but there are also alot of big reports which are run frequently over the tables. An OLAP server could take care of some of the reports but not all.

Mainly 2 table on the entire SQL server cause the biggest problem, 1 table is 40million records = 36GB, and the other 3,5million records 7,5GB. These are tables on which small SPs run, but also big reports, I've squized out as much performance I can out of these tables using index's.

Second problem is that I'm out of space (which can be fixed with some additional drives)

The current setup doesn't need to be replaced, if anyone has some good performance tips. I've exausted all of my idea's.

But a fact which I need to take into account is that within 6 months the amount of data on the entire system will increase by 40%, which is alot, this means a 60GB table and a 10GB table. And I'm afraid that our current problems will only get wors.

Rgds,

Koen

Ok what is the schedule of optimization jobs on the tables that will have frequent inserts & updates. Also you need to check on the index strategy in finding out about new indexes based on the data you have selected.

There are many areas of performance tuning in SQL Server: database design, application design, hardware/software configuration, and many more. But none are as important as indexing, becuase they are used when the data is read or accessing, so having a base strategy of the "right indexes" is the most important thing you can do for performance and scalability.

Having an index is not sufficient when you are not reindexing or optimizing to address the fragmentaiton, so http://sqlblogcasts.com/blogs/ssqanet/archive/2008/02/19/sql-server-2005-index-optimization-best-practices.aspx is the best one to adopt.

Optimization jobs run every sunday, but do they rebuild indexes? (standard SQL server maintenance jobs, which fail from time to time due to lack of space)

One test I ran:

 DBCC SHOWCONTIG scanning 'WOPOH1P' table...
Table: 'WOPOH1P' (165575628); index ID: 1, database ID: 25
TABLE level scan performed.
- Pages Scanned................................: 9076
- Extents Scanned..............................: 1147
- Extent Switches..............................: 2411
- Avg. Pages per Extent........................: 7.9
- Scan Density [Best Count:Actual Count].......: 47.06% [1135:2412]
- Logical Scan Fragmentation ..................: 4.24%
- Extent Scan Fragmentation ...................: 69.75%
- Avg. Bytes Free per Page.....................: 1054.7
- Avg. Page Density (full).....................: 86.97%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

After DBCC DBREINDEX

DBCC SHOWCONTIG scanning 'WOPOH1P' table...
Table: 'WOPOH1P' (165575628); index ID: 1, database ID: 25
TABLE level scan performed.
- Pages Scanned................................: 8789
- Extents Scanned..............................: 1105
- Extent Switches..............................: 1104
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 99.46% [1099:1105]
- Logical Scan Fragmentation ..................: 0.00%
- Extent Scan Fragmentation ...................: 0.27%
- Avg. Bytes Free per Page.....................: 824.7
- Avg. Page Density (full).....................: 89.81%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

I better rebuild all my indexes and see what performance improvements I get.

But RAM will always be a big limitation I think, currently I only have 2GB of usable RAM and I have +150GB of data. that can't be good for performance.

rgds,

Koen

What is the edition of SQL Server used here and its service pack level/

 The counter performance parameter are perfect , and no bottleneck except adding extra storage as needed.

 As you use VMWare Infrastructure 3 , you can follow other upgrade path :

- add one extra processor to the current hardware.

- add extra storage as needed.

- upgrade to  Infrastructure 3.5 (Overcoming  Memory Limitation)

- install Windows  Server 2003 x64 , sql server 2005 x64

We are running SQL Server 2000 Standard SP4 (8.00.2039)

VMWare Infrastructure 3 has some limitation, I can only add 4 cpu's. Since 2xquad cores are viewed as 8 CPU's, not 2 I'm limited to 1 CPU.

A new version of VM3 support up to 32GB or 64GB of RAM, but since I'm currently running SQL2000 std I can only use 2GB.

 .. so upgrade sql 2000 standard to next sql 2005 ENTERPRISE , is the first priority,

especially to benefit the performance enhancement.
 

SQL Enterprise is a bit to expensive. For that price I can buy a new server and SQL server 2005 standard.

Don't waste money on SQL 2000 anymore as it is out of mainstream support, you are right to go with SQL 2005 standard in this case that will have more improvement in terms of scalability on the hardware aspects you have defined.

review the Comparison Between SQL Server 2005 Standard and Enterprise Editions , to be sure that no feature loss is needed.

http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx

http://www.microsoft.com/sql/editions/enterprise/comparison.mspx

AS feature needed is included in standard - go ahead.
 

I believe you are using SQL 2000 and 32bit server. There is option called AWE which allows you to use more RAM if you have. Please check out enabling AWE option in 32bit server.

-Bala

No use if you have SQL 2000 Standard edition as it is limited to 2GB only.

I've created a maintenance jobs that manually runs DBREINDEX against all the tables. This has helped a bit.

To further speed up the process I broke the query down into some sub queries which dump their data in a table. The always seems to go alot faster then building one big query.

rgds,

Koen