Introduction
Workflow Solution provides the ability to automate various processes, including many Symantec Corporation applications. Servicedesk is an application built from Workflow that automates the Incident, Change and Problem Management processes. Since there is a very tight integration between the configuration of Workflow and Servicedesk, especially in the supporting systems like Microsoft SQL, this document will focus on performance troubleshooting for both products.
Where applicable, best practices will be outlined. This document is work in progress. As new issues are discovered, they will be added. The content here may NOT solve all performance issues, but it can address the more common issues, and provide a direction for other issues that may arise. Also, due to significant changes in design and content, the steps here will primarily focus on Workflow and Servicedesk 7.5 and higher. There may some items that are useful for earlier versions, specifically in the SQL configuration sections, but a significant number of performance issues were addressed starting at 7.5, including the ability to perform Servicedesk in-place upgrades starting at 7.5. Once a Servicedesk server is installed and running on 7.5, it can be upgraded in-place.
Symantec strongly recommends upgrading Workflow and Servicedesk environments to the latest versions. The Symantec knowledge base, found at https://support.symantec.com/en_US.html
Provides articles containing links to the latest build and updates for Workflow and Servicedesk.
Workflow 7.5 Point Fix KB – HOWTO95421
Workflow 7.6 Point Fix KB – INFO2509
Servicedesk 7.5 Sp1 Point Fix KB – TECH212326
Servicedesk 7.6 Point Fix KB – TECH231874
The Components of Well-Performing Workflow and Servicedesk Environments.
Workflow environments can be very diverse, from some very small environments automating 3-5 small processes, to very large, load-balanced environments with a lot of different custom processes from employee on-boarding to hardware provisioning, including imaging and deployment. Servicedesk implementations fall all over in this range – some with one or two technicians and 25-50 incidents monthly, to environments supporting thousands of incidents weekly, and hundreds of technicians. It should be clear that the sizing and scaling of the various components required to efficiently support these installations will also vary.
Workflow environments can have the following components:
- Workflow Server (can be dedicated, or can run on the SMP Server)
- Workflow Designer Computer
- MS-SQL Server
- SMP Server (to obtain the Workflow installer)
One area that customers often neglect is providing a testing environment. Ideally a test environment would be an exact duplicate of the production system, in terms of servers. Having a matching number of client machines is not critical, but aligning the server’s configurations is very important. If the production environment is a 3 server, load-balanced configuration with a dedicated SQL server, it is recommended to duplicate that as your test environment, especially when you are testing applications that could be performance-sensitive.
Servicedesk has somewhat different requirements, due to the design of the application and its expected data handling requirements. The following components are a part of a good Servicedesk environment:
- Workflow Server with Servicedesk Installed (Must be a dedicated server)
- Separate Client Workstation to develop custom workflows.
- Dedicated SQL Server
- SMP Server for Licensing
The Workflow Server - Options
The Workflow server can reside on a Windows Server. Most of the specifics for hardware requirements are covered in the Workflow Users Guide, found here. The focus here is more whether or not it is best to install the Workflow server on the SMP, or to build a dedicated server to house and serve the custom processes and projects. Experience has shown that for most customers who use their SMP extensively, any additional processing load tends to degrade SMP, and the resulting CMDB or ITMS performance. 1-5 small projects, with limited process creation and activity, represent about all that an active SMP should accommodate. Additionally, most ITMS administrators won’t want the performance of their SMP servers hampered by Workflow processes running.
A dedicated workflow server can accommodate more work and is recommended for most configurations. In very busy, active process and task-oriented environments, load-balancing workflow servers, including providing a dedicated background processing workflow server can accommodate a very significant load. See Appendix B in this link here, for configuring a load-balanced environment.
The Servicedesk Server - Options
Servicedesk itself is a very intensive application and requires hardware similar to an active SMP server. The Servicedesk Implementation guide, found here, documents the requirements needed, and the various options to consider, when determining how to configure the Servicedesk server and environment. Servicedesk does require a dedicated server, and a dedicated SQL server, to perform optimally. Following the Servicedesk-specific SQL server requirements found below will make each Servicedesk environment perform better.
The SQL Server – Server Information and Requirements
The requirements for SQL vary, depending on whether there are process-heavy workflows on the workflow server, or whether the bulk of the applications are simple forms and processes, and are not generating signification process data, tasks, and messages, or querying external databases extensively. If these types of workflows represent the business processes being automated, then the SQL requirements for Servicedesk should be followed more closely.
If Servicedesk is being used, the SQL requirements are very similar to the SQL requirements for a heavily used SMP server. Recommended guidelines and links to other documents and KB documents are provided in the Servicedesk subsection below.
1. SQL Server Requirements for Workflow
a. Hardware
i. The specifics are outlined in our Workflow 7.6 Users Guide, found here, on page 59. For any off-SMP environment, the recommendation is to use the Small or Lab environment or higher, for Workflow alone. Symantec does not recommend sharing database resources with other databases, but if sharing is ABSOLUTELY necessary, then a large environment configuration should be implemented as a minimum design.
ii. DISK I/O - The minimum I/O metrics for a SQL server should be adhered to. These are found on page 55 of the User’s Guide as well. Please note that they are specific for each SQL database component, the Database file itself, the log file, and TEMPDB.
b. Operating System and SQL Software
i. Workflow supports Windows 2008 and higher, and MS SQL 2008 and higher.
2. SQL Server Requirements for Servicedesk
a. Hardware
i. The specifics for planning and configuring SQL for Servicedesk are different than for a Workflow-only configuration. The two products share some requirements, but as a minimum, all Servicedesk installations should be considered to be a large environment. The Hard Drive and Disk I/O requirements are found here. Starting on 54 in the Workflow User’s guide. Under most circumstances, the database for Servicedesk has been shown to NOT perform well if shared with other databases on the same MSSQL server, thus a dedicated SQL server is recommended. Follow the DISK I/O recommendations found in the link above, for each component of the database, the database file, the transaction log file, and TEMPDB. Separate, dedicated disk I/O should be provided for each component, to provide optimal throughput. This cannot be emphasized enough.
b. Software
i. Servicedesk supports Windows 2008 and higher, and MSSQL 2008 and higher.
The SMP Server – Considerations
1. Workflow and the SMP
a. The SMP server provides little, in terms of usage for the Workflow server, unless you are using Workflow to automate some of the ITMS or CMDB functions. Other than accessing the installer for Workflow, there is little interaction. If you ARE using Workflow to automate processes on the SMP, then you will want your SMP server or environment to be able to quickly respond to component queries. As assets and resources are queried on the SMP, a well configured SMP will return that information to Workflow quickly.
2. Servicedesk and the SMP
a. Servicedesk is licensed through the SMP, and so a very reliable network connection between the two servers is optimal. Servicedesk also uses Asset Management information, such as Location, and Department, and can link assets such as computers to Servicedesk Incidents. Having a reliable link between the two servers will assure consistent and well-performing behavior. Many timeout and licensing inconsistency issues have been resolved by rebooting a SMP server.
Troubleshooting Performance-Related Items
Being able to troubleshoot the performance related items means being able to look at the various symptoms and evidence, and determine the best course of action to resolve the problem. It important to look beyond statements like “The system is going slow” and, through proper analysis, determine where the issues are.
Workflow Server: Symptoms and / or Log File Entries
Here are some performance item symptoms, and related log file entries, and questions to ask.
1. “My Workflow Process is Slow”
a. Evaluating a custom process is difficult and there can be many locations where potential design flaws or mistakes could cause a workflow to run slowly. Here are a few examples. Each needs to be resolved independently.
i. Accessing external database information frequently that is not indexed.
1. Solution A: Run the database tuning advisor or equivalent on that data information to allow it to return data quicker.
2. Solution B: Putting the query component that retrieves that data into a linked model can allow the results to be cached, a feature of linked models. That will allow the data to be returned to the process quicker.
ii. Designs where tasks can be created in a loop, creating a large amount of extra unneeded tasks.
1. The workflow design must be evaluated and determine where the loop occurs. Generally, tasks are created when workflow dialog components are used. Opening up these components and reviewing the logic can be helpful.
2. Having proper exception handling configured can help to provide information when problems arise.
iii. Incorrect Exception Handling
1. In the case of having multiple Global Logging components. Only one required per Workflow. This can often generate duplicate Processes in the ReportProcess database table.
iv. Deadlocks Entries in Log Files. Entries like “Server 'SERVNAME': Failure, Reason: Transaction (Process ID 129) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction. Production: Failure, Reason: Transaction (Process ID 129) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.”
a. A poorly written custom query component, not using the NOLOCK statements.
b. Bad Indexing – See 1.a.i above.
c. Poor hardware – running the query on the SQL server directly can show whether Workflow is slowing things down or the SQL server is inherently slow.
d. For items other than this, please review specific SQL server troubleshooting below.
v. Log File Entries
1. “System.Data.SqlClient.SqlException (0x80131904): A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) ---> System.ComponentModel.Win32Exception (0x80004005): The network path was not found” This error can often indicate that there are network reliability issues connecting to the SQL server.
a. Checking to make sure the SQL server is running is a great first step.
b. Incorrect accounts and passwords also can cause this issue to occur.
Items to Check: Servicedesk Server
There are many different items to check on the Servicedesk server. And a variety of different symptoms that show up when the server is acting slow.
1. Server is Slow – Slow Dashboard Performance or Slow Reporting
a. Making sure you are on the latest build of Workflow is important. Article and Article are the best ones to use to make sure you have the latest Workflow build on your Servicedesk Server.
b. Processor Utilization – This should not be more than about 15-50%. If it is spiking consistently above 50%, you may need to consider load balancing your environment.
c. Too many applications in one IIS App Pool.
d. Too many reports on a dashboard – See 1d in this section.
e. Reports returning too many results – Set paging values in reporting to limit the results displayed.
f. Improve SQL performance. See the MSSQL server section below
g. Reports configured incorrectly.
1. When configuring report information, report filters can be inadvertently configured, such that the resulting SQL that runs can reference the same table multiple times. A specific example of this occurs when Process Data is added to a report, if the “Only Processes I have Permissions” check box was selected, it provided very similar results as when the “Assigned to Me” filter was added. When we had BOTH items checked, the cross-joined SQL resulted. If this report is used as a part of a user’s Processmanager home page, or as part of a dashboard, any time the user logs in, slowness would result. Login times as slow as 7-25 seconds were reported. When the check box was unchecked, in conjunction with the "Assigned to Me" filter, the login times improved to 1-5 seconds.
2. Submitting Incidents is Slow – 5-10 seconds to submit an incident
a. KB TECH183248 has some suggestions for improving access to items on the SMP that can be returned for incident creation.
3. Log File Entries
a. “System.Data.SqlClient.SqlException (0x80131904): A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) ---> System.ComponentModel.Win32Exception (0x80004005): The network path was not found”. This error can often indicate that there are network reliability issues connecting to the SQL server.
1. Checking to make sure the SQL server is running is a great first step.
2. Incorrect accounts and passwords also can cause this issue to occur.
Items to Check: MSSQL Server
1. Make sure that the specifications in the SQL server configuration section above are met.
2. Disc Performance.
a. In SQL Management Studio, on the SQL server, you can open up the ProcessManager database (or however it is named, and check the file locations for:
1. The Database file (name.mdf) and,
2. The transaction log file (name.ldf).
3. You should also notice the location of the TEMPDB database file. You can then add performance counters to look at how they are performing.
b. Using Perfmon, you can track counters for each of the above locations for tracking items for like:
i. Physical Disk: Avg. Disk sec/Read, and
ii. Physical Disk: Avg. Disk sec/Write. Both of these counters should always average around 15ms, but no more than 25ms.Based on the assumption that SQL should be running an OLTP system. You can take the values reported, and multiply them by 1000 to get the correct values, and then compare. 0.130 = 130 ms, and .025 = 25 ms, etc.
iii. Physical Disk: Current Disk Queue Length
iv. Physical Disk: Average Disk Queue Length
v. Physical Disk: Average Disk Read Queue Length
vi. Physical Disk: Average Disk Write Queue Length
None of the queue length values should be above 1-2. This indicates insufficient disk I/O to keep up with the requests. See the Disk I/O requirements for recommendations on changes to be made. Other counters to help are:
c. Memory performance Counters:
i. SQL Server: Buffer Manager: Buffer Cache Hit Ratio – Lower than 90% means more memory is needed
ii. SQL Server: Buffer Manager: Page Life expectancy – less than 300 means that only 300 ms exists before data must be paged out. This means that there is insufficient memory. You should add more RAM to the SQL server, or verify that SQL is being provided as much RAM as possible,
3. Check the SQL Server Log files
a. The SQL server should have a regular maintenance plan that rebuild the indexes, resets the statistics, and runs regular backups. When statistics are not reset and indexes are not rebuilt regularly, SQL performance can be affected, causing a downstream negative effect on the overall performance of the system.
4. Specific Tuning
a. Often, as certain features are used, trends of usage begin to emerge in the way that data is accessed in the database. Symantec provides by default, pre-defined indexes on key tables and fields in those tables, the majority of which accommodate most customers. Occasionally it may be necessary to run a SQL profiler trace, capturing representative traffic of a load, and then allowing the Database Tuning Advisor in SQL Management Studio to analyze the data found in the trace file, and make recommendations on items to tune. This usually results in additional indexes and statistics being recommended. This is also very useful for any custom databases that are being accessed by Servicedesk or and Workflow applications. Adding these items can improve performance, significantly in some cases.
Items to Check: SMP Server
The SMP server often has various items that can affect performance of a Servicedesk environment. Workflow is not as vulnerable. Using Workflow’s Enterprise Management, (which is installed on the SMP), to manage published Workflow applications, will require that a reliable SMP environment be maintained.
As previously mentioned the SMP also provides data and licensing information for Servicedesk. Have a well-performing SMP will assure that no problems are encountered.
Load-Balancing Your Workflow or Servicedesk Environment: When is the Right Time?
The benefits of deciding to use load-balancing are well-defined in Chapter 4 of the Servicedesk Implementation Guide, found here. Exceeding 75 Technicians is a good point at which to determine to load balance. Load balancing after initial implementation involves converting the current Servicedesk or Workflow server to act as the primary background server, communicating with the database, and then adding additional servers to act as front-end servers for the end users to interact with. The details on how to implement load-balancing are found in Appendix B of the Workflow Users Guide, found here.
Determining the best time for Workflow-only environment is simply dependent on the load and type of processes being automated. A good discussion with a Workflow consultant can be beneficial in understanding the specifics of the applications being automated and the load those will present to the Workflow server.
Summary
This document has begun to scratch the surface of areas where performance can be affected for Workflow or Servicedesk. Following the design guidelines of the various components, the application server, the SQL server, and the SMP will help prevent some of the performance issues that arise. If not, this guide can benefit the end user in beginning to evaluate and resolve the problems that need to be addressed.