Skip to Content.
Sympa Menu

freetds - Re: Using stored procs (was Re: SQL Server Emulation)

freetds AT lists.ibiblio.org

Subject: FreeTDS Development Group

List archive

Chronological Thread  
  • From: Ken Seymour <kenaseymour AT yahoo.com>
  • To: TDS Development Group <freetds AT franklin.oit.unc.edu>
  • Subject: Re: Using stored procs (was Re: SQL Server Emulation)
  • Date: Wed, 20 Jun 2001 09:29:24 -0700 (PDT)



--- Michael Peppler <mpeppler AT peppler.org> wrote:
> Index statistics can *really* screw you up if your
> data changes
> significantly.
>
> We had that happen yesterday - the system that I
> work on (a large
> website) has been purchased by another company, but
> they want us to
> remove the old user data and only leave the data for
> the users from
> the new company.
>
> So we're running a script to delete user data, a
> user at a time.
>
> The script is chugging along, and suddenly the
> server (a Sun E4500
> with 10 Sybase engines running) is running at *full*
> load - why?
> because some of the queries in some procs have
> started to table scan
> due to index skew...
>
> So most of the day yesterday was spent doing update
> statistics
> (actually running the REORG command), and the load
> is now down to
> something more manageable.
>
> However - using procs or ad-hoc queries would not
> have made any
> difference here - the optimizer would have made the
> same (incorrect)
> choice because of the skewed statistics.
>

We have an application that does a lot of inserts.
We found that we have to do the following
automatically
from a job once a week:

1) Rebuild the indexes for the tables in question with
dbcc dbreindex ('tablename').

2) Update statistics with exec sp_updatestats.

3) drop and re-create the stored procedures that use
the tables.

Last time I checked, we could go about 10 days before
the indexes got un-balanced and the stored procs
stopped using them.

We are running MS SQL Server 7 on Windows NT4.
The biggest table is about 25 million rows which
represents 6 months worth of data.

So I don't know if this pertains to the original
poster's question, but this is just a data point
on the subject of index problems.

> Michael
>
>
> Lowden, James K writes:
> > Hi Norman,
> >
> > Unless your query winds up using different
> indexes depending on the
> > parameters it's provided, there's no advantage to
> using "with recompile".
> >
> > Indexes are updated in real time, with the data,
> but index statistics are
> > static. If the distribution of your keys is
> highly dynamic but your
> > prefered index is consistent, using "with
> recompile" could easily cause the
> > query optimizer to misconstrue the situation and
> not employ your index.
> >
> > But you probably already know that.
> >
> > --jkl
> >
> > -----Original Message-----
> > From: Norman Palardy [mailto:norm AT kirais.com]
> > Subject: [freetds] Re: SQL Server Emulation
> >
> > We have very large very active tables (something
> like 20 million rows in one
> > of them) and stored procs are not great in this
> situation because of how
> > they cache query plans. This is on Sybase 11.9.x
> and Sybase 12.x
> >
> > The only way to get around this is to create the
> proc with recompile
> > option...
> >
> > ---
> > You are currently subscribed to freetds as:
> [mpeppler AT peppler.org]
> > To unsubscribe, forward this message to
> $subst('Email.Unsub')
>
> --
> Michael Peppler - Data Migrations Inc. -
> mpeppler AT peppler.org
> http://www.mbay.net/~mpeppler - mpeppler AT mbay.net
> International Sybase User Group -
> http://www.isug.com
> *Looking for new project to tackle starting 8/1/01*
>
> ---

> You are currently subscribed to freetds as:
> [kenaseymour AT yahoo.com]
> To unsubscribe, forward this message to
$subst('Email.Unsub')


__________________________________________________
Do You Yahoo!?
Get personalized email addresses from Yahoo! Mail
http://personal.mail.yahoo.com/




Archive powered by MHonArc 2.6.24.

Top of Page