Re: MS SQL Serveror ORACLE?

This is an interesting (and to me a somewhat scary) thread. For what it's worth, we have a 10GB database consisting of some 100 tables; of these, two tables having 2.5 million and 4 million rows respectively, take up most of the used space. The first table has a clustered index and probably 14 foreign key indexes. On it, we regularly run updates on subsets of several hundreds of thousands of records, and nearly every evening, as many as 10,000 records are added. On the other large table, inserts of 90-100K records are made at regular intervals. Now I realize that this is less activity than some of the posters here describe, and I also have to say that we only have some 40 users banging on the data. But while there is the occasional (1/year) paging error, we have fortunately not experienced the kinds of horrors described in this thread. Possibly some of this has to do with the fact that I have rigorously and fanatically (and some would say dictatorially) enforced a policy of sending inserts and updates 1 transaction at a time only. I think we have saved ourselves a lot of trouble by not allowing more than one transaction in an update or insert statement. What I'm having most trouble with is ensuring the consistency of the database; we run DBCC overnight after dumping the database, but there will come a time soon when DBCC won't complete before the first users come in in the morning, and I don't like to have it running while users are logged in. I ran a couple of experiments to see whether I could run CHECKDB and NEWALLOC with index checking on, but it took far too long and I had to abort--which of course meant recycling the server. Enough rambling on--I would appreciate any suggestions regarding management of larger databases.

John Spackman wrote:

> We too are fed up with MS SQL crashing and nuking our database almost every
> week. We have a 10Gb database, one table has >85million rows. If you
> hammer the server then it simply starts reading and writing the wrong pages
> on the disk and you end up with a corrupt database. The solution is to
> spend three days with the users staring at the ceiling while you transfer
> row by row into a new database and recreate the indecies. MS admit they
> have problems with large table support. Oracle 8 eval arrives Monday am,
> porting starts immediately.
>
> Oh and the query optimiser is a joke too. We have some big queries, and on
> one day a query will fly, the next day it crawls like a dog, even if nothing
> has changed. The solution is to tear the queries up into temporary tables,
> work out and enforce a good query plan.
>
> I hate it I hate it I hate it (and now it's personal!)
>
> John
>
> Mike Adams <103701.1437_at_compuserve.com> wrote in message
> <35045496.4DBCDD0A_at_compuserve.com>...
> >What are you trying to do that causes it to crash?
> >
> >Basta wrote:
> >
> >> My SQL Sever 6.5 SP3 have CRASHED 3-RD TIME while working with Access
> >> 97
> >> database!
> >> I am going to change SQL Server to ORACLE.
> >> Have anybody Something to say?
> >>
> >> --
> >> Russian Automotive Publishing House "Za Rulom"
> >> Computer Division
> >> Ruslan Sulakov
> >> mailto:ruslan_at_zr.ru
> >
> >
> >
Received on Fri Mar 27 1998 - 00:00:00 CET