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.