patternsqlMinor
Will reloading a table cause recompiles downstream?
Viewed 0 times
downstreamreloadingrecompileswillcausetable
Problem
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.
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.
Solution
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.
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.
Context
StackExchange Database Administrators Q#186351, answer score: 8
Revisions (0)
No revisions yet.