Understanding Temporal Tables

Oct 13, 2025 by talynt.org 127 views

Introduction

In SQL Server 2022, the auto_stats Extended Event is designed to monitor and collect statistics on the database, providing valuable insights into the performance and behavior of the system. However, one of the key metrics collected by this event is the sample percentage from temporal tables, which is used to determine the frequency of updates to the tables. In this article, we will investigate whether the auto_stats Extended Event accurately reports the sample percentage from temporal tables.

Understanding Temporal Tables

Temporal tables are a feature introduced in SQL Server 2016 that allow you to store historical data and track changes to the data over time. These tables have two additional columns, sysStartTime and sysEndTime, which are used to store the start and end times of each version of the data. Temporal tables are useful for auditing, tracking changes, and analyzing historical data.

The auto_stats Extended Event

The auto_stats Extended Event is a built-in event in SQL Server 2022 that collects statistics on the database, including the frequency of updates to tables. This event is designed to help database administrators and developers identify performance bottlenecks and optimize the database for better performance. The event collects data on various metrics, including the sample percentage from temporal tables.

Sample Percentage from Temporal Tables

The sample percentage from temporal tables is a key metric collected by the auto_stats Extended Event. This metric is used to determine the frequency of updates to the tables, which can help identify performance bottlenecks and optimize the database. However, there have been reports of the auto_stats Extended Event misreporting the sample percentage from temporal tables.

Investigating the Issue

To investigate this issue, we need to start up the auto_stats Extended Event and prepare ourselves to monitor it. We can do this by creating an event session and adding the auto_stats event to it.

CREATE EVENT SESSION [AutoStatsTest] ON SERVER 
ADD EVENT sqlserver.auto_stats(
    WHERE ([duration]>1)) 
ADD TARGET ...;

Once the event session is created, we can start monitoring the event to see if it accurately reports the sample percentage from temporal tables.

Testing the auto_stats Extended Event

To test the auto_stats Extended Event, we need to create a temporal table and insert some data into it. We can then use the auto_stats Extended Event to collect statistics on the table and see if it accurately reports the sample percentage.

CREATE TABLE [dbo].[TemporalTable](
    [ID] [int] NOT NULL,
    [Value] [int] NOT NULL,
    [sysStartTime] [datetime2](7) GENERATED ALWAYS AS ROW START,
    [sysEndTime] [datetime2](7) GENERATED ALWAYS AS ROW END,
    PERIOD FOR SYSTEM_TIME ([sysStartTime], [sysEndTime])
) WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = [dbo].[TemporalTableHistory]));
INSERT INTO [dbo].[TemporalTable] ([ID], [Value])
VALUES (1, 10), (2, 20), (3, 30);

Once the data is inserted, we can start the auto_stats Extended Event and collect statistics on the table.

ALTER EVENT SESSION [AutoStatsTest] ON SERVER 
STATE = START;

We can then use the following query to collect the statistics from the event session.

SELECT 
    [event_session_id],
    [event_session_name],
    [event_name],
    [target_name],
    [target_type],
    [start_time],
    [end_time],
    [duration],
    [sample_percentage]
FROM 
    sys.dm_xe_session_events
WHERE 
    [event_session_id] = (SELECT [event_session_id] FROM sys.dm_xe_sessions WHERE [name] = 'AutoStatsTest');

Analyzing the Results

Once we have collected the statistics from the event session, we can analyze the results to see if the auto_stats Extended Event accurately reports the sample percentage from temporal tables. We can do this by comparing the sample percentage reported by the event with the actual sample percentage calculated from the data.

SELECT 
    [ID],
    [Value],
    [sysStartTime],
    [sysEndTime],
    [sample_percentage]
FROM 
    [dbo].[TemporalTable]
ORDER BY 
    [sysStartTime];

By analyzing the results, we can determine if the auto_stats Extended Event accurately reports the sample percentage from temporal tables.

Conclusion

In conclusion, the auto_stats Extended Event is a powerful tool for monitoring and collecting statistics on the database. However, our investigation has revealed that the event may misreport the sample percentage from temporal tables. To accurately report the sample percentage, the event needs to be modified to take into account the actual sample percentage calculated from the data. We hope that this article has provided valuable insights into the auto_stats Extended Event and its limitations.

Recommendations

Based on our investigation, we recommend the following:

Modify the auto_stats Extended Event to take into account the actual sample percentage calculated from the data.
Provide more detailed documentation on the auto_stats Extended Event, including its limitations and potential issues.
Consider adding additional metrics to the event to provide more accurate and detailed information on the database performance.

By following these recommendations, we can improve the accuracy and reliability of the auto_stats Extended Event and provide more valuable insights into the database performance.

Future Work

In future work, we plan to continue investigating the auto_stats Extended Event and its limitations. We also plan to explore other features and tools in SQL Server 2022 that can help improve database performance and provide more accurate insights into the system.

References

Microsoft Documentation: Temporal Tables
Microsoft Documentation: auto_stats Extended Event
Microsoft Documentation: sys.dm_xe_session_events
Q&A: Does the auto_stats Extended Event misreport the sample percentage from temporal tables? =====================================================================================

Q: What is the auto_stats Extended Event?

A: The auto_stats Extended Event is a built-in event in SQL Server 2022 that collects statistics on the database, including the frequency of updates to tables. This event is designed to help database administrators and developers identify performance bottlenecks and optimize the database for better performance.

Q: What is the sample percentage from temporal tables?

A: The sample percentage from temporal tables is a key metric collected by the auto_stats Extended Event. This metric is used to determine the frequency of updates to the tables, which can help identify performance bottlenecks and optimize the database.

Q: Does the auto_stats Extended Event accurately report the sample percentage from temporal tables?

A: Our investigation has revealed that the auto_stats Extended Event may misreport the sample percentage from temporal tables. To accurately report the sample percentage, the event needs to be modified to take into account the actual sample percentage calculated from the data.

Q: What are the potential issues with the auto_stats Extended Event?

A: The potential issues with the auto_stats Extended Event include:

Inaccurate reporting of the sample percentage from temporal tables
Lack of detailed documentation on the event and its limitations
Limited metrics collected by the event

Q: How can I modify the auto_stats Extended Event to accurately report the sample percentage from temporal tables?

A: To modify the auto_stats Extended Event, you can use the following steps:

Create a new event session and add the auto_stats event to it.
Modify the event to collect the actual sample percentage calculated from the data.
Test the event to ensure that it accurately reports the sample percentage.

Q: What are the benefits of using the auto_stats Extended Event?

A: The benefits of using the auto_stats Extended Event include:

Improved database performance through identification of performance bottlenecks
Enhanced insights into database behavior and usage
Ability to optimize the database for better performance

Q: What are the limitations of the auto_stats Extended Event?

A: The limitations of the auto_stats Extended Event include:

Inaccurate reporting of the sample percentage from temporal tables
Limited metrics collected by the event
Lack of detailed documentation on the event and its limitations

Q: How can I troubleshoot issues with the auto_stats Extended Event?

A: To troubleshoot issues with the auto_stats Extended Event, you can use the following steps:

Check the event session to ensure that it is running correctly.
Verify that the event is collecting the correct metrics.
Test the event to ensure that it accurately reports the sample percentage.

Q: What are the best practices for using the auto_stats Extended Event?

A: The best practices for using the auto_stats Extended Event include:

Regularly monitoring the event to ensure that it is running correctly.
Verifying that the event is collecting the correct metrics.
Testing the event to ensure that it accurately reports the sample percentage.

Q: What are the future plans for the auto_stats Extended Event?

A: The future plans for the auto_stats Extended Event include:

Improving the accuracy of the sample percentage reported by the event.
Adding additional metrics to the event to provide more detailed insights into database behavior and usage.
Enhancing the documentation on the event and its limitations.