SQL 2014 XML template does not collect any SQL counters
Closed this issue · 12 comments
SQL 2014 template does not any SQL counters. It only collects Windows counters. The XML file seems incorrect. Regards.
Do you use a SQL Instance name? If so did you fill it in?
or @bulentozkir do you think this is all related to #26 ?
I am not using any named instance. Just using the default instance. Therefore I leave it empty.
I tested the default SQL 2014 XML template on several different machines. They don't match the naming of the SQL perfmon counters somehow. Therefore counter names need to be corrected for SQL 2014, SQL 2016, SQL 2017 etc.
\SQLAgent:Jobs\Active jobs
\SQLAgent:Jobs\Failed jobs
\SQLAgent:Jobs\Job success rate
\SQLAgent:Jobs\Successful jobs
\SQLAgent:JobSteps\Active steps
\SQLAgent:JobSteps\Total step retries
\SQLServer:Access Methods\Forwarded Records/sec
\SQLServer:Access Methods\FreeSpace Scans/sec
\SQLServer:Access Methods\Full Scans/sec
\SQLServer:Access Methods\Index Searches/sec
\SQLServer:Access Methods\Page Splits/sec
\SQLServer:Access Methods\Scan Point Revalidations/sec
\SQLServer:Access Methods\Table Lock Escalations/sec
\SQLServer:Access Methods\Workfiles Created/sec
\SQLServer:Access Methods\Worktables Created/sec
\SQLServer:Access Methods\Worktables From Cache Ratio
\SQLServer:Availability Replica()\Bytes Received from Replica/sec
\SQLServer:Availability Replica()\Bytes Sent to Replica/sec
\SQLServer:Availability Replica()\Bytes Sent to Transport/sec
\SQLServer:Availability Replica(_Total)\Receives from Replica/sec
\SQLServer:Availability Replica(_Total)\Resent Messages/sec
\SQLServer:Availability Replica(_Total)\Sends to Replica/sec
\SQLServer:Buffer Manager()\Extension page unreferenced time
\SQLServer:Buffer Manager\Background writer pages/sec
\SQLServer:Buffer Manager\Buffer cache hit ratio
\SQLServer:Buffer Manager\Checkpoint pages/sec
\SQLServer:Buffer Manager\Extension free pages
\SQLServer:Buffer Manager\Extension outstanding IO counter
\SQLServer:Buffer Manager\Free list stalls/sec
\SQLServer:Buffer Manager\Free pages
\SQLServer:Buffer Manager\Lazy writes/sec
\SQLServer:Buffer Manager\Page life expectancy
\SQLServer:Buffer Manager\Page lookups/sec
\SQLServer:Buffer Manager\Page reads/sec
\SQLServer:Buffer Manager\Page writes/sec
\SQLServer:Buffer Manager\Readahead pages/sec
\SQLServer:Buffer Manager\Target pages
\SQLServer:Buffer Node()\Database pages
\SQLServer:Buffer Node()\Foreign pages
\SQLServer:Buffer Node()\Local node page lookups/sec
\SQLServer:Buffer Node()\Page life expectancy
\SQLServer:Buffer Node()\Remote node page lookups/sec
\SQLServer:Database Replica()\Log Bytes Received/sec
\SQLServer:Database Replica()\Mirrored Write Transactions/sec
\SQLServer:Database Replica()\Recovery Queue
\SQLServer:Database Replica(_Total)\Log remaining for undo
\SQLServer:Database Replica(_Total)\Log Send Queue
\SQLServer:Database Replica(_Total)\Redo blocked/sec
\SQLServer:Database Replica(_Total)\Redo Bytes Remaining
\SQLServer:Database Replica(_Total)\Redone Bytes/sec
\SQLServer:Database Replica(_Total)\Total Log requiring undo
\SQLServer:Database Replica(_Total)\Transaction Delay
\SQLServer:Databases()\Active Transactions
\SQLServer:Databases()\Backup/Restore Throughput/sec
\SQLServer:Databases()\Bulk Copy Throughput/sec
\SQLServer:Databases()\Data File(s) Size (KB)
\SQLServer:Databases()\Log Bytes Flushed/sec
\SQLServer:Databases()\Log File(s) Size (KB)
\SQLServer:Databases()\Log File(s) Used Size (KB)
\SQLServer:Databases()\Log Flush Wait Time
\SQLServer:Databases()\Log Flush Waits/sec
\SQLServer:Databases()\Log Flushes/sec
\SQLServer:Databases()\Log Growths
\SQLServer:Databases()\Log Shrinks
\SQLServer:Databases()\Log Truncations
\SQLServer:Databases()\Percent Log Used
\SQLServer:Deprecated Features()\Usage
\SQLServer:General Statistics\Active Temp Tables
\SQLServer:General Statistics\Logins/sec
\SQLServer:General Statistics\Logouts/sec
\SQLServer:General Statistics\Temp Tables Creation Rate
\SQLServer:General Statistics\Temp Tables For Destruction
\SQLServer:General Statistics\User Connections
\SQLServer:Latches\Latch Waits/sec
\SQLServer:Latches\Total Latch Wait Time (ms)
\SQLServer:Locks()\Average Wait Time (ms)
\SQLServer:Locks()\Lock Requests/sec
\SQLServer:Locks()\Lock Timeouts/sec
\SQLServer:Locks()\Lock Wait Time (ms)
\SQLServer:Locks()\Lock Waits/sec
\SQLServer:Locks()\Number of Deadlocks/sec
\SQLServer:Memory Manager\Granted Workspace Memory (KB)
\SQLServer:Memory Manager\Maximum Workspace Memory (KB)
\SQLServer:Memory Manager\Memory Grants Outstanding
\SQLServer:Memory Manager\Memory Grants Pending
\SQLServer:Memory Manager\Optimizer Memory (KB)
\SQLServer:Memory Manager\Stolen Server Memory (KB)
\SQLServer:Memory Manager\Target Server Memory (KB)
\SQLServer:Memory Manager\Target Server Memory(KB)
\SQLServer:Memory Manager\Total Server Memory (KB)
\SQLServer:Memory Node()\Database Node Memory (KB)
\SQLServer:Memory Node()\Foreign Node Memory (KB)
\SQLServer:Memory Node()\Stolen Node Memory (KB)
\SQLServer:Memory Node()\Target Node Memory (KB)
\SQLServer:Memory Node(000)\Total Node Memory (KB)
\SQLServer:Plan Cache()\Cache Hit Ratio
\SQLServer:Resource Pool Stats()\CPU usage %
\SQLServer:Resource Pool Stats()\Max memory (KB)
\SQLServer:Resource Pool Stats()\Target memory (KB)
\SQLServer:Resource Pool Stats()\Used memory (KB)
\SQLServer:SQL Errors()\Errors/sec
\SQLServer:SQL Statistics\Auto-Param Attempts/sec
\SQLServer:SQL Statistics\Batch Requests/sec
\SQLServer:SQL Statistics\Failed Auto-Params/sec
\SQLServer:SQL Statistics\Safe Auto-Params/sec
\SQLServer:SQL Statistics\SQL Attention rate
\SQLServer:SQL Statistics\SQL Compilations/sec
\SQLServer:SQL Statistics\SQL Re-Compilations/sec
\SQLServer:SQL Statistics\Unsafe Auto-Params/sec
\SQLServer:Transactions\Free Space in tempdb (KB)
\SQLServer:Transactions\Longest Transaction Running Time
\SQLServer:Transactions\NonSnapshot Version Transactions
\SQLServer:Transactions\Snapshot Transactions
\SQLServer:Transactions\Transactions
\SQLServer:Transactions\Version Cleanup rate (KB/s)
\SQLServer:Transactions\Version Generation rate (KB/s)
\SQLServer:Transactions\Version Store Size (KB)
\SQLServer:User Settable()\Query
\SQLServer:Workload Group Stats()\Active parallel threads
\SQLServer:Workload Group Stats()\Active requests
\SQLServer:Workload Group Stats()\CPU usage %
\SQLServer:Workload Group Stats()\Queued requests
\SQLServer:Workload Group Stats()\Reduced memory grants/sec
\SQLServer:Workload Group Stats()\Requests completed/sec
\SQLServer:Workload Group Stats(*)\Suboptimal plans/sec
Ah I see. Ill check that out thanks.
Thanks, guys. I have a repro. I'll get this fixed. Sorry!
These counter paths must be updated in the SQL Server 2014 threshold file to be fixed. I have contacted the content owner and asked him to update/fix the threshold file.
Also, I am working on a new wizard feature that will walk you through collecting performance counters. It will produce a command line (batch), powershell commands (script), and/or a perfmon threshold file depending on what you want. In the meantime, as another work around, consider using the PAL Collector script at http://aka.ms/PalCollector
Fixed in 2.8.2, but the content owner still needs to fix the counter paths in the SQL 2014 threshold file.
Fixed in 2.8.2, but the content owner still needs to fix the counter paths in the SQL 2014 threshold file.
ut the content owner still needs to fix the counter paths in the SQL 2014 threshold file.
@clinthuffman has the original owner accepted this task or is it still unassigned?
Hi, I am using SQL Server 2014 PAL template (,xml) for SQL Server 2016 SP1. I noticed the following counters not shown after generating .blg file to .htm file thru PAL Wizard (v2.8.2).
Buffer Lazy Writes per second
PAge reads/sec
free list stalls/sec
I checked template thru PAL editor; these counters are enable however not shown in the report (.htm)
Please share your feedback...
