5

We have a vendor that is requesting we truncate a data table, and reload it with the same data daily. The table is 90k rows and approx 10mb. The reason they want this done is so the ETL program picks up that the table has been updated. This table is used in a number of queries.

The question I have is will the changes made to this table cause a ripple effect of recompiles in query plans where this table is used. Or will SQL be smart enough to see that the table contents haven't changed, and avoid the recompiled?

Yes, we know how stupid this is.

2
  • 1
    How does the ETL process 'know' the table has been updated or reloaded? Are there triggers? Sep 19 '17 at 18:29
  • My understanding is the ETL process looks for the table during the load, and if it isn't seen than it assumes the table is stale.
    – SQLMac
    Sep 20 '17 at 13:25
8

Yes, truncating a table with 90k rows will cause a recompile for any queries that touch it.

You could stop that by turning off auto update stats, and in any update-stats jobs or rebuild-index jobs, you exclude this table. However, I'd suggest that you leave it on - after all, the table really could change over time, and you'd want your query plans to update.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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