Changes for page sql-performance

Last modified by Rudi Marais on 2023/05/22 23:50
From version 1.1 >
edited by Rudi Marais
on 2022/02/04 13:36
To version < 3.4 >
edited by Rudi Marais
on 2023/05/22 23:50
>
Change comment: Renamed from xwiki:Technical Documentation.analysis.sql.sql-performance.WebHome

Summary

Details

Page properties
Parent
... ... @@ -1,1 +1,1 @@
1 -Technical Documentation.analysis.WebHome
1 +xwiki:technical-documentation.analysis.sql.WebHome
Content
... ... @@ -1,0 +1,6 @@
1 +
2 +
3 +
4 +[[GitHub - BrentOzarULTD/SQL-Server-First-Responder-Kit: sp_Blitz, sp_BlitzCache, sp_BlitzFirst, sp_BlitzIndex, and other SQL Server scripts for health checks and performance tuning.>>url:https://github.com/BrentOzarULTD/SQL-Server-First-Responder-Kit]]
5 +
6 +
hammerDbBaseline.xml
Author
... ... @@ -1,0 +1,1 @@
1 +XWiki.rudim
Size
... ... @@ -1,0 +1,1 @@
1 +14.6 KB
Content
... ... @@ -1,0 +1,232 @@
1 +<?xml version="1.0" encoding="UTF-8"?>
2 +<?Copyright (c) Microsoft Corporation. All rights reserved.?>
3 +<DataCollectorSet>
4 +<Name>PAL_Microsoft_SQL_Server_2014</Name>
5 +<DisplayName>PAL_Microsoft_SQL_Server_2014</DisplayName>
6 +<Description>PAL_Microsoft_SQL_Server_2014</Description>
7 +<Keyword>CPU</Keyword>
8 +<Keyword>Memory</Keyword>
9 +<Keyword>Disk</Keyword>
10 +<Keyword>Network</Keyword>
11 +<Keyword>Performance</Keyword>
12 +<RootPath>%systemdrive%\perflogs\System\Performance</RootPath>
13 +<SubdirectoryFormat>3</SubdirectoryFormat>
14 +<SubdirectoryFormatPattern>yyyyMMdd\-NNNNNN</SubdirectoryFormatPattern>
15 +
16 +<PerformanceCounterDataCollector>
17 + <Counter>\Cache\Dirty Pages</Counter>
18 + <Counter>\Cache\Lazy Write Flushes/sec</Counter>
19 + <Counter>\LogicalDisk(*)\% Free Space</Counter>
20 + <Counter>\LogicalDisk(*)\% Idle Time</Counter>
21 + <Counter>\LogicalDisk(*)\Avg. Disk Bytes/Read</Counter>
22 + <Counter>\LogicalDisk(*)\Avg. Disk Bytes/Transfer</Counter>
23 + <Counter>\LogicalDisk(*)\Avg. Disk Bytes/Write</Counter>
24 + <Counter>\LogicalDisk(*)\Avg. Disk Queue Length</Counter>
25 + <Counter>\LogicalDisk(*)\Avg. Disk sec/Read</Counter>
26 + <Counter>\LogicalDisk(*)\Avg. Disk sec/Transfer</Counter>
27 + <Counter>\LogicalDisk(*)\Avg. Disk sec/Write</Counter>
28 + <Counter>\LogicalDisk(*)\Current Disk Queue Length</Counter>
29 + <Counter>\LogicalDisk(*)\Disk Bytes/sec</Counter>
30 + <Counter>\LogicalDisk(*)\Disk Reads/sec</Counter>
31 + <Counter>\LogicalDisk(*)\Disk Transfers/sec</Counter>
32 + <Counter>\LogicalDisk(*)\Disk Writes/sec</Counter>
33 + <Counter>\LogicalDisk(*)\Free Megabytes</Counter>
34 + <Counter>\Memory\% Committed Bytes In Use</Counter>
35 + <Counter>\Memory\Available MBytes</Counter>
36 + <Counter>\Memory\Commit Limit</Counter>
37 + <Counter>\Memory\Committed Bytes</Counter>
38 + <Counter>\Memory\Free &amp; Zero Page List Bytes</Counter>
39 + <Counter>\Memory\Free System Page Table Entries</Counter>
40 + <Counter>\Memory\Long-Term Average Standby Cache Lifetime (s)</Counter>
41 + <Counter>\Memory\Pages Input/sec</Counter>
42 + <Counter>\Memory\Pages Output/sec</Counter>
43 + <Counter>\Memory\Pages/sec</Counter>
44 + <Counter>\Memory\Pool Nonpaged Bytes</Counter>
45 + <Counter>\Memory\Pool Paged Bytes</Counter>
46 + <Counter>\Memory\Pool Paged Resident Bytes</Counter>
47 + <Counter>\Memory\System Cache Resident Bytes</Counter>
48 + <Counter>\Memory\Transition Pages RePurposed/sec</Counter>
49 + <Counter>\MSRS 2011 Web Service\Cache Misses/Sec</Counter>
50 + <Counter>\MSRS 2011 Web Service\Report Requests</Counter>
51 + <Counter>\MSRS 2011 Web Service\Total Memory Cache Misses</Counter>
52 + <Counter>\MSRS 2011 Web Service\Total Requests</Counter>
53 + <Counter>\Network Inspection System\Average inspection latency (sec/bytes)</Counter>
54 + <Counter>\Network Interface(*)\Bytes Received/sec</Counter>
55 + <Counter>\Network Interface(*)\Bytes Sent/sec</Counter>
56 + <Counter>\Network Interface(*)\Bytes Total/sec</Counter>
57 + <Counter>\Network Interface(*)\Current Bandwidth</Counter>
58 + <Counter>\Network Interface(*)\Output Queue Length</Counter>
59 + <Counter>\Network Interface(*)\Packets Outbound Errors</Counter>
60 + <Counter>\Network Interface(*)\Packets Received/sec</Counter>
61 + <Counter>\Network Interface(*)\Packets Sent/sec</Counter>
62 + <Counter>\Network Interface(*)\Packets/sec</Counter>
63 + <Counter>\Paging File(*)\% Usage</Counter>
64 + <Counter>\PhysicalDisk(*)\Avg. Disk Queue Length</Counter>
65 + <Counter>\PhysicalDisk(*)\Avg. Disk sec/Read</Counter>
66 + <Counter>\PhysicalDisk(*)\Avg. Disk sec/Write</Counter>
67 + <Counter>\PhysicalDisk(*)\Current Disk Queue Length</Counter>
68 + <Counter>\PhysicalDisk(*)\Disk Bytes/sec</Counter>
69 + <Counter>\Process(*)\Handle Count</Counter>
70 + <Counter>\Process(*)\ID Process</Counter>
71 + <Counter>\Process(*)\IO Data Operations/sec</Counter>
72 + <Counter>\Process(*)\IO Other Operations/sec</Counter>
73 + <Counter>\Process(*)\IO Read Operations/sec</Counter>
74 + <Counter>\Process(*)\IO Write Operations/sec</Counter>
75 + <Counter>\Process(*)\Private Bytes</Counter>
76 + <Counter>\Process(*)\Thread Count</Counter>
77 + <Counter>\Process(*)\Virtual Bytes</Counter>
78 + <Counter>\Process(*)\Working Set</Counter>
79 + <Counter>\Process(sqlservr)\% Privileged Time</Counter>
80 + <Counter>\Process(sqlservr)\% Processor Time</Counter>
81 + <Counter>\Processor Information(*)\% DPC Time</Counter>
82 + <Counter>\Processor Information(*)\% Interrupt Time</Counter>
83 + <Counter>\Processor Information(*)\% of Maximum Frequency</Counter>
84 + <Counter>\Processor Information(*)\% Privileged Time</Counter>
85 + <Counter>\Processor Information(*)\% Processor Time</Counter>
86 + <Counter>\Processor Information(*)\% User Time</Counter>
87 + <Counter>\Processor Information(*)\DPC Rate</Counter>
88 + <Counter>\Processor Information(*)\Parking Status</Counter>
89 + <Counter>\Processor(*)\% DPC Time</Counter>
90 + <Counter>\Processor(*)\% Interrupt Time</Counter>
91 + <Counter>\Processor(*)\% Privileged Time</Counter>
92 + <Counter>\Processor(*)\% Processor Time</Counter>
93 + <Counter>\Processor(*)\% User Time</Counter>
94 + <Counter>\Processor(*)\DPC Rate</Counter>
95 + <Counter>\ReportServer:Service\Errors Total</Counter>
96 + <Counter>\ReportServer:Service\Errors/sec</Counter>
97 + <Counter>\ReportServer:Service\Memory Pressure State</Counter>
98 + <Counter>\ReportServer:Service\Memory Shrink Amount</Counter>
99 + <Counter>\ReportServer:Service\Memory Shrink Notifications/sec</Counter>
100 + <Counter>\Server\Pool Nonpaged Failures</Counter>
101 + <Counter>\Server\Pool Paged Failures</Counter>
102 + <Counter>\SQLAgent:Jobs\Active jobs</Counter>
103 + <Counter>\SQLAgent:Jobs\Failed jobs</Counter>
104 + <Counter>\SQLAgent:Jobs\Job success rate</Counter>
105 + <Counter>\SQLAgent:Jobs\Successful jobs</Counter>
106 + <Counter>\SQLAgent:JobSteps\Active steps</Counter>
107 + <Counter>\SQLAgent:JobSteps\Total step retries</Counter>
108 + <Counter>\SQLServer:Access Methods\Forwarded Records/sec</Counter>
109 + <Counter>\SQLServer:Access Methods\FreeSpace Scans/sec</Counter>
110 + <Counter>\SQLServer:Access Methods\Full Scans/sec</Counter>
111 + <Counter>\SQLServer:Access Methods\Index Searches/sec</Counter>
112 + <Counter>\SQLServer:Access Methods\Page Splits/sec</Counter>
113 + <Counter>\SQLServer:Access Methods\Scan Point Revalidations/sec</Counter>
114 + <Counter>\SQLServer:Access Methods\Table Lock Escalations/sec</Counter>
115 + <Counter>\SQLServer:Access Methods\Workfiles Created/sec</Counter>
116 + <Counter>\SQLServer:Access Methods\Worktables Created/sec</Counter>
117 + <Counter>\SQLServer:Access Methods\Worktables From Cache Ratio</Counter>
118 + <Counter>\SQLServer:Availability Replica(*)\Bytes Received from Replica/sec</Counter>
119 + <Counter>\SQLServer:Availability Replica(*)\Bytes Sent to Replica/sec</Counter>
120 + <Counter>\SQLServer:Availability Replica(*)\Bytes Sent to Transport/sec</Counter>
121 + <Counter>\SQLServer:Availability Replica(_Total)\Receives from Replica/sec</Counter>
122 + <Counter>\SQLServer:Availability Replica(_Total)\Resent Messages/sec</Counter>
123 + <Counter>\SQLServer:Availability Replica(_Total)\Sends to Replica/sec</Counter>
124 + <Counter>\SQLServer:Buffer Manager(*)\Extension page unreferenced time</Counter>
125 + <Counter>\SQLServer:Buffer Manager\Background writer pages/sec</Counter>
126 + <Counter>\SQLServer:Buffer Manager\Buffer cache hit ratio</Counter>
127 + <Counter>\SQLServer:Buffer Manager\Checkpoint pages/sec</Counter>
128 + <Counter>\SQLServer:Buffer Manager\Extension free pages</Counter>
129 + <Counter>\SQLServer:Buffer Manager\Extension outstanding IO counter</Counter>
130 + <Counter>\SQLServer:Buffer Manager\Free list stalls/sec</Counter>
131 + <Counter>\SQLServer:Buffer Manager\Free pages</Counter>
132 + <Counter>\SQLServer:Buffer Manager\Lazy writes/sec</Counter>
133 + <Counter>\SQLServer:Buffer Manager\Page life expectancy</Counter>
134 + <Counter>\SQLServer:Buffer Manager\Page lookups/sec</Counter>
135 + <Counter>\SQLServer:Buffer Manager\Page reads/sec</Counter>
136 + <Counter>\SQLServer:Buffer Manager\Page writes/sec</Counter>
137 + <Counter>\SQLServer:Buffer Manager\Readahead pages/sec</Counter>
138 + <Counter>\SQLServer:Buffer Manager\Target pages</Counter>
139 + <Counter>\SQLServer:Buffer Node(*)\Database pages</Counter>
140 + <Counter>\SQLServer:Buffer Node(*)\Foreign pages</Counter>
141 + <Counter>\SQLServer:Buffer Node(*)\Local node page lookups/sec</Counter>
142 + <Counter>\SQLServer:Buffer Node(*)\Page life expectancy</Counter>
143 + <Counter>\SQLServer:Buffer Node(*)\Remote node page lookups/sec</Counter>
144 + <Counter>\SQLServer:Database Replica(*)\Log Bytes Received/sec</Counter>
145 + <Counter>\SQLServer:Database Replica(*)\Mirrored Write Transactions/sec</Counter>
146 + <Counter>\SQLServer:Database Replica(*)\Recovery Queue</Counter>
147 + <Counter>\SQLServer:Database Replica(_Total)\Log remaining for undo</Counter>
148 + <Counter>\SQLServer:Database Replica(_Total)\Log Send Queue</Counter>
149 + <Counter>\SQLServer:Database Replica(_Total)\Redo blocked/sec</Counter>
150 + <Counter>\SQLServer:Database Replica(_Total)\Redo Bytes Remaining</Counter>
151 + <Counter>\SQLServer:Database Replica(_Total)\Redone Bytes/sec</Counter>
152 + <Counter>\SQLServer:Database Replica(_Total)\Total Log requiring undo</Counter>
153 + <Counter>\SQLServer:Database Replica(_Total)\Transaction Delay</Counter>
154 + <Counter>\SQLServer:Databases(*)\Active Transactions</Counter>
155 + <Counter>\SQLServer:Databases(*)\Backup/Restore Throughput/sec</Counter>
156 + <Counter>\SQLServer:Databases(*)\Bulk Copy Throughput/sec</Counter>
157 + <Counter>\SQLServer:Databases(*)\Data File(s) Size (KB)</Counter>
158 + <Counter>\SQLServer:Databases(*)\Log Bytes Flushed/sec</Counter>
159 + <Counter>\SQLServer:Databases(*)\Log File(s) Size (KB)</Counter>
160 + <Counter>\SQLServer:Databases(*)\Log File(s) Used Size (KB)</Counter>
161 + <Counter>\SQLServer:Databases(*)\Log Flush Wait Time</Counter>
162 + <Counter>\SQLServer:Databases(*)\Log Flush Waits/sec</Counter>
163 + <Counter>\SQLServer:Databases(*)\Log Flushes/sec</Counter>
164 + <Counter>\SQLServer:Databases(*)\Log Growths</Counter>
165 + <Counter>\SQLServer:Databases(*)\Log Shrinks</Counter>
166 + <Counter>\SQLServer:Databases(*)\Log Truncations</Counter>
167 + <Counter>\SQLServer:Databases(*)\Percent Log Used</Counter>
168 + <Counter>\SQLServer:Deprecated Features(*)\Usage</Counter>
169 + <Counter>\SQLServer:General Statistics\Active Temp Tables</Counter>
170 + <Counter>\SQLServer:General Statistics\Logins/sec</Counter>
171 + <Counter>\SQLServer:General Statistics\Logouts/sec</Counter>
172 + <Counter>\SQLServer:General Statistics\Temp Tables Creation Rate</Counter>
173 + <Counter>\SQLServer:General Statistics\Temp Tables For Destruction</Counter>
174 + <Counter>\SQLServer:General Statistics\User Connections</Counter>
175 + <Counter>\SQLServer:Latches\Latch Waits/sec</Counter>
176 + <Counter>\SQLServer:Latches\Total Latch Wait Time (ms)</Counter>
177 + <Counter>\SQLServer:Locks(*)\Average Wait Time (ms)</Counter>
178 + <Counter>\SQLServer:Locks(*)\Lock Requests/sec</Counter>
179 + <Counter>\SQLServer:Locks(*)\Lock Timeouts/sec</Counter>
180 + <Counter>\SQLServer:Locks(*)\Lock Wait Time (ms)</Counter>
181 + <Counter>\SQLServer:Locks(*)\Lock Waits/sec</Counter>
182 + <Counter>\SQLServer:Locks(*)\Number of Deadlocks/sec</Counter>
183 + <Counter>\SQLServer:Memory Manager\Granted Workspace Memory (KB)</Counter>
184 + <Counter>\SQLServer:Memory Manager\Maximum Workspace Memory (KB)</Counter>
185 + <Counter>\SQLServer:Memory Manager\Memory Grants Outstanding</Counter>
186 + <Counter>\SQLServer:Memory Manager\Memory Grants Pending</Counter>
187 + <Counter>\SQLServer:Memory Manager\Optimizer Memory (KB)</Counter>
188 + <Counter>\SQLServer:Memory Manager\Stolen Server Memory (KB)</Counter>
189 + <Counter>\SQLServer:Memory Manager\Target Server Memory (KB)</Counter>
190 + <Counter>\SQLServer:Memory Manager\Target Server Memory(KB)</Counter>
191 + <Counter>\SQLServer:Memory Manager\Total Server Memory (KB)</Counter>
192 + <Counter>\SQLServer:Memory Node(*)\Database Node Memory (KB)</Counter>
193 + <Counter>\SQLServer:Memory Node(*)\Foreign Node Memory (KB)</Counter>
194 + <Counter>\SQLServer:Memory Node(*)\Stolen Node Memory (KB)</Counter>
195 + <Counter>\SQLServer:Memory Node(*)\Target Node Memory (KB)</Counter>
196 + <Counter>\SQLServer:Memory Node(000)\Total Node Memory (KB)</Counter>
197 + <Counter>\SQLServer:Plan Cache(*)\Cache Hit Ratio</Counter>
198 + <Counter>\SQLServer:Resource Pool Stats(*)\CPU usage %</Counter>
199 + <Counter>\SQLServer:Resource Pool Stats(*)\Max memory (KB)</Counter>
200 + <Counter>\SQLServer:Resource Pool Stats(*)\Target memory (KB)</Counter>
201 + <Counter>\SQLServer:Resource Pool Stats(*)\Used memory (KB)</Counter>
202 + <Counter>\SQLServer:SQL Errors(*)\Errors/sec</Counter>
203 + <Counter>\SQLServer:SQL Statistics\Auto-Param Attempts/sec</Counter>
204 + <Counter>\SQLServer:SQL Statistics\Batch Requests/sec</Counter>
205 + <Counter>\SQLServer:SQL Statistics\Failed Auto-Params/sec</Counter>
206 + <Counter>\SQLServer:SQL Statistics\Safe Auto-Params/sec</Counter>
207 + <Counter>\SQLServer:SQL Statistics\SQL Attention rate</Counter>
208 + <Counter>\SQLServer:SQL Statistics\SQL Compilations/sec</Counter>
209 + <Counter>\SQLServer:SQL Statistics\SQL Re-Compilations/sec</Counter>
210 + <Counter>\SQLServer:SQL Statistics\Unsafe Auto-Params/sec</Counter>
211 + <Counter>\SQLServer:Transactions\Free Space in tempdb (KB)</Counter>
212 + <Counter>\SQLServer:Transactions\Longest Transaction Running Time</Counter>
213 + <Counter>\SQLServer:Transactions\NonSnapshot Version Transactions</Counter>
214 + <Counter>\SQLServer:Transactions\Snapshot Transactions</Counter>
215 + <Counter>\SQLServer:Transactions\Transactions</Counter>
216 + <Counter>\SQLServer:Transactions\Version Cleanup rate (KB/s)</Counter>
217 + <Counter>\SQLServer:Transactions\Version Generation rate (KB/s)</Counter>
218 + <Counter>\SQLServer:Transactions\Version Store Size (KB)</Counter>
219 + <Counter>\SQLServer:User Settable(*)\Query</Counter>
220 + <Counter>\SQLServer:Workload Group Stats(*)\Active parallel threads</Counter>
221 + <Counter>\SQLServer:Workload Group Stats(*)\Active requests</Counter>
222 + <Counter>\SQLServer:Workload Group Stats(*)\CPU usage %</Counter>
223 + <Counter>\SQLServer:Workload Group Stats(*)\Queued requests</Counter>
224 + <Counter>\SQLServer:Workload Group Stats(*)\Reduced memory grants/sec</Counter>
225 + <Counter>\SQLServer:Workload Group Stats(*)\Requests completed/sec</Counter>
226 + <Counter>\SQLServer:Workload Group Stats(*)\Suboptimal plans/sec</Counter>
227 + <Counter>\System\Context Switches/sec</Counter>
228 + <Counter>\System\Processor Queue Length</Counter>
229 + <Counter>\System\System Calls/sec</Counter>
230 + <Counter>\TCPv4\Connection Failures</Counter>
231 +</PerformanceCounterDataCollector>
232 +</DataCollectorSet>

Applications

Navigation

Need help?

If you need help with XWiki you can contact: