performance problem

[tihost]

Hello,
Curent problem:
We have an compaq proliant with raid 5 512 MB server
Microsoft 4.0 NT service pack 6
SQL server 6.5
----------------------------------
program 1
inserts/updates/reads/deletes in 3 tables 25000 records a day.writen in C++ using standard ODBC via tcp/ip
----------------------------------
program 2
inserts/updates/reads/deletes in 3 tables 25000 records a day. writen in Delphy using BDE
and has 16 simular (exactly the same programs) who also are running.
-----------------------------------
After a sudden period (16 days) the performance is from one day to the other day sudenly gone!

Where can be problems:
SQL6.5 server configuration
ODBC connection
BDE connection/configuration
NT operating system

the pogram has run verry wel before with 100.000 database transactions a day. After installing NT and SQL6.5 on a new machine we get this problem!

 

[foxdev]

It could be a lot of things, of course, but one thing that comes to mind: your data and log file growth. Since it runs along fine for awhile then hits a slow patch, I'm thinking that maybe at that time the data and/or log files have reached their max, and are now autogrowing, which can take some time.

Second thought: it just so happens that the earlier writes were timed or spaced such that the lazy writer cache never got full, but when that threshold got crossed the system had to effectively pause while the dirty data was written to disk.

NOTE: my comments are based on my experience with v7, and may not completely apply to v6.5.

Robert Bradley

http://www.foxdev.com/

 

[TomSark]

Are you updating statistics, rebuilding indexes, and recompiling your procs?

Tom

 

[jnicho02]

Foxdev, good ideas, but autogrow for logs is new to 7.0. SQL 6.5 will just jam up if you completely fill the log, and believe me that can get pretty nasty!

TomSark, you could be on to a good track...but it wouldn't explain a sudden drop in performance.

Maybe it is something to do with the NT.

Were you using NT 4.0 before? it is very processor hungry compared to 3.5.

Have you checked your NT error logs. I had a machine that was sending information messages to the log, but because it wasn't set to auto-clear the logs NT log filled up causing a sudden jam.....

As to the SQL Server I hope that you are regularly backing up the db and the logs to clear them... My Home -->

http://www.geocities.com/jnicho02/

 

[calahans]

Are any of your programs running on the Server as well. Poor performance is most often not the fault of the RDBMS, and in fact is the applciations fault.

I think that there have been some good suggestions above. Definitely check the logs etc, and most definitely check the Indexes and Statistics.

Heres my ideas as to the possible cause:

SQL6.5 server configuration - possible, check the above suggestions
ODBC connection - unlikely to gradually ruin performace
BDE connection/configuration - unknown but I'd say its the same as the ODBC
NT operating system - possible to have memory leaks from other programs on the same system.

Do you have a similar test/dev environment, run Perfomance Monitor, checking the usual SQL Server monitors and also the system monitors, memory, threads, users, disk queues....

Hope this helps


Cal

 

[TomSark]

Acutally, it might (maybe) explain a sudden drop in performance. If you are updating the statistics at night, but your data in the tables is changing significantly in the day, then this may be an issue.

In addition to what I previously mentioned, and as everyone else mentioned, I would montior things that could affect performance: memory, Disk I/O, network traffic, etc.

Tom

 

[edge2000]

Also you may want to look at the Raid Controller setup, sql 6.5 was not always happy to coexisit with one which had it's own cache on board, you may want to try running without this for a while if possible.

Dave
dab@completebs.com

http://www.completebs.com

See website for more info but we can develop most things for most people.