Random and Intermittent query performance issues when running on Windows Server 2008 R2 64-Bit

By the way, this is not related to the recent posts about Server 2008 R2 / Windows 7 Service Pack 1 as none of my clients nor I have yet to install Service Pack 1.  

I'd look at two things:

1. What task manager shows is happening as far as CPU utilization, and how much memory Access is taking.

2. Maxbuffers.   By default, Maxbuffers is set to 0, which gives you a cache of 64 MB of memory.  JET has an issue with memory claenup when the pages in use grows beyond the working set limit on a multi-core processor.

  It uses sleep cycles waiting for memory to get reduced.  But because other processors may be bound, you can end up waiting a very long time.

  You can use DBEngine.SetOption dbMaxBufferSize, xxxx

  in the DB to check this out.  'xxxx' is the number of pages.  Max is 65536.   The default setting gives you a max of 3456 pages.  If it's a high memory machine, then I would set it to the max and see if it makes a difference or not.

JimD.


 

Jim
CPU utilization is under 15 percent at the peak - memory stays under 30 percent.  I don't think either one is an issue.  

I am a little confused about your second recommendation.  Do I change the number in
A - an ini file?
B - within the app or via the connection string?
C - somewhere else?

Thank you!

Jim,

Your solution to my problem has worked flawlessly for more than 2 dozen of my clients.  Thank you again for your assistance.  However, I've got one new client who has a great big new server and my application is exhibiting the same behavior but your solution has not had the same result.  

Here are the system specs:
Windows Server 2008 R2 64 Bit. The processor is a Xeon X5647 running at 2.93 GHz.  It has 16 GB of RAM and it has a RAID 5 configuration.  The system has Office 2010 Professional Plus but Access 2010 was not installed.  My app is based on the Access 2003 Runtime.  

If I put in a MaxBufferSize value greater than around 25,000 I get the following error when I start up my application: Error Number: 3035 - System resource exceeded.  

However, with only a MaxBufferSize of 25,000 the query performance is still very intermittently bad.  

Any thoughts?  HELP!  

Thank you!

Charlie

Charlie,

Glad to hear you've had some sucess.   Just to confirm something; this is an app that is running on the server itself correct?  I'm pretty sure the answer to that is yes, but after re-reading the thread, that's not 100% clear in my mind.

  From the message, it's obvious the server is running out of some resource and most likely it's memory.  Without knowing the server in detail, there are two things you can try to pin-point that.  THe first is not really a solution, but would confirm the problem.

1. Open the DB exclusive and try the operation - Locking is greatly reduced when a DB is openeded exclusive.

2. Change the queires UseTransAction property to no - This allows JET to flush the transaction as soon as it hits the lock limit rahter then trying to do the enitre query as a single operation.  

  Let me know if those work for you or not.  And it's getting close to the point where you may want to call Microsoft.  They may have some hotfixes ready for some of the problems that have been coming up.  One such is:

A computer that is running Windows 7 or Windows Server 2008 R2 takes four minutes to open a Microsoft Office 2003 document from a network share
http://support.microsoft.com/default.aspx?scid=kb;EN-US;982860

Jim.

Jim,

Yes the app is running on the server itself.  One thing that I found in a post on how to best deal with the Error Number: 3035 - System resource exceeded - was to increase the MaxLocksPerFile to some huge number like 1,000,000.  How do you feel about this?  Is the number too big, should it be bigger or smaller?  I've implemented this in a new build and have seen a slight improvement.  

If I want to use Transactions for a few queries then I should leave that property set to Yes - yes?  Also, would I need to change this property for ALL of my saved queries or just the most commonly used ones?  I have a lot of queries in my application.  What do you recommend?

Thanks for the hotfix suggestion - you recommend calling Microsoft - whom should I call?  

With immense gratitude,

Charlie

<<One thing that I found in a post on how to best deal with the Error Number: 3035 - System resource exceeded - was to increase the MaxLocksPerFile to some huge number like 1,000,000.  How do you feel about this?  Is the number too big, should it be bigger or smaller?  I've implemented this in a new build and have seen a slight improvement.  >>

 MaxLocksPerFile is an upper limit on the number of locks that JET is allowed to take.  If you have very large queries, you can easily bump into the default limit of 9500.   The setting originally was added because of Novell servers.  They had a limit in the OS of 10,000 to prevent run away apps from consuming all resources.

 You can set it to 1,000,000, and while that will mostly likely do no harm on today’s machines, you probably should be looking towards re-structuring operations and processes such that you don't generate so many locks if not from a viewpoint of just getting it to work, but from one of performance.

<<If I want to use Transactions for a few queries then I should leave that property set to Yes - yes?  Also, would I need to change this property for ALL of my saved queries or just the most commonly used ones?  I have a lot of queries in my application.  What do you recommend?>>

 Yes, that's correct.  As for all the queries, it depends on what you’re doing.  Ask yourself the question "What if it dies in the middle, can I repeat the operation?"  If the answer to that is yes, then setting the property to no would be fine.  It will give you the least amount of resources used and large queries will succeed where they might fail otherwise.  You will also see a boost in performance as JET will avoid writing a lot of temp data.

  When you set the property to yes, you’re telling JET that the query must complete from start to finish as a single unit.   So it's going to take a lock out on every page it touches (including index pages).  It may also need to write a substantial amount of data to temp files.

<<Thanks for the hotfix suggestion - you recommend calling Microsoft - whom should I call?  >>

  Start here:

http://support.microsoft.com/select/Default.aspx?target=assistance

  Unfortunately, it's most likely going to take a bit of time and persistence to get to the level where you'll get some action.  I believe Microsoft will want to charge as well until they determine that it's a bug, so be ready for that.

<<With immense gratitude,>>

Glad to be of help!
Jim.

Jim,

So - I scheduled time to work on my queries and I'm amazed that I cannot for the life of me find where to change this property.  I looked online and every article I read stated that this is a Access2003 property and that it could be changed from the Query Properties dialog.  Please see the attached screen shot and please tell me what I'm doing wrong here.  I can't seem to find the property ANYWHERE!  There were some vague references to the ability to set this property programmatically but I don't know how to do that either.  HELP!

Thanks,

Charlie

It's only on "Action" queries; one's that add/delete/change.  You won't find it on regular select queries.

Jim.

Jim,

I wrote the following two routines - one to "add" the UseTransaction property to the query and the other to set a subset of my queries to say "UseTransaction" = False.  I ran them and they seemed to work.  Of course, I could see no noticeable difference in performance - which made me doubt that I had actually acommplished something.  Is there any possibility that this code is useful or should I ignore the whole "UseTransaction" false idea because they don't apply to "Select" queries?  

Public Function SetUseTransaction(strQuery As String, strType As String)
If Len(Nz(strType)) = 0 Then
    GoTo Exit_SetUseTransaction
End If
On Error GoTo Err_SetUseTransaction
Dim db As Object
Dim prop As Object
Const ConPropNotFound As Integer = 3270
Set db = CurrentDb()
db.QueryDefs(strQuery).Properties!UseTransaction = strType
GoTo Exit_SetUseTransaction
Err_SetUseTransaction:
If Err = ConPropNotFound Then
    Set prop = db.QueryDefs(strQuery).CreateProperty("UseTransaction")
    prop.Type = dbText
    prop.Value = strType
    db.QueryDefs(strQuery).Properties.Append prop
    Resume Next
Else
    MsgBox "Function SetUseTransaction did not complete successfully - " & strQuery
     GoTo Exit_SetUseTransaction
End If
Exit_SetUseTransaction:
    'db = Nothing
    'prop = Nothing
    Exit Function
End Function
Public Function SetQueryProperties()
On Error Resume Next
SetUseTransaction "qryPatronList", "False"
SetUseTransaction "qryPatronListCombined", "False"
SetUseTransaction "qryPrimaryGroups", "False"
SetUseTransaction "qryPatronListAll", "False"
SetUseTransaction "qryPeopleReports", "False"
SetUseTransaction "qryPreferredAddresses", "False"
SetUseTransaction "qryCountries", "False"
SetUseTransaction "qryAddressType", "False"
SetUseTransaction "qryPrimaryEMailAddresses", "False"
SetUseTransaction "qryPrimaryPhoneNumbers", "False"
SetUseTransaction "qryCodesCommMethod", "False"
SetUseTransaction "qryPrefix", "False"
SetUseTransaction "qrySuffix", "False"
SetUseTransaction "qryAttributeCrossTab", "False"
SetUseTransaction "qryAttributeBase", "False"
SetUseTransaction "qryRelationships", "False"
SetUseTransaction "qryTransactionsYears", "False"
SetUseTransaction "qryTransactionsYearsBase", "False"
SetUseTransaction "qryTransactionsYearsBase2", "False"
SetUseTransaction "qryPrimaryEmployer", "False"
SetUseTransaction "qryBusinessTypeList", "False"
SetUseTransaction "qryPrimaryNameSource", "False"
SetUseTransaction "qryProductionCount", "False"
SetUseTransaction "qryProductionCountSelected", "False"
SetUseTransaction "qryMembershipTypes", "False"
End Function

<<Is there any possibility that this code is useful or should I ignore the whole "UseTransaction" false idea because they don't apply to "Select" queries? >>

  Only applies to action queries.  Code is usefull for that.

Jim.

Jim,

You wrote to me above the following sentence: "you probably should be looking towards re-structuring operations and processes such that you don't generate so many locks if not from a viewpoint of just getting it to work, but from one of performance."

I am revisiting this thread to try to glean any more nuggets of knowledge from you and I came back across this comment: "don't generate so many locks" - how do I go about doing that?  Exactly what types of operations and processes generate "locks"?  Would reducing indexes reduce the number of locks?  Is it as simple as simplifying queries?  Attaching fewer tables?  As you can see - I can guess at a number of options but I'm not quite sure what you meant by that comment.  Can you please take a moment and clarify that for me?  

I've seen other threads that you were involved in that have posed questions similar to my question - with the same fundamental issues of running Access 2003 under Server 2008 R2 64 Bit - such as:
https://www.experts-exchange.com/questions/25091616/MS-Access-runs-slow-as-a-2008-Terminal-Server-RemoteApp.html?sfQueryTermInfo=1+10+30+64+access+bit+ms+slow+under

This appears to be a major problem.  I would like to explore as many code and/or structural changes as possible before calling Microsoft.  From the post above, it appears that Microsoft is in denial about this issue.  Perhaps they've changed their mind since that post.  I'm at my wits end on this performance issue.  My system runs amazingly well on Windows 7, Vista and XP - even 64 bit but not Windows Server R2 64 Bit.  I'm completely at a loss.  There has to be a solution to this problem!!!

Thank you for taking a second to revisit my problems.  

Charlie

<<I am revisiting this thread to try to glean any more nuggets of knowledge from you and I came back across this comment: "don't generate so many locks" - how do I go about doing that? >>

  It means break an operation down into smaller parts if you can.  For example; you have an update query that joins several table and has a couple of sub tables to pull in totals, which does an update on several fields.

  Can that be broken down?  ie. Run the sub queries first and save the totals in a temp table.  Then use the temp table in the finial query.

<< Exactly what types of operations and processes generate "locks"? >>

  Anytime you have records selected, your holding a read lock.  Any "action" query; Update, Delete, or Append is holding locks, both on the records and any indexes involved.

<<Would reducing indexes reduce the number of locks?  Is it as simple as simplifying queries?  Attaching fewer tables?>>

  Yes to all of that.  In short, do not use more then you need and use it as little and as quickly as possible.

  For example, selecting all fields from a table in a query (*).  While that doesn't directly affect locking, it does indirectly.  Pulling fields you don't need takes more time and that means your holding read locks longer then you need to.  Your also consuming more CPU and memory.

 Don't use more then you have to and use it for only as long as you need to.  And for action queries, if you can get away with setting Use Transactions to No, do so.

  A for what it's worth, I do believe there are issues with Windows 2008 R2.  However I don't think Microsoft is in denial about it.  It's simply a matter of it taking a long time to confirm a problem, figure out what causes it, come up with a fix for it that won't break anything else, document it, and then distribute it.

  That easily can add up to months.

Jim.