Attached the actual plan from sql sentry plan explorer.

Attaching Actual Execution plan executed from SQL Sentry plan explorer.

Hi Joe Torre,

Thanks  for the  suggestion. I tried below but couldnt able to get the errornous record / errornous column getting logged.
Can anyone help tweaking the...

scenario 2 is a deliberate scenario and using some tsql method I wanted to check what rows and what column is causing the issue.

Thanks Chris.

Attaching table definitions and index information. "Table Structures.txt"

Please give some pointers on how to go about Indexing for the base query and optimization tips.

Chris. How can I tell that they were 16 threads spawned for parallelism operators in the execution plan?

Thanks Chris for taking a look.
Wanted to know what does the term residual I/O mean? Also, is there a problem going with Hash match?

Uploaded plan explorer generated query plan. IT should have the table diagram n table structure. Appreciate if any inputs from it. Tomorrow I ll try to get the table definitions...

Thanks Jeff and Steve. Bang on! IT was security issue.

Not able to figure out if it is a security issue or it is picking from some cache. I know its pretty hard to repro such issue but if any...

But Sir, the Always on AG Listener is also meant for the same purpose then why CNAME record to be created separately. Correct If I am wrong.