Adjusting the Disk Counter Frequency for a Large Number of Database Files

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.

---------------BEGIN SCRIPT-----------------------

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)

UPDATE PerformanceAnalysisDevice

SET CustomSampleIntervalInTicks = @NewSampleIntervalTicks

FROM PerformanceAnalysisDevice

INNER JOIN

(

SELECT

EventSourceConnection.ID AS EventSourceConnectionID,

EventSourceConnection.DeviceID,

EventSourceConnection.ObjectName,

EventSourceConnectionFileCounts.FileCount

FROM EventSourceConnection

INNER JOIN

(

SELECT EventSourceConnectionID, COUNT(*) AS FileCount

FROM PerformanceAnalysisSqlFile

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

---------------------END SCRIPT----------------------------

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.

Have more questions? Submit a request

0 Comments

Please sign in to leave a comment.