User guide

10.1.3 Performance Advisor Data Capacity Planning

PERFORMANCE ADVISOR DATA CAPACITY PLANNING

Performance Advisor (PA) uses the SQLSentry database to store all of the performance data it collects,

utilizing a high performance storage scheme. Event Manager (EM) only users should expect their

existing database to approximately double in size if all of the existing SQL Servers watched by EM,

are watched by PA. This is a very rough estimate, however, since exactly how much space will be used

by PA is directly dependent on:

The number of databases on the watched SQL Servers, since some of the performance counters

collected by PA are database specific.

The number of physical disks on the watched servers, since related counters are disk specific.

The Minimum Duration specified for the Top SQL event source. The default global setting is five

seconds, meaning that any batches or stored procedures that run for longer than five seconds

will be collected. If this threshold is lowered, the amount of Top SQL data collected will

increase. Note that a different Minimum Duration can be specified for each SQL Server.

Whether or not "Collect Statement Events" is set to True for the Top SQL event source. The

default is False. If enabled, this may increase the amount of Top SQL data collected by a factor

of two or more. This setting is also adjustable for each SQL Server.

The performance data retention settings. Different settings can be specified for detailed (or raw)

performance data, rolled up performance data, and Top SQL/Blocking SQL/Deadlock data.

For detailed performance data, retention is specified in hours for each performance

counter category in the HistoryDataRetentionHours column of the

PerformanceAnalysisCounterCategory table. The default may be either 48 or 72 hours,

depending on the category. Raw data is shown by default on the Dashboard and Disk

Activity tabs whenever the current date range is <=30 minutes. Over 30 minutes, rolled

up data is used.

If you have an unusually large number of databases on SQL Servers monitored by

PA, you may consider reducing the retention hours for the SQLSERVER:DATABASES

and SQLPERF:VIRTUAL_FILESTATS categories. Data for these categories are stored

in the PerformanceAnalysisDataDatabaseCounter and

PerformanceAnalysisDataDiskCounter tables respectively.

Likewise, if you have an unusually large number of physical disks per server

monitored by PA, you may consider reducing the retention hours for the

PHYSICALDISK category. Data for this category is stored in the

PerformanceAnalysisDataDiskCounter table.

Data for all other categories is stored in the PerformanceAnalysisData table.

Generally, it is a good idea to keep the retention hours the same for categories that

are stored in the same table, otherwise page splitting and fragmentation may result

during the pruning process which may eventually affect performance.

For rolled up performance data, retention is specified in hours for each rollup level in the

HistoryDataRetentionHours column of the PerformanceAnalysisDataRollupLevel table.

Rollup data for each break level (specified by the LevelBreakMinutes column) is stored in a

separate table, all named PerformanceAnalysisDataRollupXX, where XX represents the ID

of the break level. In general, the only rollup table that may get large is the table for

SQL Sentry Quick Start 37