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

josephdewey

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!

ara3n

what kind of select statement does he run? Is it select with nolock?

josephdewey

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;

bbrown

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.

josephdewey

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!

josephdewey

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?

bbrown

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.