HP Enterprise SQL Optimizer (HP ESO) 1.0 User Guide HP Part Number: AM426-9013A Published: April 2012 Edition: 2.
© 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...
Support and Other Resources.....................................................................26 Contacting HP .......................................................................................................................26 Before You Contact HP.......................................................................................................26 HP Contact Information.......................................................................................................
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 2.0.
Figure 2 Package Setup Window 4. The second installation window displays. Click Install to continue. The installation begins, with a progress bar indicating status. Figure 3 Setup Window #1 5. When the installation finishes successfully, click Close to exit. Figure 4 Setup Window #2 NOTE: The HP ESO application files are installed into the %ProgramFiles%\McPerfOpt folder, where %ProgramFiles% is typically: C:\Program Files.
If the prerequisite HP Insight Management WBEM Provider software is not installed on the system, you will see the following warning message during HP ESO installation: Figure 5 Install Warning Window Uninstalling the Software To uninstall HP Enterprise SQL Optimizer, follow these steps: 1. Login as Administrator on the target system. 2. Navigate to the Add/Remove panel (or Programs and Features in the Control Panel). 3.
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 7 SMH Sign In Page Your user name and password must be a valid account in a group 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. 4.
5. The Enterprise SQL Optimizer Main page displays. Refer to the next section for instructions on using HP ESO. 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.
Figure 10 Floating Help Text 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. Configuration Data Collection The Data Collection feature collects parameters from the Windows OS and SQL Server, such as CPUs, memory and IO utilization, and SQL Server transaction rates.
Figure 12 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 13 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.
• New Value ⇒ This field contains the new parameter value to be applied to the system. By default, this field contains the recommended value. However, you can change this value if desired. • SET ALL button ( ) ⇒ Sets all of the parameters shown in the New Value fields on the page. Click this button to make the changes take effect immediately. Changes requiring a system or SQL Server service restart are indicated after the changes are applied.
Lightweight Pooling Use the Lightweight Pooling option to reduce system overhead associated with the excessive context switching sometimes seen in symmetric multiprocessing (SMP) environments. When excessive context switching is present, lightweight pooling can provide better throughput by performing the context switching inline, thus helping to reduce user/kernel ring transitions.
Figure 15 Database Page File Growth The File Growth option specifies the method of incremental allocation applied when an operating system file is extended. Possible values are OFF, KB, or % (Percent). SQL Server files can grow automatically from their originally specified size. When you define a file, you can specify a specific growth increment. Every time the file is filled, it increases its size by the growth increment.
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. This logic results in less conflict over CPU resources among under- and highly-utilized SQL instances.
Figure 17 Storage Page Database RAID Level The recommended RAID level displays on this screen. Storage of TempDB or TempDB Log files is recommended on RAID 1 + 0 or RAID 1 drives. 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. Network The Network page includes controls for setting network interface NUMA Node, Interrupt, and Port affinities.
Figure 18 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.
Figure 19 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 20 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 REPORT button ( ) to create the report.
Figure 21 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 22 Online Help Page Help Pages 25
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 Edition 1 is the initial release of this document, dated November 2011. This second edition provides additional software requirement information under “Software Requirements” (page 7), and added new subsections,“Microsoft SQL Server Security Settings” (page 7) and “Uninstalling the Software” (page 9).
