Hello Friends,

Trace Flags are really important considerations in SQL Server where we wants to change some default behavior at instance level or query level. If we want to enable the trace flag globally then we can enable it for the instance level. We can also use the trace flags at specific query level as well but it will required system admin level permissions. Yes, you read is correct but this statement is no longer true in case of SQL Server 2016 with SP1 on wards. If you are using SQL Server 2016 with SP1 then you can use new feature USE HINT without having SA permission.

This feature will be really helpful in the cases where our application runs under a user which don’t have SA permission. In such cases application user account can use USE HINT in the queries without SA permission. Developers which don’t have SA permission can also use these hints as well. In short we can say that developers will have more controls as compare to earlier. With release of SP1, below hints can be used without having SA permission:

FORCE_LEGACY_CARDINALITY_ESTIMATION                                      TF-9481

ENABLE_QUERY_OPTIMIZER_HOTFIXES                                                TF-4199

DISABLE_PARAMETER_SNIFFING                                                             TF-4136


DISABLE_OPTIMIZER_ROWGOAL                                                             TF-4138

ENABLE_HIST_AMENDMENT_FOR_ASC_KEYS                                    TF-4139


DISABLE_OPTIMIZED_NESTED_LOOP                                                     TF-2340

FORCE_DEFAULT_CARDINALITY_ESTIMATION                                   TF-2312

Here, I’ll show you the use of one of above hint in USE HINT. I’ll create a user which will have only read, write and execute writes on the database. Later I’ll use USE HINTS in a query running under this user.

Step 1: Alter database compatibility to 110 on SQL Server 2016 SP1, so that database optimizer will use legacy cardinality estimation. (Here I am login with my SA account)

Step 2: Open a new query window and insert some data to the table:

Step 3: Create a login and assign read, write to that login on AdventureWorks2014 database

Step 4: Now run the below query along with include actual execution plan after logging as AppUser. As of now my database is using New Cardinality Estimator and this query is also using the same.

SQL Sever 2016 - USE HINT without having SA permission_1

You can also check the cardinality estimator version by right click on SELECT operator on execution plan and click on properties. This will open up a properties window in right hand side.

SQL Sever 2016 - USE HINT without having SA permission_2

Step 5: Now run the below query along with include actual execution plan after logging as AppUser. Now this query will be using legacy Cardinality Estimator because of the USE HINT.

SQL Sever 2016 - USE HINT without having SA permission_3

SQL Sever 2016 - USE HINT without having SA permission_4

Here Query executed using legacy cardinality estimator where CardinalityEstimationModelVersion is 70. Similar way you can check the use of rest of USE HINT options. Options supported by USE HINT can be viewed by using DMV as mention below:

SQL Sever 2016 - USE HINT without having SA permission_5

Reference: Click Here.


Prince Kumar Rastogi

Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook
Follow Prince Rastogi on Twitter | Follow Prince Rastogi on FaceBook