CMS Snapshot Analysis - Configure Snapshot into Dashboard Service - Performance Issue
*Purpose
This page is a troubleshooting guide for the problem of Performance issue during the step Configure Snapshot into Dashboard Service
*Observed in CAST AIP
| Release | Yes/No |
|---|---|
| 8.3.x | ✅ |
| 8.2.x | ✅ |
| 8.1.x | ✅ |
| 8.0.x | ✅ |
| 7.3.x | ✅ |
*Observed on RDBMS
| RDBMS | Yes/No |
|---|---|
| Oracle Server | ✅ |
| Microsoft SQL Server | ✅ |
| CSS3 | ✅ |
| CSS2 | ✅ |
Action Plan
- Check if the server is configured according to CAST recommendation, for this perform the following:
Run the following query on a postgres session:
SELECT NAME, setting FROM pg_settings
For query result sample, refer to [PG\_param.sql](http://doc.castsoftware.com/download/attachments/147849718/PG_param.sql?version=1&modificationDate=1515574700017&api=v2)
2. Compare the server configuration retrieved from the previous step with the server configuration recommended by CAST: [Server configuration](http://doc.castsoftware.com/display/DOC82/Deploying+CAST+Storage+Service+on+Linux#DeployingCASTStorageServiceonLinux-Serverconfiguration)
If the configurations are not identical then change the parameters in **postgresql.conf** (the file is in the postgresql directory\db\_data) as recommended by CAST then restart PgAdmin Service
2. Depending on the RDBMS perform the optimization of the schema
1. For CSS - refer the page [CSSOptimize](http://doc.castsoftware.com/display/DOCCOM/CAST+Storage+Service+-+maintenance+activities)
3. If the above steps do not resolve your issue then check the performance of your system hosting the database server by looking into Task Manager → Performance
Connect to the RDBMS Server and check if the memory consumption is very high and if the CPU usage is almost 100%. This could explain the performance issues during this phase.
Check the reason why the performance issue occur in the Server : may be many processes are running in parallel. If there are many processes running then kill any unwanted processes.
4. Check the character set (for Oracle Only)
With the result of the first query given in [RDBMS Information - Common to all RDBMS - Get the character set](/export-v2/tg/information-guides/information-guides-by-cast-aip-platform/rdbms-information/rdbms-information-common-to-all-rdbms/rdbms-information-common-to-all-rdbms-get-the-character-set/) check if **NLS\_CHARACTERSET** is supported by the CAST products.
The list of supported character Sets is listed in Release notes, Section : **Supported Platforms> DBMS > Supported Character Sets**.
5. Check where the performance issue occurs ,If there is still Performance issues after following the above steps.
<div class="alert alert-warning" role="alert"><div class="h4 alert-heading" role="heading">Note</div>
The "Configure Snapshot into Dashboard Service" step consists of a set of functions and stored procedure (SP) that are run in local database and central database.
Both schemas have a table called DSS\_HISTORY where the actions are logged.
DSS\_HISTORY Tables
Most of the functions and stored procedure are logged in the DSS\_HISTORY table of the Local and Central Databases. The structure of this table is the same for both databases.
Actions logged in local database end with: End smart ranking computing
Actions logged in central database start with: Start Local transfer from local\_database -
In the description field, there is the status of the stored procedure / function, the name of the function and the parameter used. Then, the structure of the filed will alway be : <Start | End> <Name of the Stored Procedure / function> <Parameters>
In the action date field, there is the date where the action has been run : Run of the Start of the SP / Function, and the End of the SP / Function.
In the history\_id, there is the id of the action. All the id are ordered by action date.
This table is very useful when performance issue occurs.
</div>
1. Check the Stored Procedure / Function that is currently running.
1. In DSS\_HISTORY, when an action is currently running, the SP / Function has a Start and No End like below :
```sql
SELECT *
FROM DSS_HISTORY
ORDER BY ACTION_DATE
2. If the function / stored procedure has an action date far from now, then this is the function that having performance issue
- Else,check for the longest function / stored procedure
Run the following query to get the time processed by Stored Procedure / Function.
Select START_HIST.HISTORY_ID, substr(START_HIST.DESCRIPTION,7), START_HIST.ACTION_DATE Start_date, END_HIST.ACTION_DATE end_date, (END_HIST.ACTION_DATE-START_HIST.ACTION_DATE) From DSS_HISTORY START_HIST, DSS_HISTORY END_HIST Where START_HIST.HISTORY_ID + 1 = END_HIST.HISTORY_ID And START_HIST.DESCRIPTION like ‘Start%’ And END_HIST.DESCRIPTION like ‘End%’ Order by (END_HIST.ACTION_DATE-START_HIST.ACTION_DATE) desc
3. Check the Server Status If the DSS\_HISTORY does not help,
1. Using PGAdmin, connect to the CSS server.
2. Click Tools,
3. Click Server Status as shown below
4. Then check in the server status the function that are currently in progress and provide to support a screenshot showing the server status with Start of the process and the function.
4. Find out the very SQL query where the performance issue lies
<div class="alert alert-danger" role="alert"><div class="h4 alert-heading" role="heading">Warning</div>
If you are in Test environment or in Support environment, then you have a way to find out which SQL query is stuck.inside the Stored Procedure or Function you have just spotted in the above paragraph.
Do not do this in Production environment, as it could result in inconsistencies in the KB or CB.
</div>
1. In Server Status in pgadmin, open the Log view
2. If the performance issue occurs with procedure AED\_INIT\_TRAN\_VIOL\_CNT\_TMP\_PREV please refer the page [CMS Snapshot Analysis - Configure Snapshot into Dashboard Service - Performance Issue - Procedure AED\_INIT\_TRAN\_VIOL\_CNT\_TMP\_PREV](/export-v2/tg/technical-guides/technical-guides-by-cast-aip-component/cms-snapshot-analysis-component/cms-snapshot-analysis-configure-snapshot-into-dashboard-service/cms-snapshot-analysis-configure-snapshot-into-dashboard-service-performance-issue/cms-snapshot-analysis-configure-snapshot-into-dashboard-service-performance-issue-procedure-aed-init-tran-viol-cnt-tmp-prev/)
3. Select the stuck Procedure or Function.
4. Click the orange button "Cancel query". A stack trace will be generated in the pgadmin Log View, it will give you the very query that was running when you canceled, along with the parameters.
1. Provide the identified query / Stored Procedure and / or the stack trace to [CAST Technical Support](https://help.castsoftware.com/hc/en-us/requests/new) along with the [relevant input](#CMSSnapshotAnalysisConfigureSnapshotintoDashboardServicePerformanceIssue-RelevantInput)
2. If you do not find the information you are looking for or solution for your problem, in this page, contact [CAST Technical Support](https://help.castsoftware.com/hc/en-us/requests/new) with the [relevant input](#CMSSnapshotAnalysisConfigureSnapshotintoDashboardServicePerformanceIssue-RelevantInput)
#### Relevant Input
- [CAST Support Tool (CST) - alias Sherlock](https://help.castsoftware.com/hc/en-us/articles/203751708-CAST-Support-Tool-CST-alias-Sherlock) with the options **Export Logs****, Export CAST databases , Export Configuration files** checked
- Screenshot showing the problem
- Screenshot of the Task Manager → Performance tab of the system which is hosting the database server
- AedCLI-<yyyymmddhhmmss>.TXT, the location of this log appears in the Cast-MS in the Statistics tab -
**Notes/comments**
**Related Pages**