Optimizing collection performance
By default, Performance Store saves local Extended Events information in 10 files with the size of 2 MB. Every time Performance Store collects information from a SQL Server, the latest file is retrieved. This means, the larger the file is, the longer the collection process will take.
To optimize performance, Performance Store will save the last collected file offset, and only retrieve events that are newer than the saved offset. If the saved offset is not found in the latest file, Performance Store will have to search in all 10 files to look for the offset, so it is ensured, that all events are collected. Searching in all 10 files can take longer time compared to searching for the offset in one specific file.
If the offset is not found in the latest file, the reason can be, that the server is handling a large number of requests, resulting in the first 2 MB file being filled out quickly, and then a new 2 MB file is created. This process will continue for all 10 files. If all 10 files are filled out before Performance Store collects the information in the files, the event_gap column in the dbo.dm_xe_sessions table will be 1. Another reason for an event_gap can be a SQL Server service restart.
To minimize the chance of a new file creation before Performance Store collects the latest file, the size of the file can be increased to e.g. 4 MB. This will increase the time Performance Store is using to search for the offset in the first file, but it will decrease the chance of searching in all 10 files.
To see if Performance Store has been searching in more than just the first file, see the look_in_all_files column in the dbo.dbo.dm_xe_sessions table.
Note, when a new file is created, Performance Store will always have to search in the current active file and the previous file(s) to search for the last retrieved offset. This is normal behavior.
For very busy SQL Servers, it is advised to increase the value from 2 MB to e.g. 4 MB if there are many occurances of look_in_all_files = 1 in the dbo.v_xe_info view. To change the value, set the new size in the max_file_size column in the dbo.xe_session_info table for the server. The unit for the max_file_size column is MB, so to set it to 4 MB, the value should be set to 4. For the changes to take effect, reinitialize monitoring for the specific server by disabling- and enabling monitoring for the server in the Performance Store Control Center.
The above is directly correlated with the “Sample servers every” value in the Performance Store Control Center. If this value is less often than the default 30 seconds, the local file sizes will be need to contain information for a longer time period, and might therefore need to have its file size increased. E.g. if the Performance Store collection sample frequency is set to the default 30 seconds, then the problem occurs if a new Extended Events file is created every 15 seconds.
If the max_file_size value is not set to an optimal value, errors might be seen in the SQL Server error log. See Log file (.xel) cannot be deleted for more information.
Optimizing on a cloud platform
When monitoring on a SQL Server on a cloud platform, e.g. Azure SQL Managed Instance, Performance Store is not storing the local Extended Events information in files. This is due to the way cloud platform operate, where there are no access to the local file system. Instead the Extended Events data is stored in a local ring buffer. The size of the ring buffer is equal to the value in the max_file_size column mentioned above. The default size is 2 MB, but can be changed in the same way as for files in the dbo.xe_session_info table. Note, since no file storage is used, the value in the max_rollover_files column in dbo.xe_session_info will not have any effect on cloud platforms.
The default value for max_rollover_files is 10, and the default value for max_file_size is 2. For non-cloud platforms this will have an Extended Event storage capacity of 10 x 2 MB = 20 MB. For cloud platforms, the Extended Event storage capacity is 2 MB. If this is too little to hold all information between the “Sample servers every” value in the Performance Store Control Center, the value can be changed to a bigger number, by changing the max_file_size value in the dbo.xe_session_info table. Note, for cloud platforms the whole ring buffer will be retrieved when sampling servers (default every 30 seconds). This means, if the size of the ring buffer is 20 MB, all 20 MB will be retrieved every 30 seconds. The same logic does not apply to non-cloud platforms, as the whole storage buffer is only retrieved if the last fetched offset information is not found in the first file.