Joeteck
asked on
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!!!
Thanks.
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.
ASKER
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?
If thats the case, would SQL have a log of its own? if so, where?
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
>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?
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?
ASKER
Yes, sp3. The application would be a big big problem. I just want to rule out the server, and be sure it is setup correctly.
Here are a few pictures of my settings. Its always easier to see it, than guess.
http://i16.photobucket.com/albums/b47/Joeteck/SQL%20server/transactionlogTAB.jpg
http://i16.photobucket.com/albums/b47/Joeteck/SQL%20server/ServerProp-serversettingsTAB.jpg
http://i16.photobucket.com/albums/b47/Joeteck/SQL%20server/ServerProp-memoryTAB.jpg
http://i16.photobucket.com/albums/b47/Joeteck/SQL%20server/ServerProp-GeneralTAB.jpg
http://i16.photobucket.com/albums/b47/Joeteck/SQL%20server/GeneralTAB.jpg
http://i16.photobucket.com/albums/b47/Joeteck/SQL%20server/DatafilesTAB.jpg
Here are a few pictures of my settings. Its always easier to see it, than guess.
http://i16.photobucket.com/albums/b47/Joeteck/SQL%20server/transactionlogTAB.jpg
http://i16.photobucket.com/albums/b47/Joeteck/SQL%20server/ServerProp-serversettingsTAB.jpg
http://i16.photobucket.com/albums/b47/Joeteck/SQL%20server/ServerProp-memoryTAB.jpg
http://i16.photobucket.com/albums/b47/Joeteck/SQL%20server/ServerProp-GeneralTAB.jpg
http://i16.photobucket.com/albums/b47/Joeteck/SQL%20server/GeneralTAB.jpg
http://i16.photobucket.com/albums/b47/Joeteck/SQL%20server/DatafilesTAB.jpg
ASKER
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?
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
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
ASKER
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...
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...
ASKER
Can I change these things live?
yes and no
the memory thing will be applied immediately, the processor setting will only get active when restarting the sql server
the memory thing will be applied immediately, the processor setting will only get active when restarting the sql server
note that these are general settings, and should NOT be the solution to your problem...
ASKER
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.
Which one or both do I change?
Do I leave minimum at 0, and change the maximum to 300MB.
ASKER
In the task manager, SQLserver.exe is using 1.7gig of RAM...
>In the task manager, SQLserver.exe is using 1.7gig of RAM...
then leave the max memory where it is.
then leave the max memory where it is.
ASKER
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...
No one has complained yet... So maybe it is ok...
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.
SQL Server will put as much data from the tables into RAM to make data access as fast as possible.
ASKER
How can I check the size of the database?
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.
check out the output of the procedure: sp_helpdb
also, check out the "Taskpad View" in the enterprise Manager when on a database.
ASKER
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.
If you break it down, I might be able to follow you.
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).
you will get another view which you should learn a bit (learning by doing).
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.
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.
ASKER
Those suggestions are wonderful... How do you do them?
I know nothing about SQL server 2000.
Thanks.
I know nothing about SQL server 2000.
Thanks.
ASKER
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.
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.
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
anyhow, do you have some live system where the application runs?
ASKER
If you're asking about the program, its a custom application for our business. Does everything except accounting
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
What is the syntax to do statistics & indexing? I have no idea what to do...
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.
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.
any error messages in the logs?
on any specific sql statement?