Záznam ze Vzdělávacího okénka HAVIT z 26. dubna 2018, kde Jiří Kanda prezentoval Temporal Tables na Microsoft SQL Serveru. Nahrávka je publikována na našem HAVIT YouTube Channelu.
Záznam ze Vzdělávacího okénka HAVIT z 5. dubna 2018. Je publikován na našem HAVIT YouTube Channelu.
Dotčená témata:
Záznam z přednášky pro konferenci FreshIT Praha z 27.3.2018. Je publikován na našem HAVIT YouTube Channelu.
a další…
Query Store je užitečnou novinkou v Azure SQL, resp. Microsoft SQL Server 2016+. Ukládá užitečné statistiky o spuštěných dotazech, jejich execution planech a mnoho dalšího. (Umí dokonce vynutit používání konkrétního execution planu a dokonce toto řešit automaticky, pokud detekuje výkonovou regresi. O tom ale někdy jindy.)
Jeden ze scénářů, který vám může být užitečný, je porovnání dvou období co se počtu spuštění jednotlivých dotazů týče, jejich průměrné doby trvání, atp. Hodit se to může při nenadálých poklesech výkonnosti, po nasazení nových verzí aplikací, atp.:
DECLARE @Period1Start datetime, @Period1End datetime, @Period2Start datetime, @Period2End datetime SET @Period1Start = '20171212 00:00' SET @Period1End = '20171213 00:00' SET @Period2Start = '20171214 00:00' SET @Period2End = '20171215 00:00' ;WITH period1 AS ( SELECT query_id, AVG(avg_duration) AS avg_duration, SUM(count_executions) AS count_executions_total FROM sys.query_store_runtime_stats INNER JOIN sys.query_store_runtime_stats_interval ON (query_store_runtime_stats_interval.runtime_stats_interval_id = query_store_runtime_stats.runtime_stats_interval_id) INNER JOIN sys.query_store_plan ON query_store_plan.plan_id = query_store_runtime_stats.plan_id WHERE (sys.query_store_runtime_stats_interval.start_time >= @Period1Start) AND (sys.query_store_runtime_stats_interval.end_time <= @Period1End) GROUP BY query_id ), period2 AS ( SELECT query_id, AVG(avg_duration) AS avg_duration, SUM(count_executions) AS count_executions_total FROM sys.query_store_runtime_stats INNER JOIN sys.query_store_runtime_stats_interval ON (query_store_runtime_stats_interval.runtime_stats_interval_id = query_store_runtime_stats.runtime_stats_interval_id) INNER JOIN sys.query_store_plan ON query_store_plan.plan_id = query_store_runtime_stats.plan_id WHERE (sys.query_store_runtime_stats_interval.start_time >= @Period2Start) AND (sys.query_store_runtime_stats_interval.end_time <= @Period2End) GROUP BY query_id ) SELECT query_store_query.query_id, period1.avg_duration AS period1_avg_duration, period2.avg_duration AS period2_avg_duration, CASE WHEN period1.count_executions_total IS NOT NULL THEN (period2.avg_duration - period1.avg_duration) * 100.0 / period1.avg_duration ELSE NULL END AS avg_duration_increase_percent, period1.count_executions_total AS period1_count_executions_total, period2.count_executions_total AS period2_count_executions_total, CASE WHEN period1.count_executions_total IS NOT NULL THEN (period2.count_executions_total - period1.count_executions_total) * 100.0 / period1.count_executions_total ELSE NULL END AS count_execution_increase_percent, query_sql_text FROM period2 LEFT JOIN period1 ON period1.query_id = period2.query_id LEFT JOIN sys.query_store_query ON query_store_query.query_id = period2.query_id LEFT JOIN sys.query_store_query_text ON query_store_query_text.query_text_id = query_store_query.query_text_id --ORDER BY period2.count_executions_total DESC ORDER BY count_execution_increase_percent DESC --ORDER BY avg_duration_increase_percent DESC
LogParser (download) je command-line utilita od Microsoftu, kterou můžete použít pro SQL-styl dotazování do libovolného textového log-souboru, resp. i mnoha souborů zároveň. Základní sada podporovaných formátů je úctyhodná: IISW3C, NCSA, IIS, IISODBC, BIN, IISMSID, HTTPERR, URLSCAN, CSV, TSV, W3C, XML, EVT, ETW, NETMON, REG, ADS, TEXTLINE, TEXTWORD, FS a COM.
Já ho používám obvykle pro dotazování IIS Logů a musím říct, že je neuskutečně rychlý. Na mém notebooku Lenovo X1 i7/16GB/SSD je schopen vyřešit následující dotaz do 8.97GB logů za 2min 12sec!
SELECT
Date,
TO_INT(COALESCE(EXTRACT_VALUE(cs-uri-query, 'id'), EXTRACT_VALUE(cs-uri-query, 'SouborSablonyID'))) AS SouborID,
COUNT(*) AS Total
FROM '[LOGFILEPATH]'
WHERE (cs-uri-stem = '/business/sablony/soubor-partner.aspx') OR (cs-uri-stem = '/business/sablony/soubor.aspx')
GROUP BY Date, SouborID
ORDER BY Total DESC
LogParser umí nejenom vyhodnocovat dotazy, ale můžete jeho výsledky nasměrovat i do databáze či mnoha dalších výstupních formátů (CSV, XML, …), např.
C:\Program Files (x86)\Log Parser 2.2>logparser "SELECT * INTO iisLogs FROM c:\temp\logs\*.log" -i:iisw3c -o:SQL -server:localhost -database:MyLogs -username:sa -password:sa -createTable: ON
Poznámka: Pokud chcete čistý import logů do DB (bez filtrování, projekce či agregací) zvažte použití Import Flat File… wizarda z SQL Management Studia, který může být ještě rychlejší. Pokud použijete LogParser, mrkněte na volbu transactionRowCount k seskupení uploadovaných dat do menšího množství transakcí (např. -transactionRowCount:-1 pro jedinou transakci).
LogParser sám je command-line utilita. Nabízí dále COM API, které můžete volat ze svých aplikací (asi ne). Nicméně právě toto API bylo využito autory několika uživatelských rozhraní, které vám při občasném využití LogParseru usnadní život:
Následující odkazy se mohou při seznamování LogParserem hodit:
/* Drop all non-system stored procs */
DECLARE @name VARCHAR(128)
DECLARE @SQL VARCHAR(254)
SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'P' AND category = 0 ORDER BY [name])
WHILE @name is not null
BEGIN
SELECT @SQL = 'DROP PROCEDURE [dbo].[' + RTRIM(@name) +']'
EXEC (@SQL)
PRINT 'Dropped Procedure: ' + @name
SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'P' AND category = 0 AND [name] > @name ORDER BY [name])
END
GO
/* Drop all views */
DECLARE @name VARCHAR(128)
DECLARE @SQL VARCHAR(254)
SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'V' AND category = 0 ORDER BY [name])
WHILE @name IS NOT NULL
BEGIN
SELECT @SQL = 'DROP VIEW [dbo].[' + RTRIM(@name) +']'
EXEC (@SQL)
PRINT 'Dropped View: ' + @name
SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'V' AND category = 0 AND [name] > @name ORDER BY [name])
END
GO
/* Drop all functions */
DECLARE @name VARCHAR(128)
DECLARE @SQL VARCHAR(254)
SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] IN (N'FN', N'IF', N'TF', N'FS', N'FT') AND category = 0 ORDER BY [name])
WHILE @name IS NOT NULL
BEGIN
SELECT @SQL = 'DROP FUNCTION [dbo].[' + RTRIM(@name) +']'
EXEC (@SQL)
PRINT 'Dropped Function: ' + @name
SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] IN (N'FN', N'IF', N'TF', N'FS', N'FT') AND category = 0 AND [name] > @name ORDER BY [name])
END
GO
/* Drop all Foreign Key constraints */
DECLARE @name VARCHAR(128)
DECLARE @constraint VARCHAR(254)
DECLARE @SQL VARCHAR(254)
SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' ORDER BY TABLE_NAME)
WHILE @name is not null
BEGIN
SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME)
WHILE @constraint IS NOT NULL
BEGIN
SELECT @SQL = 'ALTER TABLE [dbo].[' + RTRIM(@name) +'] DROP CONSTRAINT [' + RTRIM(@constraint) +']'
EXEC (@SQL)
PRINT 'Dropped FK Constraint: ' + @constraint + ' on ' + @name
SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' AND CONSTRAINT_NAME <> @constraint AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME)
END
SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' ORDER BY TABLE_NAME)
END
GO
/* Drop all Primary Key constraints */
DECLARE @name VARCHAR(128)
DECLARE @constraint VARCHAR(254)
DECLARE @SQL VARCHAR(254)
SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' ORDER BY TABLE_NAME)
WHILE @name IS NOT NULL
BEGIN
SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME)
WHILE @constraint is not null
BEGIN
SELECT @SQL = 'ALTER TABLE [dbo].[' + RTRIM(@name) +'] DROP CONSTRAINT [' + RTRIM(@constraint)+']'
EXEC (@SQL)
PRINT 'Dropped PK Constraint: ' + @constraint + ' on ' + @name
SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' AND CONSTRAINT_NAME <> @constraint AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME)
END
SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' ORDER BY TABLE_NAME)
END
GO
/* Drop all tables */
DECLARE @name VARCHAR(128)
DECLARE @SQL VARCHAR(254)
SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'U' AND category = 0 ORDER BY [name])
WHILE @name IS NOT NULL
BEGIN
SELECT @SQL = 'DROP TABLE [dbo].[' + RTRIM(@name) +']'
EXEC (@SQL)
PRINT 'Dropped Table: ' + @name
SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'U' AND category = 0 AND [name] > @name ORDER BY [name])
END
GO
SELECT [schema] = s.name,
[table] = t.name
FROM sys.schemas AS s
INNER JOIN sys.tables AS t ON s.[schema_id] = t.[schema_id]
WHERE NOT EXISTS (SELECT 1
FROM sys.identity_columns
WHERE [object_id] = t.[object_id])
ORDER BY t.name
