One more question:

Is substring() not compatible with SQL Server 2014 ?

And I found the root cause for this issue.

On 30th june somebody has changed the Compatibility to 120 on production. After that only SP is not working.

Thank You Very Much...

Grant Fritchey (7/7/2016)

---

There are differences, and this could account for everything. The first plan I looked at, 1027.sqlplan, is using the 70 Cardinality Estimation Engine. That means that database is...

These both test and prod databases have been migrated from SQL Server 2008 to SQL Server 2014.

Both test and prod databases are having same data, tables, data types and indexes...

Do we have any mechanism to trace the changes in DB.

This query was working fine previously. Since last 4 days only issue started.

Not sure what happend.

Can you please let me know the reasons.

But I did not understand why substring made all the noise.

Then how it worked after changing it to like 'UD1\%'

Thank You Chris. You and Me are on same page. 🙂

I was dividing the query and trying to execut it.

After changing the logic to

AND NL.NetworkLogonId LIKE 'UD1\%'

query completed with...

Please find the attachement with MAXDOP 1

ChrisM@Work (7/5/2016)

---

Can you please add OPTION(MAXDOP 1) to the...

TheSQLGuru (7/4/2016)

---

sp_whoisactive has an option to show you the executing query plan. It is a MARVELOUS FREE tool you really should read up on. SQLBlog.com has a 30-day series on...

Please find the attachment.

1027.sqlplan is test server where query executing with in 3 sec.

1048.sqlplan is production where we have the issue.

I have executed updatestats and updateusage on both the databases.

One observation is it is blocking it self.

I can see n number of processes with same SPID. In that where ever...

TheSQLGuru (7/4/2016)

---

Use sp_whoisactive to determine how much work has been done, query plan, tempdb usage, blocking, etc. You can also run it in differential mode and see how much work...

I have already checked in default(Old also) trace files, but could not find the information. Seems it did not recorded this event.