SQL server 2000 tweaking

We are running a custom application to enter in data on the SQL server.

That said, is it possible to have the SQL server configured wrong? Most DBA's, (not me) can tweak the server to it can achieve the highest efficiency in terms of data integrity.

Am I wasting my time, or can this be done?

Someone before me set it up, but I have reason to believe that they just installed the server and did nothing else.

The reason for the question, is sometimes this custom application may kick people out for no reason while doing a task.

I'm trying to rule out the SQL server, as it may be configured wrong. But I don't know... I'm turning to you Guru's for help.

The server is Dell, Dual 3.06Ghz Xeon's ( HT is turned off.) with 4gig of RAM running windows 2000. SQL server does not have SP4 applied. Not too sure if that will fix our problem, as I fear it may create more..

I'm assigning 500 points to this. If you can help me fix ALL of our problems, you deserve it!!!

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
>The reason for the question, is sometimes this custom application may kick people out for no reason while doing a task.

any error messages in the logs?

on any specific sql statement?
JoeteckAuthor Commented:
No, the error message pertains to the application, not SQL.

If thats the case, would SQL have a log of its own? if so, where?
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>SQL server does not have SP4 applied.
but I hope; at least SP3 ?

>If thats the case, would SQL have a log of its own? if so, where?
check in the LOG folder on the server, you will find a ERRORLOG file

>The reason for the question, is sometimes this custom application may kick people out for no reason while doing a task.
possibly the application is biggy?
Need More Insight Into What’s Killing Your Network

Flow data analysis from SolarWinds NetFlow Traffic Analyzer (NTA), along with Network Performance Monitor (NPM), can give you deeper visibility into your network’s traffic.

JoeteckAuthor Commented:
The application is written in CLARION programming language. I've never heard of it until I worked here. But in terms of speed, which is faster? VB, C++, C#, or clarion?
Guy Hengel [angelIII / a3]Billing EngineerCommented:
In the memory tab, reduce the max memory setting from ALL to ALL - 300MB
in the processor tab, set the settings that allows to run a query over multiple processors to "run over 1 processor", as in most of the cases, this avoids problems for smaller systems.

i don't know clarion, so i cannot judge
JoeteckAuthor Commented:
Also, the server has (3) 74 gig 15K drives in RAID 5. 6 gig partition for boot, and 128gig partition for database.

The server has 128MB cache on the controller that has been set to "write-through", but it was defaulted to "write-back" when the system was built.

I know that you need three seperate physical disks for OS, Logs, and database.

This "thing" was here before me...
JoeteckAuthor Commented:
Can I change these things live?
Guy Hengel [angelIII / a3]Billing EngineerCommented:
yes and no

the memory thing will be applied immediately, the processor setting will only get active when restarting the sql server
Guy Hengel [angelIII / a3]Billing EngineerCommented:
note that these are general settings, and should NOT be the solution to your problem...
JoeteckAuthor Commented:
There is a minumum and maximum on the memory.

Which one or both do I change?

Do I leave minimum at 0, and change the maximum to 300MB.

JoeteckAuthor Commented:
In the task manager, SQLserver.exe is using 1.7gig of RAM...
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>In the task manager, SQLserver.exe is using 1.7gig of RAM...
then leave the max memory where it is.

JoeteckAuthor Commented:
What is the point of the memory usage tool? If I did change it, will the 300MB limit be enough to run the server, or will things slow down?

No one has complained yet... So maybe it is ok...
Guy Hengel [angelIII / a3]Billing EngineerCommented:
300MB will quite sure slow things down when traffic on the database increases (unless the entire database is smaller than 200MB)

SQL Server will put as much data from the tables into RAM to make data access as fast as possible.
JoeteckAuthor Commented:
How can I check the size of the database?
Guy Hengel [angelIII / a3]Billing EngineerCommented:
the size of the database is the sum of the sizes of all tables + indexes.
check out the output of the procedure: sp_helpdb

also, check out the "Taskpad View" in the enterprise Manager when on a database.
JoeteckAuthor Commented:
Remember, you're talking chineese to me... I have no idea what you just asked me to do.

If you break it down, I might be able to follow you.
Guy Hengel [angelIII / a3]Billing EngineerCommented:
in the Enterprise Manager, select your database, and then right-click it, and in the context menu, you have view->taskpad
you will get another view which you should learn a bit (learning by doing).
gabesoSolution ArchitectCommented:
SQL Server runs quite well 'out of the box' but obviously can run better if tweaked!

What is not clear really is the problem you are facing: 'users kicked out of the box' suggests timeouts - is that what happens - errors occurring during use?

A timeout can occur for a number of reasons, some questions you want to ask are:

 - are all of the tables indexed properly
 - has the database been checked (DBCC CHECKDB)
 - have the statistics been updated

We have noticed that when a large database runs for a while an error can occur with tables where obviously the indexing has 'gone wrong'! This manifests itself as strange errors that don't help a lot

The solution is to rebuild the indexes on those tables!

So what I suggest is that you check that the database has all of the correct indexes and then have those indexes rebuilt (weekly?). This will help performance and possibly eliminate any odd errors you are experiencing.
JoeteckAuthor Commented:
Those suggestions are wonderful... How do you do them?

I know nothing about SQL server 2000.

JoeteckAuthor Commented:
I'm in enterprise manager, under the database name, tabes. Then I right click the table name, properties.

I just checked a few tables, and it seems the indexing is in the "inactive" state. No idea if I'm even veiwing it correctly.

But from the sounds of it, this database may be setup incorrectly.
JoeteckAuthor Commented:
Thiongs I do know.

1. All users are using the same password to connect to the database.
2  No Triggers
3. No Views
4. No Statistics.
5. Indexing - yes.
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>4. No Statistics.
that looks like it's wrong, statistics is quite (very) important.
check out the update statistics command.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Guy Hengel [angelIII / a3]Billing EngineerCommented:
anyhow, do you have some live system where the application runs?
JoeteckAuthor Commented:
If you're asking about the program, its a custom application for our business. Does everything except accounting
gabesoSolution ArchitectCommented:

  this will check the database for any issues.

  2. If you play around with options for 'generate script' (right-click database in explorer->context menu) you can generate create scripts for the database: You can break the generated scripts up into smaller sub-scripts and then use them to do various things.

  what you want is to script the entire database and then build a re-indexing script that drops all of the indexes and then re-creates them.

There are many alternative ways of doing this but from your perpective this may be an easy way of gathering the sql to do it.

When all of the indexes are re-built you will be able to prove that it isn't an issue relating to indexing! Unless critical indexes are already missing: Surely the application comes with tools or scripts for building the schema etc?

Note that these operations should really be done when nobody is using the database.

I do think you need someone in to look at the DB or alternatively provide VPN access for someone to look at it.

If you don't have the expertise yourself it can be simpler to "get someone in" - which will save a lot of time.
JoeteckAuthor Commented:
What is the syntax to do statistics & indexing? I have no idea what to do...

gabesoSolution ArchitectCommented:
You need to explore a little: Look at point #2 and generate some sql for yourself.

You will benefit a lot from downloading the microsoft help - "sql books online":

You are going to have to get your hands dirty on this one - please download and start reading the microsoft sql server help (from the url given):

The problem is that there is no alternative and you get pro-active on this - you don't know enough to benefit from the suggestions and this is not the forum for a complete tutorial - you need to start learning to handle sql yourself and you have to start experimenting to really learn anything.

Trust me - the microsoft documentation is quite good - just follow the link.

You may also want to invest in a course on sql server - if that's a possibility - or even just get a "24-hour intro to sql server" type of book.

Good luck.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.