Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2004
    Posts
    11

    Unanswered: Change SQL Server query optimizer locking

    Hi,

    Our app has been distributed on more then 300 different sites.
    On one of the sites we get the error "Could not continue scan with NOLOCK due to data movement" indicating that the query optimizer takes a NOLOCK for our select statement ( has been opened with adOpenDynamic, adLockOptimistic ).

    It's no option to change the source, we have to solve this without touching the code.

    Is there any way to tweak the query optimizer so that our app works correctly?
    I know that there will be a reduction of performance but it's our only choose.

    thnx in advance,

    adOpenDynamic, adLockOptimistic

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Can you reproduce the same behavior in a lab machine, or only on site?

    -PatP

  3. #3
    Join Date
    Feb 2004
    Posts
    11
    On our test environment it happens once, then it continues correctly.
    But on site the problem is persistent.

  4. #4
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Using NOLOCK on highly voletile data in cursor-based situations is not recommended. What you're experiencing is very typical in high activity in terms of action queries. You will have to change your cursor and lock types.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'd suggest fixing the cursor, or trying to run the query at a very low (preferably zero) usage time of day for the tables involved in the query.

    -PatP

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •