SQL Server 2016 – USE HINT without having SA permission

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

ASSUME_MIN_SELECTIVITY_FOR_FILTER_ESTIMATES                    TF-4137

DISABLE_OPTIMIZER_ROWGOAL                                                             TF-4138

ENABLE_HIST_AMENDMENT_FOR_ASC_KEYS                                    TF-4139

ASSUME_JOIN_PREDICATE_DEPENDS_ON_FILTERS                        TF-9476

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)

USE MASTER
GO
CREATE DATABASE TESTONLY
GO
USE TESTONLY
GO
CREATE TABLE USEHINTTEST
(
CUSTCODE INT IDENTITY(10001,1) PRIMARY KEY,
 BALANCE INT,
 STATE_NAME VARCHAR(50),
 COUNTRY VARCHAR(50)
)
GO

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

USE TESTONLY
GO
INSERT INTO USEHINTTEST(BALANCE,STATE_NAME,COUNTRY) VALUES(RAND(),'UP','INDIA')
GO 1000
INSERT INTO USEHINTTEST(BALANCE,STATE_NAME,COUNTRY) VALUES(RAND(),'HARYANA','INDIA')
GO 1233
INSERT INTO USEHINTTEST(BALANCE,STATE_NAME,COUNTRY) VALUES(RAND(),'KERALA','INDIA')
GO 677
INSERT INTO USEHINTTEST(BALANCE,STATE_NAME,COUNTRY) VALUES(RAND(),'CALABRIA','ITALY')
GO 1872
INSERT INTO USEHINTTEST(BALANCE,STATE_NAME,COUNTRY) VALUES(RAND(),'GOA','INDIA')
GO 4534
INSERT INTO USEHINTTEST(BALANCE,STATE_NAME,COUNTRY) VALUES(RAND(),'MP','INDIA')
GO 2763
INSERT INTO USEHINTTEST(BALANCE,STATE_NAME,COUNTRY) VALUES(RAND(),'GANGSU','CHINA')
GO 86
INSERT INTO USEHINTTEST VALUES(RAND(),'ASSAM','INDIA')
GO 123
CREATE NONCLUSTERED INDEX IX_USEHINTTEST ON USEHINTTEST(COUNTRY,STATE_NAME) INCLUDE (BALANCE)
GO

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

USE [master]
GO
CREATE LOGIN [AppUser] WITH PASSWORD=N'App@user#1231!', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
USE [TestOnly]
GO
CREATE USER [AppUser] FOR LOGIN [AppUser]
GO
USE [TestOnly]
GO
ALTER ROLE [db_datareader] ADD MEMBER [AppUser]
GO
USE [TestOnly]
GO
ALTER ROLE [db_datawriter] ADD MEMBER [AppUser]
GO
USE [TestOnly]
GO
GRANT SHOWPLAN TO [AppUser]
GO

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.

USE [TESTONLY]
GO
SELECT CUSTCODE,BALANCE FROM USEHINTTEST 
WHERE COUNTRY='CHINA' AND STATE_NAME='GANGSU'
GO

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.

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.

USE [TESTONLY]
GO
SELECT CUSTCODE,BALANCE FROM USEHINTTEST 
WHERE COUNTRY='CHINA' AND STATE_NAME='GANGSU'  
OPTION (USE HINT('FORCE_LEGACY_CARDINALITY_ESTIMATION'))

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:

Select * from sys.dm_exec_valid_use_hints

Reference: Click Here.

HAPPY LEARNING!

Regards:
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