Statistics SQL Server slow log correctly

An attractive service for RDS is to provide users with a slow log health report. Reports provide TOP20 SQL for users from different dimensions (total execution time, total execution times, total logical reads, total physical reads). RDS hopes to provide users with stable, fast service, while users can get more personalized optimization recommendations from RDS.

This information is important, and users can take some measures to improve the performance of the system. For example, for those SQL that have a lot of execution times, users can add caches to the business involved in these SQL, and if a SQL has a lot of logical reads, then it is very necessary for users to cache the fields involved in these SQL (the results of recommended services for RDS missing indexes, This kind of total logic reads a lot of SQL to get the index recommendation is very likely.

Here is an example of a report:

We need to get SQL statistics for a certain time period, such as the health of a given day. We did some very interesting work in order to get SQL Server statistics because SQL Server did not provide the SQL run information for "a certain period of time" directly. However, SQL Server's sys.dm_exec_query_stats view provides an immediate running statistic of the data (our main concern Execution_count,total_elapsed_time,total_logical_ Reads,total_physical_reads these four indicators), these statistics are growing, and these statistics are most likely to be swapped out from the cache. That is, if the statistics of a SQL are not swapped out from the cache, then the values of these statistics are constantly accumulating, for example, yesterday you see Execution_count is 120, today you will see Execution_count is 230, tomorrow, Execution_ The value of count may be 512.

In order to get "statistics for a certain time period", we need to take some measures. The core algorithm is provided by the @ Ruggiu classmate, whose idea is to make a regular snapshot of the sys.dm_exec_query_stats (snapshot), and then calculate the difference of the snapshot based on the snapshot to get the health statistics for a certain time period.

For example, at 1:00, we took a snapshot, at which point the value of a SQL Execution_count was 100, and at 6:00 we took a snapshot again, at which point the value of Execution_count was 120, and at 23:00, we took another snapshot, At this point the value of Execution_count is 900. With the snapshot information, we can tell that a) between 1:00 to 6:00, the SQL executes 20 times, b) between 1:00 to 23:00, the SQL executes 800 times. We can probably tell that the SQL was executed 800 times that day.

RDS currently makes a snapshot every hour, summarizing the current snapshot every 8 hours and generating a report. The following is a record of a large RDS snapshot:

In addition, RDS focuses on the analysis of these slow logs, which provides recommended services for missing indexes.

Statistics SQL Server slow log correctly