SQL Server Performance Analysis-periodically collects system running conditions

Recently, I want to return to the company and temporarily leave the customer's production database. The following database information cannot be obtained in a timely manner:

1. Execution of SQL statements run by the system. 2. collect statistics on data changes in all tables on a daily basis (daily incremental data ).

I specially wrote three job threads and executed them when the business is not busy at night to collect system running information. When I come back, I will analyze the SQL statements and provide a reference for system optimization.

Create Table tbsql (
[Statement Compilation Time] datetime,
[Total number of physical reads] int,
[Logical reads per time] int,
[Total number of logical writes] int,
[Execution times] int,
[Total CPU usage in ms] numeric ),
[Total time spent in ms] numeric ),
[Average time ms] numeric ),
[Statement execution] text,
[Collection time] datetime,
)
Insert into tbsql
Select creation_time n 'Statement compilation time'
, Total_physical_reads n 'Total number of physical reads'
, Total_logical_reads/execution_count n 'number of logical reads per time'
, Total_logical_writes n' total number of logical writes'
, Execution_count n 'execution times'
, Total_worker_time/1000 n' total CPU time Ms'
, Total_elapsed_time/1000 n' total consumed time Ms'
, (Total_elapsed_time/execution_count)/1000 N 'average time Ms'
, Substring (St. Text, (Qs. statement_start_offset/2) + 1,
(Case statement_end_offset
When-1 then datalength (St. Text)
Else Qs. statement_end_offset end
-Qs. statement_start_offset)/2) + 1) n'statement execution ',
Getdate ()
From SYS. dm_exec_query_stats as Qs
Cross apply SYS. dm_exec_ SQL _text (Qs. SQL _handle) ST
Where substring (St. Text, (Qs. statement_start_offset/2) + 1,
(Case statement_end_offset
When-1 then datalength (St. Text)
Else Qs. statement_end_offset end
-Qs. statement_start_offset)/2) + 1) Not like '% fetch %'
Order by total_elapsed_time/execution_count DESC;

DBCC freeproccache; -- clears the execution plan Cache

Job collects the number of table rows per day:

Select object_name (ID), max (rows) from sysindexes
Group by object_name (ID)
Order by 2 DESC