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 CounterDescriptionThreshold 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 CounterDescriptionThreshold 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 CounterDescriptionThreshold 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 CounterDescriptionThreshold 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.

Default>80

Critical>80