tempdb SQL Server locking

Question

Hello!

Our application runs alongside another application on a customers machine. We have put some efforts regarding avoiding long-running locks in tempdb since this obviously affects concurrency badly. The other application, however does things like:

begin transaction
create #Table(...);
insert into #Table(....) values(...);
operation_for_totally_six_seconds().
commit;

Since the operations take time, our application get stuck waiting for the locks aquired by the other application.

Now, I expect there to be a way to isolate my application from the other application by for example telling sql server to assign me another tempdb, but I have not found a way. Is this somehow possible or is the solution to install our database on another mssql-instance?

Regards, Jens Nordenbro

Answer 1

One option is to run your app in a different instance of sql server on the same machine. This way you would have your own tempdb.

Answer 2

long-running locks in tempdb since this obviously affects concurrency badly

That is actually not obvious at all. Long held locks are of importance only if you and the other application go after the same locks. The code sample you posted is perfectly legitimate. First of all #temp is a connection specific table that no other connection can even see it. But even if it would be global resource, it would belong to the other application and hence you would have no business acquiring locks on it.

As an exercise, open an SSMS query window and run this:

begin transaction;
create table #temp (a int);

Then opne a second query window and run the same. QED they don't block each other, despite creating the very same #temp table.

If tempdb is indeed a bottle neck you need to do some more investigation and find the actual resources that contention occurs on.

Answer 3

hmm... your right, I think the case I presented is invalid. I'll reopen it if I can find the correct case. Thanks!

/jens