question

The difference between `with (nolock)` and `nolock` is a syntactic one that was introduced a few versions back (2005 if I remember correctly), and the use without a `with` is a deprecated feature. However you cannot use a `nolock` table hint on a `delete` statement. Is the GUID unique for this table? Is it only trying to delete one row, or does that GUID span a number of rows?

The only way to speed this up is if there is a good index on the check_temp.guid column and that the number of rows being deleted doesn't lead to table scans. If you are deleting thousands and thousands of rows on a regular basis, you may want to look into some mechanism of data partitioning. That is set up for just that sort of thing. Now, the NOLOCK. This is a crutch frequently used by people in an attempt to speed up SELECT queries. It can't be applied to any query that modifies data because those queries MUST be able to apply locks in order to make changes to the data. There is no choice here. Just so we're clear, NOLOCK applied to SELECT queries will result in dirty reads. This means that if data is being updated, for example from 'Apple' to 'Pear' you could get either Apple or Pear in the result set. But, it also means that if page splits and rearrange are occurring, you can see rows multiple times or even miss rows of data. You can completely alter the data returned to your systems in ways that most businesses cannot support. Be very careful using NOLOCK. If you have exhausted every possible means of tuning queries, for SELECT queries, you can look at using one of the SNAPSHOT isolation levels. This radically reduces locking and can help performance.

You also might want to break down your delete statement so it only does, say, 5-10k records at a time, rather than attempting the full lakh. Here's an example: use tempdb go create table Testing(ID int) INSERT INTO Testing SELECT c1.ID FROM syscolumns c1 LEFT JOIN syscolumns c2 on 1=1 select id, count(*) FROM Testing group by ID order by 2 desc set rowcount 5000 while exists(select * from Testing WHERE ID = 22) BEGIN DELETE FROM Testing WHERE ID = 22 END select id, count(*) FROM Testing GROUP BY ID ORDER BY 2 desc SET ROWCOUNT 0 (I've used ID 22 as that's the one that gave the biggest number of rows (22k+) when I ran this on my SQL2012 Dev instance.)