Spotlight on SQL Server Enterprise 6.0

Spotlight on SQL Server Enterprise is a powerful database diagnostic and resolution tool. Its unique user interface provides you with an intuitive, visual representation of the activity on a SQL server. Graphical flows illustrate the rate at which data is moving between server components. Icons display the value of key statistics and alarms. Alarms inform you of bottlenecks and other potential problems. Drilldowns show detailed information on all parts of your SQL Server.

Spotlight integrates data from various sources into a single display. It seamlessly combines data from SQL Server system tables, SQL commands, performance monitor counters, and the Windows registry. Spotlight diagnoses not just your SQL Server, but also the Windows machine that SQL Server is running on.

Spotlight on Windows Enterprise is now packaged and integrated with Spotlight on SQL Server Enterprise. The purchase of Spotlight on SQL Server Enterprise on each licensed server includes Spotlight on Windows Enterprise for free.

With the Enterprise version of Spotlight, diagnostic data can be collected continuously, even when Spotlight is not open on the desktop. A Diagnostic Server can perform data collection continuously from a middleware machine, ready for Spotlight to display whenever Spotlight is started on any client machine.

New in This Release

Why version 6.0?

Spotlight on SQL Server Enterprise has included components in this build to provide Spotlight Classic customers a clearly-defined and seamless migration path, which includes a build number that will guarantee consistency.

The development team welcomes any comments on the new features of this release, and information on any problems/limitations found in the product and not listed in Known Issues.

Resolved Issues

The following is a list of issues addressed in this release of Spotlight on SQL Server Enterprise.

Category	Resolved Issue	Defect ID
General
	Alarm on long running SQL Server agent jobs	ST#1442
	Read Hit Ratio returning zero	ST#1498
	Support SQL Server 2005 Analysis Services	ST#1673
	Use default database file locations when creating the work database	ST#1674
	Standardize the terminology for opening connections used in the UI	ST#1682
	Improve alarm support for SAN drives using keyed alarms	ST#1820
	Add more detailed information into the Blocked Processes alarm	ST#1862 ST#2643
	Support wildcards (such as "*"and "?") in the key ignore list on alarms	ST#1866
	Monitor Total Server Memory and Target Server Memory performance counters	ST#2302
	Incorrect paging information displayed in Spotlight on Windows Enterprise	ST#2562
	Connection names displayed incorrectly	ST#2592
	Document the Spotlight Statistics Repository data structure	ST#2725
	Some collections that write to the Spotlight Statistics Repository are not visible in the Schedules editor	ST#4062
	Arithmetic overflow errors when collation set to SQL_Latin1_General_CP1_CI_AS	ST#5211
	Report SQL Agent job failures for the specified time range only	ST#5800
	Configure background schedules using times instead of intervals	ST#5761
	Spotlight Statistics Repository is not purging data from spotlight_stat_keys table	ST#6235
	When filtering the SQL Analysis grid on duration it is not clear what units are required in the Value field	ST#6312
	Notification emails ignore OS regional settings	ST#6403
	Time Since Last Batch column in the Sessions drilldown sorts incorrectly	ST#6538
	Database drilldown columns may show erroneous values when the Spotlight client, Diagnostic Server, and an English version of SQL Server are on the same machine with German language settings	ST#6623
	"The SQL Server Agent service is not installed" alarm is raised when the SQL Server Agent service is installed, but not running	ST#6718
	"Locks - Blocked Processes" rule has an incorrect title	ST#6778
	Linked Windows connections are changed automatically in a Polyserve environment	ST#6898
	Misleading message displayed in SQL Analysis drilldown when browsing history	ST#7041
	Send keep-alive requests regularly from Spotlight client to Diagnostic Server to avoid disconnections in firewalled environments	ST#7182
	Use of port 3166 not documented	ST#7263
	Reporting and Trending Views should not show data beyond the selected time range	ST#7423
	Changing the Database context within a Custom Counter causes execution failures	ST#7443
	Web Reports server location is not upgrading correctly	ST#7612
	Server Alarm Report does not parse correctly and fails to resolve variables	ST#7677
	Deadlocks drilldown shows no data when failing to write a temporary file	ST#7731
	"Tables Missing Clustered Indexes or Primary Keys" view truncates rows with the same TableID	ST#7752
	Unexpected null data causes an error in the Spotlight Report Viewer	ST#7818
	Spotlight Today may display an incorrect time for an alarm that has been raised twice within an hour	ST#8083
	Deadlocks - SQL Query plan incorrectly formatted XML	ST#8129
	Spotlight creates unnecessarily large support bundles	ST#8219
	When using SQL Analysis Server Side trace with the data retrieval through file system option the duration columns are not calculated	ST#8226
	Connection errors may cause the Diagnostic Server to stop writing data to the Spotlight Statistics Repository	ST#8286
	Help incorrectly states the playback database stores data for a maximum of 30 days	ST#8291

Known Issues

The following is a list of issues known to exist at the time of this release of Spotlight on SQL Server Enterprise.

Category	Known Issue	Defect ID
General
	SQL Analysis and 64-bit versions of Windows. Using server side trace with the file system data retrieval option is not supported on Diagnostic Server machines running 64-bit versions of Windows.	ST#8224
	Errors in the Windows Security Log. Specifying OS Connection Details on Windows connections can cause WMI connection errors to be reported in the Windows Security Log of the machine being monitored. This does not affect Spotlight's monitoring capabilities.	ST#6722
	Monitoring SQL Server 2008 RTM. The Deadlocks page (SQL Activity drilldown) and the CPU page (Diagnostic drilldown) may not show data when monitoring a SQL Server 2008 RTM database. Workaround: Upgrade to SQL Server 2008 SP1.	ST#7495
	Error collecting data. The collection used to populate the Top SQL drilldown is unable to run when some databases on the instance are in restricted states of access (for example, Single User Mode, Offline).	ST#7584
	Analysis Services. Spotlight on Analysis Services Enterprise can monitor only named instances.	ST#7723
	Collection scheduling and Reporting and Trending. Scheduling a collection to run at different time periods for two or more monitored servers can cause gaps in the Reporting and Trending charts for multi-server views.	ST#6362
	Spotlight Enterprise Viewer does not indicate when alarms are snoozed. The alarm tool tip in the Spotlight Enterprise Viewer does not display snoozed alarms as being snoozed.	ST#6141
	Unusually large values displayed in Spotlight. Spotlight may display unusually large values on some components and charts. These values are incorrect and are due to inaccurate raw performance counter values returned by Microsoft Windows. The following components and charts are affected: Disk Activity gauge (Spotlight on SQL Server Enterprise home page). Disk Queue Length chart (Spotlight on Windows Enterprise > Disks drilldown > Logical Disk Activity page and Physical Disk Activity page). Disk Load chart (Spotlight on Windows Enterprise > Disks drilldown > Logical Disk Activity page and Physical Disk Activity page). Workaround: Reboot the machine returning large values.	ST#5796
	Printing and Windows Vista 64-bit. When Spotlight is installed on a machine running Windows Vista 64-bit printing may not function.	ST#4214
	Negative values in Wait Statistics drilldown. Negative values may be shown in the Waits Details grid.	ST#3757
	Response time SQL statement. When upgrading Spotlight on SQL Server Enterprise the formatting in the SQL statement used to measure response time for a SQL connection may be lost. Workaround: Replace the lost formatting by opening the Spotlight on SQL Server Enterprise Options window, selecting Diagnostic Configuration and then clicking SQL Server Response Time. Only members of the Quest Diagnostic Administrators group can change the SQL statement.	ST#3547
	Spotlight Statistics Repository and SQL Server 2000. Installing the Spotlight Statistics Repository on SQL Server 2000 is not recommended. Doing so can result in errors being displayed and no data being shown in some views (including the SQL Analysis - Workload and Wait Statistics views).	N/A
	Data sourced from SQL Server 2000 performance counters may be incorrect. Spotlight may display an incorrect size or procedure cache hit rate for SQL Server 2000. This does not apply to SQL Server 2005 or 2008.	ST#3057
	SQL Analysis grid. The Text Data column in the SQL Analysis grid may occasionally display blank rows. This is due to SQL Server not returning any data in the Text Data column from the trace.	CR#219577
	Virtual PC. Running Spotlight on SQL Server Enterprise on a Microsoft Virtual PC 2004 virtual machine is not recommended. There are a number of issues with Microsoft Virtual PC 2004 that may cause Spotlight to display data incorrectly.	CR#218911
	Storing statistics in the Spotlight Statistics Repository. The Store statistics in the Spotlight Statistics Repository option applies to both the SQL Server connection and its equivalent Windows connection. If you change this option for a SQL Server connection, the change is also applied to the equivalent Windows connection.	N/A
	SQL Analysis and filtering. When default filters are set in a mixed environment of SQL Server 2000, SQL Server 2005 and SQL Server 2008, if the filter uses a column that does not exist in one of the versions of SQL Server then the filter is ignored for that version.	CR#211872
	Server permissions and NetBIOS. For information on server permissions, see "Permissions and Requirements" in the Spotlight on SQL Server Enterprise Deployment Guide. This document is available in PDF from the Windows Start menu.	N/A
	Use of ATI Rage Video card. If you are using an ATI Rage video card or chipset (such as the ones used in Dell laptops) you may encounter display problems when using Spotlight.	N/A
Installation & upgrades
	Upgrading the Spotlight Statistics Repository. It is highly recommended that maintenance is performed on the Spotlight Statistics Repository before it is upgraded. For information on the recommended maintenance plan, see the Spotlight on SQL Server Enterprise Deployment Guide.	N/A
	Installing other Spotlights. Do not install other Spotlights into the Spotlight on SQL Server Enterprise folder. You will be unable to run Spotlight on SQL Server Enterprise if you do this. When installing other Spotlights you must install them into a different folder.	N/A
	Installing for multi-user access. If multiple users of a system install Spotlight for use, the additional users must be local administrators.	N/A
	Remote installation. Remote installation of Spotlight on SQL Server Enterprise fails if the user has both a domain account and a local account with the same user name.	N/A
Diagnostic Server
	System time changes. Stop the Diagnostic Server service before changing the system time on the Diagnostic Server machine.	N/A
	Moving a Diagnostic Server. If you want to move your Diagnostic Server to a different host machine and continue to use the current Spotlight Statistics Repository, you will not be able to report on data collected across the original and moved Diagnostic Servers. Contact Quest Support for assistance if you want to report on data collected by the original Diagnostic Server after the move.	ST#4178
	Renaming the Diagnostic Server. The Diagnostic Server host cannot be renamed without reinstalling the Diagnostic Server. To conserve configuration and history, migrate the Diagnostic Server to another host before the machine is renamed.	ST#3052
	Diagnostic Server user privileges. The minimum Windows privilege level required to run the Diagnostic Server is Power User. During runtime, the Diagnostic Server requires write access to folders in the C:\Program Files directory. Without the appropriate privileges, the Diagnostic Server cannot run.	CR#206558
	Unable to perform certain operations. If, whilst using the Spotlight on SQL Server Enterprise client, a message is displayed stating: You are not connected to the Diagnostic Server as a member of the Quest 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 Enterprise client user must be added to the Quest Diagnostic Administrators group on the Diagnostic Server.	N/A
Connections
	SQL Server instance names containing non-US ASCII characters. Spotlight cannot connect to SQL Server instances where the instance name contains non-US ASCII characters.	ST#3343
	Access errors when connecting to SQL Server 2005. If an access error related to xp_cmdshell occurs and the user changes the SQL Server configuration to allow access to xp_cmdshell, the SQL Server documentation states that you must disconnect and reconnect. To disconnect in Spotlight on SQL Server Enterprise, you must disable monitoring and enable monitoring for that SQL Server. In the Spotlight Browser, right-click the SQL Server connection and select Properties. On the Monitoring page, clear the Monitor this server option and click OK. Open the Monitoring page again and select Monitor this server and then click OK.	N/A
	WMI access denied alarm messages. A large number of WMI access denied alarm messages usually indicates a network problem such as a malfunctioning domain controller or DNS server. When the network problem is resolved, restart the Diagnostic Server service.	N/A
	Microsoft Cluster Server connection problem. If you are connecting to a Microsoft Cluster Server system that does not have control of all disk resources, the uncontrolled disks may display a value of zero for space in total and space available in Spotlight. To rectify this problem customize your disk view to not show this disk. (Do this by right-clicking the disk and choosing Disk Options).	N/A
	Migrating connection properties. When changing or upgrading a Diagnostic Server, some component properties such as MaxValue, ToolTips and chart customizations are not transferred. In this case, properties that are not transferred revert to the default settings.	N/A
Playback mode	History Browser and Playback mode. 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. To change the rate at which data is collected, go to Monitoring Configuration (Ctrl+M) and click Schedules.	N/A
Spotlight on Windows Enterprise	Errors when monitoring Windows 2000 Server. When monitoring a Windows 2000 server, errors may be displayed in Spotlight Today and the Alarm log. This is due to the Win32_PerfFormattedData_PerfProc_Thread WMI class not being installed by default.	ST#3092
	Open Sessions grid. Values displayed in the Elapsed Time and Idle Time columns in the Open Sessions grid (Network drilldown > Sessions tab) may not be accurate if the session has been running for more than 18 hours.	N/A
	Processes drilldown and Windows Server 2003 Service Pack 1. The following columns in the Processes drilldown show values in kilobytes (not megabytes) when monitoring a Windows Server 2003 Service Pack 1 machine: Peak Mem Usage (MB) Page File MB Page File MB Peak Pool Paged MB Pool Nonpaged MB These columns are hidden by default.	ST#5400
	Time stamp in Event Log drilldown. When monitoring a Windows Vista 64-bit or Windows Server 2008 64-bit machine, the time shown for events in the Event Log drilldown is in GMT, not local time.	ST#4625

Upgrade and Compatibility

You can upgrade from Spotlight on SQL Server Enterprise 2.6 or 3.0. If you are using an earlier version of Spotlight, you will need to upgrade to 2.6 before upgrading to 6.0.

Upgrading to a 6.0 Diagnostic Server

For more information about upgrading, see the Spotlight on SQL Server Enterprise Deployment Guide.

The Deployment Guide is available as a separate download from www.quest.com and after install, from the Windows Start menu.

System Requirements

The following tables outline the basic system requirements required to use Spotlight on SQL Server Enterprise. For further information, see the Spotlight on SQL Server Enterprise Deployment Guide. The Deployment Guide is available as a separate download from www.quest.com and after install, from the Windows Start menu.

Spotlight Client

Before installing the Spotlight client, ensure your system meets the following minimum hardware and software requirements:

Diagnostic Server

Before installing the Diagnostic Server, ensure your system meets the following minimum hardware and software requirements:

Supported Versions for Monitoring

Spotlight Statistics Repository

Before installing the Spotlight Statistics Repository, ensure your system meets the following minimum requirements:

Playback Database

Before installing the playback database, ensure your system meets the following minimum requirements:

Deployment Guidelines

For deployment guidelines, see the Spotlight on SQL Server Enterprise Deployment Guide.

Global Operations

This section contains information about installing and operating this product in non-English configurations, such as those needed by customers outside of North America. This section does not replace the materials about supported platforms and configurations found elsewhere in the product documentation.

This release supports any single-byte character set. Double-byte or multi-byte character sets are not supported. In this release, all product components should be configured to use the same or compatible character encodings and should be installed to use the same locale and regional options. This release is targeted to support operations in the following regions: North America, Western Europe and Latin America.

Getting Started

Contents of the Release Package

The Spotlight on SQL Server Enterprise release package contains the following products:

Installation Instructions

Refer to the Spotlight on SQL Server Enterprise Deployment Guide for installation instructions.

For More Information

Contacting Quest Software:

Contacting Quest Support:

Quest Support is available to customers who have a trial version of a Quest product or who have purchased a commercial version and have a valid maintenance contract.
Quest Support provides around the clock coverage with SupportLink, our web self-service. Visit SupportLink at http://support.quest.com/.

Technical Support

If you have questions about using Spotlight, please contact our technical support staff. Please include the version number and if your question is about an error message, include the error text as well.

Support bundles contain information for the active instance only. If you are using Spotlight to diagnose multiple instances, ensure that the correct instance is displayed before creating the support bundle.

This document 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 Quest Software, Inc.

If you have any questions regarding your potential use of this material, contact:

Trademarks

Quest, Quest Software, the Quest Software logo and Spotlight are trademarks and registered trademarks of Quest Software, Inc in the United States of America and other countries. Other trademarks and registered trademarks used in this guide are property of their respective owners.

Disclaimer

Disclaimer: The information in this document is provided in connection with Quest 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 Quest products. EXCEPT AS SET FORTH IN QUEST'S TERMS AND CONDITIONS AS SPECIFIED IN THE LICENSE AGREEMENT FOR THIS PRODUCT, QUEST 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 QUEST 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 QUEST HAS BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. Quest 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. Quest does not make any commitment to update the information contained in this document.

Platform	1 GHz Pentium 4 PC
Memory	1 GB RAM
Hard Disk Space	200 MB This space should exist on a local disk drive rather than a network drive.
Operating System	Microsoft Windows 2000 (Professional, Server, Advanced Server, Datacenter server) with Service Pack 4 Microsoft Windows Server 2003 (32-bit and 64-bit) Microsoft Windows Server 2008 (32-bit and 64-bit) Microsoft Windows Server 2008 R2 (64-bit) Microsoft Windows XP (32-bit and 64-bit) Microsoft Windows Vista (32-bit and 64-bit) Microsoft Windows 7 (32-bit and 64-bit)
Additional Software	SQL Server Client Tools
	Microsoft Internet Explorer 4.0 or later
	Microsoft Data Access Components (MDAC) MDAC includes the ODBC and other components that enables a connection to be established to a SQL Server database. The database may be local or remote. Your client machine must have MDAC 2.7 (or later) installed. MDAC is available from the Microsoft website: www.microsoft.com/data/download.htm. To find out the version of MDAC that you are currently running, you can download an application from the Microsoft website called comcheck. You can review the following Microsoft Knowledge Base article for more information: http://support.microsoft.com/default.aspx?scid=kb;EN-US;3012022
Other Requirements	CD-ROM drive or Internet connection (required for installation only). A monitor capable of supporting a resolution of 1280x1024 or greater, and 65536 colors (Hi Color).

Platform	2 GHz Pentium Dual Core
Memory	2 GB RAM
Hard Disk Space	300 MB If the Diagnostic Server’s connection to the Spotlight Statistics Repository or playback database is interrupted, the Diagnostic Server will cache data to the local disk. In this event, an extra 2 MB per hour, per monitored server is required.
Operating System	Windows XP 32-bit and 64-bit (Service Pack 2 or later) Windows Server 2003 (32-bit and 64-bit) Windows Server 2008 (32-bit and 64-bit) Windows Server 2008 R2 (64-bit) Windows 7 (32-bit and 64-bit) Note: On each monitored instance, Spotlight requires the network setting "File and Print Sharing" to be enabled and any firewall to be configured to: open port 1434 for UDP packets open the SQL Server TCP port (default is 1433)
Additional Software	.NET 2.0 or later must be installed on the Diagnostic Server machine.
	SQL Server Client Tools To use the Discovery Wizard to locate SQL Servers on your network the SQL Server Client Tools must be installed. To use Spotlight on Analysis Services Enterprise the Client Connectivity Tools must be installed.
	SQL Server Management Tools To use Spotlight on Analysis Services Enterprise, Session Trace, and SQL Analysis in Spotlight on SQL Server Enterprise, SQL Server Management Tools must be installed on the Diagnostic Server. The SQL Server Management Tools are available from any SQL Server 2005 or SQL Server 2008 edition except SQL Server Express and SQL Server Compact. The version required corresponds to the latest version of SQL Server that you are monitoring. Note, if you are monitoring SQL Server 2000 servers, you need the Management Tools from SQL Server 2005.

Database sizing	By default, the size of the Spotlight Statistics Repository is set to 1000MB (data size) and 250MB (log size). These sizes are flexible and can be changed by running the Spotlight Statistics Repository creation script. The script is located in: C:\Program Files\Quest Software\Spotlight Enterprise\Plug-ins\Diagnostics Server\Scripts
Hard Disk Space	Approximately 1.2 GB for each monitored SQL Server instance and the corresponding monitored Windows host on which the SQL Server instance resides. This estimate assumes default configuration in an average environment. Actual space used will depend on your environment and any data collection/storage configuration changes that are made. For more information, see the Spotlight on SQL Server Enterprise Sizing Wizard. You can access the wizard from the Spotlight Installation Center.
Recommended SQL Server version	To use Spotlight Reporting and Trending, it is recommended that the Spotlight Statistics Repository is installed on SQL Server 2005 or SQL Server 2008. Installing the Spotlight Statistics Repository on SQL Server 2000 can result in errors being displayed and no data being shown in some views. Installing the Spotlight Statistics Repository on SQL Server 2000 is not recommended. Note: Due to the size of the Spotlight Statistics Repository, it is recommended that this database is not installed on a SQL Express instance.
Other requirements	The SQL Browser service must be running on the machine where the Spotlight Statistics Repository is installed or you can specify the host and port number of the SQL Server instance you want to connect to, when creating and connecting to the Spotlight Statistics Repository. The TCP protocol must be enabled on the instance where the Spotlight Statistics Repository resides. For more information, see the Spotlight on SQL Server Enterprise Deployment Guide.
Maintenance	It is highly recommended that regular maintenance is performed on the Spotlight Statistics Repository. The recommended maintenance plan is outlined in the online help and in the Spotlight on SQL Server Enterprise Deployment Guide.
Upgrading	It is highly recommended that the Spotlight Statistics Repository is backed up before upgrading. For more information on upgrading the Spotlight Statistics Repository, see the Spotlight on SQL Server Enterprise Deployment Guide.

Database sizing	By default, the size of the playback database is set to 500MB (data size) and 125MB (log size).
Hard Disk Space	Approximately 270 MB for each monitored SQL Server instance and the corresponding monitored Windows host on which the SQL Server instance resides. This estimate assumes default configuration in an average environment. The Diagnostic Server automatically retains 7 days of historical data for each connection in the playback database. Actual space used will depend on your environment and any data collection/storage configuration changes that are made. For more information, see the Spotlight on SQL Server Enterprise Sizing Wizard. You can access the wizard from the Spotlight Installation Center.
Recommended SQL Server version	Due to the size of the playback database, it is recommended that this database is not installed on a SQL Express instance.
Other requirements	The SQL Browser service must be running on the machine where the playback database is installed or you can specify the host and port number of the SQL Server instance you want to connect to, when creating and connecting to the playback database.

Email	info@quest.com
Mail	Quest Software, Inc. World Headquarters 5 Polaris Way Aliso Viejo, CA 92656 USA
Web	http://www.quest.com

Welcome to Spotlight on SQL Server Enterprise	New in this Release	Resolved Issues	Known Issues	Upgrade and Compatibility
System Requirements	Global Operations	Getting Started	For More Information