隨記

findDtuIncrease

flow

find loading and time

SELECT
    end_time,
    avg_cpu_percent,
    avg_data_io_percent,
    avg_log_write_percent,
    avg_memory_usage_percent
FROM sys.dm_db_resource_stats
ORDER BY end_time DESC;

find SQL

DECLARE @start_time datetimeoffset  = '2026-04-13 08:20:16 +00:00';
DECLARE @end_time   datetimeoffset  = '2026-04-13 09:05:59 +00:00';

SELECT TOP 10
    qt.query_sql_text,
    SUM(rs.avg_logical_io_reads * rs.count_executions) AS total_logical_reads,
    SUM(rs.count_executions) AS executions,
    AVG(rs.avg_duration) AS avg_duration_ms
FROM sys.query_store_runtime_stats rs
JOIN sys.query_store_plan p        ON rs.plan_id = p.plan_id
JOIN sys.query_store_query q       ON p.query_id = q.query_id
JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id
WHERE rs.first_execution_time >= @start_time
  AND rs.last_execution_time  <= @end_time
GROUP BY qt.query_sql_text
ORDER BY total_logical_reads DESC;

find data io

DECLARE @start_time datetimeoffset = '2026-05-12 04:20:16 +00:00';
DECLARE @end_time datetimeoffset = '2026-05-12 05:05:59 +00:00';

SELECT TOP 10
qt.query_sql_text,
SUM((ISNULL(rs.avg_logical_io_reads,0) + ISNULL(rs.avg_logical_io_writes,0)) * rs.count_executions) AS total_logical_io_ops,
SUM(ISNULL(rs.avg_logical_io_reads,0) * rs.count_executions) AS total_logical_reads,
SUM(ISNULL(rs.avg_logical_io_writes,0) * rs.count_executions) AS total_logical_writes,
SUM(rs.count_executions) AS executions,
AVG(rs.avg_duration) AS avg_duration_ms
FROM sys.query_store_runtime_stats rs
JOIN sys.query_store_plan p ON rs.plan_id = p.plan_id
JOIN sys.query_store_query q ON p.query_id = q.query_id
JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id
WHERE rs.first_execution_time >= @start_time
AND rs.last_execution_time <= @end_time
GROUP BY qt.query_sql_text
ORDER BY total_logical_io_ops DESC;

$server = "your_server"

$db = "your_db"

$outputFolder = "D:\SPs"

先抓 SP 名稱

$procedures = ssql -env fcst_dev -Q "

SET NOCOUNT ON;

SELECT QUOTENAME(SCHEMA_NAME(schema_id)) + '.' + QUOTENAME(name)

FROM sys.procedures

" -h -1

foreach ($sp in $procedures) {

$fileName = $sp.Replace('[','').Replace(']','').Replace('.','_') + ".sql"

ssql -env fcst_dev -Q "

SET NOCOUNT ON;

SELECT 'CREATE PROCEDURE ' + '$sp' + CHAR(13) + CHAR(10) + m.definition

FROM sys.sql_modules m

JOIN sys.objects o ON m.object_id = o.object_id

WHERE o.type='P' AND o.object_id = OBJECT_ID('$sp')

" -o "$outputFolder$fileName"

}