lunes, 6 de febrero de 2017

http://dba.stackexchange.com/questions/20973/quick-look-at-how-much-ram-is-allocated-to-sql-server

You could NEVER, EVER trust Task Manager to tell you how much memory SQL Server is using (maybe you are remembering a 32-bit system with a very small amount of memory). Stop using Task Manager for this, period. Use the performance counter - you can also query the performance counter using DMVs:
SELECT object_name, cntr_value 
  FROM sys.dm_os_performance_counters
  WHERE counter_name = 'Total Server Memory (KB)';
You could save that as a query shortcut in Tools > Options > Environment > Keyboard > Query Shortcuts, and get accurate results in a query window much faster than getting inaccurate results from Task Manager.
You can also check for memory pressure (and whether you can do anything about it) using these queries:
SELECT object_name, cntr_value
  FROM sys.dm_os_performance_counters
  WHERE counter_name IN ('Total Server Memory (KB)', 'Target Server Memory (KB)');

-- SQL Server 2012:
SELECT physical_memory_kb FROM sys.dm_os_sys_info;

-- Prior versions:
SELECT physical_memory_in_bytes FROM sys.dm_os_sys_info;

EXEC sp_configure 'max server memory';
 
 
@AaronBertrand's answer is great. Here's a variation with a bit more info from here
SELECT
(physical_memory_in_use_kb/1024) AS Memory_usedby_Sqlserver_MB,
(locked_page_allocations_kb/1024) AS Locked_pages_used_Sqlserver_MB,
(total_virtual_address_space_kb/1024) AS Total_VAS_in_MB,
process_physical_memory_low,
process_virtual_memory_low
FROM sys.dm_os_process_memory;
 
https://msdn.microsoft.com/en-us/library/ms190219.aspx
http://blog.jmacoe.com/gestion_ti/base_de_datos/servidor-sql-server-lento-pero-cpu-normal/ 
https://www.mssqltips.com/sqlservertip/2304/how-to-identify-microsoft-sql-server-memory-bottlenecks/
http://dba.stackexchange.com/questions/69741/all-queries-on-the-server-have-slowed-down-cpu-is-normal-plenty-of-free-memory
https://www.simple-talk.com/sql/performance/how-to-identify-slow-running-queries-with-sql-profiler/
https://www.brentozar.com/archive/2015/11/why-is-sql-server-slow-sometimes-but-only-sometimes/ 

 

No hay comentarios:

Publicar un comentario