FreeTDS Development Group

[Ken Seymour <kenaseymour AT yahoo.com>]

--- 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/