🚀 go-pugleaf

In article <3ou1ra$t98@Starbase.NeoSoft.COM>, vandusen@Starbase.NeoSoft.COM (Mike Vandusen) says:
>
>In article <3oohfa$1om@usenet.INS.CWRU.Edu>,
>Dhillip Anandam <dm235@cleveland.Freenet.Edu> wrote:
>>
>>I hope someone can help us.  We bought SQL server for NT for our project.
>>At present, it hasn't gone into production because of the speed.  It is
>>very slow even when we do updates to our database.  For example, for
>>SQL server to update a table of 3500 entries, it takes more than 30 minutes.
>>I was just wondering what we might be doing wrong.  Is there something
>>we will have to do to get the system running faster?
>>
>>We are running the server on a Novell Network and using Named pipes to 
>>access it.  The NT server has 128M RAm, 2 Pentium processors.  We got us 
>>the fastest machine we could get.
>
>Just a few guide lines:
>
> 1) Put the table(s) you are updating and the log on different physical
>    disk devices.  In general, if you can hear a lot of disk activity during
>    the update process, you do not have an optimized database configuration.
>    A rule of thumb is: the more disks, the better.  Use Performance Monitor
>    to watch your %cpu and %disk activities.  When %disk goes down and %cpu
>    goes up, you are doing the right thing regarding configuration.
>
> 2) Remove indices from the table(s) being updated.  After the updates,
>    rebuild the indices.  If the environment is online transaction processing,
>    dropping the indices might not be possible.  For end-of-month processing,
>    where your sql job is likely the only process touching the table, dropping
>    the indices can be a BIG win.  I saw better than a x10 speed up!
>
>Sounds like you have plenty of horsepower.  If you can't get your queries
>working better, you might try posting some more specific information.  Like:
>
> 1) Disk configuration.
> 2) Database device definitions.
> 3) Database/segment/table layout.
> 4) Sample query.
>
>Regards,
>Mike
>
Mike's right, except for the network, you have plenty of horsepower (so I'm 
prejudiced, shoot me. We use an MS network here). We're operating with many of the 
same variables here and some servers with even less power on tables 10 times 
that size. The only time I've ever seen that kind of lackluster performance 
was when the box wasn't configured right and the query was bad to begin with.

I apologize for checking out with basics, but have you looked at your SQL server 
configuration? On an 'out of the box server', I usually have to add a 50M device
and extend the 'tempdb' database into it. Check the server options for how much 
memory SQL is allowed to use (sp_configure) and make sure 'memory' is set to 
around 51200 (Good idea to read the book carefully about this and make sure 
you leave 16M for NT). 

Lastly try running the setup program in the 
NT SQL Server group and go to the Change Server Options dialogue. Here you 
should be able to check the 'Boost SQL Server priority' and since you have dual
proessors you should make sure the SMP option is checked also. This can be  
changed on the fly.

You should follow this up with a look through the server log, SQL log and running
perfmon.exe against it. You might need to stick a sniffer on the net to check out
the traffic too. Lotsa investigative work, but the payoff should be great.

Mark Teegarden