Hp ProLiant DL980 G7 Server Manuel d'utilisateur

HP Enterprise SQL Optimizer (HP ESO) 3.5User GuideAbstractThis document describes how to install and use HP's Enterprise SQL Optimizer (HP ESO) s

2 Installing HP ESOSoftware requirementsThe following prerequisite software is required in order for HP ESO to function properly:• HP System Managemen

Figure 2 Setup window #15. When the installation finishes successfully, as shown by the HP Setup window shown in Figure 3(page 11), click Close to exi

Figure 4 Installation warning messageUninstalling the softwareTo uninstall HP Enterprise SQL Optimizer, follow these steps:1. Login as Administrator o

Downgrades are not supported. For example, if HP ESO 3.5.0.0 is currently installed, and youtry to install HP ESO 1.0.0.0, 2.0.0.0, or 3.0.0.0, you wi

3 Configuring user accounts to enable access to SQL Serverand Analysis Services instancesPrior to using HP ESO, ensure that the appropriate user accou

Figure 7 SQL Server Management Studio Login Properties windowSet user account privileges in Analysis Services securityTo use HP ESO to access an Analy

Figure 8 Selecting Analysis Services properties4. In the Analysis Server Properties popup, select Security from the Select a page panel on theleft, as

Figure 9 Analysis Server Properties page5. If the account to be used to connect to the Analysis Services instance is not listed, click Add….6. Enter t

4 Using HP ESOHP ESO allows you to connect to SQL Server and Analysis Services instances to perform a varietyof tasks to help with system configuratio

Table 1 HP ESO tasks (continued)For more information...Task to perform...“Viewing and controlling CPUassignment parameters andView information about C

© Copyright 2011–2013 Hewlett-Packard Development Company, L.P.Legal NoticesConfidential computer software. Valid license from HP required for possess

NOTE: To avoid seeing this certificate error message in the future, follow the steps describedin this HP Support webpage:HP Systems Insight Manager (H

Figure 11 SMH Sign in pageYour user name and password must be a valid account in the Administrator group (seeFigure 12 (page 21)) configured in the SM

4. In the SMH Homepage (see Figure 13 (page 22)), click the HP ESO link in the Enterprise SQLOptimizer box.Figure 13 SMH Homepage5. The Enterprise SQL

Figure 14 HP ESO Introduction pageWhen you start an Administrator Session, the SQL Login page displays (see Figure 15 (page 24)),allowing you to speci

Figure 15 SQL Login pageQuick start: Tasks performed in a typical HP ESO user sessionOnce you have launched HP ESO, you can begin using HP ESO by foll

Figure 17 SQL Login credential specification options and the Connect buttonFor more information about setting credentials and logging into instances,

Figure 18 Left pane page selection links4. Configure and perform data collection. To do so, access the Data Collection page by selectingthe Data Colle

text that cannot be updated dynamically). The recommendation statements suggest changesthat you can make manually.IMPORTANT: To ensure suitability of

Figure 21 Floating help textFor information about online help provided by HP ESO, see “Using the online help Welcome page”(page 71).Starting an authen

Figure 22 Steps to enter an Admin SessionSQL Login page: Setting credentials and logging in to SQL Server and AnalysisServices instancesUpon starting

Contents1 Introduction...6Benefits of using HP ESO...

If similar login credentials will be used to log in to all SQL Server and Analysis Services instances,you can have the same login credentials applied

Settings pages: Setting parameters for data collectionUse the Data Collection page to configure some of your data collection settings prior to discove

Figure 27 Data Collection pageUser input fields in the Data Collection Settings section include the following:• START ON ⇒ Lets you select the date an

Figure 28 Performance MonitorOptimizations pages: Viewing and controlling optimizationrecommendationsThe Optimizations pages provide various types of

• SET ⇒ Sets the specific parameter shown beside it. Click this button to make the change takeeffect immediately. Changes requiring a system or SQL Se

To ensure you have the most helpful and up-to-date recommendations, first collect data for theworkload of concern, then view the Summary page (and if

Figure 31 Summary pageModifying how the Summary page displays and generates recommended valuesTwo registry keys control how the Summary page displays

2. After the script is generated, a link to the script appears further to the right of the button, asshown in Figure 32 (page 37).Figure 32 PowerShell

SQL Server database instance parametersInformation about some parameters might not be displayed, depending on the availability of thecomponents to whi

SQL Server from paging (swapping) the data to virtual memory on disk or from trimming the data.However, the Windows operating system can still page ou

Low Memory Limit...40Total Memory Limit...

that read data from disk into the system file cache, thereby lowering contention on the cache.Available only on SQL 2012 and later.Low Memory LimitSpe

Server modes (Multidimensional Mode, Tabular Mode, Sharepoint Mode)When installing SQL Server Analysis Services (SSAS), you can configure one of the f

Figure 35 Analysis Services mode typesDatabase page: Viewing and controlling database parameters and optimizationrecommendationsThe Database page cont

Figure 36 Database pageThe following subsections describe database parameters and recommendations that might be seenon the Database optimization page.

Updating statistics ensures that queries compile with up-to-date statistics. However, updating statisticscauses queries to recompile. HP recommends th

Estimated SizeThe estimated size of the partition in megabytes.Proactive CachingProactive caching provides automatic MOLAP cache creation and manageme

availability. More importantly, the Availability Group feature includes support of active secondaryreplicas, which dramatically improves resource util

Figure 38 Mirrored databaseTable 5 Possible database status valuesDescriptionValueThe database is available.NormalThe database is going through the re

Clustered SQL Server instanceIf an SQL Server is clustered, cluster status information will be displayed, including Cluster NetworkName, Status, Compu

CPU page: Viewing and controlling CPU assignment parameters and optimizationrecommendationsThe CPU page (see Figure 40 (page 50)) displays SQL Server

Volume mount point...60Drive Latency...

Figure 40 CPU pageThe following subsections describe parameters pertaining to CPU affinity and performanceoptimization.NOTE: HP ESO calculates CPU all

Viewing and understanding Database Engine Processor AffinityTo view current and recommended Database Engine Processor Affinity settings, expand the Da

master Auto check box. In Figure 42 (page 51), the master Auto check box for instance SQL2012_Blis circled in green. As described in more detail in “M

manually affinitize that CPU. If a CPU’s New Value check box is checked, it will be manuallyaffinitized after you click SET.Modifying and setting Data

1. If you want automatic affinity for all of the instance’s CPUs, check the New Value column’smaster Auto check box and click SET. Figure 45 (page 54)

2. To make your modifications take effect, click SET. The CPU page then displays a messageindicating that the corresponding CPU settings will be chang

NOTE: Do not set Database Processor Affinity and IO Affinity to the same CPUs. This can degradesystem performance (HP ESO displays a warning if the sy

NOTE: The Process GroupAffinity parameter is not supported with Analysis Services 2008 and2008 R2 instances; therefore, Analysis Services 2008 and 200

where the SQL Server is the main (or only) application running. HP ESO recommends and displaysthis flag for non-SAP instances only.Trace flag 1117Trac

One effect of using this trace flag is that the space requirements for tempdb may increase. Thetempdb may grow to as much as 5% or more of the user da

1 IntroductionThis chapter describes the main benefits and features of the HP Enterprise SQL Optimizer (HP ESO).Benefits of using HP ESOHP Enterprise

Volume mount pointThis parameter is for information purposes only; it is not settable using HP ESO. Volume mountpoints are specialized file system obj

Figure 50 NUMA Node and Interrupt Policy parametersNUMA NodeNUMA (Non Uniform Memory Access) Node is a logical grouping of processors that share commo

1. Click the white check box (under Enable IP) of the port to be affinitized, as is shown inFigure 52 (page 62). This port must have a valid IP addres

R2 (including Service Pack 1), RSS cannot support more than 64 processors. Beginning withWindows 2012, RSS can support more than 64 processors.Modern

host requires the presence of at least one Ethernet NIC. Windows 2012 supports up to 32 NICteams. NIC teaming supports RSS. The Windows Server TCP/IP

2. Click Modify Team. The Team properties dialog box appears, allowing you to rename theteam, add team members, or remove team members. If you select

• Teaming Mode — Algorithm used for NIC teaming, displayed as SwitchIndependent, Static,or LACP.• Load Balancing Mode — The method for network load ba

To specify the catalog location, specify the local directory containing the HP catalog file(hpsum.exe). This directory must be on the system where HP

Figure 58 Workload and Performance History pageTo view and/or export a report, follow these steps:IMPORTANT: To ensure that reports are most relevant

Figure 59 Generate or export file3. If you want to view a graph of the data in the selected file, click Show Graph.4. On the right side of the graph,

New features in release 3.5HP ESO 3.5 provides the following new features:• A Summary page that:Displays all recommended settable parameter values gat

Figure 60 Save/Rollback Configuration pageNOTE: Whenever you enter an Admin session, HP ESO automatically saves all network andphysical driver setting

View Detail feature responds only if the selected restoration point settings differ from thecurrent settings. To view details of a restoration point s

you to pages that describe introductory topics and tasks that are likely to be performed in typicalHP ESO sessions, as well as topics that provide rel

2. In the Search box (as shown in Figure 65 (page 73)), enter the term or terms for which youwant information. Press Enter or click List Topics.Figure

Table 10 Search features and restrictions (continued)Examples and descriptionsFeatures/rulesSearching for “cat dog mouse” renders a list of topics tha

NOTE: Before running any PowerShell script, you should set the PowerShell execution policy toeither RemoteSigned or Unrestricted, as follows:1. Set th

NOTE: By default, the script includes only those recommended settable values that differ fromthe values currently in effect. You can change this defau

Figure 68 (page 77) shows an example of such a PowerShell script.NOTE: HPSUM versions later than 5.3.5.0 are not supported.Figure 68 PowerShell script

5 TroubleshootingThe following sections describe tips for troubleshooting problems that might arise while using HPESO.HP ESO is not found on the SMH p

6 Support and other resourcesContacting HPBefore you contact HPBe sure to have the following information available before you call or contact HP:• Tec

• Data Warehouse (DW) / Business Intelligence (BI) identification and supportUser interface for entering login credentials for DW and BI instances◦◦ R

Related informationThe HP ESO release notes are available on the HP Business Support Center website from wherethe HP ESO software is obtained.The late

7 Documentation feedbackHP is committed to providing documentation that meets your needs. To help us improve thedocumentation, send any errors, sugges

• Improved graphical user interface (GUI) on all pages, with most significant changes on:CPU page — CPU affinity settings of all SQL instances are sho