CommServe Database performance is degraded

CommServe database performance appears to be experiencing periods of slow response time or high CPU utilization.

Symptoms

• CommServe database host CPU usage spikes with excessive SQLserver.exe demand.
• CommCell console refresh time appears to be slower than normal/expected.
• CommCell Jobs/Operations appear to be delayed waiting on response from SQL Server instance/database.

Causes

There are numerous Commvault background administrative processes and operations that interact with the SQL Server instance.  Normally, these are short in duration and scheduled to run non-competively during non-peak periods. However, various factors may cause these processes, or user created/scheduled processes to put a heavier than normal demand on SQLServer resources.

Resolution

To determine which SQL processes/queries are causing the most impact to SQL performance, use the following tools as may be appropriate:

SQL Activity Monitor.

Activity Monitor is a native solution available in SQL Server Management Studio.  It provides real-time information about running processes, processor time (%), waiting tasks, database I/O, and batch requests/sec in real time graphs. Activity Monitor can be started at anytime.

Look here for information on how to use SQL Activity Monitor

Data Collector

Data Collectoris a native solution available in SQL Server Management Studio,  It provides historical performance metrics monitoring and a list of expensive queries.  It has three built-in data collection sets: server activity, disk usage, and query statistics. Unlike Activity Monitor, Data Collection has to be configured to start collecting performance metrics.

Look here for information on how to setup and use Data Collector.

SP_WhoIsActive.sql

SP_WhoIsActive.sql is a free utlity procedure written by Adam Machanic used by many SQL DBA's to find out what queries and stored procedures are running slow.  You can set various parameters to filter what is being monitoring and how often.  Results are stored in a table which can be queried for analysis.

Look here for information and download of SP_WhoIsActive.sql

Commvault Support

Interpreting the results of monitoring and improving/fixing performance issues may require more in-depth Commvault product knowledge than would normally be expected to be held by a SQL DBA. As such, Commvault Support staff have built custom SQL scripts to facilitate/focus monitoring efforts and collection of results. Commvault Support can review results and provide necessary course of action to address factors degrading performance.

Contact Commvault Support for assistance.