June 9, 1994
Hosted by: Don Plaster, David Zodikoff
Moderator: Perry Bent
Topic: Backup, Restore and DBCC of VLDB's
- - - - - - - - - - - - - - - - - - - -
Conferencing Guidelines and Protocols:
If you are logged on at 9600+, you may save yourself some mon
by re-logging ot 2400 baud. You do not need highspeed comms
Type ? on a line by itself if you have a question, and then
wait to be recognized by the moderator
! on a line by itself if you have a comment, and then
wait to be recogznied by the moderator
GA when you are finished typing (GA = Go Ahead)
The moderator will recognize you by typing:
You may then type your cont or question. Everyone else should
wait until you are finished. You should type GA when you are
finished with your question or comment.
- - - - - - - - - - - - - - - - - - - -
Perry Bent: Ok, why don't we get started?
David Zodikoff: At the conference, backups of a Gig ranged from 30 min
to 2 hrs. any idea about the discrepancies?
Peter Thawley: No, I haven't heard of such a disparity. Same HW
between the two tests?
David Zodikoff: Yes, I believe they were both HP 9000.
Peter Thawley: There wasn't any optimizations in v4.9 that would lead
SQL Server from doing things differently. I suspect there
must have been other forces at work here (raw vs file system
or perhaps other issues). All the optimizations went into
v10 with the multi-device I/O and large block sizes.
Don Plaster: There are several factors that might influence backup time
for a given data size. Disk speed, processor speed, tape
speed, etc. Maybe some other things as well. Does anyone
have any tuning suggestions within the software realm, other
than throwing faster hardware at the problem?
Bill Andreas: Thanks. Could you elaborate more about DBCC in 4.9 and
System 10, Peter?
Peter Thawley: In v10, we've taken the extent-based I/O concept and
extended dbcc to do these large I/O's for dbcc tablealloc.
Bill Andreas: Are there any other differences?
Peter Thawley: As far as I know, that is the only performance change.
At some point, we reduced the granularity for tables, but
I forget when that came. We can't yet talk futures but
suffice it to say there's a lot more relief coming !!!
Just looked in the ref and found that in v10, we added
optimized versions of the checktable and added checkindex
David Zodikoff: We just completed a Disk Subsystem benchmark and got some
strange results, using HP RAID drives and an I70, we couldn't
push more than 80 - 90 I/Os against a disk set up in
independant mode, it also seemed that when we changed
from ASYNC to non ASYNC that our CPU went down
substantially, 8 fold DISCLAIMER!!! - These are
preliminary results still any thoughts?
Peter Thawley: David. What was your benchmark doing ... with Sybase's
current 2kb io on normal DML, its often unlikely that
you can push the devices to their limits. Larger IO as
David Zodikoff: Dumps and Loads
Peter Thawley: David, was the db on raw or did you dump to raw device?
David Zodikoff: DBs were on raw and we dumped to tape ... do you think
the tapes are that slow? we use DAT drives
Peter Thawley: David, off the top of my head, I can't come up with any
good rationale. Perhaps we can take it off-line and get
more details to understand what you were measuring!
Bill Andreas: When the server comes up I see a messages about load
buffers ... are these configurable and will additional
buffers increase my load speed? ANYBODY?? Peter?
Peter Thawley: Bill, which parameter are you referring? Backup Server
currently does not allow one to configure buffers. It
makes this determination based on system info obtained when
the device is opened!
Chuck Privitera: I posed this question at the Sybase conference:
when working with VLDBs, we would like a better dbcc. ;)
Namely, we want it faster like everyone else,
but also, we would like other options besides
People on the panel at the conference
didn't seem to understand why I didn't
want to dropdb a 5GB database, but I think this
Peter Thawley: Chuck, what other options would you like to see on the
repair side ( I've got an engineering mgr who's sitting
right next to me ...
Chuck Privitera: Well, in general, I would like to surgically
remove the cancer; basically remove the offending
object if possible, and try to salvage the rest
of the database. This way, if I am using something
like SQL Backtrack, I can restore a single
object (I hope!) and get on with life.
Peter Thawley: Chuck, so you want single object restore ... how about
Chuck Privitera: Well, I don't know if I'd call it single object restore,
but single object dbrepair(drop, object).
Page patching would be nice too.
I suggested something like Norton Utilities for Sybas but they were not amused.
Peter Thawley: Chuck .. could you explain what you'd like again ...
dbcc dbrepair (drop) ... does that mean you'd like to
bring up a db that's suspect and drop an object ?
Chuck Privitera: That's right.
Chuck Privitera: Basically, when you have tons of data
I would like to recover as fast as possible.
Another option I once requested which was
also shot down was to restore a device, since that is
usually what goes in case of failure.
Don Plaster: Like, restore the segment, and roll-forward the updates to
Chuck Privitera: Exactly!
Peter Thawley: So what you want is object backup and restore ...
Heidi Myers: Anyone have any more details on TEMPDB? What does it
mean for large multi-table selects?
I'm asking about TEMPDB because we've been told that it
will only help our small reporting database, not our 4 GIG
Don Plaster: If you have VLDB's, you may need a VL TEMPDB. Also, you
may want to put it on 'RAM-DISK'.
Peter Thawley: Some apps require large tempdb's, other do not. Any
application that does lots of sorting, aggregates, order
by's, etc. will often need lots of temp space. Technically,
the number of users has a bigger impact. Systems with
1000 users all doing order by's would need as much tempdb
space as a system with 10 users order by'ing a VLDB!
David Zodikoff: To Don's point of 'RAM-Disks' ...
We're looking into them, Solid State Devices,
not only for Tempdb, but possibly Trans Logs
and Stable queues for Rep Svr.
They now have reliable backups to disk and battery packs
or so I've heard.
Bill Andreas: Also anyone can set up a RAM-DISK is memory
under SUNOS no problem.
Don Plaster: First, the Solid State disk vendor is Disk Emulation Systems
(not an ad, just a comment).
Second, there was a talk at the San Jose conference on
TEMPDB. If Heidi can get the notes from there, she may
find some more info there.
Don Plaster: I wanted to suggest everyone update their member entry in
the forum, so we can see what your interests are.
Also, if we could reserve a few minutes at the end to discuss
the benefit of this conference, and the SIG in general, I'd
Now back to your regularly scheduled topic. Thanks.
Heidi Myers: What about joins between 3 mil record tables?
My previous question was about TEMPDB. The responses
were all directed to sort, order by, etc.
Also, I'm wondering about joins across 3 million record
Don Plaster: Are you asking how TEMPDB is used in such a big join?
Heidi Myers: Yes.
Don Plaster: If nobody can address that online, I can refer it to Jeff
Garbus offline. He has some experience with large joins.
Heidi Myers: Ok, who is Jeff Garbus?
Don Plaster: He's one of the other gurus who are helping with the SIG.
He could not be with us today.
Peter Thawley: Yes, joins don't necessarily use TEMPDB. Since we have
a rolling result set, we may never utilize tempdb in
Chuck Privitera: First, Peter, we should talk about my previous request.
What's your internet address?
Second, I requested that the SQL Server
device capacity be increased from 256 to
something more like 1024 or something, since
we have a large number of infrequently used
devices. Again, the panel did not understand
the rationale for this, but the Navigation Server
people told me I shouldn't have let the panel
off the hook so easily.
Peter Thawley: Chuck, increasing the # of devices per db and system
is being looked at. There may be some issues that may
limit us which we haven't uncovered yet. Stay tuned,
and as soon we announce the next release, I'll let you
Bill Andreas: What is the difference between the backup strategy in
4.9 and 10. Not just Backup Server, but queueing updates
done after the start of the dump?
Peter Thawley: The difference in the dump instant between 4.9 and
System 10 is huge.
In 4.9 the "point of the dump" (the dump instant) is
very near the beginning of the actual dump. In System
10, the dump instant is very close to the end. This
means that the majority of the transactions that
occur during the time of the dump actually make it into
The dump in System 10 is done in 3 phases. I believe
these phases are documented in the System 10 documentation,
however, it is these phases that allow us to capture
transactions (particularly non-logged operations)
during the period of the dump.
Bill Andreas: Great, thanks for the update.
German Batz: Is it really necessary to run a DBCC everyday?
Peter Thawley: No, dbcc is important to run, but most people run it once
a week depending on their transaction volumes.
David Zodikoff: *** Conference will now officially turn to SIG business
at 2:50 PDT, 5:50 EDT ; 7 > ***
German Batz: Some people told me that it is important to run a DBCC before
I do a backup.
David Zodikoff: Absolutely, since you don't know if your backup
is valid without it.
Don Plaster: And you probably should run the DBCC AFTER the backup as
well, if you REALLY want to make sure it is a good backup.
But how many hours do you have anyway.
SQL Backtrack says they are working on the ability to run
the DBCC on the backup tape. But, what do you do if you
find a problem?
German Batz: OK, if I run a daily backup then I have to run a daily
David Zodikoff: To Don's point ...
There's what you should do and what you can do.
With VLDB there's only so much time in your batch window
(pre sys 10) to do even backups, no less DBCCs.
Peter Thawley: Just a quick note .... DBCC's should be done before dumps
and after loads. These allow one to guarantee the state
of the database is "good"
Heidi Myers: My question involves "hardware" appropriate for VLDBs.
We are currently running RS/6000 500 series servers to
attempt to support a database that currently is 4GB, and is
expected to grow above 10 in the next 6 months.
We are told that the model 590 we are currently on
provides us with ample power and that the SCSI interface
is sutilable for our future needs.
Any ideas (or amo) on how to approach this issue with our
What do you all use for your platforms?
Don Plaster: I'd suggest running some benchmarks for what your performance
is with your current configuration. Then see if you can
extrapolate from there.
Also, look at your disk speed, not just the SCSI interface.
Is the SCSI SCSI 2 , fast and wide, or what?
David Zodikoff: I thought we were the only ones doing benchmarks.
Anyway, Heidi we are on HP 9000s looking at HPT500s
and have begun moving away from VLDB towards
VLS, Very Large Systems.
We were looking at a 40 Gig DB and were able to break
it up into several 2 - 4 Gig DBs by region with a driver DB
for those who need to see all the data, luckily very few.
Peter Thawley: David, perhaps you could explain your rationale behind that
David Zodikoff: No Problem. I'll try to keep it short.
The prospect of running a National, Hawaii to Maine, DB
with 5 - 7K Users was not only daunting, but operationally
a nightmare. Our system is OLTP and we would need one
heck of an expensive and big machine to satisfy that load
Luckily our business, for the most part is regional, so the
bulk of the databases could be split by region with their
associated processing. We use Replication Server for data
that needs to be shared, once again, luckily very little.
Hope that helped.
Heidi Myers: Thanks for the information, I'll continue this discussion in
the VLDB section some other day. Thanks again.
Peter Thawley: Does anyone know how much System 10 improved your batch
David Zodikoff: Better question might be is anybody on System 10, not
Mark Parsons: I was running S10 for 6 months and had lots of problems
with Solaris and SS1000 ... but the new features were
great (except for Table RI!! ;-)
Bill Andreas: Was?
Mark Parsons: Yeah, I just quit . . . independent consultant now . . anyone
got a job for me?? ;-)
I found that the nightly dbcc and dumps were quicker than
in past with 4.2 or 4.8. Yep, online backup/dumps were
hardly noticeable with S10.
Peter Thawley: Testing has shown that on-line backups are now possible
with System 10!
Benchmarking on v4.9.2 showed a transaction processing
performance degraded by 97% during a dump. In System 10,
transaction processing performance degraded by only
3.5 % WOW. Buy once, buy often ... Go TEAM GO
Enough Sales Tactics
Mark Parsons: Peter . . you or me?? ;-)
David Zodikoff: Peter, I know this is a Sybase forum and all,
but what about the issues that I've heard on the street with
Sys 10. Are most/many cleared up in newest release
have you been hearing fewer complaints?
You don't have to answer if it puts you in
a funny situation, I understand GA really this time.
Peter Thawley: David, what issues / complaints are you referring to?
David Zodikoff: Reliability mostly, but also pains in recoding
around some of the optimizer changes.
Peter Thawley: David, I think all the optimizer changes were well
documented. I guess everyone wanted ANSI until they found
out what it meant!
David Zodikoff: True and Aint that the truth. Also ...
It wouldn't hurt so bad if we coded these apps, but some of
the stuff we have to maintain, makes it painful to convert,
like 500 items in an IN list.
Peter Thawley: I suppose this is why good project managers who can plan an
upgrade make soooo much money!
Mark Parsons: One of my major complaints was the whole table RI 'thing';
limitations on the number of RI constraints(although you
could create all you wanted, code would bomb if constraints
on a single table > 16); buggy compilations with sp's that
accessed tables that had RI constraints; RI constraints that
don't function properly across database lines (could be a
problem with VLDB users who break data up into separate
databases but require the RI constraints); and, of course,
the goat rope you have to go through to drop and reload
tables that have RI constraints attached to them (you
have to 'peel back' all RI constraints, drop the table,
then re-attach the RI constraints that you 'peeled back'.
Um, that's just a couple complaints with the RI 'stuff' . . .
not so much a question as an answer to Peter's question about
Peter Thawley: Don't worry ... I don't take these personally ... where's
that martini I ordered!
Mark Parsons: Referential Integrity.
Peter Thawley: There's an EBF of 10.0.1 which eliminates the 16 table limit.
I don't recall the # or date but it's there.
Mark Parsons: Ok . . . sounds good.
Don Plaster: What are the VLDB performance issues, pros, cons for using
declarative RI, rules, etc, in 10.0 synatx, rather than the
4.x way of using triggers?
Mark Parsons: Well, just one follow up . . hey Peter, you didn't address
the other RI issues!! ;-) you just threw me a bone!! ;-)
Peter Thawley: No flies on you huh.
Obviously, you need to tune your RI to ensure adequate
indexes etc to avoid unnecessary I/O. Triggers generally
offer more flexibility in Integrity Coding but either way,
one needs to do standard tuning for performance.
Mark Parsons: Don't forget that with RI that cascaded deletes are much
trickier. Table RI is also much harder to maintain in a
development environment . . I'm done.