Sqlserver can not use delete top from procedure

Say you’ve got a table with millions or billions of rows, and you need to delete some rows. Deleting ALL of them is fast and easy – just do TRUNCATE TABLE – but things get much harder when you need to delete a small percentage of them, say 5%.

It’s especially painful if you need to do regular archiving jobs, like deleting the oldest 30 days of data from a table with 10 years of data in it.

The trick is making a view that contains the top, say, 1,000 rows that you want to delete:

CREATE VIEW dbo.Comments_ToBeDeleted AS

SELECT TOP 1000 *

FROM dbo.Comments

ORDER BY CreationDate;

GO

Make sure that there’s an index to support your view:

CREATE INDEX IX_CreationDate ON dbo.Comments(CreationDate);

And then deleting from the view, not the table:

DELETE dbo.Comments_ToBeDeleted

WHERE CreationDate < '2010-01-01';

This lets you nibble off deletes in faster, smaller chunks, all while avoiding ugly table locks. Just keep running the DELETE statement until no rows are left that match. It won’t necessarily be faster overall than just taking one lock and calling it a day, but it’ll be much more concurrency-friendly.

Wanna see it in action? No? Then just copy/paste my code, put it straight into production like you always do, and get back to work. For the rest of you, keep reading.

Demoing Fast Ordered Deletes

To demo this technique, I’m going to use the cloud setup for our Mastering Query Tuning classes:

• An 8-core, 60GB RAM VM with the data & log files on ephemeral (fast) SSD
• The Stack Overflow public database as of 2017-Aug
• The dbo.Comments table – which has 60M rows, 20GB in the clustered index
• I’ve created 5 nonclustered indexes that total about 5GB of space (to make the deletes a little tougher and more like real-world tables)

The Comments table has a CreationDate field, and let’s say I need to delete the oldest comments – we’re going to delete all the ones from 2008 and 2009:

Comments by year

2008 & 2009 had a total of 1,387,218 comments – but that’s only about 2.3% of the table’s overall rows.

First, the plain ol’ DELETE.

I could try just deleting them outright:

DELETE dbo.Comments

WHERE CreationDate < '2010-01-01';

It takes 39 seconds. Here’s what the actual execution plan (PasteThePlan) looks like:

DELETE dbo.Comments WHERE CreationDate < ‘2010-01-01’

It’s what we call a “wide” execution plan, something I first heard from Bart Duncan’s post and then later Paul White explained in much more detail. Because we’re deleting so many rows, SQL Server does a bunch of sorting, and those sorts even end up spilling to TempDB.

Plus, it’s taking a big table lock as it works. That’s no good, especially on big tables.

If you can get away with a 39-second table lock and activity in TempDB, the plain ol’ DELETE technique is fine. But let’s pretend you’re working in a mission-critical environment where a 39-second table lock is out of the question, and you need a faster background technique.

Demoing Fast Ordered Deletes

Like we talked about at the start of this odyssey, create a view:

CREATE VIEW dbo.Comments_ToBeDeleted AS

SELECT TOP 1000 *

FROM dbo.Comments

ORDER BY CreationDate;

GO

Make sure that there’s an index to support your view:

CREATE INDEX IX_CreationDate ON dbo.Comments(CreationDate);

And then deleting from the view, not the table:

DELETE dbo.Comments_ToBeDeleted

WHERE CreationDate < '2010-01-01';

It runs nearly instantly (because we’ve got an index to support it), and here’s the plan:

Fast ordered deletes plan

At first, it looks the same as the plain DELETE plan, but look closer, and there’s something missing:

Just like me with the tequila – no spills

There’s no yellow bangs because there’s fewer sort operators and they’re not spilling to disk. Similarly, the memory grant on this query is way lower:

• Plain DELETE memory grant: 118MB (only 64MB of which gets used, but it spills to disk anyway because not every operator can leverage the full grant – you can learn more about grant fractions from Joe Obbish)
• Fast Ordered Delete memory grant: 1.8MB (only 472KB of which got used)

The grants are lower because we’re handling less data, which is also evidenced by the STATISTICS IO output:

• Plain DELETE logical reads: 25,022,799 on the Comments table (plus another 4.1M on the worktables)
• Fast Ordered Delete logical reads: 24,732 on the Comments table, plus 2K on the worktables – but that’s with me using TOP 1,000 in the view. If I change it to TOP 10,000, then the reads jump to 209,163. Still way better than 25,022,799 though, but it brings up a good point….

If you need to do this regularly, tune it.

You can play around with:

• The number of rows in the view (say, 1K, 5K, 10K, etc, keeping in mind the lock escalation threshold)
• The delay time between deletions

That way you can find the sweet spot for your own deletes based on your server’s horsepower, concurrency demands from other queries (some of which might be trying to take table locks themselves), the amount of data you need to delete, etc. Use the techniques Michael J. Swart describes in Take Care When Scripting Batches.

For more learning on this topic, read Microsoft SQLCat on Fast Ordered Deletes – Wayback machine copy because Microsoft deleted a lot of pages during one of their annual corporate shuffles. You can tell it’s old because…MySpace, yeah.

Can we use DELETE with top in SQL?

In SQL Server, DELETE TOP statement is used to delete the records from a table and limit the number of records deleted regarding a fixed value or percentage. Syntax: DELETE TOP (top_value) [ PERCENT ] FROM [database_name].

How to delete data from procedure in SQL?

Use SQL Server Management Studio.

In Object Explorer, connect to an instance of Database Engine and then expand that instance..

Expand Databases, expand the database in which the procedure belongs, and then expand Programmability..

Expand Stored Procedures, right-click the procedure to remove, and then select Delete..

How do I DELETE a top order in SQL Server?

Example - Using TOP keyword Let's look at a SQL Server example, where we use the TOP keyword in the DELETE statement. For example: DELETE TOP(10) FROM employees WHERE last_name = 'Anderson'; This SQL Server DELETE TOP example would delete the first 10 records from the employees table where the last_name is 'Anderson'.

Why i can't DELETE table in SQL?

The reason SQL won't let you drop a table in this situation is because the allocation pages/extent chain appears to be damaged or cross-linked in some way. So SQL Server thinks that there is actually data from other tables in pages/extents belonging to the problem object.