The Microsoft SQL Server Monitoring Log Enablement Package
(LEP) is a Windows Performance Monitor Template which contains Performance
Counters. These counters provide basic threshold monitoring of Microsoft SQL Server
Performance Objects like Buffer Manager and Data Access. All threshold data are
sent to the Application Event Logs where the K1’s Agent or Agentless component can
manage performance data extraction. K1 then displays this data in a single page
UI which tracks the overall health of the endpoints being monitored. The
Threshold column below displays a Criteria Level of Critical, Warning, or
Error. This column should have two values, a Default value and a Threshold
value, for records without both, only a Default value of 10000 is set which
needs to be reset to an appropriate Threshold value.For instructions related to LEP’s check out How to Edit Windows Monitoring Log Enablement Package(LEP), How to Import Windows Monitoring Log Enablement Package(LEP), and Troubleshooting Windows Monitoring Log Enablement Package(LEP). Detailed instructions on how to configure the K1 to monitor Application or Threshold Monitoring on devices can be found in the Dell KACE K1000 System Management Appliance 6.3 Administrator Guide.
Users Activities & Locks
| Performance Counter | Description | Threshold Values |
| Logins/sec | Total number of logins started per second. | Default>2 Critial>2 |
| Logouts/sec | Total number of logins started per second. | Default>2 Critial>2 |
| User Connections | Total number of logouts started per second. | Default>10000 |
| Latch Waits/sec | Number of latch requests that could not be granted immediately and had to wait
before being granted. | Default>10 Critical>10 |
| Avg Latch Wait Time (ms) | Average latch wait time (milliseconds) for latch requests that had to wait. | Default>10000 |
| Total Latch Wait Time (ms) | Total latch wait time (milliseconds) for latch requests that had to wait in the
last second. | Default>10 Critical>10 |
| Lock Wait Time(ms) | Number of lock requests that could not be satisfied immediately and required
the caller to wait before being granted the lock. | Default>10000 |
| Lock Waits/sec | Number of lock requests that could not be satisfied immediately and required the
caller to wait before being granted the lock. | Default>0 Critical>0 |
| Avg Wait Time(ms) | The average amount of wait time (milliseconds) for each lock request that
resulted in a wait. | Default>500 Critical>500 |
| Lock Requests/sec | Number of new locks and lock conversions requested from the lock manager. | Default>1000 Critical>1000 |
| Lock Timeouts/sec | Number of lock requests that timed out. This includes requests for NOWAIT
locks. | Default>1 Critical>1 |
| Number of Deadlocks/sec | Number of lock requests that resulted in a deadlock. | Default>1 Critical>1 |
| Worktables From Cache Ratio | Percentage of work tables created where the initial two pages of the work table
were not allocated but were immediately available from the work table cache. | Default<90 Critical<90 |
| Table Lock Escalations/sec | The number of times locks on a table were escalated. | Default>10000 |
| Longest Running Transaction Time | The longest running time of any transcation in seconds. | Default>10000 |
Workload
| Performance Counter | Description | Threshold Values |
| Batch Requests/Sec | Number of SQL batch requests received by server. | Default>10000 |
| SQL Compilations/sec | Number of SQL compilations | Default>10 Critical>10 |
| SQL Re-Compilations/sec | Number of SQL re-compiles. | Default>10 Critical>10 |
| SQL Attention Rate/sec | Number of attentions per second. | Default>1 |
| Active Cursors | Number of active cursors. | Default>10000 |
| Errors/sec, DB Offline Errors and Errors/sec, Kill ConnectionErrors | Number of errors/sec | Default>1 |
Buffer Manager
| Performance Counter | Description | Threshold Values |
| Free List Stalls/sec | Number of requests that had to wait for a free page. | Default>2 Critical>2 |
| Lazy Writes/Sec | Number of buffers written by buffer manager's lazy writer. | Default>20 Critical>20 |
| Checkpoint Pages/sec | Number of pages flushed by checkpoint or other operations that require all
dirty pages to be flushed. | Default>10000 |
| Page Life Expectancy | Number of seconds a page will stay in the buffer pool without references. | Default<300 Critical < 300 |
| Page Lookups/sec | Number of requests to find a page in the buffer pool. | Default>100 Critical>100 |
| Page Reads/sec | Number of physical database page reads issued. | Default>90 Critical>90 |
| Page Writes/sec | Number of physical database page writes issued. | Default>90 Critical>90 |
| Readahead/sec | Number of pages read in anticipation of use. | Default>20 Critical>20 |
Data Access
| Performance Counter | Description | Threshold Values |
| Forwarded Records/sec | Number of records fetched through forwarded record pointers. | Default>10000 |
| Full Scans / sec | Number of unrestricted full scans. These can either be base table or full index
scans. | Default>10000 |
| Index Searches/sec | Number of index searches. Index searches are used to start range scans, single
index record fetches, and to reposition within an index. | Default>10000 |
| Page Splits/sec | Number of page splits per second that occur as a result of overflowing index
pages. | Default>10000 |
| Workfiles Created/sec | Number of work files created per second. For example, work files could be used
to store temporary results for hash joins and hash aggregates. | Default>20 Critical>20 |
| Worktables Created/sec | Number of work tables created per second. For example, work tables could be
used to store temporary results for query spool, LOB variables, XML variables,
and cursors. | Default>20 Critical>20 |
User Database
| Performance Counter | Description | Threshold Values |
| Data File(s) Size (KB) | The cumulative size of all the data files in the database. | Default>100000000 |
| Log Bytes Flushed/sec | Total number of log bytes flushed. | Default>10000 |
| Log File(s) Size(KB | The cumulative size of all the log files in the database. | Default>100000 |
| Log File(s) Used Size(KB) | The cumulative used size of all the log files in the database. | Default>100000 |
| Log Flush Wait Time | Total wait time (milliseconds). | Default>10000 |
| Log Flush Waits/sec | Number of commits waiting on log flush. | Default>1 |
| Log Flushes/sec | Number of log flushes. | Default>10000 |
| Log Growths | Total number of log growths for this database. | Default>10000 |
| Log Shrinks | Total number of log shrinks for this database. | Default>10000 |
| Log Truncations | Total number of log truncations for this database. | Default>10000 | |
| Percent Log Used | The percent of space in the log that is in use. |
|
Comments