Spotlight™ on SQL Server® Enterprise
11.0.2
© 2014 Dell Inc. ALL RIGHTS RESERVED.
This guide contains proprietary information protected by copyright. The software described in this guide is furnished under a software license or nondisclosure agreement. This software may be used or copied only in accordance with the terms of the applicable agreement. No part of this guide may be reproduced or
transmitted in any form or by any means, electronic or mechanical, including photocopying and recording for any purpose other than the purchaser’s personal use without the written permission of Dell Inc.
The information in this document is provided in connection with Dell products. No license, express or implied, by estoppel or otherwise, to any intellectual property right is granted by this document or in connection with the sale of Dell products. EXCEPT AS SET FORTH IN THE TERMS AND CONDITIONS AS SPECIFIED IN THE LICENSE AGREEMENT FOR THIS PRODUCT, DELL ASSUMES NO LIABILITY WHATSOEVER AND DISCLAIMS ANY EXPRESS, IMPLIED OR STATUTORY WARRANTY RELATING TO ITS PRODUCTS INCLUDING, BUT NOT LIMITED TO, THE IMPLIED
WARRANTY OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE, OR NON-INFRINGEMENT. IN NO EVENT SHALL DELL BE LIABLE FOR ANY DIRECT, INDIRECT, CONSEQUENTIAL, PUNITIVE, SPECIAL OR INCIDENTAL DAMAGES (INCLUDING, WITHOUT LIMITATION, DAMAGES FOR LOSS OF PROFITS, BUSINESS INTERRUPTION OR LOSS OF INFORMATION) ARISING OUT OF THE USE OR INABILITY TO USE THIS DOCUMENT, EVEN IF DELL HAS BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. Dell makes no representations or warranties with respect to the accuracy or completeness of the contents of this document and reserves the right to make changes to specifications and product descriptions at any time without notice. Dell does not make any commitment to update the information contained in this document.
If you have any questions regarding your potential use of this material, contact: Dell Inc.
Attn: LEGAL Dept 5 Polaris Way Aliso Viejo, CA 92656
Refer to our web site (software.dell.com) for regional and international office information. Patents
Protected by U.S. Patent #6,901,582. Additional patents pending. Trademarks
Dell, the Dell logo, Toad, Toad World and Spotlight are trademarks of Dell Inc. and/or its affiliates. Microsoft, Windows, Windows Vista, Windows Server, Internet Explorer and SQL Server are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries. VMware and ESX Server are either registered trademarks or trademarks of EMC Corporation in the United States and/or other countries. Citrix® and Xen® are trademarks of Citrix Systems, Inc. and/or one or more of its subsidiaries, and may be registered in the United States Patent and Trademark Office and in other countries. Pentium is a trademark of Intel Corporation in the U.S. and/or other countries. Other trademarks and trade names may be used in this document to refer to either the entities claiming the marks and names or their products. Dell disclaims any proprietary interest in the marks and names of others.
Legend
CAUTION:A CAUTION icon indicates potential damage to hardware or loss of data if instructions are not followed.
WARNING:A WARNING icon indicates a potential for property damage, personal injury, or death. IMPORTANT NOTE,NOTE,TIP,MOBILE, orVIDEO:An information icon indicates supporting information.
Contents
Use Spotlight on SQL Server 4
Diagnose CPU 4
Diagnose IO 5
Diagnose blocking 7
Playback 8
Identify expensive SQL 10 Turn on and configure SQL Analysis 10 Real-time data and expensive SQL 12 Data over a selected time frame and expensive SQL 13 Filter displayed and collected data 14 Report on database growth 15 Analyze wait statistics 17 SQL - Long Running SQL alarm 17 Configure the virtualization overhead gauge 18 VMware | Connection Details 19
Troubleshoot Spotlight on SQL Server 20
Installation issues 20
Remote installation of the diagnostic server fails 20
Connection issues 20
Problems connecting to a SQL Server instance 20
WMI errors 21
Miscellaneous issues 24
Monitor multiple SQL Server instances 24 Unable to perform certain operations 24 Connection to a SQL Server - missing performance counters 25 Connection to a SQL Server - SQL Activity Drilldown | Sessions page 25
Playback 26
About Dell 27
Contacting Dell 27
Technical support resources 27
1
Use Spotlight on SQL Server
Diagnose CPU
Spotlight continually monitors CPU usage on the target server and alarms if it crosses a threshold. The thresholds are:
CPU Usage Alarm severity
0 – 67% Normal
67 – 80% Low
80 – 93% Medium
93 – 100% High
A CPU alarm indicates that a bottleneck on CPU is likely. This bottleneck could exist for a number of reasons – expensive queries could be running, excessive compilation or recompilation could be occurring, or a process on the server, not even related to SQL Server, is ‘hogging’ the CPU. With Spotlight, you can investigate the causes of a bottleneck both in real-time and in the immediate past.
When Spotlight detects that there is a bottleneck on the CPU, an alarm is raised. You can see alarms on the Spotlight home page, and in Spotlight Today.
Our first order of business is to find out what process is using all of the CPU.
From the Spotlight Home Page From Spotlight Today
ClickDiagnostics. Select the alarm and clickDiagnose.
This opens the Diagnostics drilldown. The Diagnostics drilldown helps you answer questions such as:
l Why is CPU usage higher than usual? l Is the SQL Server causing high CPU usage?
l If it is the SQL Server, is it optimizing queries or executing them?
The first grid on the CPU page shows possible causes of CPU pressure and the likelihood that each is contributing to high CPU usage on the server being monitored. Click an item in the grid to display an explanation of the diagnosis and supporting information.
Diagnose IO
Sustained high I/O wait time rates are a good indicator that of a disk subsystem bottleneck and that I/O device service times will be degraded. When Spotlight detects unusually high disk activity on the SQL Server machine
it raises an alarm.
Spotlight looks at SQL Server I/O wait times to determine if there is an IO bottleneck. A value larger than 15 ms/IO indicates a possible bottleneck.
The thresholds for the I/O wait time alarm are:
I/O Wait Time Alarm severity
0 - 15 Normal
15 - 50 Medium
50 - infinity High
After detecting a potential disk bottleneck, Spotlight displays an alarm on the home page and in Spotlight Today.
The first thing we need to do is identify the process that is generating high amounts of I/O activity.
From the Spotlight Home Page From Spotlight Today
ClickDiagnostics | IO. Select the alarm and clickDiagnose.
This opens the I/O Diagnostics drilldown. The I/O Diagnostics drilldown helps you answer questions such as:
l Why is the I/O higher than usual? l Is the high I/O due to paging activity? l Is the SQL Server causing the high I/O?
l If the SQL Server is causing the high I/O, is it through query executions, scan operators, PAGE_IO_LATCH
or LOG waits?
The first grid on the I/O page shows the possible causes of high I/O and the likelihood of each contributing to this instance of the alarm. Click an item in the grid to display an explanation of the diagnosis and supporting information.
Diagnose blocking
Blocking in SQL Server occurs when a session that was performing some task is unable to progress because it must wait on a resource that is currently being used by another session. The resource that is being waited on can be either a physical structure like a lock on a table or an internal SQL Server resource such as a latch. Excessive blocking can be a major cause of poor application performance since it reduces the throughput of the system. Often a user of an application does not realize that they are waiting on a resource held by another user. From their point of view, it often seems like their application has stopped responding.
When diagnosing blocking, you want to start your investigation by answering the following questions:
l Who is waiting on what?
l How long have they been waiting?
l What SQL was running while they were waiting?
Spotlight not only alerts you to blocking issues in your database but also helps you answer the above questions so you can quickly resolve the problem causing the blocking.
When Spotlight detects a blocking issue, an alarm is displayed on the home page.
The first thing we want to do is look at the Blocking drilldown. The Blocking drilldown shows details about the current blocks and in particular, answers the questions stated above.
1. On the Spotlight homepage, click the Blocked Processes button.
2. If multiple alarms have been raised, select the alarm you want to investigate further. Note that some alarms remain raised until they are acknowledged.
3. Click Diagnose to open theBlockingdrilldown.
The Blocking grid shows the sessions that are either blocked or are causing other sessions to be blocked. If the selected alarm was raised in the past the Blocking drilldown opens at the moment in time when the alarm was raised.
The hierarchy in the tree diagram makes it easy to see which session is causing the blocking. In addition, the resource on which the sessions are waiting is displayed. The time spent waiting and the SQL last executed are also displayed.
To view session details.
1. Select the entry in the grid. 2. ClickView Session Details.
Playback
View events and data collected in the recent past as though they were happening in real-time.
Once a time is selected, the display of the Spotlight home page, Spotlight Today and Spotlight drilldowns returns to that time, so you can view historical data in the same way as you view a live connection.
To playback time
1. Select a monitored connection. 2. Select theMonitorribbon tab.
3. Select from the available options on theMonitor | Playbackribbon tab.
Icon Name Description
Playback Select an alarm or moment in time in the past to return to. Note to clickReal Timeto return to the present time.
Control Description
Select the date. Use the back and forward buttons to change the date.
Select the time of day.
Press and hold to magnify the time scale. The time scale is colored according to the most severe alarm raised against the connection at that time.
The alarms list shows alarms raised on the selected day. It scrolls to show those alarms raised at the given time.
Select an alarm to playback to the time when the alarm was raised.
Real Time
Return to present time.
Rewind Go back in time. Click the associate arrow to define how far back in time to travel: 1 minute, 5 minutes, 10 minutes, 1 hour or 1 day.
Icon Name Description
Skip Starting from the past, skip forward in time. Click the associate arrow to define how far forward to skip: 1 minute, 5 minutes, 10 minutes, 1 hour or 1 day.
Play Starting from the past, step forward in time through the alarms in sequence. Click the associate arrow to define the speed of play.
NOTE:Playback data is not displayed for SQL Azure database connections.
TIP:On the Spotlight Home Page you can view the recent history of a single component.
Identify expensive SQL
Identifying expensive SQL can be a time consuming process. Use the SQL Analysis feature in Spotlight to identify SQL that is consuming resources on your server.
With SQL Analysis, you can quickly answer questions such as:
l What SQL has executed the most?
l What SQL consumed the most CPU or I/O in total? l What SQL consumed the most Average CPU or I/O? l What SQL took to longest time to execute?
Answering these questions will help you to direct your tuning efforts more productively.
SQL Analysis uses either ROWSET or sampling Server Side trace to collect raw data on SQL statement execution. It then aggregates this data by similar SQL statements so that potentially thousands or even millions of rows of raw data are distilled down into an aggregate picture of workload.
You can set filters at point of collection so that only the events that you are interested in are collected and when viewing the data, you can also filter the data to narrow the focus down even further.
Turn on and configure SQL Analysis
1. ClickConfigure | SQL Analysis2. Choose one of the following from the connection list:
l To change the setting for an individual connection, select the connection name. l SelectOverride the default settings.
l SelectEnable SQL Analysis.
l To change the setting for all connections, selectDefault Settingsand selectOverride the
3. ClickData Collection.
4. Configure the following fields on the Data Collection page:
Option Description
SQL Analysis is scheduled to collect everynminutes
How often Spotlight collects SQL Analysis data. The default is six hours.
To change how often Spotlight collects SQL Analysis data
a. Click the link.
b. SelectOverride the default settings for the collection 'SQL Analysis'.
c. Under ‘Store reporting data in the Spotlight Statistics Repository’, click theStore datalink.
d. Set the collection schedule. Data can be collected at regular intervals or at a specific time on one or more days.
Use continuous Rowset trace Select to use Rowset trace to collect SQL Analysis data.
Rowset trace is convenient though may affect throughput on busy servers. It should be used with care.
Use sampling Server Side trace
Select to use sampling Server Side trace to collect SQL Analysis data. Server Side trace is more suitable for busy servers than Rowset trace. Server Side trace requires additional configuration.
Run trace for or until trace file reaches
Server Side trace will run until one of these conditions is met.
Specify the maximum amount of time Server Side trace should run for. Specify the maximum size of the trace file.
Server trace file location (on the SQL Server host)
Type a location for the server trace file. The path is relative to the SQL Server instance.
For example,C:\Trace Files.
Retrieve data through SQL Server
Select to process trace files on the SQL Server instance. This option has the potential to affect throughput on busy servers.
Use this option when there are permission or firewall settings preventing the Spotlight Diagnostic Server machine from retrieving files from the SQL Server machine.
This is the default option.
Retrieve data from the file system
Select to process trace files on the Spotlight Diagnostic Server. The Spotlight Diagnostic Server machine must have the appropriate permissions required to connect to the SQL Server instance. This option is not available when configuring default settings.
Trace file location (from Diagnostic Server host)
Type the location of the server trace files on the SQL Server instance. The location should be a shared folder the Spotlight Diagnostic Server
Option Description can connect to.
For example,\\computername\Trace Files.
This option is not available when configuring default settings.
Real-time data and expensive SQL
You can use the SQL Analysis grid when load testing in a testing environment. It shows SQL Analysis data in real time.
If you are trying to answer the question “I am simulating application workload in my test environment and I want to look at a breakdown of SQL statements in real-time. I am interested in what SQL statements are consuming the most average CPU right now.”, you would want to look at an aggregate of SQL statement executions (workload) in real-time. The SQL Analysis grid in the SQL Activity drilldown allows you to do exactly that.
1. Select a SQL Server connection. 2. ClickMonitor | SQL Activity.
3. SelectSQL Analysis. 4. Do the following:
To identify Sort the grid by
SQL that consumed the most CPU Total CPU
The average CPU consumed by a particular statement Average CPU
SQL that was executed the most Execution Count
SQL with the highest logical I/O Average Reads
TIP:
l Use filters to refine the data shown in the grid. For more information, seeFilter displayed and
collected dataon page14.
l Some columns are hidden by default. To view hidden columns, right-click the grid header row
and selectOrganize Columns.
l Results are cleared when you leave the SQL Analysis grid. ClickClear Gridto clear the grid
manually.
l To wrap a column onto multiple lines, right-click the data content of the grid and select
Properties | Options.
NOTE:Word wrap may degrade the performance of Spotlight.
identified. To do this, clickOptimize SQL.
Data over a selected time frame and expensive SQL
The SQL Analysis - Workload view shows the aggregate workload over a selected time period. Use the SQL Analysis - Workload view when diagnosing slow response times.If you are trying to answer the question “What SQL statement consumed the most CPU over month end
processing?”, you would want to look at an aggregate of SQL statement executions (workload) over a time range. The SQL Analysis – Workload view allows you to do exactly that.
1. SelectReports | Views | SoSSE | SQL Server Performance | SQL Analysis Workload.
2. ClickReport | Select Connectionsand select at least one connection.
3. Select aTime Range.
4. ClickRefresh.
5. Do the following:
To identify Sort the grid by
SQL that consumed the most CPU Total CPU
The average CPU consumed by a particular statement Avg CPU
SQL that was executed the most Execution Count
TIP:Use filters to refine the data shown in the grid. For more information, seeFilter displayed and collected dataon page14.
Filter displayed and collected data
To filter the data displayed
Start from the data displayed in the SQL Analysis grid or the SQL Analysis Workload view
SQL Analysis grid SQL Analysis Workload view
1. ClickChange Filter.
2. SelectFilter results.
1. ClickFilter Data.
2. SelectFilter results.
To add a filter to the list
1. ClickAdd.
2. From theColumnlist, select the column you want to base your filter on. 3. From theConditionlist, select a condition to apply to the column.
4. In theValuefield, type a value to filter events by. You can use the percent sign (%) wildcard with theis like/is not likecondition.
TIP:
l You cannot use wildcard characters for the Database name when connected to a SQL Server 2000
instance.
l To turn off filtering, clear theFilter resultscheckbox.
l To edit or delete a filter select it in the filter list and click the appropriate button.
To filter the data collected
1. ClickConfigure | SQL Analysis.
2. Choose one of the following from the connection list:
l To change the setting for an individual connection, select the connection name. l SelectOverride the default settings.
SelectEnable SQL Analysis.
l To change the setting for all connections, selectDefault Settingsand selectOverride the
default settings.
l ClickAdd.
TIP:
l You can use the percent sign (%) and underscore (_) as wildcards.
l You cannot use wildcard characters in the Database name field when connected
to a SQL Server 2000 instance.
l When filtering on a string field such as DatabaseName or ApplicationName, use
only the "LIKE" and "NOT LIKE" operators. The other operators apply only to numeric values.
TIP:
l Use the Arrow buttons to change the order of the filters.
l Edit or delete a filter by selecting it in the filter list and clicking the appropriate button. l Filtering affects the average, minimum, maximum, and last values displayed in the grid,
as calculations are performed only on data retrieved from the SQL Server instance.
l To turn off filtering, on the Filters page, clickClear. This removes all filters.
4. You can specify the number and type of SQL Executions retrieved from the SQL Server instance a. Select theAdvancedpage.
b. In theAggregate the top n SQL statementsfield, enter the maximum number of SQL executions to be retrieved from the SQL Server instance.
c. From theSorting bylist, specify the criterion you want Spotlight to use to determine what the "top" SQL statements are. Choose from Average Duration, Average CPU, or Average IO.
Spotlight aggregates the data collected and then stores only the aggregated data.
Report on database growth
To report on database growth across multiple SQL servers, use the Large Databases view in Spotlight Reporting and Trending. The Large Databases view shows the largest databases on the selected SQL Servers. With Spotlight, you can view this data in a number of different ways.
1. SelectReports | Views | SoSSE | SQL Server Environment | Large Databases.
2. ClickReport | Select Connectionsand select at least one connection.
3. Select aTime Range.
4. ClickRefresh.
By default, the data is shown as a bar chart, listing each database on the selected servers in order of size. You can change the way the data is displayed.
For example, if you wanted to view the growth of your largest databases over time you can change the chart to a line graph. To do this, right click on the chart and selectProperties. With theCharttab to the front, select Linefrom the list.
To look at the space consumption proportionally you could use a pie chart.
You can use the data and chart from the view in your own reports by exporting the view. To do this, right click the chart and selectExport. Choose a location to save the file. Spotlight saves the chart as a JPG file and the grid as a CSV file.
Analyze wait statistics
When troubleshooting SQL Server performance issues, one area you can focus your investigation on is waits. Long wait times can be an indicator of performance bottlenecks so identifying where waits are occurring may assist you in your tuning efforts.
Spotlight not only provides an easy way to see if your SQL server has a problem with waits but also identifies on which particular resource waits are occurring. Spotlight takes the raw data from SQL Server and automatically calculates the rate of wait over time so you have a more current perspective.
To start your investigation into waits, go to the SQL Activity drilldown and click the Wait Statistics tab.
The Waits Detail grid shows detailed waits statistics for the SQL Server instance being monitored. It shows all wait types in SQL Server and breaks down the statistics into signal time and resource wait time. (SQL Server provides only wait time and signal wait time.) By default, the Waits Detail grid is sorted by Wait Time Rate (ms/s) which allows you to immediately see which wait types are being waited on right now.
SQL - Long Running SQL alarm
TheSQL - Long Running SQLalarm is raised when SQL has been running longer than a configured time. Because the (configured time) 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 page to view the long running SQL.
When the alarm is raised
l Open theSQL Server | SQL Activity Drilldown|Sessionspage. View the long running SQL.
Configuration
1. ClickConfigure | Alarms.
2. Select a connection to configure. SelectDefault Settingsto apply the configuration to all connections. 3. Select the alarmSQL - Long Running SQL.
4. SelectOverride the default settings for the alarm "SQL - Long Running SQL". 5. ClickAdd Severityand select a severity.
Scenario:ClickAdd Severityand selectMedium. 6. Select the check box of the new severity.
7. Copy and paste the text in the Description cell from the Normal severity to the new severity. 8. 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 alarm severities using keys. In the SQL - Long Running SQL alarm, the key is the SPID. See "Configure Keyed Alarms" in the online help for more information.
Configure the virtualization overhead
gauge
The Virtualization Overhead gauge shows the percentage of CPU that is unavailable to a 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
1. There needs to be a connection to the VMware Server.
2. Right-click the connection to the Windows Server® and selectProperties. 3. In the Host field, select the connection to the VMware Server.
4. 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.
corresponding connection to the Windows server host. You do not need to configure the connection to the SQL Server once the connection to the Windows server is configured.
VMware | Connection Details
Create a VMware® connection to an ESX Server™ or VirtualCenter server hosting a Windows server®.
To create / edit a connection
1. ClickConfigure | Connections.
2. Select theVMwareconnection type.
3. ClickAdd a new connectionor to edit, right click the connection and selectProperties.
Field Description
Address The IP address or hostname of the ESX server or VirtualCenter server hosting the Windows server.
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 VMware server.
For Spotlight to monitor the VMware server, the user must have at least a read-only role for the ESX server or VirtualCenter server.
Password The password required to connect the specified user to the server.
TIP:When done, clickTestto verify Spotlight can successfully establish a connection with the details provided.
2
Troubleshoot Spotlight on SQL Server
Installation issues
Remote installation of the diagnostic server fails
NOTE:This feature is available only in Spotlight on SQL Server Enterprise.Remote installation of the Diagnostic Server fails if the user has both a domain account and a local account with the same user name.
Additionally, if the user uses a domain account that does not have permission to access the remote machine (that is, the account does not have administrator privileges) then the remote installation will not complete.
Connection issues
Spotlight on SQL Server retrieves its data from the SQL Server via the Spotlight Diagnostic Server. The Spotlight Diagnostic Server also uses WMI queries to retrieve performance counter information from the Windows server on which SQL Server is running.
The Spotlight Diagnostic Server cannot connect to a server unless it can access all of this information. This means you need to have the appropriate permissions on the server.
Also, the data must be retrievable over your network. Spotlight on SQL Server requires more than just a specific port TCP/IP connection to SQL Server to do this. The port on the Spotlight Diagnostic Server must be open. For more information, see theSpotlight on SQL Server Deployment Guide.
Problems connecting to a SQL Server instance
If you have trouble connecting to a SQL Server instance, check that you can:l Connect to the SQL Server using another tool such as Microsoft's SQL Server Management
Studio, or sqlcmd.
l Connect to WMI using another tool such as Microsoft's WMI CIM Studio, orwmic. For more information see
the online help.
If you have problems with any of these checks, you will need to investigate and resolve these issues before you can connect to that server using Spotlight on SQL Server.
WMI errors
The following is a guide to troubleshooting WMI errors. For more information see the online help.
Error Solution The RPC server is unavailable. Exception from HRESULT: 0x800706BA
Spotlight on SQL Server is not able to establish a connection with the Windows server to be monitored.
Verify the following:
l The address of the server is entered correctly.Verify correct host name or IP
address. Verify the host is available (not currently shut down).
l The Remote Procedure Call (RPC) service is running on the Windows server to be
monitored.Verity that "Remote Procedure Call (RPC)" is running and set to auto start after restart.
l TCP Port 135 is open to internal traffic on the Spotlight Diagnostic Server and
the Windows server to be monitored.
WMI opens an undetermined port in addition to port 135. This can be troublesome in a firewalled environment. Specifying a port range for WMI is recommended for this type of environment.
l The Windows server to be monitored is not blocked by the firewall.
Either configure WMI to use a fixed ports range or enable remote administration exception.
Follow these steps to enable remote administration exception. On the Windows server to be monitored:
1) Open the Group Policy Object Editor (gpedit.msc), open Computer Configuration | Administrative Templates | Network | Network Connections | Windows Firewall 2) Open either Domain Profile or Standard Profile, depending on which profile you want to configure.
3) Enable the following exceptions: "Allow Remote Administration Exception" and "Allow File and Printer Sharing Exception".
http://msdn.microsoft.com/en-us/library/aa389286(VS.85).aspx
l The "TCP/IP NetBIOS Helper" service is running.Verity that "TCP/IP NetBIOS
Helper" is running and set to auto start after restart.
l The "Windows Management Instrumentation" service is running on the Windows
server to be monitored.Verity that "Windows Management Instrumentation" is running and set to auto start after restart.
Access is denied. Exception from HRESULT: 0x80070005 (E_
The Windows user specified is unknown to the Windows server or does not have administrator rights.
Error Solution ACCESSDENIE D) WMI connection time outs
The timeout value defaults to the value DCOM specifies (usually 60 seconds). You can adjust this value via dcomcnfg.exe
WMI query failed: Invalid class. [0x80041010]
The WMI class does not exist on the Windows server being monitored. Solution: Recreate the WMI classes.
Windows Connections
Either of the following will recreate the WMI classes:
l Open a command prompt window and run the following command:
wmiadap /f
l Use the Microsoft Extensible Counter List (Exctrlst) utility to enable the following
counters:
Win32_PerfDisk, Win32_PerfNet, Win32_PerfOS and Win32_PerfProc.
http://www.microsoft.com/downloads/details.aspx?displaylang=en&familyid=7FF996 83-B7EC-4DA6-92AB-793193604BA4
A reboot of the Windows server is required.
SQL Server Analysis Services Connections
Either of the following will recreate the WMI classes:
l Open a command prompt window on the server hosting the SQL Server Analysis
Services instance. Run the following command: wmiadap /f
l Unregister and re-register the WMI classes. To do so:
1. On the problematic machine with SQL Server Analysis Services installed, find out the SQL installation path. See below for the default installation path. Your installation path may be different.
SQL Server Analysis Services 2014 C:\Program Files\Microsoft SQL
Server\MSAS12.MSSQLSERVER\OLAP\bin\Counters SQL Server Analysis Services 2012
C:\Program Files\Microsoft SQL
Server\MSAS11.MSSQLSERVER\OLAP\bin\Counters SQL Server Analysis Services 2008
C:\Program Files\Microsoft SQL
Error Solution
SQL Server Analysis Services 2005
C:\Program Files\Microsoft SQL Server\MSSQL.2\OLAP\bin
Note:You are looking for the folder that contains the following files: For unamed instances:
MSSQLServerOLAPService
perf-MSSQLServerOLAPServicemsmdctr.ini For named instances:
MSOLAP$Your_SSAS_NamedInstanceName
perf-Your_SSAS_NamedInstanceNamemsmdctr.ini
If you need to check the named instance name, use services.msc. 2. At the command prompt, change folder to the installation path.
For example, cd C:\Program Files\Microsoft SQL Server\MSAS10.MSSQLSERVER\OLAP\bin\Counters
3. Run the following commands to unload and load counters:
unamed instances unlodctr MSSQLServerOLAPService lodctr perf-MSSQLServerOLAPServicemsmdctr.ini named instances
unlodctr MSOLAP$Your_SSAS_NamedInstanceName
lodctr perf-Your_SSAS_
NamedInstanceNamemsmdctr.ini
Tip:ReplaceYour_SSAS_NamedInstanceNamewith the SQL Server Analysis Services named instance name. If you need to check the named instance name, use services.msc.
4. Run the following command to parse all the performance libraries on the Windows server and refresh the performance counter classes on the Windows server:
wmiadap /f
5. Use wbemtest.exe to verify the WMI Classes exist.
WMI query failed: Invalid query. [0x80041017]
The columns returned by the WMI class are incomplete or unexpected.
Solution: On the Windows server to be monitored, update the WMI classes by running the following command at the command prompt.
wmiadap.exe /f
Seehttp://msdn.microsoft.com/en-us/library/aa394528(VS.85).aspxfor more information. 0x80014064
User
This error occurs when wmic command is executed locally on the target machine and credentials specified
Error Solution credentials
cannot be used for local connections
[0x800705af] The paging file is too small for this operation to complete. You may find the following link useful:
http://blogs.technet.com/b/kevinholman/archive/2010/06/09/wmi-leaks-memory-on-server-2008-r2-monitored-agents.aspx
WMI query failed: Out of memory. [0x80041006]
1. At the command prompt run "wbemtest"
2. Connect to the "root" namespace (not "root\default", just "root") 3. ClickOpen Instance. Specify "__ProviderHostQuotaConfiguration=@" 4. SelectLocal Onlyfor easier readability. You will see the threshold values.
5. Increase the MemoryPerHost value to something greater. For example, double it (256 MB)
6. Save Property 7. Save Object 8. ClickExit.
9. Restart WMI services.
Invalid verb The wmic command has attempted to access a WMI class that does not exist. Solution: Check the spelling of parameters on the wmic command.
Invalid Global Switch
The specified host, user or domain name contains special characters like '-' or '/'. Solution: Modify the command by adding quotation marks.
wmic /node: 'MonHostFQDN' /user: 'DOMAIN\USER' path Win32_PerfRawData_PerfDisk_ LogicalDisk get FreeMegabytes
Miscellaneous issues
Monitor multiple SQL Server instances
Spotlight on SQL Server is designed to monitor a maximum of 100 SQL Servers, Analysis Services or
Replication instances and 100 Windows servers. Exceeding this recommended limit on 32 bit environments may result in poor performance or product instability, due to the 1 Gb memory limit. On 64 bit environments theoretically more connections can be monitored as the 1 Gb limit no longer applies, however testing of this has been limited.
Unable to perform certain operations
If, whilst using the Spotlight on SQL Server client, a message is displayed stating:You are not connected to the Diagnostic Server as a member of the Spotlight Diagnostic Administrators group. Only members of that group can perform the requested operation.
and the user needs to be able to perform the operation that led to this message, the Spotlight on SQL Server client user must be added to the Spotlight Diagnostic Administrators group on the Diagnostic Server.
See “Update the Spotlight Diagnostic user groups” in theSpotlight on SQL Server Deployment Guide, for more information.
Connection to a SQL Server - missing performance
counters
Spotlight on SQL Server usessys.dm_os_performance_counters(SQL Server 2005 or later) and the SQL Server virtual tablesysperfinfo(SQL Server 2000) to retrieve data for many of its displays. In some rare cases, this table may not contain information. When this is the case, Spotlight on SQL Server will not be able to collect the data it requires, and will display "0" for many of its components. Most obvious will be theMemoryicons on the home page, which will show 0 MB of memory used by SQL Server. Also, many of the flows on the home page will show no activity, and many drilldowns will show incomplete information.
Spotlight on SQL Server raises an informational alarm shortly after connecting if it detects that thesysperfinfo orsys.dm_os_performance_counterstable contains no data.
You can confirm the existence of this problem by running the following SQL in SQL Server Management Studio for SQL Server 2005 or later:
select * from sys.dm_os_performance_counters
or by running the following SQL in Query Analyzer for SQL Server 2000: select * from master..sysperfinfo
If this query returns no records, then your SQL Server performance counters are missing and Spotlight on SQL Server will not be able to operate correctly.
Sometimes, for a variety of reasons, the SQL Server Performance Monitor counters will not show up as they should. Often, but not always, this problem can be fixed by following these steps:
1. At the command prompt, type the following: unlodctr.exe MSSQLServer 2. Then type: lodctr.exe <SQL Server path>\binn\sqlctr.ini
3. Reboot the server.
More information can be found in the SQL Server Knowledge Base atmsdn.microsoft.com.
Connection to a SQL Server - SQL Activity Drilldown |
Sessions page
If errors are displayed on the Session Trace page (SQL Activity drilldown | Sessions page), install on the Spotlight Diagnostic Server machine:
l SQL Server Management Tools
The version of SQL Server Management Tools required is dependent on the latest version of SQL Server monitored. When monitoring SQL Server 2012, SQL Server 2012 Management Tools are required.
Playback
Playback data is collected at scheduled intervals, in response to some alarms, and also while viewing a drilldown using the Spotlight client. This means that history data may not be available for a drilldown in Playback mode. You can change the rate at which data is collected. For more information see the online help.
About Dell
A b o u t D e llDell listens to customers and delivers worldwide innovative technology, business solutions and services they trust and value. For more information, visitwww.software.dell.com.
Contacting Dell
Technical Support:Online Support
Product Questions and Sales: (800) 306-9329
Email:
Technical support resources
Technical support is available to customers who have purchased Dell software with a valid maintenance contract and to customers who have trial versions. To access the Support Portal, go to
https://support.software.dell.com/.
The Support Portal provides self-help tools you can use to solve problems quickly and independently, 24 hours a day, 365 days a year. In addition, the portal provides direct access to product support engineers through an online Service Request system.
The site enables you to:
l Create, update, and manage Service Requests (cases) l View Knowledge Base articles
l Obtain product notifications
l Download software. For trial software, go toTrial Downloads. l View how-to videos
l Engage in community discussions l Chat with a support engineer
A
alarmsSQL - Long Running SQL 17
B
blocking 7
C
connection limit 24 connections
issues 20 troubleshoot 20 counters 25
CPU usage 4
D
database growth 15
E
error codes, WMI 21 expensive SQL 10
H
history 8 view 8 history browser 8
I
installation issues 20 IO bottleneck 5
L
live view 8
P
performance counters missing 25 playback 8
R
record and playback 8 rowset trace 11
S
server side trace 11 session trace errors 25 snapshots 8
Spotlight browser 8 SQL
expensive 10
SQL - Long Running SQL alarm 17 SQL Analysis
configure 10 filter data 14 SQL Server
connection limit 24 performance counters 25
T
troubleshoot connections 20 installation 20
performance counters 25 Playback 26
session trace 25
unable to perform certain operations 24 user groups 24
WMI 21
V
viewhistory 8
Virtualization Overhead gauge 18
W
wait statistics 17 WMI issues 21
