Collect the following data when high CPU use on an SQL Server is reported:
- Answer the following clarifying questions:
- About how many endpoints is ePO managing?
- Does the SQL CPU use diminish if all three ePO services are stopped? If you have remote Agent Handlers, you need to stop the two ePO services on them as well.
The three ePO services are:
- ePolicy Orchestrator Server (often referenced as Apache)
- ePolicy Orchestrator Event Parser (often referenced as Eventparser)
- ePolicy Orchestrator Application Server (often referenced as Tomcat; this service doesn’t exist on agent handlers)
- If stopping all ePO services diminishes the SQL CPU use, consider stopping just one of the three services.
For example, if you stop the Apache service on the ePO server and all Agent Handlers, but leave the other services running, does the CPU use diminish?
- Does the SQL Server hosting the ePO database host any other databases?
- Collect the following data:
- Collect an SQL trace for a 10-minute duration taken while the SQL CPU use is high. Use the following instructions:
- Open SQL Server Management Studio (SSMS) on the SQL Server hosting the ePO database.
- Click Tools, SQL Server Profiler.
- Enter your password if needed, and click Connect.
- Click the Events Selection tab.
- Deselect all trace selections.
- Click the Show all events and Show all columns options.
- Expand Error and Warnings and select Sort Warnings and User Error Message.
- Expand Locks and select Deadlock Graphs, Lock: Deadlock and Lock: Deadlock Chain.
- Expand Stored Procedures and select RPC:Completed, SP:Completed, SP:StmtCompleted.
- Expand TSQL and select SQL:BatchCompleted and SQL:StmtCompleted.
- Click Run.
- Wait for 10 minutes for the trace to capture information about queries which are using a large amount of CPU.
- Click File then select Stop Trace.
- Click File, Save As then select Trace File.
- Give the file a name and select a location to save the file.
- Click Save.
- Zip the file. The trace file can be large, but compresses significantly when archived.
NOTE: In some environments, an SQL trace isn’t possible to easily collect. If you can collect the SQL trace, it gives us the most complete data set on the SQL CPU use. But, if the data can't be collected in your environment, the data collected in step 2 is often sufficient.
Justification for data requested:
- The attached SQL query provides information regarding recent expensive SQL queries.
- Information about the node count and the SQL Server specifications can be used to determine whether ePO might be overloading the SQL Server.
- The SQL trace shows all queries which completed in the 10 minutes the trace was running. The results can reveal how many CPU cycles each query needed.
- The SQL error logs might reveal an error condition that could explain the CPU use.
- Understanding which ePO services have to be running to generate the CPU use can help narrow down either of the following:
- Which component of ePO might be generating queries
- Which components are consuming excessive CPU
