MSSQL query store - Cronologia

WikiSysop: /* query con full scan onerose */

2026-09-18T13:44:33Z

query con full scan onerose

@@ Riga 56: / Riga 56: @@
 WHERE rsi.start_time >= '2026-06-11 14:00'
   AND rsi.end_time   <= '2026-06-11 16:00';
 </pre>

WikiSysop: /* Analisi */

2026-09-18T09:32:54Z

Analisi

← Versione meno recente		Versione delle 09:32, 18 set 2026
Riga 58:		Riga 58:
	</pre>		</pre>

			==query con full scan onerose==

			<pre>
			SET NOCOUNT ON;

			-- Tabella temporanea per raccogliere i risultati
			IF OBJECT_ID('tempdb..#ScanResults') IS NOT NULL DROP TABLE #ScanResults;

			CREATE TABLE #ScanResults (
			query_id BIGINT,
			plan_id BIGINT,
			query_sql_text NVARCHAR(MAX),
			scan_type VARCHAR(50),
			scan_cost_percent DECIMAL(5,2),
			avg_duration_ms DECIMAL(38,2),
			avg_logical_io_reads BIGINT,
			total_executions BIGINT
			);

			-- Step 1: Estrazione rapida dei soli 50 piani a più alto I/O
			IF OBJECT_ID('tempdb..#TopPlans') IS NOT NULL DROP TABLE #TopPlans;

			SELECT TOP 50
			p.plan_id,
			p.query_id,
			qt.query_sql_text,
			p.query_plan,
			SUM(rs.count_executions) AS total_executions,
			CAST(AVG(rs.avg_duration / 1000.0) AS DECIMAL(38,2)) AS avg_duration_ms,
			CAST(AVG(rs.avg_logical_io_reads) AS BIGINT) AS avg_logical_io_reads
			INTO #TopPlans
			FROM sys.query_store_plan p
			INNER JOIN sys.query_store_query q ON p.query_id = q.query_id
			INNER JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id
			INNER JOIN sys.query_store_runtime_stats rs ON p.plan_id = rs.plan_id
			WHERE p.query_plan LIKE '%PhysicalOp="Table Scan"%'
			OR p.query_plan LIKE '%PhysicalOp="Clustered Index Scan"%'
			OR p.query_plan LIKE '%PhysicalOp="Index Scan"%'
			GROUP BY p.plan_id, p.query_id, qt.query_sql_text, p.query_plan
			ORDER BY avg_logical_io_reads DESC;

			-- Step 2: Ciclo protetto sui 50 piani per gestire gli errori XML individualmente
			DECLARE @plan_id BIGINT, @query_id BIGINT, @sql_text NVARCHAR(MAX), @plan_xml_str NVARCHAR(MAX);
			DECLARE @avg_dur DECIMAL(38,2), @avg_io BIGINT, @tot_exec BIGINT;

			DECLARE plan_cursor CURSOR LOCAL FAST_FORWARD FOR
			SELECT plan_id, query_id, query_sql_text, query_plan, avg_duration_ms, avg_logical_io_reads, total_executions
			FROM #TopPlans;

			OPEN plan_cursor;
			FETCH NEXT FROM plan_cursor INTO @plan_id, @query_id, @sql_text, @plan_xml_str, @avg_dur, @avg_io, @tot_exec;

			WHILE @@FETCH_STATUS = 0
			BEGIN
			BEGIN TRY
			-- Esegue il parsing XML preciso per il singolo piano
			WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
			INSERT INTO #ScanResults
			SELECT
			@query_id,
			@plan_id,
			@sql_text,
			scan_nodes.scan_op.value('@PhysicalOp', 'VARCHAR(50)') AS scan_type,
			CAST((scan_nodes.scan_op.value('@EstimatedTotalSubtreeCost', 'FLOAT') / NULLIF(stmt.stmt_xml.value('@StatementSubTreeCost', 'FLOAT'), 0)) * 100 AS DECIMAL(5,2)) AS scan_cost_percent,
			@avg_dur,
			@avg_io,
			@tot_exec
			FROM (SELECT CAST(@plan_xml_str AS XML) AS xPlan) AS xml_plan
			CROSS APPLY xml_plan.xPlan.nodes('//StmtSimple') AS stmt(stmt_xml)
			CROSS APPLY stmt.stmt_xml.nodes('.//RelOp[@PhysicalOp="Table Scan" or @PhysicalOp="Clustered Index Scan" or @PhysicalOp="Index Scan"]') AS scan_nodes(scan_op)
			WHERE stmt.stmt_xml.value('@StatementSubTreeCost', 'FLOAT') > 0
			AND (scan_nodes.scan_op.value('@EstimatedTotalSubtreeCost', 'FLOAT') / stmt.stmt_xml.value('@StatementSubTreeCost', 'FLOAT')) >= 0.50;
			END TRY
			BEGIN CATCH
			-- In caso di errore XML (>128 livelli di annidamento), ignora solo questo piano e prosegue
			END CATCH;

			FETCH NEXT FROM plan_cursor INTO @plan_id, @query_id, @sql_text, @plan_xml_str, @avg_dur, @avg_io, @tot_exec;
			END

			CLOSE plan_cursor;
			DEALLOCATE plan_cursor;

			-- Step 3: Estrazione del risultato finale
			SELECT
			query_id,
			plan_id,
			query_sql_text,
			scan_type,
			scan_cost_percent,
			avg_duration_ms,
			avg_logical_io_reads,
			total_executions
			FROM #ScanResults
			ORDER BY avg_logical_io_reads DESC;
			</pre>

	[[Categoria:Database]]		[[Categoria:Database]]

WikiSysop il 09:04, 18 set 2026

2026-09-18T09:04:23Z

← Versione meno recente		Versione delle 09:04, 18 set 2026
Riga 1:		Riga 1:
			=Attivare e Disattivare=
	==attivare query store==		==attivare query store==
	ALTER DATABASE [NomeTuoDatabase]		ALTER DATABASE [NomeTuoDatabase]
Riga 34:		Riga 35:
	FROM sys.database_query_store_options;		FROM sys.database_query_store_options;

			=Analisi=

	=analizzare query=		==analizzare query==
	<pre>		<pre>
	SELECT		SELECT

WikiSysop: /* disattiva query store */

2026-09-11T15:37:09Z

disattiva query store

← Versione meno recente		Versione delle 15:37, 11 set 2026
Riga 18:		Riga 18:
	SIZE_BASED_CLEANUP_MODE = OFF -- Disabilita la pulizia al raggiungimento del limite di spazio		SIZE_BASED_CLEANUP_MODE = OFF -- Disabilita la pulizia al raggiungimento del limite di spazio
	);		);

			o più drastico:

			ALTER DATABASE [NomeTuoDatabase] SET QUERY_STORE = OFF;

	==interrogare query store==		==interrogare query store==

WikiSysop: /* attivare query store */

2026-09-11T15:33:07Z

attivare query store

@@ Riga 7: / Riga 7: @@
     MAX_STORAGE_SIZE_MB = 1000,                 -- Spazio massimo occupato dal Query Store (es. 1 GB)
     QUERY_CAPTURE_MODE = AUTO                   -- Modalità di cattura (cattura le query rilevanti evitando il rumore)
  );

WikiSysop: /* interrogare query store */

2026-09-11T15:28:45Z

interrogare query store

@@ Riga 10: / Riga 10: @@
 ==interrogare query store==
 <pre>
 SELECT

WikiSysop il 15:27, 11 set 2026

2026-09-11T15:27:42Z

← Versione meno recente		Versione delle 15:27, 11 set 2026
Riga 1:		Riga 1:
			==attivare query store==
			ALTER DATABASE [NomeTuoDatabase]
			SET QUERY_STORE = ON (
			OPERATION_MODE = READ_WRITE, -- Attiva la raccolta dati
			CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30), -- Conserva i dati per 30 giorni (regolabile)
			INTERVAL_LENGTH_MINUTES = 60, -- Intervallo di aggregazione (es. 1 ora)
			MAX_STORAGE_SIZE_MB = 1000, -- Spazio massimo occupato dal Query Store (es. 1 GB)
			QUERY_CAPTURE_MODE = AUTO -- Modalità di cattura (cattura le query rilevanti evitando il rumore)
			);

			==interrogare query store==
	<pre>		<pre>
	SELECT		SELECT

WikiSysop il 14:42, 11 giu 2026

2026-06-11T14:42:34Z

← Versione meno recente		Versione delle 14:42, 11 giu 2026
Riga 1:		Riga 1:

	<~~code~~>		<pre>
	SELECT		SELECT
	qt.query_sql_text,		qt.query_sql_text,
Riga 19:		Riga 19:
	WHERE rsi.start_time >= '2026-06-11 14:00'		WHERE rsi.start_time >= '2026-06-11 14:00'
	AND rsi.end_time <= '2026-06-11 16:00';		AND rsi.end_time <= '2026-06-11 16:00';
	</~~code~~>		</pre>

WikiSysop: Creata pagina con " `SELECT qt.query_sql_text, rs.avg_duration, rs.count_executions, rs.avg_cpu_time, rsi.start_time, rsi.end_time FROM sys.query_store_runtime_stats rs JOIN sys.query_store_runtime_stats_interval rsi ON rs.runtime_stats_interval_id = rsi.runtime_stats_interval_id 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 = q..."`


2026-06-11T14:42:15Z
Creata pagina con " <code> SELECT     qt.query_sql_text,     rs.avg_duration,     rs.count_executions,     rs.avg_cpu_time,     rsi.start_time,     rsi.end_time FROM sys.query_store_runtime_stats rs JOIN sys.query_store_runtime_stats_interval rsi     ON rs.runtime_stats_interval_id = rsi.runtime_stats_interval_id 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 = q..."
Nuova pagina


<code>

SELECT

    qt.query_sql_text,

    rs.avg_duration,

    rs.count_executions,

    rs.avg_cpu_time,

    rsi.start_time,

    rsi.end_time

FROM sys.query_store_runtime_stats rs

JOIN sys.query_store_runtime_stats_interval rsi

    ON rs.runtime_stats_interval_id = rsi.runtime_stats_interval_id

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 rsi.start_time >= '2026-06-11 14:00'

  AND rsi.end_time   <= '2026-06-11 16:00';

</code>





[[Categoria:Database]]

[[Categoria:MS SQL Server]]