Memory dynamic Views.
Following DMV displays number of counters in the sqlserver instance.
Ex: the following query displays number of Logins\sec
FROM sys.dm_os_performance_counters AS dopc
WHERE dopc.object_name = ‘SQLServer:General Statistics’
AND dopc.counter_name = ‘Logins/sec’;
sys.dm_os_wait_stats displays accumulated view of thereads that are waiting on various resources. These are accumulated values since the sqlserver is restarted or counters reset.
Memory settings without restarting sql server instance
EXEC sp_configure ‘show advanced option’, 1;
exec sp_configure ‘min server memory (MB)’, 5120;
exec sp_configure ‘max server memory (MB)’, 10240;
RECONFIGURE WITH OVERRIDE;
Another way to check the configurations of the server from a view, but your not allowed to change here
select * from sys.configurations
Metrics Bench Marks.
Pages/ sec <50 is acceptable value.
Page faults/sec 0- 1000.
Pagefaults — soft pages + hard pages.
There are two types of page faults hard and soft page faults.
- Hard page faults occur when the requested page is not in the physical memory.
- Soft page faults occur when the requested page is in the memory, but cannot be accessed by the program as it is not on the right address, or is being accessed by another program
Pagefaults = softpages faults + hard page faults.
Buffer Cache Hit Ratio: it is a buffer pool, where most of the process read data pages.. 90> is good.
Page life exptancey
Checkpoint Pages/Sec = 30 higher means more memory pressure
Lazy Writes/Sec = 20 higher more Io issues.
While most of the memory within SQL Server is allocated to the buffer cache, there are a number of processes within
SQL Server that also can, and will, consume memory. These processes expose their memory allocations through this
DMO. You can use this to see what processes might be taking resources away from the buffer cache in the event you
have other indications of a memory bottleneck.
Sys.dm_os_memory_clerks(check internal; or external issue)
A memory clerk is the process that allocates memory within SQL Server. Looking at what these processes are up to
allows you to understand whether there are internal memory allocation issues going on within SQL Server that might
rob the procedure cache of needed memory. If the Performance Monitor counter for Private Bytes is high, you can
determine which parts of the system are being consumed through the DMV.
If you have a database using in-memory OLTP storage, you can use sys.dm_db_xtp_table_memory_stats to look
at the individual database objects. But if you want to look at the allocations of these objects across the entire instance,
you’ll need to use sys.dm_os_memory_clerks.
SELECT TYPE, SUM(single_pages_kb) InternalPressure,SUM(multi_pages_kb) ExtermalPressure
GROUP BY TYPE
ORDER BY SUM(single_pages_kb) DESC, SUM(multi_pages_kb) DESC