4

If I put a database in single user mode, would I have to worry about locks? Or will SQL Server basically perform (with exception to my open transactions) like the isolation level is read uncommitted?

We have a heavy ETL process that was written and sped up by utilizing query hints such as TABLOCK and NOLOCK depending on the data. I was wondering if putting the database into single user mode during these processes would be beneficial.

  • Let's take a step back. What problem are you trying to solve? – Tara Kizer Jan 23 '17 at 23:02
  • Heavy ETL processes which were sped up with query hints (tablock or nolock depending on the data). I was wondering if single user mode would change anything – Dan Andrews Jan 23 '17 at 23:45
5

So in single_user mode - you would be quite unlikely to have locking problems in that database. It is what it sounds like - single user - and it doesn't mean Single Username - it means one user. So it's used when you as a DBA want to do something that can't be done with others users in. Maybe you are trying to do a repair option of a checkdb. Maybe you are trying to change some object metadata and don't have a better way to kick other users out. Etc.

But it really isn't an isolation level "thing" it's really an "access thing" - if you want to eliminate or avoid locking - there are a lot of things to look at such as better performing queries, snapshot isolation levels, improvements to code, etc.

3

Single user is related to the number of connections that can be opened to the database. (including the background thread or sql agent connection). It does not govern the isolation levels.

1

To actually answer your question Dan, instead of going off into as myriad of tangents: "Yes it will make a difference".

If a database is in single user mode (or read only) there will be effectively no locking going on in the database. There is nothing for the Lock Manager to do as there is no possibility of contention, no need to isolate transactions, etc.

So this can potentially improve performance. No locks being acquired, released and escalated for the database. If you have other databases in the system, they too will be competing for the lock memory space, which is a finite resource that needs to be managed.

I have done such, successfully in the past, but your mileage might vary.

HTH

0

One of the few ways to get around the locking section of the SQL Server Engine is to either set the DB to read only mode which disables locking, or setup in memory tables which also bypasses locking. Outside of that you're largely limited to creative app design or directly working with isolation levels in your code and schema.

0

No, putting the database into single user mode will not solve your issue. Single user mode will prevent others to connect to the database. Is used to isolate the database and be able to do some type of operations that requires that no user is connected. But if no user is connected, then no ETL process will be able to run and no user can check the results of that.

You or someone else will need to go through all those queries and/or app code and do a performance checkup. Also I will advice on doing a check on the general configuration of your SQL Server box to rest assure that everything is correctly configured, say amount of RAM given to the SQL server engine, max_dop, etc, etc, etc.

And about using NOLOCK hint, take a look here Bad habits: Putting NOLOCK everywhere.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.