HP Enterprise SQL Optimizer (HP ESO) 2.0 User Guide HP Part Number: AM426-9013B Published: June 2012 Edition: 3.
© Copyright 2011–2012 Hewlett-Packard Development Company, L.P. Legal Notices Confidential computer software. Valid license from HP required for possession, use or copying. Consistent with FAR 12.211 and 12.212, Commercial Computer Software, Computer Software Documentation, and Technical Data for Commercial Items are licensed to the U.S. Government under vendor's standard commercial license. The information contained herein is subject to change without notice.
Contents About This Document.....................................................................................5 Publishing History.....................................................................................................................5 Organization...........................................................................................................................5 1 Introduction...............................................................................................6 Features...
NUMA Node Affinity....................................................................................................28 Interrupt Affinity............................................................................................................28 Port Affinity..................................................................................................................28 Receive-Side Scaling..........................................................................................................
About This Document This document describes how to install and use HP's Enterprise SQL Optimizer (HP ESO) software. HP ESO was created for system administrators and HP support personnel responsible for installing, configuring, and managing HP ProLiant servers running Microsoft SQL Server. This document is not a tutorial. For the latest version, see the HP Technical Documentation website: http://www.hp.
1 Introduction HP Enterprise SQL Optimizer (HP ESO) is a software tool that greatly simplifies and consolidates the process of tuning HP ProLiant DL980 G7 servers running Microsoft Windows Server and SQL Server 2008, SQL Server 2008 R2, or SQL Server 2012.
2 Installing HP ESO Software Requirements The following prerequisite software is required in order for HP ESO to function properly: • HP System Management Homepage (HP SMH), Version 6.0 or later • Any Internet browser supported by HP SMH (refer to your HP SMH documentation for more information) • Microsoft .NET Framework, Version 3.
5. When the installation finishes successfully, click Close to exit. Figure 3 Setup Window #2 NOTE: The HP ESO application files are installed into the %ProgramFiles%\McPerfOpt folder, where %ProgramFiles% is typically: C:\Program Files.
Figure 5 Upgrade Window Downgrades are not supported. For example, if HP ESO 2.0.0.0 is currently installed you will see the following error screen if you try to install HP ESO 1.0.0.0, and the process will fail. Note the “Red X” icon. Figure 6 Downgrade Error Window Silent Installation HP ESO can be installed in silent (non-interactive) mode by adding the “/s” or “/silent” switch to the installer package's executable file, such as “cp016281.exe /s” when executing.
3 Using HP ESO To use HP ESO, you must first launch the HP System Management Homepage (SMH) software, which is also the starting point for many other HP server management tools. Starting System Management Homepage To run the HP System Management Homepage (SMH), follow these steps: 1. From the target system Desktop, click Start → All Programs → HP Management Agents → HP System Management Homepage. 2. When the security certificate warning displays, click Continue to this website.
Figure 8 SMH Sign In Page Your user name and password must be a valid account under the Administrator group (see figure below) configured in the SMH users groups. The Sign In button validates the values in the User Name and Password fields. If both values are valid, the HP SMH Home page displays. The Clear button clears the User Name and Password fields. The Question Mark icon (?) displays or hides a floating tooltip box containing information about the authentication mechanism and sign in process.
Figure 10 SMH Home Page 5. The Enterprise SQL Optimizer Main page displays. Refer to the next section for instructions on using HP ESO. Microsoft SQL Server Security Settings To access the SQL instances and set parameters through HP ESO, the specified user account must have serveradmin and/or sysadmin SQL server role. If you want to set these properties, follow these steps: 1. Open the Microsoft SQL Server Management Studio (see figure below). 2.
Getting Started Pages When first run, HP ESO displays the Introduction page in the Getting Started section. The left navigation pane displays links to all of the various HP ESO pages. IMPORTANT: Before using the HP ESO software, it is highly recommended that you go to the Configuration page to configure a few initial parameters that are recommended for best optimization of SQL Server. Quick Start Guide Because of the new session-based usage model in place for ESO 2.
Figure 13 Floating Help Text Administrator Session (Admin Session) Administrator session is created to ensure that only one authenticated user can use and change the different parameters in the system and the SQL server. When an Admin Session is started by a user, other users will be blocked from accessing the ESO pages. Before using ESO, make sure to start an Admin Session by clicking on the Enter Admin Session button ( ) on the top left corner of the page.
NOTE: The default session timeout of 30 minutes is configurable and may be overridden by typing the new timeout value in the New Value field under “ESO Session Configuration” section as shown in the SQL Server Login page, see below. SQL Server Login Upon starting an Admin Session, all SQL server instances will be shown along with input boxes for entering login credentials. The type of login (Windows Authentication or SQL Authentication) can be selected under the Authentication Type column.
NOTE: When there is only less than one minute remaining in the current Admin Session, some of the pages may not be accessible. To resolve the issue, click on the Extend Session button ( ). Settings Pages Use the Settings pages to configure some of your settings prior to discovery and optimization of the HP server, the Windows Operating System (OS), and SQL Server.
Figure 18 Configuration Page User input fields in the Data Collection Settings section include the following: • START ON ⇒ Lets you select the date and time that Data Collection starts. NOW is the default value and manually starts Data Collection right now. Click on the calendar icon ( ) to display a floating calendar for selecting a future date and time for starting Data Collection. • STOP ON ⇒ Lets you select the date and time when Data Collection stops.
Figure 19 Performance Monitor Software Discovery HP ESO's Software Discovery feature checks to see if the latest HP software, drivers, and firmware installed in the system. Software Discovery Reference Catalog Locations specify where to find the information about the latest available HP software, driver, and firmware updates. Possible choices are: • Web ⇒ HP ESO connects to an HP File Transfer Protocol (FTP) site to get the latest catalog. • Local ⇒ Lets you specify a system location for the HP catalog.
Figure 20 SQL Startup Option Page This Startup Option page provides a tab for each particular SQL instance. Clicking on a tab displays the trace flags available for that particular SQL instance. Trace flags are very sensitive to SQL server edition and versions. Only trace flags supported by the SQL instance are shown on the page with status. Detailed description on each supported trace flag is displayed on this page.
• SET button ( ) ⇒ Sets the specific parameter shown beside it. Click this button to make the change take effect immediately. Changes requiring a system or SQL Server service restart are indicated after the change is applied. • Enable Parameter Change check box ( ) ⇒ When checked, allows the corresponding parameter to be changed. When unchecked, the parameter retains its current value after clicking the SET or SET ALL buttons.
Figure 22 SQL Server Page Maximum Degree of Parallelism When SQL Server runs on a system with more than one microprocessor or CPU, it detects the best degree of parallelism. Specifically, it recognizes the number of processors employed to run a single statement, for each parallel plan execution. Use the Max Degree of Parallelism option to limit the number of processors to use in parallel plan execution.
applications have sufficient memory to run. If these other applications, such as Web or e-mail servers, request memory only as needed, then do not set the option because SQL Server will release memory to them as needed. However, applications often use whatever memory is available when they start and do not request more if needed.
Multiple Database File on a Drive HP ESO also recommends that the database files are located on a dedicated drive. If multiple database files are found on a drive, HP ESO's recommendation for relocating them on separate drives displays on this page. High Availability and Disaster Recovery Mirrored Database If a database is configured for mirroring, additional parameters for Database Status, Read-only Status and Mirror Status will be displayed, as shown in the figure below.
Table 3 Possible Mirror Status Values (continued) Value Description Disconnected The principal database and the mirror database are disconnected. Synchronizing The principal database and the mirror database are in the process of synchronizing. PendingFailover The principal database is initiating a failover. Synchronized The principal database and the mirror database are synchronized.
Table 5 Possible Replica Roles (continued) Role Description Secondary The Role is Secondary. Unknown The Role is Unknown. Clustered SQL Server Instance If a SQL server is clustered, additional parameters will be displayed for Cluster Network Name, Cluster Status and Computer Role, as shown in the figure below. Figure 26 Clustered Database Table 6 Possible Cluster Status Values Value Description Unknown The instance of SQL Server is in an unknown state.
processors (thereby reducing context switching). Such an association between a thread and a processor is called processor affinity. HP ESO generally recommends that you confine SQL instances with relatively light workloads to under-utilized CPUs. This results in some SQL instance affinities being assigned an unusually high order, or non-contiguous logical CPU number. Similarly, SQL Server and the operating system automatically manage the CPU affinities of highly-utilized SQL instances.
Figure 28 Storage Page Database RAID Level It is highly recommended that TempDB files and Log files for user databases be on physical disks with RAID 1 + 0 or RAID 1. Drive Free Space When drive free space reaches less than 20%, a Warning message displays, with a recommendation to either move the file to a larger drive or free up more space on the current drive. Volume Mount Point Volume mount points are specialized file system objects which are used to mount and provide an entry point to other volumes.
Figure 29 Network Page NUMA Node Affinity NUMA (Non Uniform Memory Access) Node is a logical grouping of processors that share common, directly-linked physical memory. HP ESO recommends CPU NUMA Nodes closest to the network interface I/O. Interrupt Affinity Network interface interrupt affinity settings include All Close CPUs, One Close CPU, or All CPUs. HP ESO recommends All Close CPUs in most cases.
Received-side scaling is enabled by default in Windows Server 2008 and Windows Server 2008 R2. To take advantage of RSS, network drivers must be written with RSS capabilities. Currently, RSS cannot make use of more than 64 processors with Windows 2008 R2, even with Service Pack 1, released in February 2011. Modern network drivers are configured to use RSS through settings found in the Windows Device Manager, in the Advanced Properties of the network interface.
Figure 31 System Page Power Plan The settings in this section resemble the Windows Power Option settings. The default HP ESO setting here is High Performance. System Update Advisory Information This section uses the Web catalog file to enumerate any system software components that are out-of-date and should be upgraded to their latest versions. PCI Controller Information This table enumerates all of the system's add-on PCI cards.
Figure 32 Workload and Performance History Page To view and/or export a performance report, follow these steps: 1. Select the CSV file you want to view in the Select the report from the list field. 2. On the right side of the page, select the counters you want included in the graph (use the Select/Unselect All check box for convenience). 3. Select the desired export file format (XML or CSV). 4. Click the Generate File button to create the report. To compare log files, follow these steps: 1.
Figure 33 Save/Rollback SQL Configuration Page The Save/Rollback SQL Configuration page displays the following values or controls: • Save Current Settings button ( ) ⇒ Saves the current settings to an XML file. You can enter comments and a description to make it easier to identify the saved settings in the future. • Restore Settings section ⇒ To retrieve, apply, or delete previously saved settings, follow these steps: 1.
Figure 34 Online Help Page Help Pages 33
4 Support and Other Resources Contacting HP Before You Contact HP Be sure to have the following information available before you call or contact HP: • Technical support registration number (if applicable) • Product serial number • Product model name and number • Product identification number • Applicable error message • Add-on boards or hardware • Third-party hardware or software • Operating system type and revision level HP Contact Information For the name of the nearest HP authorized resel
New and Changed Information in This Edition The initial release of this document covers HP ESO 1.0, published in November 2011. A second edition for an update to the HP ESO 1.0 was published in March 2012. This current edition provides documentation for HP ESO 2.0. The new features for HP ESO 2.0 are summarized in “What Is New In Release 2.0” (page 6).
