Planning For Database Growth With SentryOne

Performance Analysis (PA) uses the SentryOne database to store all of the performance data it collects, utilizing a high performance storage scheme. Event Calendar users should expect their existing database to approximately double in size if all of the same SQL Servers are watched by PA that are currently watched by Event Calendar. 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 5 seconds, meaning that any batches or stored procedures that run for longer than 5 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 True as of v6 (prior to v6, the default was False). If enabled, this may increase the amount of Top SQL data collected by a factor of 2 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.
  • In general it's 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 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 the 2 minute break level, or PerformanceAnalysisDataRollup2. The retention hours for this, or any other break level, can be adjusted as needed.
  • Retention for raw Top SQL, Blocking and Deadlock data is controlled by the Purge History Older Than setting on the Storage tab under Configuration -> Global Settings in the Navigator pane. The default is 15 days.
  • If you are using EM with PA, which enables viewing PA data on the EM calendar, the raw Top SQL, Blocking, and Deadlock data is also converted to the native EM storage format and stored in the EventSourceHistory table alongside data for other EM event sources like SQL Agent Jobs and DTS. Retention for all EM sources is controlled by the Purge History Older Than setting on the Event History Monitor tab under SQL Sentry Server->Settings in the Navigator pane.

Expired performance data is pruned by the SentryOne Monitoring service every minute or so. The default settings are such that you should always have detailed performance data for the last 2 or 3 days. However, if you find that you are frequently navigating to date ranges using the Dashboard or Disk Activity tabs where no data is shown, it may mean that you need to increase the retention hours for the detailed and/or rolled up performance data. You should of course balance any changes with the resulting impact it will have on database size.

When you start using PA, you will likely find that your SentryOne database grows quickly at first. After a few days this will level off though, once the pruning of expired data begins and starts keeping pace with the incoming new data. You can get a quick idea of the mix of PA data in your environment by inspecting sizes for the related tables using the script below. Bear in mind that much of the data in EventSourceHistory is likely related to Event Calendar sources.

SELECT

TableName = OBJECT_SCHEMA_NAME([object_id]) + '.' + OBJECT_NAME([object_id]),

[RowCount] = SUM(CASE WHEN index_id IN (0,1) THEN row_count ELSE 0 END),

UsedSpaceMB = SUM(used_page_count / 128),

ReservedSpaceMB = SUM(reserved_page_count / 128)

FROM sys.dm_db_partition_stats

WHERE OBJECT_NAME([object_id]) IN (

'BlockChainDetail',

'EventSourceHistory',

'MetaHistorySqlServerBlockLog',

'MetaHistorySqlServerTraceLog',

'PerformanceAnalysisData',

'PerformanceAnalysisDataDatabaseCounter',

'PerformanceAnalysisDataDiskCounter',

'PerformanceAnalysisDataRollup11',

'PerformanceAnalysisDataRollup2',

'PerformanceAnalysisDataRollup4',

'PerformanceAnalysisDataRollup6',

'PerformanceAnalysisDataRollup8',

'PerformanceAnalysisTraceData',

'PerformanceAnalysisPlan',

'PerformanceAnalysisPlanOpTotals',

'PerformanceAnalysisTraceCachedPlanItems',

'PerformanceAnalysisTraceDataToCachedPlans',

'PerformanceAnalysisTraceQueryStats',

'MetaHistorySharePointTimerJob',

'PerformanceAnalysisSsasUsageTotals',

'PerformanceAnalysisSsasCubeDimensionAttribute',

'PerformanceAnalysisSsasTraceDataDetail'

)

AND OBJECTPROPERTY([object_id], 'IsUserTable') = 1

GROUP BY [object_id]

ORDER BY TableName;

For information on adjusting the disk counter collection frequency for systems with large numbers of database files, please review this article.

Have more questions? Submit a request

0 Comments

Please sign in to leave a comment.