sp_configure

A while back, I got a question about enabling SQL Server’s ‘Optimize for Adhoc Workloads’ setting. The gist of the question was whether or not enabling this setting might free up extra memory on their SQL Server instance.

What tools in SQL Server will notify you about blocking and help track the queries behind your toughest blocking and deadlocking problems?