question

Tom Staab avatar image
Tom Staab asked

Can a check constraint impact SELECT queries?

I have 34 nearly identical SELECT statements that follow this pattern: SELECT x.* FROM MySchema.SomeTable x INNER JOIN OneSchema.OneTable t ON t.TableId = x.Id ; The "SomeTable" varies for the 34 tables, but "OneTable" is the same in all queries. This generates a parallel execution plan for tables that do not have any check constraints, but it does not for the others. The NonParallelPlanReason is CouldNotGenerateValidParallelPlan. All source tables have "Id" as the clustered index. This is not just a curiosity. For the tables for which the parallel plan is chosen, execution time is typically under 30 seconds. For the others, nothing returns even if I wait for several hours. In fact, the number of reads performed during that time far exceeded what should be required for multiple full table scans. Thanks in advance for any help. UPDATE: Perhaps the check constraints are a red herring. I'm not sure, but the "OneTable" was actually a view that includes a ROW_NUMBER partition function which serializes at least that part of the plan. I'm now wondering if the decision is at least partially based on the estimated size of the result set. I modified the code to use the view to populate a table and then have the other queries all join to that table. I don't know why I didn't think of that before because, even without this latest issue, it's obviously more efficient to calculate the view once at the beginning instead of 34 times later. All of this runs within a single SSIS package. After the change, the package executes faster than ever before. Although that solves my immediate problem, it doesn't answer my original question or my new one regarding table sizes being the potential culprit. Therefore, I'll leave this open a bit longer to see if anyone has any thoughts.
tsqlconstraintparallelism
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Would you be able to provide a sample table schema?
0 Likes 0 ·
My only thought is that a CHECK constraint shouldn't be validated against a SELECT. Only an INSERT/UPDATE statement I believe. I would say that your discovery of the view and the repeated scanning of it is the culprit for the performance issues.
0 Likes 0 ·

1 Answer

· Write an Answer
Grant Fritchey avatar image
Grant Fritchey answered
The optimizer absolutely does take check constraints into account. I have a demo that shows it directly affecting the query plan when different WHERE clauses are affected or not affected by the data within the constraint. So, yes, without seeing the query or the plan in question, it could be affected by the check constraint. From what you're saying though, I think it sounds more like statistics and/or cardinality and cardinality estimates. It may also be the cost threshold for parallelism is too high, or too low. Hard to say without details.
8 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

I stand corrected. ;-) Do you have a post about that demo @Grant? I'd like to see that if possible. Thanks!
0 Likes 0 ·
No. I guess I'll have to write one up. It's in the presentation I did at SQLBits a couple of weeks ago, so it'll be in the recording.
0 Likes 0 ·
Thanks, Grant. I was hoping you'd respond. I had mostly assumed the check constraints weren't the true cause, but now I'm very curious to see your research/thoughts on the matter. I decided to ask my question here when we couldn't find the answer in your book. Great book, btw. :)
0 Likes 0 ·
I'm going to mark your answer as correct. As I explained in my update, I already worked around the problem by populating a working table, and that solution improved performance across the board. Please do post a link if you are able to point us toward your presentation or thoughts on the matter. Thanks.
0 Likes 0 ·
I think you're right on the root cause and breaking down the data being accessed is a pretty standard method. But yeah, constraints matter. I'll try to get at least a small blog post put together soon. Adding it to the list.
0 Likes 0 ·
Show more comments

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.