we've just moved an 11.1 64 bit database to 11.4.
The clients are 11.4 , 32 bit.
Prior to the update, performance was good. reports ran well and with no problems.
However, since the move to 11.4, any client that runs an odbc query causes huge performance problems (100% cpu), abl clients getting disconnected from the database etc
has anyone else seen problems like this ? were some parameters changed between 11.1 and 11.4 (I copied the 11.1 properties files and moved them back after installing 11.4)
thanks ...
addendum : just installed the 64 bit odbc drivers (knowledgebase.progress.com/.../000052803) however, all we get now is an "odbc general error" when running the report.
Is it the same issue if you query a small table?
With the new drivers are you seeing the connecting in the db.lg file?
If yes, can you enable SQL logging on the server side?
Can you connect via jdbc with sqlexp or Squrriel?
I think that the problem is isolation level. user sqlrep-5 (for example) has a number of SHR locks in the locking table, even though the default isolation level of the odbc connection is set to READ UNCOMMITTED
we have multiple queries running, and they seem to get into a deadly embrace. if I disconnect one of the users, then the other report completes ok
can a query store the isolation level in it somewhere ? We are using excel 2013 with msquery
whe
when a sql user logs in and runs the query, it looks like it sets a SHR lock on a certain record, which goes when the report is finished.
now, the table in question is _sec-authentication-domain
is that right ?
Are you using an existing ODBC DSN? Did you remember to change the isolation level when you created the new one with the 64 bit drivers? (Yes yes I know you probably did but this is for the benefit of the reader).
Are you writing your own queries? If yes you can add "WITH NOLOCK" to the query.
From the documentation:
The NOLOCK locking hint ensures records are not locked during the execution of a
SELECT statement when the transaction isolation level is set to READ COMMITTED.
Yes that looks right. -207 is _sec-authentication-domain. Regardless of whether or not this is good, this should not block anyone as no one should be trying to get an EXCL on _sec-authentication-domain.
Are you seeing "Blocked Clients" in promon or are you guessing that there might be a deadly embrace?
Since you upgraded and moved to a new version, has update statistics been performed against the "new/migrated" db ?
Libor,
What makes you think the DB is new or migrated? He just upgraded the DLC from 11.1.
still probably a good idea to update the statistics. with the table partitioning work, there have been sql query processor changes.
The suggestion from both Libor and Gus is good.
After 11.1, there were changes to sql statistics to make the optimizer work better with non-uniform data distributions, and with situations with many duplicate key values (uniform or non-uniform). So the statistics have a slightly different content now. The old content should be OK, but the old statistics won't get the best optimizer performance.
So definitely do UPDATE STATISTICS.
And do UPDATE INDEX STATISTICS - which gives very useful statistics for joining and searching.
Note that the base UPDATE STATISTICS does not create index stats.
For detailed syntax, the sql reference manual is a good resource (it is avaliable onlline).
hope this helps, ....steve pittman [sql development software architect]
@Rob - I meant 'version has changed' - sorry for not expressing myself clearly.
Is it safe to say then, as a general rule, that we should run update statistics after installing a new version?
Reply by Rob FitzpatrickIs it safe to say then, as a general rule, that we should run update statistics after installing a new version?
Stop receiving emails on this subject.Flag this post as spam/abuse.
-- Tom Bascom 603 396 4886 tom@greenfieldtech.com
In short, it never hurts to run it, so running it on a version change is a bit changing the battery in the smoke detector when the time changes.
how long would this process be expected to take though ?
the users are reporting that when anyone runs a reasonanbly large report, normal abl clients are losing the connection to the DB
Reply by jmlshow long would this process be expected to take though ?
Stop receiving emails on this subject.Flag this post as spam/abuse.
-- Tom Bascom 603 396 4886 tom@greenfieldtech.com
oh, ok. thanks.
Are you seeing any errors in any of the logs?
I can see statistics causing performance problems, but not kicking other users out of the system.
As far as updating statistics... if you have sample SQL that is causing issues make sure to update those tables first before moving to the others. Either that helps those queries or it doesn't. That would at least give you a hint that your are on the right path or there is another issue (bugs or isolation level).
Unless of course your DB is small enough to do them in all very quickly. It would take all day here :(
I'm getting nothing but these in the logs :
Usernum 479 terminated abnormally.
Logout usernum 479, userid AMcarthur, on TS3.
Usernum 505 terminated abnormally.
Logout usernum 505, userid VManning, on TS7.
this goes on for several users on different terminal servers
What worries me is that this has only started since we upgraded to 11.4 on the server. Previously, all the clients were 11.4 , talking to an 11.1 DB.
What about protrace or core files? Probably a good idea to open a support call if you haven't already.
mmm. ok. So backup takes around 3 hours. This update stats is still going, 12 hours later ... :(
@julian: has it finished yet? did redoing the statistics solve the problem?
I never got round to redoing the stats - I actually reduced the -B from around 16gb (4000000) to 1gb (250000) and the performance problems went away ...
I cannot reproduce the problems on any other database, even one that was dumped and loaded.