지금 지원 담당자와 채팅
지원 담당자와 채팅

Foglight for SQL Server (Cartridge) 5.7.5.10 - User Guide

Managing SQL Server Database Systems Introduction to this Guide Using Foglight for SQL Server Monitoring Business Intelligence Services Administering Foglight for SQL Server Glossary Reference

Reference

This chapter contains reference information about the following topics:
•
SQL PI Repository Cold Backup Procedure
•
Foglight for SQL Server SQL Performance Investigator Metrics
•
Foglight for SQL Server SQL Performance Investigator Metrics
•
Foglight for SQL Server Collections and Metrics
 

SQL PI Repository Cold Backup Procedure

To perform a cold backup of the SQL PI Repository:
1
Deactivate the SPIRepository Agent.
2
Stop the Infobright process by double-clicking <foglight home> Infobright/Infobright-stop.
3
Back up the <foglight home>/Infobright/ib_data directory.

Foglight for SQL Server SQL Performance Investigator Metrics

Active Time
Sum of all active wait events; equal to the session total activity within the specified time range.
All SQL Agents CPU Usage
CPU consumption by all of the SQL Agents running on the monitored host.
All SQL Agents Resident Memory Usage
The amount of physical memory (RAM) consumed by the monitored instance’s SQL Agent service.
Availability
This metric indicates whether the SQL Server instance is available.
Average Physical I/O Operations
The average time spent on performing physical I/O operations (both read and write), out of the total active time during the specified time range.
This metric is calculated as follows:
(Page Reads + Page Writes)/Active Time
Average SQL Response Time
Average duration of the SQL statements that were executed during the specified time range.
This metric is calculated as follows: Ended Duration/Executions Ended
Backup Recovery Wait
Time spent by the various sessions waiting for backup/recovery tasks to complete.
Blocked Lock Requests
Number of lock requests that could not be satisfied immediately, causing the caller to block and wait before acquiring the lock.
Checkpoint Pages
The number of pages flushed by checkpoint or other operations that require all dirty pages to be flushed.
CLR Wait
Time spent by the statement waiting for CLR code execution to complete.
CLR, which stands for Common Language Runtime, introduced in SQL Server 2005, allows hosting of the Microsoft .NET common language runtime engine within SQL Server.
CPU Usage
Time spent (in seconds) by the various sessions consuming CPU cycles.
This reading is taken directly from the operating system, rather than SQL Server wait states.
CPU Wait
Wait Time (seconds) until the CPU resource is available. Time spent by the session waiting in the system's run queue for CPU cycles. The amount of time is dependent upon the number of concurrent processes and threads requesting CPU time. The metric value should be inspected in conjunction with the value of the Run Queue Length metric.
Cursor Synchronization Wait
The time spent by the various processes synchronizing information flow within cursors.
Cursors are extensions to result sets, which provide the mechanism for working with individual rows, or a small block of rows, in a table.
Because a cursor points to a currently selected set of records, a cursor can be used by only one connection at a time. However, the compiled plan to which the cursor is linked can be used simultaneously by multiple connections.
Database Replication Wait
Time spent by the various sessions waiting for replication synchronization events to complete.
Deferred Task Worker Wait
Time spent by the various sessions waiting for I/O requests; this wait indicates that either a large number of suspect pages have been encountered or the disk subsystem is overloaded.
Degree of Parallelism
Average number of SQL Server threads assigned to serve a SQL statement, for each parallel plan execution.
Disk Utilization
The percentage of time the busiest disk spent serving system-wide I/O requests.
This metric serves as a measure for the system I/O load. High values may indicate a device bottleneck.
DTC CPU Usage
The percentage of CPU consumption by the MS-DTC service.
DTC Resident Memory Usage
The amount (MB) of physical memory consumed by the MS-DTC service.
Distributed Transaction Wait
Time spent by the various sessions waiting for various distributed transaction events to complete.
This wait type can occur either because the MS-DTC service becomes unavailable, or because a task is waiting for the completion of an MS- DTC transaction’s commit.
Executions Ended
The average number of statements whose activity completed during the specified time range.
Executions Started
The average number of statements that started running during the specified time range.
External Procedures Wait
Time spent by the various sessions waiting for external procedures to end.
An external procedure, also sometimes referred to as an external routine, is a procedure stored in a dynamic link library (DLL) on Windows platform or shared library under UNIX. The external procedure is registered with the base language, and then invoked to perform special-purpose processing.
Because external procedures run in a process separated from the database instance, using these procedures ensures that any problems on the client side do not adversely affect the database.
Full Scans
Number of unrestricted full scans per second. These can be either base-table or full-index scans.
Full Text Search CPU Usage
Average CPU consumption by the Full Text Search service.
Full Text Search Resident Memory Usage
Average amount of physical memory consumed by the Full Text Search service.
Full Text Search Wait
Time spent by the various sessions waiting for full text synchronization operations.
Full text search is a Windows service that can optionally be installed with SQL Server. This service, which is also called MSSearch service, allows indexing of selected columns in a way that allows users to search any word, phrase, or derivative of any word contained in that column.
Full Text indexes are not updated in real time.
Free Buffer Wait
This wait event occurs when the session needs a free buffer, so it can bring a data block into the buffer cache, and is waiting for a buffer that is not dirty to become available. This can occur if DBWR is not writing dirty buffers to disk fast enough.
Hosted Components Wait
Time spent by the various sessions waiting for hosted components, such as, but not exclusively, CLR.
IO Bulk Load Wait
Time spent by the various sessions waiting for the completion of I/O operations required to carry out a bulk load I/O.
While bulk loading significantly reduces the amount of I/O activity required for loading large amounts of data to the database inside of a single transaction, this operations still takes a relatively long time to complete.
IO Completion Wait
Time spent by the various sessions waiting for I/O operations to complete.
IO Data Page Wait
Time spent by the various sessions waiting to latch a buffer for an I/O request; this wait is related to issues with the disk I/O subsystem.
IO Wait
Time spent waiting for disk input/output operations to complete.
Input/output (I/O) is one of the most expensive operations in a database system. SQL statements that are I/O intensive can monopolize memory and disk use and cause other database operations to compete for these resources.
Generally, I/O Wait is caused by poorly-tuned SQL queries or applications which generate a significant amount of logical I/O translating into excessive physical disk usage. In this case, SQL/application tuning can reduce the logical I/O- induced load. However, it could also be caused by poorly-configured disks or storage sub-systems.
Latch Buffer Wait
Time spent by the various sessions waiting for SQL Server to obtain a buffer latch. This wait event type usually indicates results from a high amount of I/O operations on the host machine.
Latch Wait
Time spent by the session being blocked by a latch, waiting for it to be released.
Latches do not need to be locked for the duration of a transaction. They are low-overhead, short-term memory synchronization objects. They are used mostly to protect a row when queried for a connection.
Latch Savepoint Wait
Time spent by the various sessions waiting to synchronize commits to marked transactions.
Lazy Writes
The number of buffer pages that have been modified in the buffer cache, which the Lazy Writer process is flushing to disk.
A high value of lazy writes may indicate that SQL Server is running out of available space in the buffer pool cache.
Lock Wait
Time spent by the session being blocked, waiting for the blocking lock to be released.
Lock Bulk Update Wait
Time spent by the various sessions waiting to acquire bulk update locks.
Bulk update locks are applied by the database engine when multiple threads perform bulk copying of data into the same table. Applying these locks allows the threads to bulk load data at the same time into the same table, while preventing access to the table from other processes, which do not carry out data bulk loading.
Lock Exclusive Wait
Time spent by the various sessions waiting to acquire bulk update locks.
Lock Intent Wait
Time spent by the various sessions waiting to acquire intent locks.
Intent locks are locks used by the database in order to protect a resource that is found lower in the lock hierarchy from a shared (S) lock or exclusive (X) lock. Because these locks are acquired before a lock at the lower level, they signal intent to place locks at a lower level.
Lock Requests
Number of new locks and lock conversions requested from the lock manager.
Lock Schema Wait
Time spent by the various sessions waiting to acquire schema locks. Schema locks are used for preventing changes to the underlying tables structures while the query is compiled.
Lock Shared Wait
Time spent by the various sessions waiting to acquire shared locks.
Lock Update Wait
Time spent by the various sessions waiting to acquire a lock update.
Lock Wait
Time spent waiting for blocking locks to be released.
Log Buffer Wait
Time spent by the various sessions waiting for space in the log buffer or otherwise waiting for memory to be made available to write log records. Consistently high values of this metric can indicate the log devices’ inability to keep up with the amount of log generated by the server.
Log Flushes
The Number of log flushes.
As users make modifications to SQL Server databases, SQL Server records these changes in a memory structure called the Log Cache. Each SQL Server database has its own log cache.
When a user transaction is committed (either explicitly using a COMMIT statement, or implicitly), SQL Server writes all changes from the Log Cache out to the log files on disk. This process is termed a log flush. The user that issued the commit must wait until the log flush is complete before they can continue. If the log flush takes a long time, this will degrade the user's response time.
Log Other Wait
Time spent by the various sessions waiting for miscellaneous logs.
Log Synchronization Wait
Time spent by the various sessions waiting for log space to be freed by log truncation, that is, deleting log records from the transaction log.
Even though log transaction is an automatic process, this process can be delayed as a result of several factors, such as long running transaction, a data backup taking place, or if no checkpoint took place since the last log truncation.
Log Wait
Time spent waiting by the various processes waiting for a log operation to complete.
Log Write Wait
Log write wait takes place when a task is waiting to write the contents of the log cache to the disk that stores the transaction log.
Memory Wait
Time spent waiting by the various processes waiting for the completion of a log operation.
Time spent by the various sessions waiting for outstanding HTTP connections to complete and exit.
Network IO Wait
Time spent by the various sessions waiting for network packets.
Network IPC Wait
Time spent by the various sessions waiting for sub-tasks to generate data; long waits are indicative of unexpected blockages.
Network Mirror Wait
Time spent by the various sessions waiting for database mirroring events to complete.
Network Wait
Network wait events occur when a session spends time waiting for messages to be sent or received over the network interface.
Network performance is measured in number of packets sent and received per second. It can be used just like disk statistics to detect overload or non-optimal performance within a network or a network interface.
Excessive network wait can result from either:
•
Excessive network usage, originating in the application
•
Physical issues, identifiable by network errors and network collisions
Non SQL Server CPU Usage
CPU consumption on the server by processes other than SQL Server.
Non SQL Resident Memory Usage
The amount of physical (resident) memory consumed on the server by processes other than SQL Server.
OLAP CPU Usage
CPU consumption of the Analysis Services processes.
OLAP Resident Memory Usage
The amount of physical (resident) memory consumed on the server by the Analysis Services processes.
OLEDB Provider Full Text Wait
Time spent by various processes waiting for the Microsoft Full Text Engine for SQL Server.
Other CPU Usage
CPU consumption of processes not related to SQL Server or associated services.
Other Miscellaneous Wait
Time spent by the various sessions waiting for miscellaneous database operations.
Other Wait
Time spent by the various sessions waiting for other database operations.
Overall CPU
Time spent by the various sessions either consuming or waiting for CPU cycles. This reading is an aggregation of CPU Usage and CPU Wait, which are both taken directly from the operating system (rather than MSSQL wait states).
Page Life Expectancy
Number of seconds a page will stay in the buffer pool without references. Low values of this metric require the buffer pool to frequently flush the page to the physical disk, thereby possibly indicating a memory issue. Such an issue should be investigated to see if it results from lack of physical memory, inefficient queries and so on.
Page Splits
Number of page splits taking place.
Page splitting occurs when an index or data page becomes full and then is split between the existing page and a newly allocated page.
Excess page splitting can lead to I/O performance issues, indicating a need to rebuild indexes and/or increase the indexes’ fill factor.
Parallel Coordination Wait
The time spent by the various processes coordinating parallel query threads and exchanging data.
If the server on which SQL Server is running has multiple CPU cores, SQL Server can run a single query in parallel, using multiple threads. In addition to running user queries on multiple processors, SQL Server can also use multiple threads to build indexes. Typically, queries that make heavy use of CPU cycles are good candidates for parallel execution. For example, a query joining several large tables and sorting the output before returning it to the user is likely to benefit from a parallel execution plan.
Physical I/O
The amount of physical I/O operations (both read and write) carried out by SQL Server during the specified time range.
Physical Memory Used
The amount of physical memory, or RAM, being used by the server.
Physical Page Reads
The number of physical database page reads issued during the specified time range.
Physical Page Writes
The number of physical database page writes issued during the specified time range.
Probe Scans
The rate per second of probe scans. A probe scan is used for directly looking up rows in an index or base table.
Plan Cache Hit Rate
The Plan Cache is an area in the SQL Server memory, used for storing objects such as stored procedures, ad hoc and prepared Transact-SQL statements, and triggers.
The plan cache hit ratio indicates the ratio between physical accesses and logical accesses, that is, how much work (for example, I/O and compilations) is being saved by finding the required information already in the plan cache.
Range Scans
Number of qualified range scans through indexes.
Rec Ended Duration
Elapsed (gross) duration of the statements that ended during the current time interval.
Remote Provider Wait
The time spent by the various processes waiting for a remote OLEDB call to complete or DTC synchronization.
Run Queue Length
System average run queue.
The CPU run queue is a holding area for threads and processes that require the CPU when the CPU is busy serving other processes. The run queue length is an indicator of whether the system has sufficient CPU resources for all the processes it executes.
High values, along with high CPU utilization, indicate that the system requires faster or additional CPU units to handle the given load.
Samples
Number of Collector samples that took place during the specified time range.
Service Broker Wait
Time spent by the various sessions waiting for Service Broker event handlers and endpoints.
Session Logons
Number of sessions whose activity started during the current interval.
Session Logoffs
Number of sessions that logged out during the current interval.
SQL Agent CPU Usage
The CPU utilization percentage by the SQL Agent service.
SQL Agent Resident Memory Usage
The percentage of physical RAM (resident) memory consumed by the SQL Agent service.
SQL Executions
The number of SQL statements executed during the specified time range.
SQL Mail CPU Usage
The percentage of CPU used by the SQL Mail service.
SQL Mail Resident Memory Usage
The percentage of physical RAM (resident) memory used by the SQL Mail service.
SQL Recompilations
The number of SQL recompilations. A recompile occurs when an execution plan for an SQL statement needs to be changed, due to implicit or explicit decision.
Implicit recompilations occurs when the data structure (views, tables and their indexes) undergoes changes, or when their statistic data is updated. Explicit recompilations result from procedures that include explicit demand for recompilation (either in the procedure definition or in its execution), or from use of dynamic code, when parameterization is not forced.
Unusually high rate of recompilations vs. compilations ratio indicates that the re-compilation is inefficient, and prevents the use of existing, cached execution plans.
SQL Response Time
Average duration of the SQL statements that executed during the specified time range.
SQL Server Background CPU Usage
The percentage of CPU utilization by the SQL Server background services.
SQL Server Cache Memory
Total amount of dynamic memory the server is using for the dynamic SQL cache.
SQL Server Connections Memory
Total amount of dynamic memory the server is using for maintaining connections.
SQL Server Connections Summary
The total number of SQL Server sessions; both user and system sessions.
SQL Server Foreground CPU Usage
The percentage of CPU utilization by the SQL Server background services.
SQL Server Resident Memory Usage
The percentage of physical RAM (resident) memory utilized by SQL Server.
SQL Server Swap Memory Usage
The amount of virtual memory that the SQL Server process has reserved for use in the paging file (s).
Synchronous Task Wait
Time spent by the various sessions waiting for tasks started synchronously (most SQL Server processes are started asynchronously).
Table Lock Escalation
Lock escalation takes place when a specific query, which obtains a large number of locks at the row or page level, is automatically upgraded to a table lock level. This upgrade is being carried out as granting a single lock at this level; this is deemed more effective than creating and granting a many locks at lower levels.
Target Instance Memory
Total amount of dynamic memory the instance can consume.
Total CPU Usage
Overall CPU utilization by all processes running on the host (both OS and SQL Server processes).
Total Instance Memory
Total amount of dynamic memory the instance is currently consuming.
Virtual Memory Used
The current amount of used virtual memory.
 

Rules

Foglight for SQL Server includes a set of predefined rules that are applied to the incoming monitoring data. The rules that monitor SQL Server instances and databases have the prefix DBSS. You can and should modify rules to better suit your environment.
To modify Foglight for SQL Server rule threshold values, alarm settings, and alarm email notifications, use the Databases Administration — Alarms View. To review all Foglight rules, copy rules, edit rules, or create new user-defined rules, use the Foglight Administration — Rules Management Dashboard.
* 
TIP: For rules that reference registry variables for threshold values, modify the threshold in the registry variable, rather than modifying the rule. For help finding and editing registry variables, search for “Registry Variable” in the online help.
Databases Administration — Alarms View
In the Alarms view, you can customize alarm settings and email notifications for all alarms, a category of rules, or a single rule. The threshold changes can be localized to one agent or all selected agents.
* 
NOTE: When you make edits to alarm settings on the Alarms view, the edits are saved separately and applied over the predefined rules, which protects your changes from software updates.
To manage alarm thresholds and notifications for rules:
1
In the navigation panel, under Homes, click Databases.
2
Select the row check boxes beside one or more Foglight for SQL Server instances.
3
Click Agent settings and then click Administration.
The Databases Administration dashboard opens.
4
Click Alarms.
The Alarms view contains the predefined rules organized by category.
5
From here, you can perform the following tasks:
•
Set alarm sensitivity levels
•
Enable/disable alarms
•
Change alarm threshold values
•
Enable/disable severity levels for each alarm
•
Configure email notifications
 
Foglight Administration — Rules Management Dashboard
In the Rules Management dashboard, all Foglight for SQL Server rules are displayed in the list. You can restrict the list to show only the rules installed with a cartridge. You can also search the list for a single rule or a group of rules.
* 
NOTE: Predefined rules may be modified during regular software updates. To avoid losing your changes to these rules, we recommend copying the rule and making edits to the copy. Enable the copy and disable the original rule. You may want to identify your custom rules with a unique prefix.
To review and edit rules:
1
In the navigation panel, under Homes, click Administration.
2
In the Administration dashboard, click Rules.
The Rule Management dashboard opens.
3
To show only Foglight for SQL Server rules, in the Search box, type the prefix: DBSS
* 
TIP: Alternatively, you can select DB_SQL_Server_UI from the Cartridge list.
4
From here, you can perform the following tasks:
•
Review a short description of the rule.
•
Review and edit threshold values.
* 
TIP: For DBSS rules, use the Databases Administration — Alarms View instead.
•
Copy rules.
•
Edit rule conditions.
•
Associate actions with rules.
* 
NOTE: By default, predefined Foglight for SQL Server rules do not trigger any actions.
•
Create user-defined rules.
* 
NOTE: User-defined rules are always managed from the Rule Management dashboard; these rules are not displayed in the Databases Administration — Alarms View.
For help with these tasks, open the online help from the Rules Management dashboard.
관련 문서

The document was helpful.

평가 결과 선택

I easily found the information I needed.

평가 결과 선택