Customer's queries to CSP database are slow to respond.
SQL Temporary Database tempdb setup recommendations
The size, settings, and physical placement of a SQL Server Temporary Database tempdb can affect the performance of a system with Critical System Protection (CSP), especially when querying the database.
For example, if the size that is defined for tempdb is too small, part of the system-processing load may be taken up with ‘auto-growing’ tempdb to the size required to support a specific workload by a query. Or worse, queries will fail if they require more space than what is available on the disk where the tempdb files are located.
It is important to note that tempdb is shared between all databases residing on the same instance of SQL Server.
On an average, CSP consumes ~1 MB of tempdb space per 1000 event records when querying/reporting on events. For example, if you plan on querying ‘Event Details’ for the last 30 days’ events from a CSP system which manages 30 agents, each producing 2000 events a day, the tempdb file size will grow by a minimum of 1.8 GB.
The recommended file size is calculated by the following formula:
Recommended file size (in MB) = Agent events per day * Number of agents * Days / 1000
To achieve optimal tempdb performance with CSP, it is recommended to use the following configuration for tempdb in a production environment:
|tempdb File size||FILEGROWTH increment|
|< 100 MB||10 MB|
|100 – 200 MB||20 MB|
|> 200 MB||10%|