Repository navigation
Expand file tree
/
Copy pathSQLInstance.Memory.cs
More file actions
124 lines (117 loc) · 7.17 KB
/
Copy pathSQLInstance.Memory.cs
File metadata and controls
124 lines (117 loc) · 7.17 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
using System;
using System.Collections.Generic;
namespace Opserver.Data.SQL
{
public partial class SQLInstance
{
private Cache<List<SQLMemoryClerkSummaryInfo>> _memoryClerkSummary;
public Cache<List<SQLMemoryClerkSummaryInfo>> MemoryClerkSummary => _memoryClerkSummary ??= SqlCacheList<SQLMemoryClerkSummaryInfo>(RefreshInterval);
public class SQLMemoryClerkSummaryInfo : ISQLVersioned
{
Version IMinVersioned.MinVersion => SQLServerVersions.SQL2005.RTM;
SQLServerEditions ISQLVersioned.SupportedEditions => SQLServerEditions.All;
public string ClerkType { get; internal set; }
public long UsedBytes { get; internal set; }
public decimal UsedPercent { get; internal set; }
public string Name =>
ClerkType switch
{
"CACHESTORE_BROKERDSH" => "Service Broker Dialog Security Header Cache",
"CACHESTORE_BROKERKEK" => "Service Broker Key Exchange Key Cache",
"CACHESTORE_BROKERREADONLY" => "Service Broker (Read-Only)",
"CACHESTORE_BROKERRSB" => "Service Broker Null Remote Service Binding Cache",
"CACHESTORE_BROKERTBLACS" => "Broker dormant rowsets",
"CACHESTORE_BROKERTO" => "Service Broker Transmission Object Cache",
"CACHESTORE_BROKERUSERCERTLOOKUP" => "Service Broker user certificates lookup result cache",
"CACHESTORE_CLRPROC" => "CLR Procedure Cache",
"CACHESTORE_CLRUDTINFO" => "CLR UDT Info",
"CACHESTORE_COLUMNSTOREOBJECTPOOL" => "Column Store Object Pool",
"CACHESTORE_CONVPRI" => "Conversation Priority Cache",
"CACHESTORE_EVENTS" => "Event Notification Cache",
"CACHESTORE_FULLTEXTSTOPLIST" => "Full Text Stoplist Cache",
"CACHESTORE_NOTIF" => "Notification Store",
"CACHESTORE_OBJCP" => "Object Plans",
"CACHESTORE_PHDR" => "Bound Trees",
"CACHESTORE_SEARCHPROPERTYLIST" => "Search Property List Cache",
"CACHESTORE_SEHOBTCOLUMNATTRIBUTE" => "SE Shared Column Metadata Cache",
"CACHESTORE_SQLCP" => "SQL Plans",
"CACHESTORE_STACKFRAMES" => "SOS_StackFramesStore",
"CACHESTORE_SYSTEMROWSET" => "System Rowset Store",
"CACHESTORE_TEMPTABLES" => "Temporary Tables & Table Variables",
"CACHESTORE_VIEWDEFINITIONS" => "View Definition Cache",
"CACHESTORE_XML_SELECTIVE_DG" => "XML DB Cache (Selective)",
"CACHESTORE_XMLDBATTRIBUTE" => "XML DB Cache (Attribute)",
"CACHESTORE_XMLDBELEMENT" => "XML DB Cache (Element)",
"CACHESTORE_XMLDBTYPE" => "XML DB Cache (Type)",
"CACHESTORE_XPROC" => "Extended Stored Procedures",
"MEMORYCLERK_BHF" => "Best Hugging Friend (According to Brent Ozar)",
"MEMORYCLERK_FILETABLE" => "Memory Clerk (File Table)",
"MEMORYCLERK_FSCHUNKER" => "Memory Clerk (FS Chunker)",
"MEMORYCLERK_FULLTEXT" => "Full Text",
"MEMORYCLERK_FULLTEXT_SHMEM" => "Full-text IG",
"MEMORYCLERK_HADR" => "HADR",
"MEMORYCLERK_HOST" => "Host",
"MEMORYCLERK_LANGSVC" => "Language Service",
"MEMORYCLERK_LWC" => "Light Weight Cache",
"MEMORYCLERK_QSRANGEPREFETCH" => "QS Range Prefetch",
"MEMORYCLERK_SERIALIZATION" => "Serialization",
"MEMORYCLERK_SNI" => "SNI",
"MEMORYCLERK_SOSMEMMANAGER" => "SOS Memory Manager",
"MEMORYCLERK_SOSNODE" => "SOS Node",
"MEMORYCLERK_SOSOS" => "SOS Memory Clerk",
"MEMORYCLERK_SQLBUFFERPOOL" => "Buffer Pool",
"MEMORYCLERK_SQLCLR" => "CLR",
"MEMORYCLERK_SQLCLRASSEMBLY" => "CLR Assembly",
"MEMORYCLERK_SQLCONNECTIONPOOL" => "Connection Pool",
"MEMORYCLERK_SQLGENERAL" => "General",
"MEMORYCLERK_SQLHTTP" => "HTTP",
"MEMORYCLERK_SQLLOGPOOL" => "Log Pool",
"MEMORYCLERK_SQLOPTIMIZER" => "SQL Optimizer",
"MEMORYCLERK_SQLQERESERVATIONS" => "SQL Reservations",
"MEMORYCLERK_SQLQUERYCOMPILE" => "SQL Query Compile",
"MEMORYCLERK_SQLQUERYEXEC" => "SQL Query Exec",
"MEMORYCLERK_SQLQUERYPLAN" => "SQL Query Plan",
"MEMORYCLERK_SQLSERVICEBROKER" => "SQL Service Broker",
"MEMORYCLERK_SQLSERVICEBROKERTRANSPORT" => "Unified Communication Stack",
"MEMORYCLERK_SQLSOAP" => "SQL SOAP",
"MEMORYCLERK_SQLSOAPSESSIONSTORE" => "SQL SOAP (Session Store)",
"MEMORYCLERK_SQLSTORENG" => "SQL Storage Engine",
"MEMORYCLERK_SQLUTILITIES" => "SQL Utilities",
"MEMORYCLERK_SQLXML" => "SQL XML",
"MEMORYCLERK_SQLXP" => "SQL XP",
"MEMORYCLERK_TRACE_EVTNOTIF" => "Trace Event Notification",
"MEMORYCLERK_XE" => "XE Engine",
"MEMORYCLERK_XE_BUFFER" => "XE Buffer",
"MEMORYCLERK_XTP" => "XTP Processor",
"OBJECTSTORE_LBSS" => "Lbss Cache (Object Store)",
"OBJECTSTORE_LOCK_MANAGER" => "Lock Manager (Object Store)",
"OBJECTSTORE_SECAUDIT_EVENT_BUFFER" => "Audit Event Buffer (Object Store)",
"OBJECTSTORE_SERVICE_BROKER" => "Service Broker (Object Store)",
"OBJECTSTORE_SNI_PACKET" => "SNI Packet (Object Store)",
"OBJECTSTORE_XACT_CACHE" => "Transactions Cache (Object Store)",
"USERSTORE_DBMETADATA" => "DB Metadata (User Store)",
"USERSTORE_OBJPERM" => "Object Permissions (User Store)",
"USERSTORE_SCHEMAMGR" => "Schema Manager (User Store)",
"USERSTORE_SXC" => "SXC (User Store)",
"USERSTORE_TOKENPERM" => "Token Permissions (User Store)",
_ => "Other (" + ClerkType + ")",
};
public bool IsBufferPool => ClerkType == "MEMORYCLERK_SQLBUFFERPOOL";
public bool IsPlanCache => ClerkType == "CACHESTORE_SQLCP";
internal const string FetchSQL = @"
Select [type] ClerkType,
Sum(pages_kb * 1024) AS UsedBytes,
Cast(100 * Sum(pages_kb)*1.0/(Select Sum(pages_kb) From sys.dm_os_memory_clerks) as Decimal(7, 4)) UsedPercent
From sys.dm_os_memory_clerks
Where pages_kb > 0
Group By [type]
Order By Sum(pages_kb) Desc";
public string GetFetchSQL(in SQLServerEngine e)
{
if (e.Version < SQLServerVersions.SQL2012.RTM)
return FetchSQL.Replace("pages_kb", "(single_pages_kb + multi_pages_kb)");
return FetchSQL;
}
}
}
}