Transaction Priority

[Crowley16]

Hi Everyone

I've got a extremely long running delete process that's going to be run on a live system, this process isn't top priority so I was wondering if it's possible to lower the priority of this process, in the same way you can with windows processes.

Does sql server 2005 support this?

Thanks

--------------------
Procrastinate Now!

 

[ESquared]

Break the process up into small batches using SET ROWCOUNT 100 or some appropriate value. If the delete involves a join, maybe you can select the primary keys involved in the join as a separate step to a temp table (use a clustered index on them), then join to that instead of the original other table.

There are many things you can do to lower the amount of work and the locking scope for each action taken.

Another thing is that sometimes having fewer records involved in a query will not just take less time but also cause the optimizer to switch from an index scan to an index seek. Doing the index seek 20 times each on small chunks will overall use less resources than one big index scan in a very large table. It's not just 1/20th x 20 in every case. It could be 1/40th x 20, overall using half the resources.

As long as you limit each bit of work to something fairly small, your impact to other users can be minimized.

[COLOR=black #e0e0e0]For SQL and technical ideas, visit my blog, Squared Thoughts.

_{The best part about anything that has cheese is the cheese.}[/color]

 

[Crowley16]

trouble is, there's about 1/4 of a billion records speread across 10 tables and all of them reference a central table which has to be deleted last...

I'm already partitioning this across sections of the primary key, just wondering if there is something like that, if not, I'll reduce the partition sizes...

--------------------
Procrastinate Now!

 

[mrdenny]

There isn't. Deleting requires that exclusive locks be taken on the data pages being used which will block all other users from accessing the pages until the delete is complete.

You'll want to reduce the partition sizes.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[ESquared]

Is the central table fairly small? That is, would a temp table containing just the pks and join-values from the central table be fairly small?

Have you considered turning on ON DELETE CASCADE for your foreign keys, assuming you have any? Letting the database engine handle cascaded deletes is more efficient than doing it yourself.

Are your tables properly indexed? Having the right clustered index and nonclustered indexes can make all the difference in the world.

Have you actually examined any of the execution plans of your delete statements? Table scans are just as bad in deletes as they are in selects, real performance killers.

What are the join types being used: hash, merge, loop?

Improving performance of ANY query, delete, update, or select, involves all these factors.

One strategy that has worked for me, but may not apply in your case (though it might so I'm offering it) is to use a simplified select query that makes good use of the table indexes. You basically select MORE than you need into a separate work/temp table, using conditions that get all the rows you need to work with, plus some extra. Then, working with that smaller table, you do additional work against that table to remove the items you don't need. When you are done manipulating the work table, then you join to it and delete. I don't know if this would make a difference for you.

For me this strategy was the difference between a 15-minute query and a series of queries that all together took about 4 minutes and only hit the main production tables for 3 minutes. The extra conditions that were slowing down the main query then took minimal time against the smaller work table I built.