SQL SERVER 2000 ENTERPRISE CONFIGURATION

[NJAnalyst]

We are looking to ensure that we have configured our SQL server to the optimum settings for Macola. SQL Server 2000 Enterprise is running on Windows 2000 Advanced Server platform with 4 processors and 8 gb of ram. We are on Macola version 7.6.200 with the necessary patches. We are also running the WMS designed by IPS/Exact NJ. We seem to have some speed issues through the course of the day but the system resources of the SQL box and the app server barely seem to be touched. I would love some insight into the correct configuration. We have discussed this with Exact NJ and got some information that turned out wrong. They recommended we find a SQL expert but the networking companies that we have ties to do not have anyone that has impressed us yet. I can email the sp_configure to anyone that may have some insight. Thank you for any help you may be able to provide.

Jonathan Nelson
Business Systems Analyst
Horizon Group, USA

 

[vbajock]

I you using gigabit nics and hubs network wide?

 

[stravis]

More often than not, speed issues with SQL Server can be attributed to just about anything, other than the configuration of the SQL Server settings themselves. Usually, there are three main bottlenecks that will slow down a SQL Server.

The first bottleneck is data access speed. If your software (SQL Server) can not access and update your data fast on the physical hard drive(s) then you will get slow performance regardless of any other system hardware specification. It doesn't matter how much memory you have, how many CPUs you have, or how fast your network connection is, you will get slow performance with one user and slow performance with a thousand users. You need to make sure that your SQL data is contained on fast SCSI drives. The most ideal situation is to have a drive for your system, a drive for the SQL Data, and a drive for the SQL Log; each of these running on separate SCSI drive controllers. The reality is that the cost of doing this is often overwhelming so you need to find a happy medium.

The second performance bottlneck is in processing requests. If your SQL Server is overwhelmed by requests by too many clients then the system will slow down. The way to improve your SQL Server's ability to process requests is to increase CPU speed and multiplicity as well as increase the amount of memory in the server.

The third performance bottleneck is request result bandwidth. This bottleneck occurs when you have to many clients requesting large amounts of data to be returned over the network. The solution for this problem is a faster and smarter network. You should use switches not hubs, and if at all possible provide a Gigabit network connection to your SQL Server from your switch. My personal feeling is that providing a Gigabit connection to each client is overkill, and rarely provides a cost benifit.

That being said, it is possible that SQL Server is not the one slowing down your processing throughout the day. It is possible that some program such as a backup application or a scheduling program is running a process that is slowing things down. Of course it is also possible that SQL Server's own backups are slowing things down during the middle of the day.

Scott Travis
infoSpring, LLC.
scott.travis@infospring.net

 

[NJAnalyst]

Thank you for your responses. The servers are all connected on Gigabit switches. We have 40 users and 25 of them access Macola via two Citrix servers (also on gig connections). I will bring up some of the other issues you discussed with the Network Manager. Thank you.

Jonathan Nelson
Business Systems Analyst
Horizon Group, USA

 

[stravis]

Just a word of caution; make sure you don't just run out and buy all the hardware I suggested as the "ideal" hardware configuration. There are very few "ideal" SQL Server installations out there, and with a load of 40 users are you'll need is a decently proficient setup, that is unless you are doing a high amount of analysis or data intensive processes.

If you have not already done so, you should have Macola running from a separate file server from your SQL Server. With 40 Macola users you will see a good amount of speed increase by moving your MacApps share to another server.

Scott Travis
infoSpring, LLC.
scott.travis@infospring.net

 

[kscheeler]

What are the specs / versions on the two Citrix servers. I also recommend that you run Macola locally on the Citrix servers if you're not doing so already.

In previous threads, you've mentioned that your Macola databases are quite large. We've had one client who's rewritten some of the Macola processess like posting invoices as stored procedures as well as some of the canned reports which sped up those processess tremendously because of the number of transactions they are doing.

Kevin Scheeler

 

[NJAnalyst]

The citrix servers are dual processor boxes with 4 gb of RAM. We have third box for overflow and work from home users. I think we are running Macola locally on the Citrix server as a published app. Our databases are quite large. Our main database is about 135 gb now and our secondary database is 34 gb. We also just created two more companies to handle new product lines. We are EDI with most customers as well with an average of 1200 orders a day being integrated and I am not sure on the number of invoices. We also send ASN's for Wal-mart and Target. Some of the orders can have as many as 400 line items as well. We have our support direct with ES NJ for some modules and corporate for EDI, WMS. I think that our network is in pretty good shape in reference to the bottlenecks. The drive speed I will look into. I am sure that we have multiple large calls for data all the time. The WMS has been a resource hog. They have done quite a few rewrites because of our issues. The apps are on a seperate server. All switches and all but one managed. The one dumb switch is required for the RF in the warehouse. We actually post by using a deferred process and it runs at 0100. Our backups do not start until 2100. The SQL box itself barely looks like it is burping, RAM utilization is at around 2.1 gb with 6 gb made available of the 8 on the box. I am not sure on the processor utilization. But 4 P4 Xeon processors should be able to hanle it. Any other insight is of course appreciated. Thank you.

Jonathan Nelson
Business Systems Analyst
Horizon Group, USA

 

[vbajock]

I'd really look into the hard drive set up. Everything else your describing is state of the art, and for only 40 users it should serve your purposes. I've seen it where servers are ordered without the drives and then the old drives are swapped in during set up, later to be discovered as bottleneck, especially if they have been swapped in several times. I believe Checkit of Belarc will show what level SCSI you are running. You should be running Ultra 320, or one of the newer technologies that achieve that or greater speed.

Stravis is right, there really isn't a whole lot to configure on sql server. Since most Macola traffic is the retrieval and saving of a single record, bandwidth and drive performance are your biggest factors if you have a lot of users. The other possibility is that during initial configuration some one made a needle in the haystack mistake on something, like the wrong driver on a critical component somewhere.