• Home
  • Contact

John Sansom

SQL Server DBA Blog

  • About
    • The Blog
    • John Sansom
    • Contact
  • Ebook
  • Archives
    • Start Here
    • Popular Posts
    • All Posts
    • SFTW
  • Becoming a DBA
    • What it’s Really Like
    • Interview Tips
    • Certification
    • FAQ
  • Books
  • Resources
    • Blog Tools and Technology
    • UK Events Schedule
    • References & Resource Lists
  • Subscribe

Performing fast SQL Server delete operations

May 27, 2009 by John Sansom 4 Comments

There have been quite a few queries on the forums this past week with regard to performing fast delete operations on large tables and I wanted to share some of the possible solutions here with you.

Fast ordered delete using a view

Performing Fast SQL Server Deletes

“Performing Fast Operations”

To perform a fast ordered delete, take a look at the article Fast Ordered Delete provided by Kevin Stephenson of MySpace and Lubor Kollar, a member of the SQL Server Customer Advisory Team. What makes the article of particular interest is that the client is MySpace. It stands to reason then that the database in question here will be particularly large and provides a good test case for this proof of concept. The crux of the solution presented focuses on replacing a subquery within the where clause, with a view definition that identifies the records to be deleted .

Before:

delete from t1 where a in (select top (10000) a from t1 order by a);

After:

create view v1 as (select top (10000) * from t1 order by a)

delete from v1

You can see the difference and resulting simplification in the exeuction plans produced for these queries in the article Fast Ordered Delete.

This implementation lends itself well to batching delete operations, a technique that is used to limit the transaction log space used by a delete operation and to facilitate more frequent truncation of the transaction log.

Fast ordered delete using a derived table

The previous solution  focused on utilising a view in order to simplify the execution plan produced for the query. This was achieved by referencing the given table once, rather than twice which in turn reduces the amount of I/O required. Interestingly, the same goal can be achieved by using a derived table as explained by Simon Sabin in the article Delete Top X rows avoiding a table scan

Working with large database tables

Perhaps one further consideration that is worthy of being raised here is database table design. For example, if during the design process for a database system it becomes apparent that a significant number of delete operations will be required for a given table, it is then likely that the table in question would be a good candidate for SQL Server Partitioning and in particular Sliding Window Partitioning. Such an implementation would allow for the Switching in and out of partitions without adverse performance implications.

For further reading, consult the Microsoft Whitepaper: How to Implement an Automatic Sliding Window in a Partitioned Table on SQL Server 2005






Get my Free Tips and Advice on SQL Server delivered direct to you by E-mail


Can’t Find the Answer Your Looking for?

Do you have a SQL Server question that you can’t find the answer to? Sounds like a great problem to have , let me know about it!

You may also be interested in…………..

  • How much memory is each SQL Server database using?
  • How to identify the most costly SQL Server queries using DMV’s
  • The SQL Server Buffer Pool and Buffer Cache Hit Ratio
  • How to become a SQL Server DBA

Did you find this post helpful? Subscribe to the Blog feed and show your support by clicking the  Google+ button at the top of the page.

It spreads the word helping our SQL community grow and shows you care :-)

Filed Under: Administration Tagged With: Query Optimisation

About John Sansom

I’m a Microsoft Certified Master(MCM) of SQL Server. I’ve been working with database technology in a variety of flavors for over fifteen years. I absolutely love what I do and genuinely feel privileged to be a part of our tremendous technology community. Got a question about SQL Server or being a DBA? Ask me!

Popular Articles

  • Top 10 Free SQL Server Tools
  • Performing fast SQL Server delete operations
  • How to identify the most costly SQL Server queries using DMV’s
  • Top 10 Junior DBA Interview Tips
  • The Database Administrator’s Primary Responsibility
  • Your Road to Becoming a DBA: Laying a Strong Foundation
  • Top 5 SQL Forums
  • SQL Server Memory Configuration, Determining MemToLeave Settings
  • Script SQL Server Agent Jobs Using PowerShell
  • Using sys.dm_os_ring_buffers to Troubleshoot Connectivity Issues

Categories

  • Administration (38)
  • Blogging (8)
  • Customer Service (5)
  • Disaster Recovery (5)
  • DMVs (4)
  • Index Optimisation (6)
  • Interviews (1)
  • Link Posts (243)
  • Memory (2)
  • Performance Tuning (15)
  • Professional Development (70)
  • Reporting Services (5)
  • Reviews (1)
  • SQL Server Community (144)
  • SQL Server Tips (11)
  • SQLServerCentral Syndication (112)
  • SQLServerPedia Syndication (116)
  • Tools (7)

Copyright © 2023 · Santech Solutions Limited · Powered by the Genesis Framework · Privacy Policy