Find Long Running SQL
The SQL - Long Running SQL alarm alerts you to SQL that has been running longer than a specified time frame. Because this time frame is unique to each environment, the SQL - Long Running SQL alarm, by default, is not configured.
|
Scenario You work in an OLTP environment and often get complaints about delays in server processing. You want to be notified when SQL on a particular server is running longer than expected so you can investigate what is causing the delay. You configure Spotlight to raise a medium severity alarm when it detects SQL running for longer than five minutes. When the SQL - Long Running SQL alarm is raised, you use the Sessions drilldown to view the long running SQL. |
To configure the Alarm: SQL - Long Running SQL
-
Click Configure | Alarms.
- Select a connection to configure. Select Default Settings to apply the configuration to all connections.
- Select the alarm SQL - Long Running SQL.
- Select Override the default settings for the alarm "SQL - Long Running SQL".
-
Click Add Severity and select a severity.
Scenario: Click Add Severity and select Medium.
- Select the check box of the new severity.
- Copy and paste the text in the Description cell from the Normal severity to the new severity.
-
In the Start cell, type the duration in seconds, of how long the SQL should run for before the alarm is raised.
Scenario: In the Start cell, type 300 .
Tip: You can also configure different alarm severities using keys. In the SQL - Long Running SQL alarm, the key is the SPID.
Configure the Virtualization Overhead Guage
The Virtualization Overhead gauge shows the percentage of CPU that is unavailable to this virtual machine because it is being consumed either by other virtual machines or by VMware itself.
In a virtual environment, the physical CPU of a host is shared by virtual machines and the VMware Hypervisor. This means there can be multiple processes wanting to use the physical CPU of the host at the same time. The result is that virtual machines may be ready to run but have to wait to be scheduled on a CPU. Vmware call this “ready time”. The Virtualization Overhead gauge shows the amount of ready time as a percentage of the theoretical maximum CPU available to the virtual machine.
To see virtualization data on Spotlight Home Pages
- There needs to be a connection to the VMware Server. See "Connections to VMware Servers" (page 1) for more information.
- In the Spotlight Browser, right-click the Windows connection and select Properties.
- In the Host field, select the connection to the VMware Server.
- In the VM Name field, select the name of the virtual machine.
The Virtualization Overhead gauge now displays data on the corresponding Windows Home Page and SQL Server Home Page.
Tip: SQL Server connections automatically use the virtualization data from their corresponding Windows connections. You do not need to configure the SQL Server connection once the Windows connection is configured.
Connections to VMware Servers
To create a connection:
Note: Use the following procedure to create a VMware connection to the ESX or VirtualCenter server that hosts the Windows machine.
-
Click Configure | Connections.
- In the Connection Bar on the left, click VMware.
-
Double-click Add new connection.
-
Complete the connection details:
Field Description Address
The IP address or hostname of the ESX server or VirtualCenter server that hosts the Windows machine.
Tip: If you have a VirtualCenter server then connect to that in preference to the ESX host. That way if the virtual machine migrates from one host to another the VirtualCenter server can still be interrogated for data for the Virtual Machine.
User A valid user account on the target system.
Note: For Spotlight to monitor a VMware server, the username specified must have at least a read-only role for the ESX server or VirtualCenter server that you want to connect to.
Password The password required to connect the specified user to the server.
