Databases Reference
In-Depth Information
Server. If you see Other Process CPU Utilization above 5% for a sustained period, you should inves-
tigate what else is using CPU on your database server.
After looking at your CPU utilization history, it is a good idea to see what is happening with the
physical memory at the operating-system level on your database server. You can do that using
the query shown in Listing 15-25.
LISTING 15-25:
Operating system memory information
-- Good basic information about OS memory amounts and state
SELECT total_physical_memory_kb, available_physical_memory_kb,
total_page_file_kb, available_page_file_kb,
system_memory_state_desc
FROM sys.dm_os_sys_memory WITH (NOLOCK) OPTION (RECOMPILE);
-- You want to see "Available physical memory is high"
-- This indicates that you are not under external memory pressure
This query tells you how much physical memory is in the server, how much physical memory is
available, how large the operating system page i le is, and how much space is available in the page
i le. It also signals whether the operating system is low on physical memory, which would mean that
SQL Server was under external memory pressure. It is rare to see the operating system signaling that
is under severe memory pressure, especially if you have set the max server memory (MB) instance-
level setting in SQL Server to an appropriate value that leaves enough memory available for the
operating system.
After looking at memory usage at the operating-system level, you are going to want to take a look
at what is happening with SQL Server's internal memory usage, which you can do using the query
shown in Listing 15-26.
LISTING 15-26:
SQL server memory information
-- SQL Server Process Address space info
--(shows whether locked pages is enabled, among other things)
SELECT physical_memory_in_use_kb,locked_page_allocations_kb,
page_fault_count, memory_utilization_percentage,
available_commit_limit_kb, process_physical_memory_low,
process_virtual_memory_low
FROM sys.dm_os_process_memory WITH (NOLOCK) OPTION (RECOMPILE);
-- You want to see 0 for process_physical_memory_low
-- You want to see 0 for process_virtual_memory_low
-- This indicates that you are not under internal memory pressure
This query tells you how much memory is actually being used by the SQL Server Database
Engine. This information is more reliable than what is displayed in Windows Task Manager. It also
tells you whether this SQL Server instance is using locked pages in memory. Finally, it indicates
whether the SQL Server process is signaling that it is low on physical or virtual memory.
Search WWH ::
Custom Search