1

Background:

I am a developer working on an application that has a relatively large table (in my experience at least) of 1.7 billion records.

The table is a history table, and the application imports to this table daily. On average, it will insert approximately 1.2 million records per day.

Yes, there is a lot of history there, no, it should not all be online at the same time.

The table is not partitioned.

The table contains a report date column and a currency column, among others, but those are the major logical "partitioning" ones if you could call them that.

The table has a clustered index on (ID, ReportDate) as well as a non clustered index on ReportDate, Currency, and a couple other columns.

Today I was tasked with troubleshooting an application issue where there was a command timeout doing a delete by Report Date and Currency. I couldn't retrieve the execution plan for the delete of the entire record set (about 1.2 million rows). I did a delete of 10k records which took 54 seconds.

While working on a completely separate application also dealing with large tables, I found a scenario where the table is big enough that the threshold for automatic statistics updates was higher that the number of records being inserted into the table, so queries against that newly inserted data was not included in the statistics, and so any query assumed there would be 1 record (according to the execution plan) and so SQL chose to use nested loop joins on millions of records.

The actual question:

Do statistics play any role in how SQL actually executes delete operations?

My team has the statistics experience in very recent memory so they asked me if it could be related, and my intuition says "probably not" because there are no joins involved in this delete that timed out.

I went ahead and updated the statistics on the table (specifically the statistics on the indexes) and ran the delete again and it went from taking 54 seconds for a batch delete of 10k records, down to 18 seconds after the statistics update. So there's a correlation there. However, the database is on a shared server with a number of other databases, so I can't isolate it and say "yes the statistics helped" when it could simply be that the server was busy at the time the delete timed out and now when trying it again it's less busy.

In trying to troubleshoot I looked at the activity monitor and saw that the process was frequently waiting for PAGEIOLATCH_SH and I/O was really low, under 3MB/sec.

Improve this question
4
  • Statistics can effect any operation since sql uses them to decide how to execute your query. Lack of it can cause bad exec plan and creating them can cause slowness in execution. But I would try another approach: Is there DBA in your company that can help you? If not you should try to understand what is going on using execution plan. There is option to view it in ssms and there is a free sentryone tool that can show you execution plan. There you can see where sql "spends" majority of time. You can see exec plan on deletes, but probably you'll see that on select only. I would also check
    – Michael Cherevko
    Commented Jul 4, 2023 at 15:20
  • Locks - try to look for whoisactive proc online and create it on your server, if its not created yet. There is an option that there are locks preventing from quick delete. In this case you can try do delete by smaller batches of first select id column\s to temp table with nolock and then delete using this table+ verify that data wasn't changed.
    – Michael Cherevko
    Commented Jul 4, 2023 at 15:23
  • The 54 -> 18 could have been due to caching?
    – Rick James
    Commented Jul 26, 2023 at 23:33
  • Do ID and ReportDate somewhat track each other? That is, all the rows for one date have consecutive IDs?
    – Rick James
    Commented Jul 26, 2023 at 23:35

1 Answer 1

Reset to default
3

Do statistics play any role in how SQL actually executes delete operations?

Yes. Statistics affect estimates and estimates affect anticipated costs.

SQL Server has a few optimizations it can apply to statements that change data, if it determines it worthwhile.

For example, it can choose to sort the rows into target index order, minimising the number fetches from disk and reducing small/random I/O patterns.

It can also perform ordered or unordered prefetching, where soon-to-be modified pages are brought into memory before they are needed. This helps to reduce latency and may also increase I/O size. You can think of it as read-ahead for target indexes.

Clustered Index Delete with unordered prefetch

See my article, Optimizing T-SQL queries that change data for more details.

Improve this answer
1
  • Is there any way to see those kinds of optimizations in the query plan? I would assume something would show up in the clustered index delete operator?
    – ldam
    Commented Jul 5, 2023 at 9:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.