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,
system_memory_state_desc
FROM sys.dm_os_sys_memory;
2. /**General Info about physical \ Virtual memory low and lock pages (SQL 2008 onwards) **/
SELECT
physical_memory_in_use_kb/1024 as Memory_used_MB,
locked_page_allocations_kb/1024 as Memory_locked_page_MB,
page_fault_count,
memory_utilization_percentage,
available_commit_limit_kb/1024 as available_commit_limit_MB,
process_physical_memory_low,
process_virtual_memory_low
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
Cache
|
Type
|
Description
|
SQL Plans
|
CACHESTORE_SQLCP
|
These are cached SQL statements or batches that aren't in stored procedures, functions and triggers
|
Object Plans
|
CACHESTORE_OBJCP
|
These are compiled Plans for stored procedures, functions and triggers
|
Algebrizer Trees
|
CACHESTORE_PHDR
|
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