Foglight for SQL Server (Cartridge) 5.7.5.30

The Databases pane on the vmExplorer dashboard (Dashboards > VMware > vmExplorer > Databases) is the main tool for investigating the share of the SQL Server-related processes on the virtual machine’s overall system workload.

Figure 6. The Databases pane on the vmExplorer dashboard.

This pane allows viewing SQL Server activity in the modes described in the following sections:

•

Reviewing SQL Server Activity for All Instances

•

Reviewing SQL Server Activity for a Specific Instance

Reviewing SQL Server Activity for All Instances

The SQL Server Activity for All Instances view, displayed by clicking Summary under the SQL Servers section, displays summarized data of SQL Server activity for all instances residing on the selected Virtual Machine.

This screen contains the following sections:

•

Resource Utilization — displays data derived from VMware, showing the SQL Server instances, monitored in both monitoring modes, which had the greatest impact on the system performance:

•

Top 5 CPU consumers

•

Top 5 consumers of network resources

•

Top 5 memory consumers

•

Top 5 disk space consumers

•

vFoglight for SQL Server Add-on related Alarms — displays all alarms invoked for instances monitoring in vFoglight for SQL Server Add-on mode. These alarms correspond, both in number and time range, to the alarms displayed on the Databases table and Quick View panel for instances monitored in this mode.

•

SQL Server - FAQts — allows viewing the most activity-intensive instances in various parameters.

This section contains multiple questions regarding these issues, such as: “Which instances had the highest rate of I/O Reads?”. The answer to each of these questions is provided, in both a chart and a table format, using a popup displayed by clicking Show Me.

Reviewing SQL Server Activity for a Specific Instance

The SQL Server Activity view is displayed by clicking a specific instance under the SQL Servers section.

Figure 7. The SQL Server Activity view.

This view allows carrying out the tasks described in the following sections:

•

Viewing instance properties

•

Viewing resource utilization

•

Viewing session details

•

Viewing the instance’s change of size

•

Viewing disk space usage by logical disk

Viewing instance properties

The Instance Properties section contains the following components:

•

Instance indicators — Availability, Version, Up Since, and Response Time.

•

Alarms — the system-related alarms, derived from VMware, which were invoked for the selected instance during the specified time range.

Viewing resource utilization

The Resource Utilization section displays the instance’s utilization of the following resource types: CPU, Network, Memory, and Disk I/O.

For each of the sections, whenever there is an equivalent metric in the Virtual Machine topology, a bar chart is displayed, comparing the average between the period values of the SQL Server instance and the Virtual Machine metrics. The chart displays the selected metric’s values throughout the specified time range, and the table displays the metric’s average value.

Figure 8. Resource utilization chart.


	NOTE: For the Memory resource, the table displays the latest memory utilization.

Except Memory, the utilization of each resource category can be seen using several metrics, as listed below.

•

For the CPU resource:

•

Usage (CPU Utilization)

•

Idle

•

For the Network resource:

•

•

•

•

•

For the Disks resource:

•

•

•

•

•

Viewing session details

The Session Details (Average) section displays the following summarized data:

•

Total Sessions — the average number of SQL Server processes, both user and system processes.

•

Active Sessions — the number of non-system sessions that are actively processing in SQL Server or that are waiting on locks (blocked).

•

Blocking Sessions — the average number of blocking processes that were collected during the specified time range.

•

Blocked Sessions — the average number of blocked processes that were collected during the specified time range.

•

Deadlocks — the average number of deadlock events that took place during the specified time range, plotted over time.


	NOTE: Clicking the icon near each of the numerical values displays a popup with a chart that shows these values plotted over the specified time range. For instances monitored in Foglight for SQL Server mode, a link at the bottom of each popup allows further investigation using the relevant panel in the SQL Activity drilldown.

Viewing the instance’s change of size

The throughputs section contains two bars, which display the instance’s accumulated change of size caused by running SQL queries of the type selected from the Query Type list (Insert, Update, Delete, Select, or All) during the specified time range. The size change is shown using the following indicators:

•

Number of megabytes

•

Number of rows


	NOTE: Clicking anywhere on the bar displays a popup. For instances monitored in Foglight for SQL Server mode, a link at the bottom of this popup allows further investigation using the SQL Activity > I/O by File panel.

Viewing disk space usage by logical disk

The Storage section displays, for each of the logical disks, storage-related data about the disk space allocation and utilization by the virtual machine and the SQL Server instance. Virtual machine-related data is retrieved using vFoglight, while SQL Server instance-related data is retrieved using Foglight for SQL Server. Data is displayed in a graphic (bar) and textual formats.

The Disk Space Utilization bar displays the space breakdown of the logical disk into the following categories:

•

Database used space — disk space actually used by the selected instance.

•

Database free space — disk space allocated to the selected instance but not used.

•

Virtual machine used space — total disk space used by the virtual machine out of the entire disk space, excluding space allocated to the selected instance.

•

Virtual Machine free space — total space allocated to the virtual machine but not used, excluding space allocated to the selected instance.

The Virtual Machine section displays the following data regarding the virtual machine’s disk space utilization:

•

Total space — total disk space allocated to the virtual machine.

•

Free space — total disk space allocated but not used. This value is a summary of the database free space and the virtual machine free space.

•

Used space — total disk space used by both the virtual machine and the selected instance.

The Database section displays the following data regarding the SQL Server instance’s disk space utilization:

•

Allocated Space — disk space allocated to the instance

•

Used Space — disk space actually used by the instance

Glossary

A

Access methods

A functional module within SQL Server that is responsible for managing the retrieval and modification of data and index pages.

Adhoc SQL Plans

Query plans produced from an ad hoc Transact-SQL query, including auto-parameterized queries. SQL Server stores such query plans in the plan cache for later reuse, if an identical Transact-SQL statement is later executed.

Alarm

The mechanism by which Foglight for SQL Server alerts to a condition that might be a problem in the SQL Server instance. While an alarm is active, the color of icons on the home page changes.

Alert

A SQL Server Agent object that performs an action when a condition is satisfied. Categorized as either Event Alerts or Performance Alerts. The action performed can be to send an e-mail, pager or Net Send message, or to run an SQL Agent job.

Allow updates

A SQL Server configuration parameter that determines if system tables can be modified using standard INSERT, UPDATE, DELETE statements. A value of 0 means updates are not allowed, and 1 indicates they are allowed.

Anonymous subscription

Anonymous subscription is a specific type of pull subscription, for which information regarding the subscription and the subscribing server (Subscriber) is not stored, for various reasons such as security (if the subscribing server accesses publications over the Internet) or cutting maintenance overhead involved in storing extra information at either the publication or distribution database. Anonymous subscriptions are initiated at the subscribing server, which is also responsible for keeping such subscriptions synchronized.

Authentication

The process of identifying and verifying a user who is attempting to establish a SQL Server session. Can be either Windows Authentication or SQL Server Authentication. With Windows Authentication (trusted logins), SQL Server uses Windows security mechanisms to determine who the users are and what they have access to. With SQL Server Authentication, users must have a SQL Server login and password, which are validated against a SQL Server system table. SQL Server can be configured to allow only Windows Authentication, or both Windows and SQL Server Authentication (Mixed mode).

AutoClose

An option that can be set on SQL Server databases. This option causes SQL Server to periodically attempt to reorganize the data, thereby acquiring additional space. Continually opening and closing the SQL Server database significantly affects the database. Therefore, the AutoClose option is disabled by default, thereby keeping the database always open and ready, regardless of user activity.

AutoGrow

An option that can be set on SQL Server data and log files, to determine whether they can grow.

Automatic Discovery

Automatic Discovery is a cartridge that is installed as part of the Foglight for SQL Server installation package. This cartridge allows running the database discovery wizard, which provides a common entry point for adding and discovering all of the database instances within a user-specified range, and then configuring these instances to be monitored.

AutoShrink

An option that can be set on SQL Server databases, which causes SQL Server to periodically attempt to reorganize the data, thereby acquiring additional space or unused space from the file.

Batch

A set of Transact-SQL statements that are sent from a client program to SQL Server for execution. Even though batches can contain multiple statements, they often hold only a single statement.

BCP (Bulk Copy Program)

A stand-alone executable program that is extremely fast at copying data between SQL Server and text files. BCP can be used for loading or unloading data.

Blocking

The situation where a SQL Server session is waiting on a lock held by another session. The session waiting is said to be Blocked, and the one holding the lock is Blocking.

Books Online

SQL Server Books Online. The online help system that is provided with the SQL Server utilities.

Bound trees

Normalized trees for views, rules, computed columns, and check constraints.

Bound trees are used in SQL Server 2005 and later versions.

Buffer

A memory structure. Equates to a page that is currently held in memory (RAM).

Buffer cache

SQL Server's main memory cache. The buffer cache is an in-memory copy of recently used database pages, and contains database, free, and stolen pages.

The buffer cache’s size is calculated by <free pages> * 8K / 1024.

Buffer pull

The buffer pool is the total buffer memory used by SQL Server.

When the SQL Server starts, it begins by computing the upper size of the buffer pool, which is the maximum size to which the Server allows the buffer pool to grow. The lower value between the maximal buffer pool size and the amount of physical RAM on the disk is the total amount of memory currently allocated to SQL Server.

If the dynamic memory management mechanism is properly tuned, SQL Server can automatically tune its memory allocations based on the load it is processing and the demands of other Windows processes on the Server.

Bulk copy

See BCP (Bulk Copy Program).

Bulkinsert

A Transact-SQL statement designed to load data from a text file into a SQL Server table. This is the fastest way of getting data into SQL Server. The bulk insert effectively runs a BCP Load in the SQL Server address space.

Bulk load

See Bulk Insert.

Cache

An area of memory that holds a copy of data or objects. Because accessing information from RAM is faster than from disk, caching data in RAM improves performance by reducing disk I/O.

The SQL Server uses two types of cache storage: buffer cache, used for storing recently used database pages, and plan cache, which stores execution plans. For details, see glossary definitions of Buffer cache and Procedure cache.

CAL

Client Access License. A license purchased from Microsoft, which allows a user to access SQL Server. For further details, see glossary definition of Licensing.

Calibration

The process by which Foglight for SQL Server determines the maximum and minimum values for every dataflow on the home page, by observing data moving through the database system. This information helps Foglight for SQL Server display the dataflows correctly.

Cardinality

A measure of the number of unique values within a column or index. The higher the cardinality of an index, the fewer the number of rows that are returned by an exact lookup of a key value (and hence the more useful the index).

Cartridge

Cartridges extend the functionality of Foglight and are installed on the Foglight Management Server. A cartridge contains one or more components, such as agents for deployment, communication capabilities, and modifications to the way that data is transformed or handled, as well as rules, reports, and views.

Chart

A graphical representation of a statistic over a period of time. One or more statistics may be shown on the same chart.

Checkpoint process

A SQL Server process that is dedicated to managing checkpoints. The Checkpoint process minimizes the amount of work SQL Server is required to do on restart, by periodically scanning the buffer cache for modified pages and writing all modified data out to disk.

Client network utility

An executable program installed with SQL Server. Assists in configuring which network libraries are used by client software that establishes a connection to SQL Server.

CLR

CLR (Common Language Runtime) is part of Microsoft’s .NET Framework, which is integrated in SQL Server 2005 and later versions.

CLR provides the execution environment for all .NET Framework code, and allows using all of the available .Net languages, such as VB.NET or C#, for creating stored procedures, triggers, user-defined functions, user-defined types, and user-defined aggregates. Code that runs within the CLR, which is referred to as managed code, compiles to native code prior to execution, thereby helping to achieve significant performance increases in certain scenarios.

Compile

The process the SQL Server Query Optimizer uses for determining how a query or stored procedure is executed. During a compile, the Query Optimizer examines the query, including the tables, clause conditions, joins, sub queries, sort and grouping requirements used. The Optimizer then takes into account all of the existing indexes, locking methods and join algorithms that could help the query run faster. The result of this process is what the SQL Server Query Optimizer considers to be the fastest way of executing the query. This is known as the execution plan.

Connect

The process of establishing a link to SQL Server, including authorization checking. After a connection is established, a client program can access, modify, and store data in a SQL Server database.

Connection

See Session.

Connectivity software

A program used for establishing and maintaining a connection between a client program and SQL Server. The connectivity software usually used under SQL Server is Microsoft Data Access Components (see MDAC).

CPU Usage

When SQL statements and other types of calls are made to SQL Server, an amount of CPU time is necessary to process the call. Average calls require a small amount of CPU time. However, an SQL statement involving a large amount of data or a runaway query can potentially consume a large amount of CPU time, reducing CPU time available for other processing.

CPU utilization is the most important operating system statistic in the tuning process. Excessive CPU usage usually means that there is little idle CPU on the system. This could be caused by an inadequately-sized system, by untuned SQL statements, or by inefficient application programs.

Cursors

Extensions to result sets that 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.

Data access components

See MDAC.

Data file

A file that is one part of the “data” portion of a SQL Server database. Stores all data such as tables, rows, stored procedures, and indexes. While each database has one or more data files, each data file can belong to only one database.

Data storage engine

The major functional component of SQL Server, which parses and optimizes SQL requests, and processes results from the Storage Engine.

Database

A collection of tables and other objects. Each database contains a data portion and a log portion. The data portion contains all tables, rows, indexes and so on, and resides on at least one data file. The log portion tracks updates to the data, and aids recovery. Logs reside on one or more log files.

Database object

Anything that is stored in a database, such as Tables, Stored Procedures, Views, Triggers, User Defined Data Types, System Tables, and Constraints.

DataFlow

A Dataflow displays the current level of activity. As the rate of data transfer increases, so too does the speed of the flow. If the statistic represented by the flow moves to another threshold, the flow may change color. The combination of movement and color makes it easy to spot congested areas. A graph on top of the flow indicates how the load has varied over time.

DBCC

Database Console Command (sometimes called Database Consistency Checker). A SQL Server Transact-SQL statement that allows carrying out operations that are specific to SQL Server and are not covered by the ANSII SQL specification, such as checking the integrity of databases, retrieving database statistics and performance information, and viewing SQL Server internal information.

DBID

Database ID. A number that uniquely identifies a database within a SQL Server.

DBO

Database Owner. The user who owns the database.

Deadlock

A deadlock occurs when there is a cyclic dependency between two or more threads, or processes, for some set of resources within SQL Server, that is, each task has a lock on a resource which the other tasks are trying to lock.

When an instance of the Microsoft SQL Server Database Engine detects a deadlock, it chooses a transaction as a deadlock victim, terminates the current batch, rolls back the transaction and returns an error message to the application.

Deadlocks cannot be completely avoided; however, following certain coding conventions can minimize the occurrence of deadlocks.

Disk queue length

Disk queue length tracks the average number of I/O requests that are queued and waiting for a disk during a sampled interval. This figure may include I/O activity generated by processes other than SQL Server. Values that exceed the threshold set in this metric indicate a bottleneck.

Disk transfer time

Reading or writing data requires a disk to access the disk sector where the requested data resides. After this sector was accessed, the amount of time required for a disk to read or write data from or to a storage media is referred to as disk transfer time.

Transfer time, usually expressed in milliseconds, is part of the disk access time, that is, the total time required for the computer to process the data request from the processor and then retrieve the needed data from a storage device.

Disk utilization

The percentage of elapsed time that a disk is busy servicing I/O requests.

DiskPerf

A Windows command line utility, which enables or disables the collection of I/O statistics.

Distributing instance

A distributing instance, also known as Distributor, is a database instance that stores replication-specific data associated with one or more publishing servers.

The distributing instance includes several databases. Each of these databases is associated with a single publishing server, and stores replication status data, metadata about the publication, and, in some cases, acts as a queue for data moving from the publishing servers to the subscribing servers.

The distributing instance can take part in the replication process in either of the following methods:

•

Local Distributor — when a single database server instance acts as both the publishing server and the distributing instance.

•

Remote Distributor — when the publishing server and the distributing instance are configured on separate database server instances.

Distributor

See Distributing instance.

DMO

Distributed Management Objects. A set of COM objects is used for maintaining SQL Server objects such as databases, tables and indexes, as well as to administer SQL Server itself. Also called SQL-DMO.

Drilldown

A Foglight for SQL Server view that provides more detailed information than what is available on the main window. Often contains charts or tables showing SQL Server or Windows statistics or objects.

DTC

DTC (Distributed Transaction Coordinator) service is an integral component of Microsoft Windows (on Windows 2003 and higher versions), and is also available as part of the SQL Server installation if a previous version of Windows is used. This service is responsible for coordinating distributed transactions, that is, transactions that involve multiple resource managers, such as databases, message queues, and file systems.

DTS

DTS (Data Transformation Services) is a SQL Server component which provides a set of tools that allow extracting, transforming and consolidating data from various sources (for example, Oracle database, Excel spreadsheet or Microsoft Access) into single or multiple destinations that use uniform SQL Server format.

DTS can be customized to efficiently remap columns, convert data types, and change the database schema.

Error log

SQL Server error log is a standard text file that holds SQL Server information and error messages. This file can provide meaningful information for handling SQL Server-related issues. This information can either be used for tracking down problems or alert to potential or existing problems.

Event alert

A SQL Server Agent object that performs an action when a specified type of message is encountered by SQL Server. The action performed can be one of the following:

•

Sending e-mail, pager, or Net Send messages

•

Running an SQL Agent job

Execution contexts

Execution contexts, also known as executable plan, are created when, during the execution of a compiled plan, SQL Server keeps track of information about the state of execution and stores this information in the plan cache.

Extended stored procedures

A SQL Server object that dynamically loads and executes a function within a dynamic-link library (DLL) in a manner similar to a stored procedure. Actions outside SQL Server can be triggered, and external information returned to SQL Server. SQL Server comes pre-installed with a large number of extended stored procedures. In addition, SQL Server includes system-stored procedures that allow adding and dropping extended stored procedures, or provide information about such procedures.

Extent

The unit of space allocated to a SQL Server object, such as a table or index. In SQL Server, an extent is eight continuous pages, or 64 KB.

External procedures

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.

File

An on-disk structure in which SQL Server stores data and log information. Corresponds to a Windows File.

Filegroup

A filegroup is a logical storage unit that consists of one or more database objects. The filegroup, which forms a single unit of allocation and administration, maps to a file system file or multiple files.

Each database has at least one File Group (DEFAULT), which stores all system objects. When creating user-defined objects such as a table or index, it is possible to define a custom file group (usually comprising several files and disks) on which these objects reside.

File cache

An area or physical memory that Windows has allocated to speed the reading and writing of files from disk. SQL Server data and log files bypass the Windows file cache, as SQL Server already caches these files in its own memory structures.

Foglight Agent Manager

The Foglight Agent Manager manages certain types of agents installed on monitored hosts. It provides a centralized communications link between the Foglight Management Server and these agents and manages the agents’ life cycles. The Foglight Agent Manager also provides a number of support services such as deployment, upgrade, and the ability to configure agents.

For further details, see Foglight Administration and Configuration Guide.

Foglight Management Server

The Foglight Management Server (FMS) is the central component of Foglight. The Management Server receives information from agents and makes it available in the browser interface.

The Foglight database stores all system, application, and performance data. Over time, it becomes an invaluable source of historical information for planning future system capacity requirements and for doing point-in-time analysis. The information in the database can also be made available for use in external systems.

The Management Server performs continuous internal monitoring on itself and other Foglight components, as well as on the Java Virtual Machine (JVM) and the database.

Foreign key

A column or columns within one table that relate to the primary key within a master or a parent table.

Forwarded records

When a row in a table that does not have a clustering index is updated, and if the modified row no longer fits on the old page, then SQL Server moves the row to a new page and leaves a forwarding pointer in the old page. The forwarding pointer saves the need to modify the non-clustered indexes to make them reflect the row’s new location; however, extra I/O resources are now required for subsequent retrieval of this row using non-clustered indexes.

A high rate of forwarded records can indicate a need to reorganize tables (unload/reload) or define clustered indexes.

Free buffers

See Free List.

Free list

The set of buffer pages that are currently available for immediate re-use. These are SQL Server memory pages that are not currently allocated to any cache. The Free List is maintained by the Lazy Writer process (see glossary definition of Lazy writer). If the Free List becomes empty, free pages are not immediately available to SQL Server when required, and the connections needing them might have to wait while SQL Server makes buffers available. This can result in lower performance.

Free pages

Pages located in one of the SQL Server cache memory types, which are available for immediate reuse.

The Lazy Writer Process periodically scans all SQL Server caches, and maintains a list of such “free” pages.

When SQL Server needs a free memory page (for example, when reading a database page from disk into the buffer cache), and no free pages are immediately available, the connection needing the free page must wait while SQL Server makes buffers available. This results in slower performance. In the worst case, the connection will have to wait while SQL Server writes a modified page out to disk in order to make a free buffer.

Free pages values that are consistently close to zero may indicate use of inefficient queries, or shortage of SQL Server cache memory.

Free potential

The term Foglight for SQL Server uses to describe the amount of space that is available for allocation for a File Group. Free potential is defined as the amount of free space in the file group, plus the file group's Potential Growth (the amount by which the file group's files could grow).

Free space

The amount of disk space within a Database or a File Group that can be allocated to any table or index as required, but is currently allocated to no table or index. See also glossary definition of Unused space.

Freespace scans

A Freespace Scan is initiated when SQL Server needs to determine where a new record can be inserted.

Full text search

A Windows service that can optionally be installed with SQL Server. This service, which is also called MSSearch service, allows indexing 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.

GAM

Global Allocation Map. Pages in SQL Server databases that record the extents that have been allocated, and can therefore be used for locating free extents in a database.

Ghosted records

When a record is deleted from a table, to improve concurrency SQL Server does not physically remove the row from index Leaf levels. Instead, the record is marked as deleted (ghosted). At a later point, a housekeeping process asynchronously removes the ghosted records from the Leaf level. Until the records are removed, SQL Server must skip the ghosted records during Leaf level scans.

Growth increment

The amount by which a file grows when SQL Server decides to extend it. This amount can be expressed in an absolute size (megabytes — MB), or as a percentage of the current file size.

GUID

Globally Unique Identifier. A 16-byte binary value that is guaranteed to be unique. Corresponds to the SQL Server uniqueidentifier data type.

Hash buckets

SQL Server pages in memory, which contain an array of pointers to buffer pages, thereby allowing faster al location of a page in memory. Can be thought of as conceptually similar to an index into SQL Server's buffer cache.

Hashing

In general, hashing refers to the technique of mathematically transforming a key value into a relative address that can be used to rapidly locate records.

Heap

The name given to tables that do not have a clustered index. The rows in these table are not stored in any specific order.

Hit rate

A ratio between logical accesses and physical accesses. Indicates how much work (I/O, compilations, etc) is being saved by caching information in memory.

Hit ratio

See Hit rate.

Host name

The name of the client computer that established the SQL Server connection.

Host process

A string that uniquely identifies the instance of the client application that established the SQL Server connection.

I/O

Input or output to a peripheral device. In a database context, I/O refers to refers to input or output to disk devices.

Index

A database object that provides fast access to data in the rows of a table, based on key values. Indexes provide quick access to data and can enforce uniqueness on the rows in a table.

Indid

A number that uniquely identifies an index on a table. All tables have at least an indid of 0 or 1. Indid 1 corresponds to a Clustered index, indid 0 corresponds to a Heap structure. Also called IndexID.

Integrated security

See Windows authentication mode.

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.

Intent locks serve the following purposes:

•

Preventing the modification of higher-level resource by other transactions, in a way that would invalidate the lock at the lower level

•

Improving the Database Engine’s efficiency in detecting lock conflicts at the higher level of granularity

Intent locks include intent shared (IS), intent exclusive (IX), and shared with intent exclusive (SIX).

Job

An SQL Server Agent object that can be scheduled to execute any number of tasks. A Job consists of one or more steps, which can each run SQL statements, Windows commands, and so on.

Kernel memory

The physical memory allocated to Windows kernel.

Kernel mode

See Privileged mode.

Kill

A Transact-SQL statement that terminates a SQL Server connection. Any outstanding transactions for the selected session are rolled back and all locks are released.

Latch

A Latch is like a mini-lock that is used internally by SQL Server to serialize access to certain resources. Latches do not participate in transactions and are typically held for very short durations.

Lazy writer

A SQL Server process that periodically scans all SQL Server caches, and maintains the Free List, which is the collection of buffer pages available for immediate re-use. If necessary, the Lazy Writer process writes modified pages out to disk in order to make them available. A high value of lazy writer writes may indicate non-optimal buffer cache memory size or use of inefficient queries.

Licensing

The legal agreement that determines how many users can access each SQL Server installation. See glossary definitions of Per seat licensing and Per server licensing.

Lightweight pooling

A SQL Server configuration parameter that causes SQL Server to schedule its sessions as Fibres instead of Threads.

Lock

An object used by a software, such as SQL Server, to indicate that a user has an interest in a resource, and to prevent multiple users accessing the same resource in conflicting ways simultaneously. The simplest example is to prevent two people from updating the same information at the same time.

Lock area

An area of memory used by SQL Server for managing locks.

Lock escalation

The process of converting many lower level locks into fewer higher level locks, and reducing system overhead. For example, if a user has many row-level locks on a table, SQL Server may escalate these into a single table-level lock.

Lock mode

A lock mode (for example: Shared, Exclusive, Intent Exclusive) indicates the level of dependency a user has on a lock, and is used for determining which locks can be granted simultaneously on a resource.

Log

An on-disk structure, which contains a record of a database's transactions. A log consists of one or more Files.

Log cache

An area of memory used by SQL Server to speed up access to Logs. Used primarily for rollback processing.

Log writer

A SQL Server background service that writes all changes from the Log Cache out to the log files on disk. This process is termed a log flush.

Logical I/O

I/O operation performed in the memory, thereby requiring no disk access. Logical read operations use a page that was already found in memory. When logical write operation takes place, the modifications are completed in memory and only some time later written to disk.

LRU

Least Recently Used. Often refers to pages in memory. The LRU list is the mechanism by which SQL Server determines which pages should be moved to the free list first.

LSN

LSN (Log Sequence Number|), which provides a unique identification of a point in a database's log, is used for determining determine when a page was last modified.

Master

A SQL Server system database that contains information about SQL Server's configurations, and the location of all other databases.

MaxSize

A parameter on each SQL Server file, which determines the maximum size to which the file can grow (assuming there is sufficient free space on the disk).

MDAC

Microsoft Data Access Components. A layer of software that provides higher-level software with the ability to communicate with SQL Server. Can be downloaded for free from Microsoft's Web site.

Metric

A Unit of Measurement/Indicator that can be applied to a database. Metrics can help gauging the performance of a system.

A metric is an individual piece of information that Foglight for SQL Server has collected about the performance of a system. The information may be a numeric value (a number or percentage), a string of text, or some other valuable piece of data.

Every time that the Foglight for SQL Server window is refreshed, the cartridge retrieves the latest value of the metric, which can then be displayed in a drilldown or on the home page.

Misc.normalized trees

Normalized trees for views, rules, computed columns, and check constraints.

Miscellaneous normalized trees are used in SQL Server 2005/2008.

Model

A SQL Server system database that is used as a template when new databases are created.

Monitor page file

The monitor page file contains the Metrics and Threshold settings for a Server. This file is created when the standard settings are updated, either through calibration or configuration.

Named pipes

A network protocol that client applications can use for communicating with SQL Server. See Net Library.

Net library

A layer of software that enables SQL Server to communicate with client applications (and conversely). Enables communication using a variety of network protocols such as TCP/IP, Named Pipes, and IPX/SPX.

NIC

Network Interface Card. A piece of hardware that connects a computer to the network.

Null

Indicates that a value is missing, unknown, or inapplicable.

OBID

Object ID. A unique number that identifies a SQL Server object within a database. Objects can be tables, views, stored procedures, triggers and so on.

Object plans

Query plans generated by creating a stored procedure, function, or trigger.

OLAP

Online Analytical Processing. OLAP allows users to analyze database information (usually historical data) from multiple database systems at one time. Instead of providing data in a multi-dimensional (row and column) format, OLAP data is multidimensional, thereby facilitating the grouping, summarization and comparison of data in various ways. Such reports include, for example, comparing purchases in Q1 to purchases in Q2 to purchases of a branch in another country in Q1. These reports provide business managers with data required for trend analysis and decision making.

OLAP service

An optional part of SQL Server, which runs as a Windows service to provide OLAP facilities.

OLTP

Online Transaction Processing. OLTP allows real-time processing of SQL transactions, in order to support CRM, ERP, and other time-critical applications. OLTP is characterized by high rates of index lookups, single row modifications, and frequent commits.

Because real-time transaction processing is being increasingly carried out on a network, and can include more than one company, OLTP databases use client/Server processing and allow transactions to run on different computer platforms in a network.

Optimizer

A component of SQL Server, which takes the Query Tree and produces an optimal Execution Plan based on data sizes, indexes, join techniques and CPU, I/O & memory estimates. For further details, see Compile.

Optimizer cache

An area of memory used by SQL Server for managing the optimization of SQL statements.

osql

A command line utility that allows issuing SQL statements. Uses OLE-DB to connect to SQL Server.

Page life expectancy

The length of time in seconds that a database page stays in the buffer cache without being accessed, before it is flushed out.

Small values (less than 300) indicate that pages are being flushed out of the cache within a small period of time.

Paging

Disk I/O activity done by the operating system to manage its virtual memory. High paging rates can adversely affect performance.

Panel

A group of related components on the Foglight for SQL Server Instance Home page.

Parse

The process of breaking an SQL statement down into a format that is more easily understood by software. SQL statements are parsed before they are compiled into an execution plan.

Parser

The part of SQL Server that performs all parsing.

Per seat licensing

The SQL Server licensing mode, which enables deploying multiple instances of SQL Server, and allows given users to access all of these instances. See Licensing and Per server licensing.

Per server licensing

The SQL Server licensing mode that allows supporting a stated maximum number of simultaneous users for a specific installation of SQL Server. See Licensing, and Per seat licensing.

Performance alert

An SQL Server Agent object, which performs an action when the value of a performance monitor counter satisfies a condition. The action performed can be to send e-mail, pager, or Net Send messages, or to run an SQL Agent job.

Physical I/O

An I/O operation that is performed at the hardware level. See also Logical I/O.

Physical read

A disk read I/O where the requester must wait for the disk read operation to complete. In SQL Server, the requesting session waits while the page is read from disk. This is normally the most common type of read operation in SQL Server.

Physical write

A disk write I/O where the requester must wait for the disk write operation to complete. Normally SQL Server sessions do not wait for data and index write operations to complete.

Most modifications to data and index pages are made in the Buffer Cache, and after the change has been recorded in the Log, the user can continue without having to wait for the data and index pages to be written to disk. However, certain operations, such as create index, bulk insert, and restore, can require the user to wait for the data and index writes to complete.

PID

Process ID; a unique number that identifies a Windows process at any given point in time.

Pinned

A table-level option that can be used to force pages from that table to remain in memory after they are accessed. After a page of a pinned table or index is read into the Buffer Cache, it remains there until SQL Server is restarted.

Plan

See Query plan.

Plan cache

An area of memory that SQL Server uses for storing execution plans so that they can be re-used, thereby avoiding the Recompile procedure. This area of memory was formerly known as Procedure cache. For details, see Recompile.

Potential growth

The term that Foglight for SQL Server uses to refer to the amount of disk space by which a File, File Group, Log, or Database can expand. This is a combination of the file's Auto Grow parameters, current size, and the available free disk space.

Prepared SQL plans

Query plans corresponding to statements prepared using sp_prepare, sp_cursorprepare, or using auto-parameterization.

Primary key

A column or columns that have been formally declared as uniquely identifying a row in a table.

Privileged mode

An operational state of hardware or software that has the highest priority. This mode is primarily used by operating system services such as I/O and paging. Also known as Supervisor Mode and Kernel Mode. See User mode.

Procedure cache

See Plan cache.

Procedure plans

Query plans generated by creating a stored procedure.

Process

An instance of an application executing in Windows.

Profiler

A Microsoft tool that is part of SQL Server and allows tracing events within SQL Server itself.

Publication database

A database residing on the publishing server (Publisher), which is the source of data and database objects that are to be replicated.

Publisher

See Publishing server.

Publisher databases

See Publishing server.

Publishing server

Publishing server (also known as Publisher) is a SQL Server instance that makes data available to other locations through replication, by hosting one or more published databases. Each of these databases can have one or more publications, which define a logically related set of objects and data to replicate.

Pull subscription

In SQL Server replication, pull subscriptions acquire the replicated data from the publishing server using periodic updates, initiated by the subscribing server. When pull subscriptions are used, each subscribing server can specify the preferred update time. Pull subscriptions are mainly used for replicating data to many subscribers, or for load balancing of a heavily loaded network.

Pulse

Pulses in the Foglight for SQL Server home page move in the direction of dataflows. As the rate of data transfer increases, so too does the speed of the pulse. Pulses can change color when the statistic represented by the pulse moves to another threshold.

Push subscription

In SQL Server replication, push subscriptions require the publishing server to push any changes made to the data out to the subscribers. Push subscriptions are mainly used when data needs to be replicated in near real time.

Query plan

The method that SQL Server has determined to be the optimal way to satisfy an SQL statement. Also known as Execution Plan.

RAID

Redundant Array of Inexpensive Disks. RAID is used for describing the configuration of multiple physical disks into one logical disk. Windows supports both hardware RAID and Software RAID. The main RAID types are as follows:

•

RAID 0 — referred to as disk striping. Each part of the logical disk is spread out over multiple physical disks. Provides very good read and write performance, but no failure recovery.

•

RAID 1 — referred to as disk mirroring. Provides good recovery, but average performance.

•

RAID 5 — referred to as disk striping with parity. Provides good recovery and read performance, but sub-optimal write performance.

•

RAID 10 - (RAID 0 + 1) — referred to as disk striping and mirroring. Provides good recovery and read/write performance.

Random I/O

I/O in which a specific disk block is directly accessed. This is typically the I/O that results from index lookups.

Read ahead

The process by which SQL Server anticipates which pages a session will require, and reads them into the Buffer Cache before they are requested by the user connection. Used when SQL Server determines that a session is accessing table or index pages in a sequential manner. If the sequential processing does indeed continue, these pages will already be in the cache memory when required, thereby avoiding the need to wait for an I/O operation.

Recompile

The process of compiling a stored procedure part way through that procedure's execution.

Referential integrity

Referential Integrity ensures that foreign keys correctly map to primary keys. A referential constraint prevents the insertion or update of foreign keys for which there are no matching primary keys, by either preventing the deletion of primary keys if foreign keys exist, or deleting these foreign rows (DELETE CASCADE).

Relational data engine

A major functional part of SQL Server. Responsible for the parsing and optimization of SQL requests, controls query plan execution, and processes row sets from the storage engine.

Replication procedure plans

Query plans of a replication system stored procedure.

Role

An administrative group within SQL Server, which contains SQL Server logins, Windows logins, groups, or other roles. Roles can be thought of as a group of users, and can be assigned permissions.

Rollback

The process of undoing all database modifications that have been performed by a session since the beginning of its current transaction.

System Administrator. A SQL Server login that is created by SQL Server installation. Sa is part of the SYSADMIN system role, and as such has full access within SQL Server.

Schema locks

A lock mode that is used when compiling a query. Schema locks are used for preventing changes to the underlying tables structures while the query is compiled. See Lock mode.

Sequential I/O

I/O in which disk blocks are read in sequence. This is typical of the I/O that results from full table scans.

Session

A single connection from a client application to SQL Server. Many applications have multiple SQL Server sessions open at any point in time. See SPID.

Severity

Represents how critical an alarm is. A severity determines how Foglight for SQL Server behaves when the values for a metric fall within a user-defined range of values. A severity specifies whether the information returned in the metric represents normal or abnormal behavior for the system under diagnosis. For example, unusually high values might mean that a metric has crossed a threshold into a high severity state. This, in turn, could change the color of a component on the home page; for example, from orange to red when moving from a critical to a fatal state.

The severity determines what action Foglight for SQL Server takes when a metric value falls into the range defined by a threshold.

SGAM

Shared Global Allocation Map. Pages in SQL Server databases, which record the extents that are currently used as mixed extents and have at least one unused page.

Shared locks

Shared locks are applied to enforce a read-only state on a data page, allowing other transactions to acquire a shared lock even when the first transaction’s run has not been completed.

By default, SQL Server releases shared page locks after completing the page scanning is complete, rather then holding these locks until the statement completes or until the end of its transaction.

Show advanced options

A SQL Server configuration option that turns on the display of advanced configurations.

SMP

Symmetric Multi Processing. An SMP computer contains multiple equivalent CPU units.

Sort, Hash, Index Area

An area of memory used by SQL Server to manage sorting and index creation.

SPID

A unique number that identifies each SQL Server session at a given point in time. See Session.

Spike

An abnormally high value in a dataflow or graph.

Spinner

In the Foglight for SQL Server home page, a Spinner displays the current level of activity for a statistic that is not directional (for example, CPU usage). As the load increases, so too does the speed of the spin. If the statistic represented by the flow moves to another threshold, the spinner may change color. The combination of movement and color makes it easy to spot congested areas.

SQL Agent Mail

A MAPI session created by the SQL Server Agent service in order to send e-mail and pager messages.

SQL Mail

A MAPI session created by the MS SQL Server service. This allows SQL Sessions to send e-mail and pager messages using extended stored procedures.

SQL Plans

Query plans corresponding to statements prepared using sp_prepare, sp_cursorprepare, or using auto-parameterization. This definition includes also user-parameterized queries.

SQL Server Agent

A part of SQL Server that schedules and controls the execution of jobs, and monitors SQL Server for any defined alerts. Runs as a Windows service.

SQL Server authentication

The authentication mode where the user passes a SQL Server login name and password, which is checked against a SQL Server system table. The other authentication mode used is Windows authentication. For additional details, see Windows authentication mode.

SQL Server books online

See Books Online.

Standard deviation

A statistical function of SQL Server, which measures how widely values deviate from the mean (that is, the average).

Stolen pages

Pages “stolen” from the buffer pool to be used for miscellaneous server purposes, when Windows requires memory for another application. A large number of stolen pages may be normal for a short period (for example: if the system backup begins after large database batch run completes). However, constantly high value of this metric may indicate an overall system memory shortage.

Stored procedure

A series of Transact-SQL statements, stored as an object within a SQL Server database, that can be called (executed) by SQL Server sessions with appropriate permissions.

Support service

The term Foglight for SQL Server uses to identify SQL Server-related features that can be installed, and stopped or started independently from SQL Server. These services often run as Windows services in their own right. Examples are SQLServerAgent, MSDTC, and SQL Mail.

SYSADMIN role

The Server role that has full access to all SQL Server facilities. By default, this role contains the sa login and the Windows “Administrators” group. The use of Foglight for SQL Server is limited to members of the SYSADMIN role.

TDS

Tabular Data Stream. A private protocol self-describing data stream, which SQL Server uses in the “conversation” between client and Server.

TempDB

A SQL Server system database used for temporary storage and work areas. This database, which stores all temporary tables and stored procedures, is used by SQL Server for large sort operations.

Temporary tables and table variables

Temporary tables are tables created in the TempDB database. These tables are session-specific tables, that is, even though they are backed by physical disk and logged into the transaction log, the tables are automatically dropped when the session is closed. As a result, temporary tables are usually not written on disk, but only saved in the plan cache memory.

Table variables, on the other hand, are created in the memory and exist there until the running of a single Transact-SQL (T-SQL) batch is completed. Unlike temporary tables, they do not require locking and are less I/O-intensive.

Threshold

A range of values that may be returned by a metric. If the metric falls within this range, Foglight for SQL Server checks the threshold's severity to determine how to behave. For example, the component representing this metric might change color.

Torn page detection

A database-level option that causes SQL Server to detect I/O operations that were partially complete due to system crashes (for example, power failures).

Transaction

A group of one or more database modification statements, which are combined into a logical unit of work that is either wholly committed or rolled back.

Trigger

A series of Transact-SQL statements, which is stored as an object within a SQL Server database and linked to a data modification statement and a specific table. Whenever data in that table is modified, the trigger is executed by SQL Server, and can perform complex data validation or extra processing.

Trigger plans

Query plans generated by creating a trigger.

Truncate