Improve Performace 1

[philosinger]

I am managing a SQL 7 Server that contains serveral databases. I frequently run SQL statements against some very large database (one inparticular is 7 million records) updating tables based on data in other tables. When the query is running I watch the servers tasks manager (The server is a Dual PIII 550 with 512MB of RAM and 3 10K RPM SCSI Disks in RAID5 running Windows 2000 Server (SP1)) and the processor utilization rarely peaks above 10% (averages around 8%)(It also seems to be balanced pretty well across both processors).

Being that this servers #1 (and for that matter only) job is to run these queries, I would like the ability to make the queries use more of the processing power (in an effort to speed the queries up). I alreay have the following settings turned on on the server:
Boost SQL Server Priority on Windows NT
Use Windows NT Fibers
Use all available Processors
Maximum worker threads 1024

Are these setting limiting the performace? How should I have this server configured to give me the absolute best performace on my queries?

Also, is anybody out there running MS SQL 7 on a Quad-Processor or greater server? We will be purchasing a new Database server in the next few months (4 PIII 700+ Processors with 2GB RAM). This server will have the ablity to upgrade to a 8 Processors. If SQL 7 does not gain any performance in this type of enviroment, then I may look at a smaller (cheaper) server.

 

[TomSark]

I would not Boot SQL Server Priority if this is only a SQL Server box.

I would not use fiber mode unless I had over 8,000 connections to the server.

I would use all processors, and use the default number of worker threads (256 if I remember correctly).

THE MAIN THING YOU CAN DO TO SPEED UP QUERIES IS TO MAKE SURE THAT YOUR APPLICATIONS AND SQL ARE EFFICIENT. If you don't have any network, I/O, or hardware bottlenecks, then you must focus on your applications. It is a common mistake to not focus on improving applications and to scrutine the server.
Tom Davis
tdavis@sark.com

http://www.sarkdallas.com

 

[TomSark]

I would also ensure that my databases are architected properly and that I am making use of indexes properly. Tom Davis
tdavis@sark.com

http://www.sarkdallas.com

 

[TomSark]

There is a white paper on the Microsoft Web site at:

http://www.microsoft.com/sql

written by Henry Lau concerning performance and tuning. It is an excellent recource for questions of this nature. Tom Davis
tdavis@sark.com

http://www.sarkdallas.com

 

[foxdev]

Also, is anybody out there running MS SQL 7 on a Quad-Processor or greater server?

I've run a database having a table with 200 million rows on a Compaq ProLiant 4-processor, 1.7Gb RAM 18-disk RAID box. The vast majority of the time, the processor load was very, very low - mainly because the number of users was quite low. I think one advantage of having 4+ processors is serving several users running large queries at the same time.

If I had to choose between 4 processors and an OK disk system, or 2 processors (with expandibility) with a stellar disk system, I'd choose the latter. In my experience with large (comparatively) databases, it is the disk I/O that is the bottleneck.
Robert Bradley

http://www.foxdev.com/

http://www.agrainofhope.org