We help IT Professionals succeed at work.

SQL server 2000 tweaking

Joeteck
Joeteck asked
on
Medium Priority
395 Views
Last Modified: 2012-06-21
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!!!

Thanks.
Comment
Watch Question

Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
>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?

Author

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 Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
>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?

Author

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 Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
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

Author

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...

Author

Commented:
Can I change these things live?
Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
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 Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
note that these are general settings, and should NOT be the solution to your problem...

Author

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.

Author

Commented:
In the task manager, SQLserver.exe is using 1.7gig of RAM...
Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
>In the task manager, SQLserver.exe is using 1.7gig of RAM...
then leave the max memory where it is.

Author

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 Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
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.

Author

Commented:
How can I check the size of the database?
Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
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.

Author

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 Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
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 Architect

Commented:
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.

Author

Commented:
Those suggestions are wonderful... How do you do them?

I know nothing about SQL server 2000.

Thanks.

Author

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.

Author

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.
Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009
Commented:
>4. No Statistics.
that looks like it's wrong, statistics is quite (very) important.
check out the update statistics command.

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
anyhow, do you have some live system where the application runs?

Author

Commented:
If you're asking about the program, its a custom application for our business. Does everything except accounting
gabesoSolution Architect
Commented:
Try:

 1. DBCC CHECKDB
  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.

Author

Commented:
What is the syntax to do statistics & indexing? I have no idea what to do...

gabesoSolution Architect

Commented:
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":
http://www.microsoft.com/downloads/details.aspx?FamilyID=a6f79cb1-a420-445f-8a4b-bd77a7da194b&DisplayLang=en

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.
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.