SQL Server problems

[pvmurphy]

We have one (of four) SQL Server on out network that has been causing problems the last couple of days. Some fairly intensive procedures from VB seem to grinding SQL to its knees, we are getting timeouts, connection problems (automation errors), and low memory messages. We have restarted the server 2 times today, and will do it one more time before the end of the day. This server has 4 user databases, about 42 GB’s of data, and 1 GB ram. Nothing new as been added, and out other servers (with the same databases and applications, just different customers) is working fine. This server had been working fine until this problem. I other note that might be of interest, is that it seems the tempDB after restart is about 7 MB, and very shortly afterwards increase to about 70. Any suggestions as to what might be causing the problem.

Thanks,

Jim

 

[nigelrivett]

Yep - poor coding.

Is all access via SPs? If not change it so it is.
In the meantime use the profiler to see what is going on and you will spot some queries that are taking a long time and doing large aggregates or the like - or maybe just creating large temp tables.
Also look out for things that create server side cursors or return much larger recordsets than they need.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[nigelrivett]

Also make sure you have the right indexes and statistics are up to date. Maybe it's creating work tables because it has to table scan.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[pvmurphy]

Thanks for the reply, we are going to try updating indexes and statistics.

Some of the code is in SP, nut a majority is not. The code is 4 huge Active EXE's, which does a lot of Drop Table, Create Table, Insert into etc, and the tables are a good size (~ .25 millon). Also, the task of changing this program will fall on my lap, I having enough to hanlde with the 4 SQL Servers and doing fixes on the code.


Thanks,

Jim

 

[nigelrivett]

>> which does a lot of Drop Table, Create Table
That's a bad thing.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[pvmurphy]

>> which does a lot of Drop Table, Create Table
That's a bad thing.

I walked into a situation (been their 3 weeks) where I'm in charge of the applications and the databases, and the person I replaced was let go the previous day, so I have a lot of catching up to do on my own. The code does the drop table, create table and such as a protection against any problems mid way though the massive update. If things don't look good they can just back up though the procedure, similar to a wizard.

It seems like I starting to go off on another tract but I think it relevant. I believe the program is coded as such because they use a simple backup plan rather than do logging, so if they had problems they would need to restore to the previous night. Is logging doable here, with the large transaction sizes, or any other suggestion other than a massive reprogramming effort?

Thanks again for your help.

Jim