2 replaced http://stackoverflow.com/ with https://stackoverflow.com/
source | link

The difference between the repro that you have posted with 10 columns and 100 rows and 11 columns and 100 rows is that the execution plan for the first one uses Simple Parameterization.

The actual execution plan for the 10 columns lists parameters from @1 to @1000.

11 * 100 is 1100. But one thousand seems to be the maximum number of parameters an auto parameterised query can reach.

You are doing 10 inserts for each. In the 10 column case the plan can be compiled once and reused for the other 9 inserts. In the 11 column case each insert statement needs to be compiled individually.

Moreover the process of compilation takes longer when SQL Server has the literal values to look at as it spends time working out properties of the group (or at least this used to be the casethis used to be the case I'm not sure if this has changed in more recent versions).

The difference between the repro that you have posted with 10 columns and 100 rows and 11 columns and 100 rows is that the execution plan for the first one uses Simple Parameterization.

The actual execution plan for the 10 columns lists parameters from @1 to @1000.

11 * 100 is 1100. But one thousand seems to be the maximum number of parameters an auto parameterised query can reach.

You are doing 10 inserts for each. In the 10 column case the plan can be compiled once and reused for the other 9 inserts. In the 11 column case each insert statement needs to be compiled individually.

Moreover the process of compilation takes longer when SQL Server has the literal values to look at as it spends time working out properties of the group (or at least this used to be the case I'm not sure if this has changed in more recent versions).

The difference between the repro that you have posted with 10 columns and 100 rows and 11 columns and 100 rows is that the execution plan for the first one uses Simple Parameterization.

The actual execution plan for the 10 columns lists parameters from @1 to @1000.

11 * 100 is 1100. But one thousand seems to be the maximum number of parameters an auto parameterised query can reach.

You are doing 10 inserts for each. In the 10 column case the plan can be compiled once and reused for the other 9 inserts. In the 11 column case each insert statement needs to be compiled individually.

Moreover the process of compilation takes longer when SQL Server has the literal values to look at as it spends time working out properties of the group (or at least this used to be the case I'm not sure if this has changed in more recent versions).

1
source | link

The difference between the repro that you have posted with 10 columns and 100 rows and 11 columns and 100 rows is that the execution plan for the first one uses Simple Parameterization.

The actual execution plan for the 10 columns lists parameters from @1 to @1000.

11 * 100 is 1100. But one thousand seems to be the maximum number of parameters an auto parameterised query can reach.

You are doing 10 inserts for each. In the 10 column case the plan can be compiled once and reused for the other 9 inserts. In the 11 column case each insert statement needs to be compiled individually.

Moreover the process of compilation takes longer when SQL Server has the literal values to look at as it spends time working out properties of the group (or at least this used to be the case I'm not sure if this has changed in more recent versions).