ASE 15 issue with locks not releasing

[kdb]

What should I be looking for?

We recently upgraded from ASE 12.5.3 to ASE 15 We are now running
15.0.3 ESD2 on Solaris 10 64 bit, Sun E25K server hardware. The
upgrade with smoothly on the following day when most of the users
(Approx 2000) were using the databases the users were complaining that
the application was running extremely slow. While troubleshooting
this issue we notice that there was a lot of blocking and locks. Our
sy_mon, sp_locks show that our CPU was at 100 % and lots of blocking.
We were running 6 engines and increase it to (8) this did not help had
all. We got reports from users that the they were getting the
following message "SQL Server has run out of LOCKS. Re-run your
command when there are fewer active users, or contact a user with
System Administrator (SA) role to reconfigure SQL Server with more
LOCKS." We have increase the locks from 48000 to 300000 and we still
have the same problem. We had basically double our resources on
engines, locks, cache and nothing seems to work. We have brought our
self some time to troubleshoot the issue and switching to
"compatibility mode" which is running in 12.5 on ASE 15 software.
It's amazing how when switching to "compatibility mode" the issue with
the locking and blocking cease. We did not find any issues with the
store procedures. Any help or direction will certainly be
appreciated. We have run out of ideas. SYBASE Engineers hasn't been
able to help us track down the problems.

[kdb]

I forgot to mention that we did not run updatestats after the upgrade
this was done over the weekend. We were instructed to make sure that
we deleted the old stats.

[Sherlock, Kevin [TeamSybase]]

Are you saying that you deleted stats after the upgrade, but didn't run
update stats?

"kdb" <kenw...@gmail.com> wrote in message
news:86c47bdc-dc56-48c9...@r5g2000yqb.googlegroups.com...

[kdb]

On Nov 9, 11:16 am, "Sherlock, Kevin [TeamSybase]"

<kevin.sherl...@teamsybase.com> wrote:
> Are you saying that you deleted stats after the upgrade, but didn't run
> update stats?
>

> "kdb" <kenwo...@gmail.com> wrote in message

That is correct we deleted the updatestats a week after the after the
upgrade.

[Sherlock, Kevin [TeamSybase]]

So, after your upgrade was done, you did:

delete statistics <tablename>

for every table, and then did not run any

"update [index] statistics <tablename>"

afterwards? If so, that would be "a bad thing".

"kdb" <kenw...@gmail.com> wrote in message

news:9503fa2e-00b9-4979...@p8g2000yqb.googlegroups.com...

[kdb]

On Nov 9, 6:21 pm, "Sherlock, Kevin [TeamSybase]"

<kevin.sherl...@teamsybase.com> wrote:
> So, after your upgrade was done, you did:
>
> delete statistics <tablename>
>
> for every table, and then did not run any
>
> "update [index] statistics <tablename>"
>
> afterwards?  If so, that would be "a bad thing".
>

Hi Kevin,

To answer your question correctly. After we migrated from ASE 12.5.3
to ASE 15.0.3 everything went well until the next day after the
upgrade. When we had approximately 2000 users using the Application
Database everyone was complaining about the slowness of the
application. After some troubleshooting we notice that there was lots
of blocking and locking on the database was causing us to run out of
locks. (We increase the locks for 40000 to 300000 and still the locks
were not releasing). Now to ask your question about the maintenance.
I was saying that we did not do a reorg or run updatestats until a
week later. (What we did - we ran updatestats on all the tables in the
database (409 tables) We ran a script that deleted the stats one by
one we deleted the old status then updatestats each of the tables for
all the tables. For example: delete table1, update stats for table1,
delete table 2, update stats table2.....) The reason that we ran
updatestats was because we have a very small maintenance window and
because these tables are very large 50 to 100 million rows, for the
table 10 large tables. Our maintenance plan is to spread the large
tables out thru the week to perform the reorgs on the tables during
several each day. Yes, we do the rest of the maintenance as well
DBCC, Checkstorage, Checkalloc, etc. hope that I'm not confusing you.
This is my first post, HTH

[mpeppler@peppler.org [Team Sybase]]

One reason you may see more locks held for longer time is if your
update or delete statements have bad query plans. If they take longer
to execute, they will hold the locks longer.
We experienced similar things yesterday after one of our major
dataservers was upgraded to 15.0.3 ESD 2 over the week-end.
Using the monitoring tools we have set up we were able to quickly find
the procs/triggers that were behaving badly, and temporarily fix them
by using the compatibility mode in those procs/triggers.

So I think you need to focus on the performance of your queries/
updates/etc. as this will be the main reason why locks are held for
longer time.

Michael

[John McVicker]

One thing I've done with a few customers is to recommend that they start
with:
sp_configure 'optimization goal', 0, 'allrows_oltp'

For reporting or other types of systems, you can either go allrows_mix,
allrows_dss at the session level or thru an application login trigger.

Running allrows_mix as the default has hurt at times and you may want to try
allrows_oltp to act more like 12.5 for the time being and then setup a QA
system to properly test the new optimization mode before going into
production.

Tread softly into the new release by turning on new features slowly but
surely.

- John

[kdb]

On Nov 10, 2:04 am, "mpepp...@peppler.org [Team Sybase]"

<michael.pepp...@gmail.com> wrote:
> On Nov 10, 5:14 am, kdb <kenwo...@gmail.com> wrote:
>
>
>
> > On Nov 9, 6:21 pm, "Sherlock, Kevin [TeamSybase]"
>
> > <kevin.sherl...@teamsybase.com> wrote:

> > > So, after yourupgradewas done, you did:

>
> > > delete statistics <tablename>
>
> > > for every table, and then did not run any
>
> > > "update [index] statistics <tablename>"
>
> > > afterwards?  If so, that would be "a bad thing".
>
> > > "kdb" <kenwo...@gmail.com> wrote in message
>
> > >news:9503fa2e-00b9-4979...@p8g2000yqb.googlegroups.com...
> > > On Nov 9, 11:16 am, "Sherlock, Kevin [TeamSybase]"
>
> > > <kevin.sherl...@teamsybase.com> wrote:

> > > > Are you saying that you deleted stats after theupgrade, but didn't run

> > > > update stats?
>
> > > > "kdb" <kenwo...@gmail.com> wrote in message
>
> > > >news:86c47bdc-dc56-48c9...@r5g2000yqb.googlegroups.com...
> > > > On Nov 8, 7:28 pm, kdb <kenwo...@gmail.com> wrote:
>
> > > > > What should I be looking for?
>

> > > > > We recently upgraded fromASE12.5.3 toASE15We are now running

> > > > > 15.0.3 ESD2 on Solaris 10 64 bit, Sun E25K server hardware. The

> > > > >upgradewith smoothly on the following day when most of the users

> > toASE15.0.3 everything went well until the next day after the

Everyone,

By delete the old stats and updating the updatestatics the problem
with releasing the locks cease. However, the CPU usage is very high,
We originally had 6 engines and added two more engines for a total of
8. The CPU usage is hovering around 85 to 95 percent on all CPU's.
We are currently reviewing our queries.

[Mark A. Parsons]

re: heavy cpu usage ...

------------------

Another cpu hog is large volumes of dynamic SQL (as opposed to stored procs and/or prepared statements).

The ASE 15 optimizer has to do more work (ie, more cpu cycles) to compile SQL queries.

The larger the volume of queries that have to be compiled ... the more work the compiler has to do ... the more cpu
cycles you'll see being used by the dataserver.

------------------

Another cpu hog (at the OS and dataserver level) is a high volume of (dis)connects with the dataserver.

There's extra overhead (eg, cpu cycles) for the OS and dataserver to (de)establish new connections.

sp_sysmon has a one-line item that shows new user connections per second.

While a surge in connections per second is expected upon starting up an application, over time the steady-state
(dis)connect rate is typically less than 1 or 2 (dis)connects per second. [NOTE: What constitutes 'normal'
(dis)connect volume is dependent on your environment.]

The level of degradation usually depends on the OS and underlying hardware. I've seen some dataservers (on slower
hardware) noticeably degraded with 10-15 (dis)connects per second. Other dataservers (on faster hardware) start showing
noticeable performance degradations with 70+ (dis)connects per second.

'course, heavy (dis)connects should show up as a performance degradation in ASE 12.x, too.

-------------------

Another cpu hog would be a bug in the dataserver ... rare, but not unheard of.

Bouncing the dataserver may eliminate the heavy cpu usage issue, but chances are the issue will come up again in the future.

Under this scenario the best option is to contact Sybase TechSupport to see if there's a known issue with your version
of ASE.

[ozgurkara]

you might want to check these dynamic configuration parameters:

sp_configure 'enable literal autoparam' -- > ,1
sp_configure 'statement cache size' -- > 75000 -- (let's say)

this might help you since you will be using statement cache
effectively.

good luck :)

[kdb]

On Nov 10, 2:04 am, "mpepp...@peppler.org [Team Sybase]"
<michael.pepp...@gmail.com> wrote:

Hi Mike,
After running updatestats on all table columns and doing a reorg the
next day, things seem to be running fine for about 2 hours and then
high CPU reappeared. Then we ran QP Tune again and we discovered there
were lots of queries missing. This is very baffling to us and we do
not know how or why this is happening. It's very frustrating to me
but I'm learning a lot because of it. The database that we ran the
updatestats on has over 400 tables. Again when we turn on
compatibility mode the CPU drops tremendously. So frustrating.
Running in compatibility mode has certainly be a life saver until we
can figure this thing out. I know that its something simple but just
can't figure it out. We certainly appreciate the help provided on
this forum.

[John McVicker]

You may want to get your MDA tables analyzed by Sybase TS or a consultant
(like Mark P.) who can read through the situation and maybe find something.

Keys to solving your performance problem are - monSysStatement finding those
statements in the system that are consuming large amounts of LogicalReads
(ie. the high CPU usage).

Were you using optimization goal = 'allrow_mix' ? If you change to
allrows_oltp - you still may have to disable merge joins - sp_configure
'allow merge joins', 0. The docs seem to say merge joins are disabled under
allrows_oltp - but they still seem to be on. Worked with a customer the
other day and did both of those changes and they also added the
compatibility mode on for one of their procedures. Mainly, that customer
needed a few indexes reworked in ASE 15 to be more appropriate (would have
helped ASE 12.5 as well).

- John McVicker
Inventa Technologies, Inc.

[kdb]

Thanks, ALL I have a update our statement cache was not enabled and
after we enabled it performance greatly improve. I do not believe
that we are totally out of the woods because we will need to enable
encryption which uses a lot of CPU and statement cache will need to be
disabled when we decide to move to encryption.

[kdb]

On Nov 20, 1:18 pm, "John McVicker" <jmcvic...@inventa.com> wrote:

Thanks John I will certainly do what you are suggesting.

[Rob V [ Sybase ]]

As for statistics in ASE 15, it is recommended to run "update index
statistics" rather than "update statistics". Having such additional
statistics is pretty important in ASE 15. NB: For large tables, you may need
to use the clause "with sampling=1" if it takes too long.

HTH,

Rob V.
-----------------------------------------------------------------
Rob Verschoor

Certified Sybase Professional DBA for ASE 15.0/12.5/12.0/11.5/11.0
and Replication Server 15.0.1/12.5 // TeamSybase

Author of Sybase books (order online at www.sypron.nl/shop):
"Tips, Tricks & Recipes for Sybase ASE" (ASE 15 edition)
"The Complete Sybase ASE Quick Reference Guide"
"The Complete Sybase Replication Server Quick Reference Guide"

mailto:r...@YOUR.SPAM.sypron.nl.NOT.FOR.ME
http://www.sypron.nl
Sypron B.V., Amersfoort, The Netherlands
Chamber of Commerce 27138666
-----------------------------------------------------------------

"kdb" <kenw...@gmail.com> wrote in message

news:b8b4e3bb-296f-4e94...@l13g2000yqb.googlegroups.com...

[John McVicker]

Sampling is good for large tables for another reason. Tempdb space is taken
up by the update index statistics (or update all statistics) to maintain
work tables for each column in the index(es) being done. For big tables,
the tempdb that the SPID is assigned to can be filled up by the update index
statistics. So, using sampling helps lower the sizes of these work tables.
Can be a surprise if you fill up your production tempdb and don't understand
why other than "but, I was just doing update index statistics and all the
users blocked on a full tempdb - what's up with that?"

- John

"Rob V [ Sybase ]" <ro...@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY> wrote in
message news:4b0abdce@forums-1-dub...

[Rob V [ Sybase ]]

Indeed -- and not just for tempdb, but also you procedure cache will be used
by the sorting operations. For large tables, you may run out of iit when
doing 'update index statistics' with a 701 error. Sampling will help also
here.

HTH,

Rob V.

"John McVicker" <jmcv...@inventa.com> wrote in message
news:4b0d3ee2$1@forums-1-dub...

[kdb]

On Nov 25, 10:02 am, "Rob V [ Sybase ]"

<r...@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY> wrote:
> Indeed -- and not just for tempdb, but also you procedure cache will be used
> by the sorting operations. For large tables, you may run out of iit when
> doing 'update index statistics' with a 701 error. Sampling will help also
> here.
>
> HTH,
>
> Rob V.
>

> "John McVicker" <jmcvic...@inventa.com> wrote in message

>
> news:4b0d3ee2$1@forums-1-dub...
>
>
>
> > Sampling is good for large tables for another reason.  Tempdb space is
> > taken up by the update index statistics (or update all statistics) to
> > maintain work tables for each column in the index(es) being done.  For big
> > tables, the tempdb that the SPID is assigned to can be filled up by the
> > update index statistics.  So, using sampling helps lower the sizes of
> > these work tables. Can be a surprise if you fill up your production tempdb
> > and don't understand why other than "but, I was just doing update index
> > statistics and all the users blocked on a full tempdb - what's up with
> > that?"
>
> > - John
>

> > "Rob V [ Sybase ]" <r...@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY> wrote in
> > messagenews:4b0abdce@forums-1-dub...

> >> As for statistics in ASE 15, it is recommended to run "update index
> >> statistics" rather than "update statistics". Having such additional
> >> statistics is pretty important in ASE 15. NB: For large tables, you may
> >> need to use the clause "with sampling=1" if it takes too long.
>
> >> HTH,
>

> >> Rob V.- Hide quoted text -
>
> - Show quoted text -

Everyone,

We have compared some of our old (ASE 12.5) sysmon reports against our
new ASE 15 sysmon reports and have noticed that the WaitTime counter
on our ASE 12.5 sysmon reports are 0 and on our ASE 15 sysmon reports
their is lots of WaitTime on most of our objects. Question: How do we
detemine what is it waiting on? We are ONLY able to operation in
compatibility mode until this issue is resolved. We have try to
implement several suggestions in this threads. We are planning on
dropping and re-creating all of our INDEXES which will take a very
long time. We have bind our 6 large object to there own memory,
switch off merge joins, reorged all tables, increase cache for
securtiy, procedure, default. We feel that its a configuration
setting that is killing us. We certainly appreciate everyones help on
this forum. its been a great help. As always thanks.

[kdb]

A correction...The reports are from MDA tables..especially
dbo.mdaSysStatement.

[shanaka...@gmail.com]

Hi,

Just verify the processes who use this locks by

sp_configure 'number of locks'

go

Then increase the value , value depends onhow many objects are accessed the same time and what locking shema your objects do have at a given particular time.

Normally new value should set 10-25 % higher than the current value.

Command :-
sp_configure 'number of locks', 10000

go

Shanaka.