Clever Geek Handbook

Single user: disable blocking for Microsoft SQL Server

I am running an update script for a database hosted on Microsoft SQL Server. It will take some time. Some of the queries should not be optimized differently.

I am the only person using this database: is there a way that I can tell SQL Server not worrying about transactions / locking?

For example, on DELETE ... WHERE does SQL need to acquire exclusive locks on the rows it is about to delete? If so, can I tell you not to bother, as this is the only query being executed?

+1


source to share


2 answers


See SQL Query Performance - Are You Feeling Dirty? (Dirty Reads) .



Edit: This is only a guess, but if you are the only connection to SQL Server, you can get exclusive table-level locking with WITH (TABLOCKX)

. You are sacrificing concurrency, but it can speed up.

+1


source


Disable Autocommit (also implicit transactions); you will need to do commit () at the end. The log file will grow accordingly large, make sure you have enough free disk space. Is tempdb on the same drive?



0


source






More articles:


All Articles