We had to set up Management Data Warehouse (MDW) to monitor. I was able to trace the SQL Code that was causing the blocking by drilling down the Server...

Thanks. I actually found it in our MDW report when drilling down on Locks.

Thank you.

Yes, that is one of the options I will test in our mock environment. Also going to test adding new columns, running a crawler to update the new columns...

Thanks. That may be one of the cases but it doesn't count for the high % of NULL Query_Plans.

The fix was to basically Add back the Article and than drop it again and it cleared the flag that was on the table.

The table was removed from Replication and it won't be added back. I am still not sure why the flag was not changed.

Thanks, I will review from the link.

Thank you for your reply. On one of the identified poor performing procs, With Recompile' was add to see if proc performance would improve. It still returned slowly....

Thanks.

I think I am going to use the File Watcher component. Thanks.

Also, you still would need to set up trustworthiness if you use sys.dm_exec_sessions instead of sysprocesses.

But will use this instead of sysprocesses.

Thanks for your help. I did have to add one additional step.

1. ALTER AUTHORIZATION ON DATABASE::dbname...

Thanks.

Here is what it looks like if I modify a test proc in SQL Server Management Studio I just created for this example:

As you can see it automatically throws in a an...