Recommendations for tuning EPM (LDMS) and MS SQL for large enterprise Core Servers

Version 48

    Verified Product Versions

    Endpoint Manager 9.6Endpoint Manager 2016.xEndpoint Manager 2017.x

    Recommendations for tuning LDMS and MS SQL for large enterprise Core Servers

    Applies to LDMS 9.5, 9.6, 2016 & 2017

    For initial architecture design specifications refer to DOC-47706

    The following document is intended to provide some guidance for users to tune their Enterprise sized (5,000+ nodes) LDMS Core Servers and DBMS to enhance performance. Careful planning and additional tuning may be required based on Core / DBMS load and throughput.

    Many of the same principals do apply to environments of 1,000 - 5,000 nodes however often times environments in that size range can operate with less in depth tuning.

    This document assumes the user has experience with LDMS, Windows Server and has qualified MS SQL staff involved.

    Tuning may require assistance from your DBA or LANDESK Professional Services. Contact your sales representative if you need guidance to implement any changes and they can discuss a Services engagement with you.

    Performance tuning is a process. As each Core and Database will have different load and resources there are no cookie-cutter solutions. So further measurement and tuning will likely be needed following any adjustments made.

    Note: For additional definitions related to the discussed MS SQL settings / topics and what they are please contact Microsoft Support.

    Warning: Prior to making changes to the Core or Database, all current settings should be recorded in the event they need to be reverted. A Database backup should also be captured & where possible the Core should be backed up / a snapshot taken.

    Microsoft SQL Database

     

    • MAX Degree Of Parallelism

      • For default installs this is set to 0 (unlimited). Currently not recommended for LDMS databases.
      • LANDESK recommends this should be set to the count of CPU Cores / 2 with a maximum of 8 (in the event there are 16 or more CPU Cores).
        • For Databases that are still exhibiting concurrent waiting tasks averaging above 10 over time at any given time or are undersized this setting may need to be set to CPU Cores / 3. Average concurrent tasks over time can be viewed in the activity monitor and other database tools, there are also many wait statistics scripts that can be found.
    • Cost Threshold for Parallelism

      • This can be left at default (5) unless further tuning is needed.
      • In the event the DBMS is experiencing problems related to Parallelism causing pressure instead of benefit this can be increased in small increments. Consult your DBA to determine if this is occurring.

    MAXDOP.JPG

    • Maintenance (Index, statistics, backup, cleanup, checkdb)

      • Maintenance should take place at night or during a maintenance window.
        • 1,000-5,000 Nodes: Reorganize indexes daily up to weekly, backup daily, checkdb daily up to weekly, cleanup weekly, update statistics weekly & rebuild indexes weekly (unless fragmentation levels indicate this should be done more often).
        • 5,000-10,000 Nodes: Reorganize indexes daily, backup daily, checkdb daily up to weekly, cleanup daily, update statistics daily & rebuild indexes daily up to weekly depending on fragmentation levels.
        • 10,000+ Nodes: Reorganize indexes daily, backup daily, checkdb daily up to weekly , cleanup daily, update statistics daily, rebuild indexes daily.
      • Recovery Model - Set to Simple unless using Full and logs are cleaned up and or shipped.
      • Auto Update Statistics - By default this runs automatically, also they are rebuilt after an index rebuild. In some environments experiencing pressure, waits and other problems related to statistics updating; users may need to disable this. Otherwise leave it enabled.

    SimpleRecovery.JPG

     

    • Hardware Allocation for the Database Management Server

      • Your DBMS should have enough RAM allocated to the LDMS database so that MS SQL Server can load all of the data pages, indexes, execution plans, etc into memory. If it does not have enough RAM to load all of this into memory then it will depend on disk more and this can become a bottleneck and reduce performance.
        • To ensure you have enough memory check the amount of used space for data on your database and allocate memory resources beyond that to allow for data file growth. MS SQL has a built in report to gather this information. Open SQL Management Studio, right click on the LDMS database, choose reports, standard reports & then click disk usage. The LDMS database should have more RAM allocated to it than the used data file size & index size. Always leave room for growth & take into account any other databases that may be on that database server.
      • Please reference to DOC-32223 for planning a server implementation and if the planned server node management count exceeds the outlined specifications then contact your Sales Representative. They can get in touch with the right people to supply hardware recommendations based on your needs.

     

    • Performance Monitoring on the Database Management Server

      • Performance of the DBMS can also be bench marked through perfmon. These performance monitoring counters can be used as indicators of how the main resources are performing (CPU, Memory, Network I/O and Disk I/O). These counters should be monitored over a typical 24 hour period. It is recommended that perfmon be configured to create a log over this period of time to ensure a large enough data sample is gathered.
      • For reference on the fundamentals of perfmon and definitions behind the counters please refer to TechNet. If the results of the capture show one or more counters do not meet recommendations this likely will mean there is a bottleneck in the associated resource.
      • To launch perfmon press the windows key and type perfmon.exe or locate perfmon.exe in Windows\System32 and run it from there.
      • This performance monitoring may need to take place repeatedly under different loads to verify performance.

     

    Counter Name

    Recommended

    Actual

    Processor: %Processor Time

    <70%

     

    System: Processor Queue Length

    <2

     

    Physical Disk: % Idle Time (Disk 0)

    >50%

     

    Physical Disk: Avg. Disk Queue Length (Disk 0)

    <2 per disk

     

    SQL Server Buffer Manager: Page Life Expectancy

    >300

     

    SQL Server Buffer Manager: Buffer Cache Hit Ratio

    >99

     

     

    Core Tuning

     

    • Tuning Predictable Core Traffic

      • LDMS agents have client settings for their regular check-in frequency for the major components. The check-in load is the majority of the work the Core and DBMS will do in most cases and can be tuned. Most check-ins are on a daily basis by default. Users can scale back local scheduled check-ins for components that don't need updates at that frequency.
      • Adhoc tasks and jobs don't fall under predictable traffic however users can still be mindful of low traffic times for scheduling non immediately required jobs.
    • IIS Application Pools logs

      • Review the c:\Windows\System32\LogFiles\HTTPERR\ logs for patterns around specific application pools. Generally warning signs are indicated by pages full of warning messages.
    • IIS Application Pools Tuning

      • LDAppVulnerability - This pools handles everything from security and patch as well as many other components of LANDESK. Some users are increasing the maximum worker processes, this is not recommended for this pool.
        • In the event that this pool needs more throughput the following reg key can be created.
          • HKLM\SOFTWARE\LANDesk\ManagementSuite\PatchManagement with the string value of "WebServiceMaxThreads" and data "25" or higher depending on throughput
        • Warning: This should not be increased by large increments until the impact to the Core and SQL Server is measured. By giving this application pool more sub-threads it can increase the resource utilization, therefore care should be taken that other components are not impacted by increasing the resource allocation of this pool.
        • By default it is using 20 sub-threads, this can be increased by increments of 5 until the performance goal is achieved.

    Registry.JPG

      • LDAppAPM - This pool handles Software Distribution requests. Its maximum worker threads can be adjusted up to 2 and observed to review if the performance goal is achieved.

    APM Threads.JPG

     

     

    • Inventory Settings

      • DB Threads in the Configure Services - Inventory - Advanced menu should be set between 1-4 depending on performance. In cases where the Database is not keeping up, turning the threads up will consume those resources and its own threads may deadlock on the SQL Server if it is not responding quick enough.
      • Note also that in LDMS 10 there is a degradation sample and percent configuration. When performance deviates based on the thresholds set for Inventory, it will log an event in the Windows Application Log. This is a good thing to keep an eye on to see if turning settings up is actually having a negative impact on performance.

     

    InvAdv.JPG

     

    Server Hardware & Virtualization

     

    • NUMA Enabled Core Server Hardware (Non-Uniform Memory Access)

      • NUMA enabled servers running a Core may need additional tuning for maximum performance. MS SQL currently doesn't require additional configuration for NUMA hardware when used with LDMS.
      • How to know if the Core Server is NUMA enabled.
        • The quickest way to check is to go into task manager on the Core Server, go to the performance tab, on the CPU tab right click on a CPU chart, change the graph to option and see if the NUMA option is available below the logical processors option.
        • Users can also consult their hardware vendor for this information.
      • Application pool tuning recommendations specifically for NUMA enabled Core Servers:
        • If the Core Server is NUMA enabled, LANDESK currently recommends the following changes. If the Core is not NUMA then do not make the process model changes listed below.
          • LDAppAPM - Application Pool - Advanced Settings - Process Model - numaNodeAffinityMode = Hard & numaNodeAssignment = Most Memory Available
            • Note: Monitor performance improvement or degradation that results from these changes and adjust accordingly.
          • LDAppVulnerability - Application Pool - Advanced Settings - Process Model - numaNodeAffinityMode = Hard & numaNodeAssignment = Most Memory Available
            • Note: Monitor performance improvement or degradation that results from these changes and adjust accordingly.
          • LDAppInventory - Application Pool - Advanced Settings - Process Model - numaNodeAffinityMode = Hard & numaNodeAssignment = Most Memory Available
            • Note: Monitor performance improvement or degradation that results from these changes and adjust accordingly.

     

    • NUMA Enabled Virtual Machine Server Hardware (Non-Uniform Memory Access)

      • In some cases the server hosting a Core or DB as a virtual machine may not have NUMA configured / enabled. This would prevent the administrator from properly tuning the Core server VM and performance can suffer.
      • VMware has also documented in their NUMA configuration best practices that it should be enabled in most cases.

     

    • Virtual Sockets or Virtual Cores

      • With some hypervisors there are options to add Virtual Sockets (processors) and or Virtual CPU cores. Some System Administrators reduce the number of sockets and increase the number of CPU cores in the event of software license restrictions on socket count for some applications. Endpoint Manager does not have CPU socket licensing restrictions but this is still a consideration to make during the original design. Performance tests between using more sockets vs more cores has shown no gain or loss so long as the total virtual CPU count is the same. Performance differences may however vary depending on the hypervisor and its configuration.

     

    This document contains the confidential information and/or proprietary property of Ivanti, Inc. and its affiliates (referred to collectively as “Ivanti”), and may not be disclosed or copied without prior written consent of Ivanti.

    Ivanti retains the right to make changes to this document or related product specifications and descriptions, at any time, without notice. Ivanti makes no warranty for the use of this document and assumes no responsibility for any errors that can appear in the document nor does it make a commitment to update the information contained herein. For the most current product information, please visit www.ivanti.com.

    Copyright © 2017, Ivanti. All rights reserved.

    Ivanti and its logos are registered trademarks or trademarks of Ivanti, Inc. and its affiliates in the United States and/or other countries. Other brands and names may be claimed as the property of others.