1

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

Remus Rusanu
  • 288,378
  • 40
  • 442
  • 569
Jens Nordenbro
  • 31
  • 1
  • 3

2 Answers2

3

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.

Remus Rusanu
  • 288,378
  • 40
  • 442
  • 569
  • Great answer and so true. @jens, while you might be misinterpreting your observations, you could ensure that you have more than one tempdb file. The recommendation is usually, but not always, one file per two CPU cores. – Darek Oct 14 '18 at 13:40
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.

NotMe
  • 87,343
  • 27
  • 171
  • 245
  • Yes, but my question is rather if this is possible without the rather uncomfortable one-instance-per-database hosting-scenario? – Jens Nordenbro Dec 07 '09 at 14:51