Software agent modules classification

Status verification in SQL Server
The verification of the status of the SQL servers is done through the plugin Pandora_Plugin_SQL.ps1 and could be applied in the SQL specific technology, in a generic way (in the case of local instances) or adding different instance lists.

Generated modules:

– SQL Monitoring
– CPU_Busy – InstanceName, CPU_Timetick – InstanceName
– Idle Time – InstanceName, IO_Busy – InstanceName
– Input Packets Read – InstanceName, Input Packets Sent – InstanceName
– Instance Status – InstanceName, DatabaseName Status – InstanceName
– Packet Errors – InstanceName, Total Disk R/W Errors – InstanceName
– Total Disk Reads – InstanceName, Total Disk Writes – InstanceName
– Total Login Attempts – InstanceName

Verification of Performance Counters

The verification of performance counters is done through the Pandora_Plugin_PerfCounter.ps1 plugin and could be applied in different policies for different technologies, each of them with different counter lists depending on which we want to monitor in each of those technologies.

Next we are going to show the list of counters to monitor in the case of SQL Server:
– SQL Server Monitoring
– \SQLServer:Access Methods\Full Scans/sec
– \SQLServer:Access Methods\Range Scans/sec
– \SQLServer:Access Methods\Probe Scans/sec
– \SQLServer:Access Methods\Scan Point Revalidations/sec
– \SQLServer:Access Methods\Workfiles Created/sec
– \SQLServer:Access Methods\Worktables Created/sec
– \SQLServer:Access Methods\Worktables From Cache Ratio
– \SQLServer:Access Methods\Forwarded Records/sec
– \SQLServer:Access Methods\Skipped Ghosted Records/sec
– \SQLServer:Access Methods\Index Searches/sec
– \SQLServer:Access Methods\FreeSpace Scans/sec
– \SQLServer:Access Methods\FreeSpace Page Fetches/sec
– \SQLServer:Access Methods\Pages Allocated/sec
– \SQLServer:Access Methods\Extents Allocated/sec
– \SQLServer:Access Methods\Mixed page allocations/sec
– \SQLServer:Access Methods\Extent Deallocations/sec
– \SQLServer:Access Methods\Page Deallocations/sec
– \SQLServer:Access Methods\Page Splits/sec
– \SQLServer:Access Methods\Table Lock Escalations/sec
– \SQLServer:Access Methods\Deferred Dropped rowsets
– \SQLServer:Access Methods\Dropped rowset cleanups/sec
– \SQLServer:Access Methods\Dropped rowsets skipped/sec
– \SQLServer:Access Methods\Deferred dropped AUs
– \SQLServer:Access Methods\AU cleanups/sec
– \SQLServer:Access Methods\AU cleanup batches/sec
– \SQLServer:Access Methods\Failed AU cleanup batches/sec
– \SQLServer:Access Methods\Used tree page cookie
– \SQLServer:Access Methods\Failed tree page cookie
– \SQLServer:Access Methods\Used leaf page cookie
– \SQLServer:Access Methods\Failed leaf page cookie
– \SQLServer:Access Methods\LobSS Provider Create Count
– \SQLServer:Access Methods\LobSS Provider Destroy Count
– \SQLServer:Access Methods\LobSS Provider Truncation Count
– \SQLServer:Access Methods\LobHandle Create Count
– \SQLServer:Access Methods\LobHandle Destroy Count
– \SQLServer:Access Methods\By-reference Lob Create Count

\SQLServer:Access Methods\By-reference Lob Use Count
– \SQLServer:Access Methods\Count Push Off Row
– \SQLServer:Access Methods\Count Pull In Row
– \SQLServer:Access Methods\Count Lob Readahead
– \SQLServer:Access Methods\Page compression attempts/sec
– \SQLServer:Access Methods\Pages compressed/sec
– \SQLServer:Backup Device(*)\Device Throughput Bytes/sec
– \SQLServer:Buffer Manager\Buffer cache hit ratio
– \SQLServer:Buffer Manager\Page lookups/sec
– \SQLServer:Buffer Manager\Free list stalls/sec
– \SQLServer:Buffer Manager\Free pages
– \SQLServer:Buffer Manager\Total pages
– \SQLServer:Buffer Manager\Target pages
– \SQLServer:Buffer Manager\Database pages
– \SQLServer:Buffer Manager\Reserved pages
– \SQLServer:Buffer Manager\Stolen pages
– \SQLServer:Buffer Manager\Lazy writes/sec
– \SQLServer:Buffer Manager\Readahead pages/sec
– \SQLServer:Buffer Manager\Page reads/sec
– \SQLServer:Buffer Manager\Page writes/sec
– \SQLServer:Buffer Manager\Checkpoint pages/sec
– \SQLServer:Buffer Manager\AWE lookup maps/sec
– \SQLServer:Buffer Manager\AWE stolen maps/sec
– \SQLServer:Buffer Manager\AWE write maps/sec
– \SQLServer:Buffer Manager\AWE unmap calls/sec
– \SQLServer:Buffer Manager\AWE unmap pages/sec
– \SQLServer:Buffer Manager\Page life expectancy
– \SQLServer:Buffer Partition(*)\Free pages
– \SQLServer:Buffer Partition(*)\Free list requests/sec
– \SQLServer:Buffer Partition(*)\Free list empty/sec
– \SQLServer:CLR\CLR Execution
– \SQLServer:Cursor Manager by Type(*)\Cache Hit Ratio
– \SQLServer:Cursor Manager by Type(*)\Cached Cursor Counts
– \SQLServer:Cursor Manager by Type(*)\Cursor Cache Use Counts/sec
– \SQLServer:Cursor Manager by Type(*)\Cursor Requests/sec
– \SQLServer:Cursor Manager by Type(*)\Active cursors
– \SQLServer:Cursor Manager by Type(*)\Cursor memory usage
– \SQLServer:Cursor Manager by Type(*)\Cursor worktable usage
– \SQLServer:Cursor Manager by Type(*)\Number of active cursor plans
– \SQLServer:Cursor Manager Total\Cursor conversion rate
– \SQLServer:Cursor Manager Total\Async population count
– \SQLServer:Cursor Manager Total\Cursor flushes
– \SQLServer:Database Mirroring(*)\Bytes Sent/sec
– \SQLServer:Database Mirroring(*)\Pages Sent/sec
– \SQLServer:Database Mirroring(*)\Sends/sec
– \SQLServer:Database Mirroring(*)\Transaction Delay
– \SQLServer:Database Mirroring(*)\Redo Queue KB
– \SQLServer:Database Mirroring(*)\Redo Bytes/sec
– \SQLServer:Database Mirroring(*)\Log Send Queue KB
– \SQLServer:Database Mirroring(*)\Bytes Received/sec
– \SQLServer:Database Mirroring(*)\Receives/sec

\SQLServer:Database Mirroring(*)\Log Bytes Received/sec
– \SQLServer:Database Mirroring(*)\Log Bytes Sent/sec
– \SQLServer:Database Mirroring(*)\Send/Receive Ack Time
– \SQLServer:Database Mirroring(*)\Log Compressed Bytes Rcvd/sec
– \SQLServer:Database Mirroring(*)\Log Compressed Bytes Sent/sec
– \SQLServer:Database Mirroring(*)\Mirrored Write Transactions/sec
– \SQLServer:Database Mirroring(*)\Log Scanned for Undo KB
– \SQLServer:Database Mirroring(*)\Log Remaining for Undo KB
– \SQLServer:Database Mirroring(*)\Log Bytes Sent from Cache/sec
– \SQLServer:Database Mirroring(*)\Log Bytes Redone from Cache/sec
– \SQLServer:Database Mirroring(*)\Log Send Flow Control Time (ms)
– \SQLServer:Database Mirroring(*)\Log Harden Time (ms)
– \SQLServer:Databases(*)\Data File(s) Size (KB)
– \SQLServer:Databases(*)\Log File(s) Size (KB)
– \SQLServer:Databases(*)\Log File(s) Used Size (KB)
– \SQLServer:Databases(*)\Percent Log Used
– \SQLServer:Databases(*)\Active Transactions
– \SQLServer:Databases(*)\Transactions/sec
– \SQLServer:Databases(*)\Repl. Pending Xacts
– \SQLServer:Databases(*)\Repl. Trans. Rate
– \SQLServer:Databases(*)\Log Cache Reads/sec
– \SQLServer:Databases(*)\Log Cache Hit Ratio
– \SQLServer:Databases(*)\Bulk Copy Rows/sec
– \SQLServer:Databases(*)\Bulk Copy Throughput/sec
– \SQLServer:Databases(*)\Backup/Restore Throughput/sec
– \SQLServer:Databases(*)\DBCC Logical Scan Bytes/sec
– \SQLServer:Databases(*)\Shrink Data Movement Bytes/sec
– \SQLServer:Databases(*)\Log Flushes/sec
– \SQLServer:Databases(*)\Log Bytes Flushed/sec
– \SQLServer:Databases(*)\Log Flush Waits/sec
– \SQLServer:Databases(*)\Log Flush Wait Time
– \SQLServer:Databases(*)\Log Truncations
– \SQLServer:Databases(*)\Log Growths
– \SQLServer:Databases(*)\Log Shrinks
– \SQLServer:Databases(*)\Tracked transactions/sec
– \SQLServer:Databases(*)\Write Transactions/sec
– \SQLServer:Databases(*)\Commit table entries
– \SQLServer:Exec Statistics(*)\Extended Procedures
– \SQLServer:Exec Statistics(*)\DTC calls
– \SQLServer:Exec Statistics(*)\OLEDB calls
– \SQLServer:Exec Statistics(*)\Distributed Query
– \SQLServer:General Statistics\Active Temp Tables
– \SQLServer:General Statistics\Temp Tables Creation Rate
– \SQLServer:General Statistics\Logins/sec
– \SQLServer:General Statistics\Connection Reset/sec
– \SQLServer:General Statistics\Logouts/sec
– \SQLServer:General Statistics\User Connections
– \SQLServer:General Statistics\Logical Connections
– \SQLServer:General Statistics\Transactions
– \SQLServer:General Statistics\Non-atomic yield rate
– \SQLServer:General Statistics\Mars Deadlocks

\SQLServer:General Statistics\HTTP Authenticated Requests
– \SQLServer:General Statistics\SOAP Empty Requests
– \SQLServer:General Statistics\SOAP SQL Requests
– \SQLServer:General Statistics\SOAP Method Invocations
– \SQLServer:General Statistics\SOAP WSDL Requests
– \SQLServer:General Statistics\SOAP Session Initiate Requests
– \SQLServer:General Statistics\SOAP Session Terminate Requests
– \SQLServer:General Statistics\Processes blocked
– \SQLServer:General Statistics\Temp Tables For Destruction
– \SQLServer:General Statistics\Event Notifications Delayed Drop
– \SQLServer:General Statistics\Trace Event Notification Queue
– \SQLServer:General Statistics\SQL Trace IO Provider Lock Waits
– \SQLServer:General Statistics\Tempdb recovery unit id
– \SQLServer:General Statistics\Tempdb rowset id
– \SQLServer:Latches\Latch Waits/sec
– \SQLServer:Latches\Average Latch Wait Time (ms)
– \SQLServer:Latches\Total Latch Wait Time (ms)
– \SQLServer:Latches\Number of SuperLatches
– \SQLServer:Latches\SuperLatch Promotions/sec
– \SQLServer:Latches\SuperLatch Demotions/sec
– \SQLServer:Locks(*)\Lock Requests/sec
– \SQLServer:Locks(*)\Lock Timeouts/sec
– \SQLServer:Locks(*)\Number of Deadlocks/sec
– \SQLServer:Locks(*)\Lock Waits/sec
– \SQLServer:Locks(*)\Lock Wait Time (ms)
– \SQLServer:Locks(*)\Average Wait Time (ms)
– \SQLServer:Locks(*)\Lock Timeouts (timeout > 0)/sec
– \SQLServer:Memory Manager\Connection Memory (KB)
– \SQLServer:Memory Manager\Granted Workspace Memory (KB)
– \SQLServer:Memory Manager\Lock Memory (KB)
– \SQLServer:Memory Manager\Lock Blocks Allocated
– \SQLServer:Memory Manager\Lock Owner Blocks Allocated
– \SQLServer:Memory Manager\Lock Blocks
– \SQLServer:Memory Manager\Lock Owner Blocks
– \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\SQL Cache Memory (KB)
– \SQLServer:Memory Manager\Target Server Memory (KB)
– \SQLServer:Memory Manager\Total Server Memory (KB)
– \SQLServer:Plan Cache(*)\Cache Hit Ratio
– \SQLServer:Plan Cache(*)\Cache Pages
– \SQLServer:Plan Cache(*)\Cache Object Counts
– \SQLServer:Plan Cache(*)\Cache Objects in use
– \SQLServer:Replication Agents(*)\Running
– \SQLServer:Replication Dist.(*)\Dist:Delivery Latency
– \SQLServer:Replication Dist.(*)\Dist:Delivered Cmds/sec
– \SQLServer:Replication Dist.(*)\Dist:Delivered Trans/sec
– \SQLServer:Replication Logreader(*)\Logreader:Delivery Latency
– \SQLServer:Replication Logreader(*)\Logreader:Delivered Cmds/sec

\SQLServer:Replication Logreader(*)\Logreader:Delivered Trans/sec
– \SQLServer:Replication Merge(*)\Uploaded Changes/sec
– \SQLServer:Replication Merge(*)\Downloaded Changes/sec
– \SQLServer:Replication Merge(*)\Conflicts/sec
– \SQLServer:Replication Snapshot(*)\Snapshot:Delivered Cmds/sec
– \SQLServer:Replication Snapshot(*)\Snapshot:Delivered Trans/sec
– \SQLServer:SQL Errors(*)\Errors/sec
– \SQLServer:SQL Statistics\Batch Requests/sec
– \SQLServer:SQL Statistics\Forced Parameterizations/sec
– \SQLServer:SQL Statistics\Auto-Param Attempts/sec
– \SQLServer:SQL Statistics\Failed Auto-Params/sec
– \SQLServer:SQL Statistics\Safe Auto-Params/sec
– \SQLServer:SQL Statistics\Unsafe Auto-Params/sec
– \SQLServer:SQL Statistics\SQL Compilations/sec
– \SQLServer:SQL Statistics\SQL Re-Compilations/sec
– \SQLServer:SQL Statistics\SQL Attention rate
– \SQLServer:SQL Statistics\Guided plan executions/sec
– \SQLServer:SQL Statistics\Misguided plan executions/sec
– \SQLServer:Transactions\Transactions
– \SQLServer:Transactions\Snapshot Transactions
– \SQLServer:Transactions\Update Snapshot Transactions
– \SQLServer:Transactions\NonSnapshot Version Transactions
– \SQLServer:Transactions\Longest Transaction Running Time
– \SQLServer:Transactions\Update conflict ratio
– \SQLServer:Transactions\Free Space in tempdb (KB)
– \SQLServer:Transactions\Version Generation rate (KB/s)
– \SQLServer:Transactions\Version Cleanup rate (KB/s)
– \SQLServer:Transactions\Version Store Size (KB)
– \SQLServer:Transactions\Version Store unit count
– \SQLServer:Transactions\Version Store unit creation
– \SQLServer:Transactions\Version Store unit truncation
– \SQLServer:User Settable(*)\Query
– \SQLServer:Wait Statistics(*)\Lock waits
– \SQLServer:Wait Statistics(*)\Memory grant queue waits
– \SQLServer:Wait Statistics(*)\Thread-safe memory objects waits
– \SQLServer:Wait Statistics(*)\Log write waits
– \SQLServer:Wait Statistics(*)\Log buffer waits
– \SQLServer:Wait Statistics(*)\Network IO waits
– \SQLServer:Wait Statistics(*)\Page IO latch waits
– \SQLServer:Wait Statistics(*)\Page latch waits
– \SQLServer:Wait Statistics(*)\Non-Page latch waits
– \SQLServer:Wait Statistics(*)\Wait for the worker
– \SQLServer:Wait Statistics(*)\Workspace synchronization waits
– \SQLServer:Wait Statistics(*)\Transaction ownership waits

Checking sql querys and executing time

It´s necessary to execute a script (ps1) to obtain information about an instance/database or calculate the query executing time.

It will be indicated the script configuration in monitoring.