SQL Server Memory related Query's

1. /**General Info about Memory Status (SQL 2008 onwards) **/

SELECT total_physical_memory_kb, available_physical_memory_kb,
       total_page_file_kb, available_page_file_kb,
FROM sys.dm_os_sys_memory;

2. /**General Info about physical \ Virtual memory low and lock pages (SQL 2008 onwards) **/

            physical_memory_in_use_kb/1024 as Memory_used_MB,
            locked_page_allocations_kb/1024 as Memory_locked_page_MB,
    available_commit_limit_kb/1024 as available_commit_limit_MB,
FROM sys.dm_os_process_memory;

-- If Physical memory is low ,process_physical_memory_low will be non zero value
-- If Virtual memory is low ,process_virtual_memory_low  will be non zero value
-- If locked pages is enabled then
-- Page fault count since server started

3. /** Page Life Expectancy (PLE) value for default instance, for named change object name (SQL 2005 onwards) **/

-- MSSQL$<instance >:Buffer Manager\Page Life Expectancy
-- MSSQL$<instance >:Memory Manager\Target Server Memory (KB)

SELECT cntr_value AS [Page Life Expectancy]
FROM sys.dm_os_performance_counters
WHERE OBJECT_NAME = N'SQLServer:Buffer Manager' -- Modify this if you have named instances
AND counter_name = N'Page life expectancy';

-- PLE is a good measurement of memory pressure.
-- Higher PLE is better. Below 300 for 4 GB Ram is generally bad.
-- Watch the trend, not the absolute value.

4. /**Get total buffer usage by database for current instance (SQL 2005 Onward) **/

SELECT DB_NAME(database_id) AS [Database Name],
COUNT(*) * 8/1024.0 AS [Cached Size (MB)]
FROM sys.dm_os_buffer_descriptors
WHERE database_id > 4 -- system databases
AND database_id <> 32767 -- ResourceDB
GROUP BY DB_NAME(database_id)
ORDER BY [Cached Size (MB)] DESC;

-- Helps determine which databases are using the most memory on an instance
-- This might run longer on a busy system, as its resource intensive query

5. /**Memory Clerk Usage instance level**/

--(SQL 2005 Onwards)
SELECT TOP(20) [type], [name], SUM(pages_kb) AS [SPA Mem, Kb]
FROM sys.dm_os_memory_clerks
GROUP BY [type], [name] 
ORDER BY SUM(pages_kb) DESC;

-- Look for high value for CACHESTORE_SQLCP (Ad-hoc query plans)

--Few of explanations are

SQL Plans
These are cached SQL statements or batches that aren't in stored procedures, functions and triggers
Object Plans
These are compiled Plans for stored procedures, functions and triggers
Algebrizer Trees
An Algebrizer tree is the parsed SQL text that resolves the table and column names

6. /**Find single-use, ad-hoc queries that are bloating the plan cache */

SELECT TOP(100) [text], cp.size_in_bytes/1024 as Size_KB
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
WHERE cp.cacheobjtype = N'Compiled Plan'
AND cp.objtype = N'Adhoc'
AND cp.usecounts = 1
ORDER BY cp.size_in_bytes DESC;

-- Gives you the text and size of single-use ad-hoc queries that waste space in the plan cache
-- Enabling 'optimize for ad hoc workloads' for the instance can help (SQL Server 2008 and 2008 R2 only)
-- Enabling forced parameterization for the database can help, but test first!

7. /**Breaks down buffers used by current database by object (table, index) in the buffer cache**/

-- Database level queries (switch to your database)
-- (SQL 2008 onwards)
SELECT OBJECT_NAME(p.[object_id]) AS [ObjectName],
p.index_id, COUNT(*)/128 AS [Buffer size(MB)],  COUNT(*) AS [BufferCount],
p.data_compression_desc AS [CompressionType]
FROM sys.allocation_units AS a
INNER JOIN sys.dm_os_buffer_descriptors AS b
ON a.allocation_unit_id = b.allocation_unit_id
INNER JOIN sys.partitions AS p
ON a.container_id = p.hobt_id
WHERE b.database_id = CONVERT(int,DB_ID())
AND p.[object_id] > 100
GROUP BY p.[object_id], p.index_id, p.data_compression_desc
ORDER BY [BufferCount] DESC;

-- This might take some time on busy system, Performance intensive query

8. /**Top Cached SPs by Total Logical Reads and Logical reads relate to memory pressure (SQL 2008 onwards) **/

SELECT TOP(25) p.name AS [SP Name], qs.total_logical_reads AS [TotalLogicalReads],
qs.total_logical_reads/qs.execution_count AS [AvgLogicalReads],qs.execution_count,
ISNULL(qs.execution_count/DATEDIFF(Second, qs.cached_time, GETDATE()), 0) AS [Calls/Second],
qs.total_elapsed_time, qs.total_elapsed_time/qs.execution_count
AS [avg_elapsed_time], qs.cached_time
FROM sys.procedures AS p
INNER JOIN sys.dm_exec_procedure_stats AS qs
ON p.[object_id] = qs.[object_id]
WHERE qs.database_id = DB_ID()
ORDER BY qs.total_logical_reads DESC;

-- This helps you find the most expensive cached stored procedures from a memory perspective
-- You should look at this if you see signs of memory pressure

No comments:

Post a Comment