An unusually large number of database files per database can cause a significant amount of disk counter data to be collected relative to the number of databases. This can lead to increased SQLSentry database growth and transaction log activity. This disk counter data is stored in the PerformanceAnalysisDataDiskCounter table. If, using the table size query found in the Planning for database growth KB, you note that this table has a substantially higher number of records than the other PerformanceAnalysisData tables, you can increase the sample frequency for disk counters from the default of 20 seconds (stored in ticks as 200000000) to something higher.
A custom sample interval for disk data can be specified at the device, or Windows server, level, and will affect all SQL Server instances on that server. Below is a script that will find any server that contains more than a specified number of database files and increase the sample interval to a specified number of seconds. The defaults will increase the sample interval to 60 seconds for any server with more than 100 database files across all SQL Server instances. If this doesn't return the database size to something more manageable the sample interval can be increased further as needed.
The net effect of increasing the sample interval is that the disk-related metrics on the Performance Advisor Dashboard and Disk Activity tabs will appear to refresh less frequently.
DECLARE @MaxDBFiles smallint
DECLARE @NewSampleIntervalSeconds int
DECLARE @NewSampleIntervalTicks bigint
/* Set these values */
SET @MaxDBFiles = 100 --Max # of db files on a SQL Server to allow before changing sample interval.
SET @NewSampleIntervalSeconds = 60 --New sample interval--will be converted to ticks below before update.
/* Set these values */
SET @NewSampleIntervalTicks = dbo.fnConvertSecsToTicks(@NewSampleIntervalSeconds)
SET CustomSampleIntervalInTicks = @NewSampleIntervalTicks
EventSourceConnection.ID AS EventSourceConnectionID,
SELECT EventSourceConnectionID, COUNT(*) AS FileCount
GROUP BY EventSourceConnectionID
HAVING COUNT(*) >= @MaxDBFiles
) AS EventSourceConnectionFileCounts
ON EventSourceConnection.ID = EventSourceConnectionFileCounts.EventSourceConnectionID
) AS HighFileCountDevices
ON HighFileCountDevices.DeviceID = PerformanceAnalysisDevice.DeviceID
WHERE PerformanceAnalysisDevice.PerformanceAnalysisSampleIntervalID = 5
--Verify update completed successfully.
SELECT * FROM PerformanceAnalysisDevice
WHERE CustomSampleIntervalInTicks = @NewSampleIntervalTicks
AND PerformanceAnalysisSampleIntervalID = 5
An alternative to the above targeted approach is to increase the default sample interval for ID 5 in the PerformanceAnalysisSampleInterval table. This will change the disk counter collection frequency globally for all servers. Please note that this value is stored in ticks, so 60 seconds is represented as 600000000. The dbo.fnConvertSecsToTicks() function in the SQLSentry database can be used to ensure the seconds value is converted properly when updating.
NOTE: You must stop all SQL Sentry Monitoring Services prior to running this script for any sample frequency changes to take effect. The services are not aware of the changes being made here, and may actually overwrite the changes you make if they are running when you make them.