There is an SQL union all query with 3 union all queries. The query execution time changed significantly after I had added a text column CAST(c.getQuestionId AS VARCHAR(300)) in the query. The database being used is SQL SERVER 2014. Performance tuning expert, please help.
There are 3 indexes created on JcccustomersAssessmentProxy. This is a table with 80 million records. Explicit use of index is only mentioned in the 3rd query only
(ie (NOLOCK, INDEX=IX_AssessmentProxy_myJcAssessmentContext))
There is a non clustered index on myJcAssessmentContext
There is a non clustered index on getmycoordtoyplanver
There is a non clustered index on getMyEventItem
SELECT ass.P_KEY
,ass.SS_CODE
,CAST(evp.EVENT_SSID AS VARCHAR(11)) AS EVENT_SSID
,CAST(QUESTIONNUMBER AS VARCHAR(3)) AS EVENT_NO
,CAST(ISNULL(CAST(TEMPLATEVERSION AS VARCHAR), 0) AS INT) AS TEMPLATEVERSION
,ISNULL(CAST(TEMPLATENAME AS VARCHAR(50)), ' ') AS TEMPLATENAME
,evp.ASS_DATE
,ISNULL(ANSWERNUMBER, 0) AS ANSWER_NO
,ass.CASE_SSID
,ass.RE_SSID
,ass.RE_DATE
,ass.EPISODE_SSID
,ass.[SERVICE]
,ass.SERVICE_DESC
,ass.TAM_KEY
,ass.PRv_KEY
,CAST(QUESTIONNUMBER AS VARCHAR(3)) AS QUESTION_NO
,ISNULL(CONVERT(VARCHAR(100), REPLACE(REPLACE(CAST(QUESTIONTEXT AS VARCHAR(650)), char(10), ''), char(13), ' ')), ' ') AS QUESTION_TEXT
,ISNULL(CAST(ANSWER AS VARCHAR(125)), ' ') AS ANSWER_TEXT
,a.MyAssessment AS ASSESSMENT_SSID
-----------------------------------------------------------------------------------------
,CAST(RIGHT(a.getMyCoordtoyPlanVer, 10) AS INT) AS toy_PLAN_VERSION_SSID
,CAST(RIGHT(a.myJcAssessmentContext, 10) AS INT) AS CONTEXT_FORM_SSID
-----------------------------------------------------------------------------------------
,ass.RECORD_DATE
**,CAST(a.getQuestionId AS VARCHAR(300) ) AS QUESTION_ID /*NEW Text Column ,this remove the parallelism*/**
FROM SourceFeed.dbo.JcccustomersAssessmentProxy AS a WITH(NOLOCK)
INNER JOIN
TEMP_DATABASE.dbo.jsystemReport_AllEventItems_AllPersons evp WITH(NOLOCK)
ON a.getMyEventItem = evp.oid
INNER JOIN
TEMP_DATABASE.dbo.jsystemAssessment ass WITH(NOLOCK)
ON evp.getcustomersId = ass.customersID
AND evp.EVENT_SSID = ass.EVENTID
UNION ALL
SELECT ass.P_KEY
,ass.SS_CODE
,CAST(csp.ENT_SSID AS VARCHAR(11)) AS EVENT_SSID
,CAST(jcc.QUESTIONNUMBER AS VARCHAR(3)) AS EVENT_NO
,CAST(ISNULL(CAST(CAST(jcc.TEMPLATEVERSION AS VARCHAR) AS VARCHAR), 0) AS INT) AS TEMPLATEVERSION
,ISNULL(CAST(jcc.TEMPLATENAME AS VARCHAR(50)), ' ') AS TEMPLATENAME
,csp.ASSESSMENT_DATE
,ISNULL(jcc.ANSWERNUMBER, 0) AS ANSWER_NO
,ass.CASE_SSID
,ass.REF_SSID
,ass.RE_DATE
,ass.EPISODE_SSID
,ass.[SERVICE]
,ass.SERVICE_DESC
,ass.TEAM_KEY
,ass.PROVIDER_KEY
,CAST(ISNULL(jcc.QUESTIONNUMBER, 0) AS VARCHAR(3)) AS QUESTION_NO
,isnull(CONVERT(VARCHAR(100), replace(replace(CAST(jcc.QUESTIONTEXT AS VARCHAR(650)), char(10), ''), char(13), ' ')), ' ') AS QUESTION_TEXT
,ISNULL(CAST(jcc.ANSWER AS VARCHAR(125)), ' ') AS ANSWER_TEXT
,jcc.MyAssessment AS ASSESSMENT_SSID
-----------------------------------------------------------------------------------------
,CAST(RIGHT(jcc.getMyCoordtoyPlanVer, 10) AS INT) AS toy_PLAN_VERSION_SSID
,CAST(RIGHT(jcc.myJcAssessmentContext, 10) AS INT) AS CONTEXT_FORM_SSID
-----------------------------------------------------------------------------------------
,ass.RECORD_DATE
**,CAST(jcc.getQuestionId AS VARCHAR(300) ) AS QUESTION_ID /*NEW Text Column ,this remove the parallelism*/**
FROM TEMP_DATABASE.dbo.jsystemReport_toySpell csp WITH(NOLOCK)
INNER JOIN
TEMP_DATABASE.dbo.jsystemAssessment ass WITH(NOLOCK)
ON csp.getcustomersId = ass.customersID
AND csp.EVENT_SSID = ass.EVENTID
LEFT OUTER JOIN
SourceFeed.dbo.JcccustomersAssessmentProxy jcc WITH(NOLOCK)
ON csp.OID = jcc.getmycoordtoyplanver
WHERE NOT EXISTS (SELECT tp1.OID
FROM TEMP_DATABASE.dbo.AssessmentTransferPart1 tp1
WHERE tp1.OID = jcc.OID)
UNION ALL
SELECT ass.P_KEY
,ass.SS_CODE
,CAST(a.EVENT_SSID AS VARCHAR(11)) AS EVENT_SSID
,CAST(CAST(C.QUESTIONNUMBER AS INT) AS CHAR(3)) AS EVENT_NO
,CAST(ISNULL(CAST(C.TEMPLATEVERSION AS VARCHAR), 0) AS INT) AS TEMPLATEVERSION
,ISNULL(CAST(C.TEMPLATENAME AS VARCHAR(50)), ' ') AS TEMPLATENAME
,a.ASSESSMENT_DATE
,ISNULL(C.ANSWERNUMBER, 0) AS ANSWER_NO
,ass.CASE_SSID
,ass.REL_SSID
,ass.REAL_DATE
,ass.EPISODE_SSID
,ass.[SERVICE]
,ass.SERVICE_DESC
,ass.TEAM_KEY
,ass.PROVIDER_KEY
,CAST(ISNULL(CAST(C.QUESTIONNUMBER AS INT), 0) AS VARCHAR(3)) AS QUESTION_NO
,isnull(CONVERT(VARCHAR(100), CAST(C.QUESTIONTEXT AS VARCHAR(650))), ' ') AS QUESTION_TEXT
,ISNULL(CAST(C.ANSWER AS VARCHAR(125)), ' ') AS ANSWER_TEXT
,a.MyAssessment AS ASSESSMENT_SSID
-----------------------------------------------------------------------------------------
,CAST(RIGHT(C.getMyCoordtoyPlanVer, 10) AS INT) AS toy_PLAN_VERSION_SSID
,CAST(RIGHT(C.myJcAssessmentContext, 10) AS INT) AS CONTEXT_FORM_SSID
-----------------------------------------------------------------------------------------
,ass.RECORD_DATE
**,CAST(c.getQuestionId AS VARCHAR(300) )** AS QUESTION_ID /*NEW Text Column ,this remove the parallelism*/
FROM SourceFeed.dbo.toyClusterReviewAssessment B WITH(NOLOCK)
INNER JOIN
SourceFeed.dbo.JcccustomersAssessmentProxy C WITH(NOLOCK, INDEX=IX_AssessmentProxy_myJcAssessmentContext)
ON B.myJCJccUserFormContext = C.myJcAssessmentContext
INNER JOIN
SourceFeed.dbo.JcccustomersAssessmentScoreProxy D WITH(NOLOCK)
ON B.myJCJccUserFormContext = D.myJcAssessmentContext
INNER JOIN
TEMP_DATABASE.dbo.jsystemReport_toyClusterReviewEvent_AllPersons A WITH(NOLOCK)
ON a.myAssessment = B.oid
INNER JOIN
TEMP_DATABASE.dbo.jsystemAssessment ass WITH(NOLOCK)
ON a.getcustomersId = ass.customersID
AND a.EVENT_SSID = ass.EVENTID
WHERE NOT EXISTS (SELECT OID
FROM (SELECT tp1.OID
FROM TEMP_DATABASE.dbo.AssessmentTransferPart1 tp1
UNION ALL
SELECT tp2.OID
FROM TEMP_DATABASE.dbo.AssessmentTransferPart2 tp2) jcc
WHERE jcc.OID = C.OID)
Here is the link to the actual execution plan.
After adding an index on JccClientAssessmentProxy jcc WITH(NOLOCK,INDEX=IX_AssessmentProxy_GETMYCOORDCAREPLANVER)
here is the new execution plan.
The problematic part is:
,CAST(a.getQuestionId AS VARCHAR(300)) AS QUESTION_ID
FROM cnlPjccR_Report.dbo.JccClientAssessmentProxy AS a
WITH(NOLOCK,INDEX=IX_AssessmentProxy_getMyEventItem)
INNER JOIN ...
and the problem repeats itself in two of your UNION ALL subqueries.
Your execution plans shows expensive Key lookups.
The second method is to see if you can create a “covering index” that satisfies the entire query or at least eliminates the key lookups. A “covering index” is simply a non-clustered index that has all of the columns needed to either satisfy the entire query or in our case, eliminate the need for a key lookup operation. One challenge is to get a list of columns that are generating the key lookup. You can do this in SQL Server Management Studio (SSMS) by right-clicking on the key lookup operator, and then choosing Properties. Then find the Output List row in the Properties window, and click on the ellipsis button. This will open a window (see below) with a list of all of the columns that the key lookup is looking for. You can use this list to help you decide whether and how to create an index to “cover” the query or key lookup.
In your case following columns shoud be INCLUDEd to create a covering index:
[cnlPjccR_Report].[dbo].[JccClientAssessmentProxy].answer;
[cnlPjccR_Report].[dbo].[JccClientAssessmentProxy].answerNumber;
[cnlPjccR_Report].[dbo].[JccClientAssessmentProxy].getMyCoordCarePlanVer;
[cnlPjccR_Report].[dbo].[JccClientAssessmentProxy].getQuestionId;
[cnlPjccR_Report].[dbo].[JccClientAssessmentProxy].myAssessment;
[cnlPjccR_Report].[dbo].[JccClientAssessmentProxy].myJcAssessmentContext;
[cnlPjccR_Report].[dbo].[JccClientAssessmentProxy].questionNumber;
[cnlPjccR_Report].[dbo].[JccClientAssessmentProxy].questionText;
[cnlPjccR_Report].[dbo].[JccClientAssessmentProxy].templateName;
[cnlPjccR_Report].[dbo].[JccClientAssessmentProxy].templateVersion
Probably your index did not include getQuestionId column and that fact has created slower Key lookup or RID lookup in your query execution plan.
See also here or this answer.