Memory Monitoring with Profiler

Set up a custom trace to monitor:


Execution Warnings

What it monitors: Before a query can be run it must have memory, If the server has no memory to give for the query then the Execution Warning event will be fired.

What you want to see: No execution warnings happening at all.


Sort Warnings

What it monitors: When a query asks for a ORDER BY query. SQL Will try to run this in memory. If the query results are large the data might be written to TempDB instead of memory, This courses I/O which will slow down the query.


Server Memory change

What it Monitors: When the SQL Server increases or decreases by either 1mb or 5% of the maximum memory (Whichever is larger).

What you want to see: After the server has been running for a period you would hope that the memory does not change much. If it does it indicates that SQL Server is fighting with either another instance or the OS.