How to force a user to always be non-locking?

josephdeweyjosephdewey Member Posts: 87
edited 2012-12-05 in SQL Performance
The automated user that extracts our Dynamics NAV database tables into the data warehouse locks the tables with a locking type of LCK_M_S.

This isn't good, because the data warehouse extract takes a very long time, and is blocking our users from doing their jobs.

The guy in charge of our data warehouse said that there wasn't anything he could do within his extract program to change the locking at all.

So, I'm wondering if there's anything I can do within SQL Server to make the automated data warehouse user never lock any of the live tables.

Thanks!
Joseph Dewey
Microsoft Dynamics NAV User

Comments

  • ara3nara3n Member Posts: 9,255
    what kind of select statement does he run? Is it select with nolock?
    Ahmed Rashed Amini
    Independent Consultant/Developer


    blog: https://dynamicsuser.net/nav/b/ara3n
  • josephdeweyjosephdewey Member Posts: 87
    Basically they select all of the records for about 30 different NAV tables.

    The answer to your NOLOCK question is no. Adding a NOLOCK hint is what the data warehouse guy is saying that he can't do, so I'm wondering if there's a way that I can force the data warehouse user to do that.

    I'm hoping for some magical SQL Server command, like:

    FORCE dwuser FOR ALWAYS NOLOCK HINT;
    Joseph Dewey
    Microsoft Dynamics NAV User
  • bbrownbbrown Member Posts: 3,268
    2 options to consider:

    1. Do the BI update from a coy of the NAV database. (backup/restore) This is an approach we use on a number of systems. The short time it takes to spawn the copy is minor compared to the reduced impact to users.

    2. Look at implementing snapshot isolation.
    There are no bugs - only undocumented features.
  • josephdeweyjosephdewey Member Posts: 87
    Hi bbrown,

    #2 is my ultimate plan on how to fix this issue, but this will take some time and resources that I'm trying to acquire.

    I had not considered your #1 option before, and I really, really like it. This is something that we could automate, and then the data warehouse people could just redirect their costly extracts from that backup database. Thanks so much for your suggestion!

    Thanks!
    Joseph Dewey
    Microsoft Dynamics NAV User
  • josephdeweyjosephdewey Member Posts: 87
    Oh, I guess I didn't understand what you meant by Snapshot Isolation. I just looked that up.

    With Dynamics NAV, what are the pros and cons of using Snapshot Isolation? Does Snapshot Isolation cause any issues with NAV?
    Joseph Dewey
    Microsoft Dynamics NAV User
  • bbrownbbrown Member Posts: 3,268
    I've not seen it create any issues for NAV, but neither does it provide any advantages for NAV. It's benefit is for external processes needing to read the database. This reduces their lockign impact on the NAV users.

    It will put extra pressure on temp db, so consider that in your system configuration. I'd recommend having temp DB on a small RAID 10.
    There are no bugs - only undocumented features.
Sign In or Register to comment.