[Solved] SQL Job fails due to QUOTED_IDENTIFIER = OFF

EverSQL Database Performance Knowledge Base

SQL Job fails due to QUOTED_IDENTIFIER = OFF

I have a SQL agent job that runs nightly at 2am successfully for the last year; We haven't made any change to that job specifically but it suddenly gives error:

MERGE failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operation

I read around and looks like a quick fix is to insert the command "SET QUOTED_IDENTIFIER ON" at the very top of this job.

However, I really want to know why this happens? the job does not call any store procedure, instead it uses merge, insert, update using some views, tables.

I verified all my views and tables have quoted_identifier set to true which I believe is the default value.

I ran the following script and I see that my SQLAgent - Job Invocation Engine has quoted_identifier set to off.

SELECT *
FROM   sys.dm_exec_sessions
WHERE  is_user_process = 1
AND    quoted_identifier = 0 ;

This perhaps explains why my job fails? But why it fails all of a sudden with the same job that ran successful the night before?

Could a script that adds new index or alter a store procedure caused this to quoted_identifier value to false? can a database restart do it?

How to optimize this SQL query?

The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:

  1. Description of the steps you can take to speed up the query.
  2. The optimal indexes for this query, which you can copy and create in your database.
  3. An automatically re-written query you can copy and execute in your database.
The optimization process and recommendations:
  1. Avoid Selecting Unnecessary Columns (query line: 2): Avoid selecting all columns with the '*' wildcard, unless you intend to use them all. Selecting redundant columns may result in unnecessary performance degradation.
  2. Create Optimal Indexes (modified query below): The recommended indexes are an integral part of this optimization effort and should be created before testing the execution duration of the optimized query.
Optimal indexes for this query:
ALTER TABLE `dm_exec_sessions` ADD INDEX `dm_sessions_idx_is_process_quoted_identifi` (`is_user_process`,`quoted_identifier`);
The optimized query:
SELECT
        * 
    FROM
        sys.dm_exec_sessions 
    WHERE
        sys.dm_exec_sessions.is_user_process = 1 
        AND sys.dm_exec_sessions.quoted_identifier = 0

Related Articles



* original question posted on StackOverflow here.