Query Execution / Functionality¶
This category mashes up several smaller categories:
- Query Execution –> Flags in this category affect runtime decisions made just before a query plan begins execution (or prints information about runtime behavior).
- Query Functionality –> Flags in this category modify the functional definition of the syntax; most flags are quite old, and many cannot be confirmed.
- Query Functionality (Unable to Confirm) –> Other public repos contain large numbers of flags that we cannot confirm. Many may be from old Sybase days, and these flags typically modify the functionality of syntax. These have been put into their own section.
The standard Fixes and Past Relevance section holds, as usual, flags that have trustworthy links but have ceased to be relevant for modern builds (or are fix flags whose expected utility is very short).
TODO: move 6532, 6533, and 6534 here. All are documented
Short Descriptions¶
| Flag | Short Description |
|---|---|
| See Also | |
| … | |
| QueryExec | |
| 646 | Prints to errlog which ColStore segments were eliminated at runtime. |
| 1504 | Prints to console or errlog when an index rebuild increases its mem grant. |
| 2466 | Affects runtime DOP choice; reverts to older polling-based logic. |
| 2467 | Affects runtime DOP choice; attempts to fit query within least-loaded node. |
| 2468 | Affects runtime DOP choice; round-robin search for node that can handle query. |
| 2479 | Affects runtime DOP choice; restrict query to node the connection is tied to. |
| 2486 | Enables output for query_trace_column_values Extended Event. |
| 7525 | Affects when cursors are closed upon ROLLBACK |
| 9389 | Enables dynamic memory grant for batch mode operators |
| … | |
| QueryFunc | |
| 3640 | Like SET NOCOUNT ON, eliminates sending DONE_IN_PROC messages to client. |
| … | |
| QueryFunc ? | |
| 237 | ?? Use correlated sub-queries in Non-ANSI backward compatibility. ?? |
| 242 | ?? Provides backward compat for correlated sub-queries w/non-ANSI results. ?? |
| 243 | ?? Provides backward compat for nullability behavior. ?? |
| 244 | ?? Disables checking for allowed interim constraint violations. ?? |
| 506 | ?? Enforces SQL-92 standards re: null values for parm/var comparisons ?? |
| 8783 | ?? Allows INSERT/UPDATE/DELETE statements to honor SET ROWCOUNT ON ?? |
| 8816 | ?? Logs every two-digit year conversion to a four-digit year. ?? |
| … | |
| Fix/PastRel | |
| 107 | Enabled the insertion of ‘0 into columns of type float, decimal, numeric, or real. |
| 262 | Enables a SQL 7 fix re: strings w/trailing spaces being truncated. |
| 6530 | Enables a fix for poor performance when building an index on a spatial data type. |
| 6532 | Enables a fix for poor query performance on spatial data types. |
| 6533 | Similar functionality as 6532 (performance fix for queries on spatial data). |
| 6534 | Similar functionality as 6532 (performance fix for queries on spatial data). |
Query Execution¶
- 646 (Info)
Prints (to SQL error log) which segments were eliminated at runtime by columnstore segment elimination. Requires 3605.
- 1504 (Info)
Prints to the console (w/3604) or the error log (w/3605; required for parallel index builds) when an index DDL command requires more memory to be granted to continue sorting rows in memory.
- 2466
When the optimizer is choosing the runtime DOP for a parallel plan, this directs it to use logic found in “older versions” (the post doesn’t say which versions) to determine which NUMA node to place the parallel plan on. This older logic relies on a polling mechanism (roughly every 1 second), and can result in race conditions where 2 parallel plans end up on the same node. The newer logic “significantly reduces” the likelihood of this happening.
- 2467
“If target MAXDOP target is less than a single node can provide and if trace flag 2467 is enabled, attempt to locate the least loaded node.”
- 2468
“Find the next node that can service the DOP request. Unlike full mode, the global, resource manager keeps track of the last node used. Starting from the last position, and moving to the next node, SQL Server checks for query placement opportunities. If a node can’t support the request SQL Server continues advancing nodes and searching.”
- 2479
When SQL Server is determining the runtime DOP for a parallel plan, this flag directs it to limit the NUMA Node placement for the query to the node that the connection is associated with.
- 2486 (Info)
In SQL 2016 (CTP 3.0 at least), enables output for the “query_trace_column_values” Extended Event, allowing the value of output columns from individual plan iterators to be traced.
- 7525
Affects when cursors are closed upon ROLLBACK. This flag reverts to SQL 7.0 RTM behavior. Unsure of whether this is still applicable to modern versions.
- 9389
Doc2014 - BOL 2014: “Enables dynamic memory grant for batch mode operators…If the dynamic memory grant trace flag is enabled, a batch mode operator may ask for additional memory and avoid spilling to tempdb if additional memory is available.”
Query Functionality¶
- 3640
Eliminates sending DONE_IN_PROC messages to client for each statement in stored procedure. This is similar to the session setting of SET NOCOUNT ON. (The flag gives the ability to control at a wider level without changing code).
Query Functionality (Unable to Confirm)¶
Initial attempts to find online documentation that is reasonably trustworthy has not been successful.
- 237
- Tells SQL Server to use correlated sub-queries in Non-ANSI standard backward compatibility mode
- 242
- Provides backward compatibility for correlated subqueries where non-ANSI-standard results are desired.
- 243
Provides backward compatibility for nullability behavior. When set, SQL Server has the same nullability violation behavior as that of a ver 4.2:
- Processing of the entire batch is terminated if the nullability error (inserting NULL into a NOT NULL field) can be detected at compile time.
- Processing of offending row is skipped, but the command continues if the nullability violation is detected at run time.
Behavior of SQL Server is now more consistent because nullability checks are made at run time and a nullability violation results in the command terminating and the batch or transaction process continuing.
- 244
- Disables checking for allowed interim constraint violations. By default, SQL Server checks for and allows interim constraint violations. An interim constraint violation is caused by a change that removes the violation such that the constraint is met, all within a single statement and transaction. SQL Server checks for interim constraint violations for self-referencing DELETE statements, INSERT, and multi-row UPDATE statements. This checking requires more work tables. With this trace flag you can disallow interim constraint violations, thus requiring fewer work tables.
- 506
- Enforces SQL-92 standards regarding null values for comparisons between variables and parameters. Any comparison of variables and parameters that contain a NULL always results in a NULL.
- 8783
- Allows DELETE, INSERT, and UPDATE statements to honor the SET ROWCOUNT ON setting when enabled.
- 8816
- Logs every two-digit year conversion to a four-digit year.
Fixes and Past Relevance¶
These flags either are old and irrelevant for modern builds, appear only in CTPs, or enable a fix in a CU but are baselined in a later service pack or release.
- 107
Enabled the insertion of ‘0 into columns of type float, decimal, numeric, or real.
- 262
Enables a fix in SQL 7.0 to correct a problem with strings w/trailing spaces being truncated even when ANSI PADDING was on.
- 6530
Enables a fix for poor performance when building an index on a spatial data type.
- 6532
Doc2014 BOL 2014: “Enables performance improvement of query operations with spatial data types in SQL Server 2012 and SQL Server 2014. The performance gain will vary, depending on the configuration, the types of queries, and the objects. Note: Beginning with SQL Server 2016 this behavior is controlled by the engine and trace flag 6532 has no effect.”