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.
Best Answer
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.