The SQL Server Memory Usage (Percent) alert provides the total server memory (Total Server Memory per sysperfinfo
) as a percent of total physical memory from WMI.
If this value is regularly over 80%, SQL Server needs more memory or needs to use the memory it has more efficiently. Consider implementing one or more of the following solutions:
DBCC DLLNAME (FREE)
. Once an extended stored procedure is called, it remains in memory until SQL Server is shut down even if it is never again called, which then wastes available memory. A DBCC dllname (FREE)
releases that memory for use by both the procedure cache and Data Pages, which has a significant positive effect on both the Procedure Cache Hit Ratio and the Buffer Cache Hit Ratio. Execute sp_helpextendedproc
to view the extended stored procedures currently loaded in memory by SQL Server. (NOLOCK)
optimizer hint or SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
on select statements as this neither issues any shared locks on the data it reads nor honors any exclusive locks set by other transactions.TABLOCKX
table hint. This table hint prevents SQL Server from initially taking exclusive row locks, granting many of these locks, and then escalating them to an exclusive table lock.To enable alerting when this metric is outside its established baseline, click the Baseline Thresholds Enabled (as percentage of baseline) check box in the Alert Configuration window. |
Idera Website | Products | Purchase | Support | Community | About Us | Resources | Legal |
---|