Thanks for the replies Shafat & Grant. I have attached both the query execution plans.

We also figured out that the same query is throwing different execution plans in SQL Server 2012 and SQL Server 2014. Could be SQL Server Optimizer enhancements in 2014 causing this kind of behavior, we are not sure what is the exact cause here?

And also we have slightly tweaked the query by using DMVs instead of using system catalogs/tables, results were coming little earlier when comparing to the previous scenario. But still this is still an issue to be prone to have timeout. We are seeing risk here as well.

Any additional inputs from your end would be of real help.