Category Archives: Database

SQL Query Store: Porovnání dvou období (počty spuštění dotazů, trvání)

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

References

Tip: Microsoft LogParser [Studio] – rychlé SQL dotazování do textových log-souborů

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

Výstup do databáze

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).

Uživatelské rozhraní?

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:

  • Microsoft LogParser Studio (download) je přímo od Microsoftu a kromě GUI přináší i mnoho (181) připravených šablon dotazů pro různé typy logů.
    2017-11-28_2-46-39
  • Log Parser Lizard GUI je bezplatná varianta (s placenou Pro edicí) vytvořená mimo Microsoft, která vypadá celkem schopně. Nezkoušel jsem, ale pokud byste to potřebovali dennodenně, asi to může být zajímavé.

Reference

Následující odkazy se mohou při seznamování LogParserem hodit:

 

T-SQL: Smazání všech tabulek a dalších běžných objektů z DB

Zdroj: http://stackoverflow.com/questions/536350/drop-all-the-tables-stored-procedures-triggers-constraints-and-all-the-depend

/* 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

T-SQL: Seznam tabulek bez IDENTITY

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

Instalace SQL Server 2016 (Express) LocalDB

SQL Server 2016 RTM, na rozdíl třeba od své RC0, již ve svém hlavním instalátoru nenabízí check-box na instalaci LocalDB, přesněji řečeno “někdy nabízí, někdy nenabízí”. Zřejmě podle toho, jestli už na stroji něco nachází, nebo ne. Každopádně doinstalaci provedete samostatným instalátorem, který je na SQL Server DVD:

<dvd>:\1033_ENU_LP\x64\Setup\x64\SqlLocalDB.msi

PS: Při instalaci SQL Serveru 2016 Express by měl být checkbox LocalDB ve fázi Feature Selection, ale ani na Expressu se mi nenabízel.

SQL Worst Practices & Optimalizace – slides a dema [WUG Hradec Králové 11/2015]

Slides a dema z mé přednášky pro Windows User Group Hradec Králové z 12.11.2015.

Záznam je publikován na našem HAVIT YouTube Channelu.

Dotčená témata:

  • Pyramida zacílení optimalizačních snah
  • Connection Pooling
  • SQL Injection
  • DB Schema
  • Import vs. hromadné Enable/Disable Constraints
  • [Non-]Clustered Index, Heap
  • Execution Plans
    • Query Cost, Statistiky
    • Operace v execution planu
  • Set-based vs. Row-based logika
  • Různé zápisy stejného dotazu
  • Cachování execution planu
  • Práce s časem a datam vs. rychlost

Microsoft SQL Server – kill locku na databázi

Pokud chceme odblokovat lock-nutou databázi na Microsoft SQL Serveru (například pokud chceme přejmenovat databázi, provést obnovu databáze, výmaz databáze…), lze to udělat následujícími způsoby.

První možností je použít tento kód:

SELECT DISTINCT
  name AS database_name,
  session_id,
  host_name,
  login_time,
  login_name,
  reads,
  writes
FROM sys.dm_exec_sessions
    LEFT OUTER JOIN sys.dm_tran_locks ON sys.dm_exec_sessions.session_id = sys.dm_tran_locks.request_session_id
    INNER JOIN sys.databases ON sys.dm_tran_locks.resource_database_id = sys.databases.database_id
  WHERE name ='NazevMeDatabaze'
  ORDER BY name

Následně se nám objeví, kdo má nad databází zámek:

SQL_select_lock

Pak lze použít například „KILL 68„, čímž zámek uvolníme. Výhodou je, že vidíme, kdo má lock a lze vše ručně řídit. Nevýhodou je, že mohou přibýt další zámky před operací, kterou chceme vykonat.

Další možností je použít tento kód:

ALTER DATABASE YourDatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE
--do you stuff here
ALTER DATABASE YourDatabase SET MULTI_USER

Zde je určitou nevýhodou to, že nám zůstanou na databázi naše vlastní zámky a také to, že můžeme zapomenout vrátit databázi do multi-user režimu. Akce provádíme v místě označeném „do you stuff here“.

sp_MSForeachDB – Vykonání SQL příkazů pro každou DB

Microsoft SQL Server má nedokumentovanou uloženou proceduru, která usnadní vykonání stejného SQL příkazu pro více DB. Např. nalezení CLR assemblies přes všechny DB:

sp_MSForeachDB 'use [?]; SELECT DB_NAME(), * FROM sys.assemblies WHERE name LIKE ''Havit%'';'

Pokud chceme příkaz vykonat s výjimkou některých DB, můžeme použít IF:

sp_MSForeach 'IF ? NOT IN (''master'', ''msdb'', ''tempdb'', ''model'') USE [?]; -- atp'

(Pozor, že pokud dáte pod IF jenom USE, tak se Vám následný příkaz vykoná pro aktuální DB několikrát, protože neproběhne jenom přepnutí USE na jinou DB.)

SQL Server Performance – Execution Plans – záznam, slides a dema [TechEd Praha 2014]

Dema a slides z mé přednášky pro Gopas TechEd DevCon Praha 2014 – inovovaná podoba přednášky, kterou jsem již dělal pro MS Fest (skoro stejné) nebo WUG Praha (asi nejlepší plná verze bez časové tísně, ale zas má něco přes tři hodiny):

Z přednášky jsem pořizoval záznam, který najdete na našem HAVIT YouTube Channelu, tato podoba má ze tří možných asi nejkvalitnější záznam (720p screenrec):

SQL 2014 LocalDB: Instance “v12.0” není automaticky založena (50 – Local Database Runtime error occurred. The specified LocalDB instance does not exist.)

Mám na počítači čerstvou instalaci SQL Serveru 2014 (včetně LocalDB) a hraju si s Entity Frameworkem 6.1.0, Výchozí konektivitou je “(localdb)\v12.0” a dostávám krásnou chybu:

System.Data.Entity.Core.ProviderIncompatibleException, but exception System.Data.Entity.Validation.DbEntityValidationException was expected. Exception message: System.Data.Entity.Core.ProviderIncompatibleException: An error occurred accessing the database. This usually means that the connection to the database failed. Check that the connection string is correct and that the appropriate DbContext constructor is being used to specify it or find it in the application’s config file. See http://go.microsoft.com/fwlink/?LinkId=386386 for information on DbContext and connections. See the inner exception for details of the failure. —> System.Data.Entity.Core.ProviderIncompatibleException: The provider did not return a ProviderManifestToken string. —> System.Data.SqlClient.SqlException: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 50 – Local Database Runtime error occurred. The specified LocalDB instance does not exist.)

Po chvilce bojů zjišťuji, že SQL Server 2014 LocalDB má v sobě ?bug?, a to že nezaloží automaticky instanci “v12.0”, ač to o sobě v dokumentaci tvrdí a EntityFramework to očekává: https://connect.microsoft.com/SQLServer/feedback/details/845278/sql-server-2014-express-localdb-does-not-create-automatic-instance-v12-0

Řešení je naštěstí poměrně snadné, z Command Promptu stačí instanci vytvořit:

sqllocaldb create v12.0 12.0

…a hrátky s Entity Frameworkem mohou pokračovat. ;-)

Update: Test/Build Server

Pozor, že na test/build serveru, kde se budou pouštět unit-testy, je potřeba instanci založit pod účtem, který testy pouští. Instance LocalDB nejsou sdíleny mezi účty, není-li explicitně nastaveno jinak (a pozor, že prostý „sqllocaldb share v12.0 v12.0“ ve skutečnosti nasdílí instanci pod názvem „.\v12.0“, nikoliv „v12.0“).