SQL Server 2000 Performance Tuning

[ewin740]

Are there any resources out there that somebody can point me to accomplish this?

users complain of Slow performance from database front end clients.

Running SQL server 2000 on a Windows 2003 server with 4 GB RAM and Dual Pentium 4. 2 Disk Drives. New Dell machine just installed 2 months ago.

 

[SQLDenis]

start here

http://www.sql-server-performance.com/tips/all_main.aspx

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[mrdenny]

You haven't exactly provided a lot of information.

Some basic things to check are:
Do you need to add some indexes?
Are your disks queueing?
Are you using SQL 2000 Enterprise Edition or Standard?
If Enterprise do you have AWE enabled?
Do you have the /3GB and /PAE switches in the boot.ini?
What does the buffer cache hit ratio look like?
Are you using dynamic SQL, stored procedures, or dynamic SQL within stored procedures?

These would be some places to start.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[ewin740]

Thanks my mrdenny. I'll find time to review those checklist
what my production sql server settings are. I installed this myself and i basically had default settings. some i manually altered e.g. " boost sql priority in windows", query timeout =0

It's SQL 2000 Std ed.
also, The database is about 10GB and being backed up everyday, every 4 hrs from 9am to 10pm...each backup file is more than 5GB in size. Transact log backup is created every 30 minute.

I have sql sp4 applied.
Windows 2003 SP3

My accounting dept. claimed that when their monthly maintenance (data extraction job) they used to do on the old server only took 10 minutes...but after we switched to this New machine, the job is taking at least 30 minutes to finish in this new machine.
the last time they ran this batch job, it crashed SQL on the server (front end clients could not connect, but those already connected were ok) and i had to reboot the machine to resolve the issue.

 

[mrdenny]

What software was installed on the old machine? And what were the hardware specs of that machine?

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog