Date: Thu, 28 Mar 2024 11:13:08 +0000 (UTC)
Message-ID: <1544109798.67865.1711624388810@ip-10-0-1-26.ec2.internal>
Subject: Exported From Confluence
MIME-Version: 1.0
Content-Type: multipart/related;
boundary="----=_Part_67864_203474764.1711624388808"
------=_Part_67864_203474764.1711624388808
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: quoted-printable
Content-Location: file:///C:/exported.html
Host Memory Usage (Percent) alert
Host Memory Usage (Percent) alert
The Host Memory Usage (Percent) alert provides the percent of availa=
ble memory used by the host server hosting the virtual machine on whic=
h the monitored SQL Server resides. This alert is enabled by default a=
nd is available only on instances hosted on virtual machines enabled for VM=
monitoring.
Reduce your host memory usage
If thi=
s 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 t=
he following solutions:
- If yo=
ur site makes use of extended stored procedures that are infrequently calle=
d, then after calling them, issue a
DBCC DLLNAME (FREE)
. Once =
an extended stored procedure is called, it remains in memory until SQL Serv=
er is shut down even if it is never again called, which then wastes availab=
le memory. A DBCC dllname (FREE)
releases that memory for use =
by both the procedure cache and Data Pages, which has a significant positiv=
e effect on both the Procedure Cache Hit Ratio and the Buffer Cache Hit Rat=
io. Execute sp_helpextendedproc
to view the extended stored pr=
ocedures currently loaded in memory by SQL Server.
- As ea=
ch SQL Server lock requires 96 bytes of memory, the granting of lock space =
is done at the expense of Data Pages and Procedure Cache Pages. To maintain=
proper system performance and throughput, keep the number of locks to a mi=
nimum by:
- Where=
ver possible, using the
(NOLOCK)
optimizer hint or SET T=
RANSACTION ISOLATION LEVEL READ UNCOMMITTED
on select statements as =
this neither issues any shared locks on the data it reads nor honors any ex=
clusive locks set by other transactions.
- When =
updating all rows in a table with more than 50 rows, using the
TABLOC=
KX
table hint. This table hint prevents SQL Server from initially ta=
king exclusive row locks, granting many of these locks, and then escalating=
them to an exclusive table lock.
- When =
deleting all rows in any table, using the TRUNCATE TABLE statement instead =
of the DELETE statement as fewer locks and other system resources are consu=
med in the process.
- Reduc=
ing the time that a lock is held by:
- Performing as much work as possible befo=
re the transaction performs its first update, delete, or insert. For exampl=
e, add any necessary SELECT statements.
- Grouping all UPDATES, DELETES, and INSER=
TS as closely as possible within a transaction with as few SELECTS as possi=
ble separating them.
- Committing the transaction as soon as po=
ssible after the final DML statement.
- Avoiding any stops for user input once t=
he transaction begins. Be sure to gather all user inputs before the transac=
tion starts.
- Allowing SQL Server to consume more=
of the available memory, making sure that OS Paging does not increase.
- Add m=
ore physical memory (RAM) to the computer.
- If th=
e computer is running multiple instances of SQL Server, then consider placi=
ng each instance on a separate physical computer.
- If th=
e computer is running other memory-intensive applications, such as IIS or E=
xchange, then consider moving each instance to a separate physical computer=
.
- Limit=
SQL Server computers to performing only SQL Server work. Stop any unnecess=
ary programs, such as allowing the computer to act as either a primary or b=
ackup domain controller.
<=
/span>
------=_Part_67864_203474764.1711624388808--