Archiv pro měsíc: Březen 2013

Statistiky spuštění reportů

V databázi report serveru je užitečný pohled ExecutionLog3. Umožňuje získat informace o spouštění reportů, o tom, kteří uživatelé reporty spouští a s jakými parametry, jak dlouho trvalo načtení dat, jestli report nezhavaroval atd.

USE ReportServer

SELECT * FROM ExecutionLog3

--nejcasteji spoustene reporty
SELECT ItemPath, COUNT(*) pocetSpusteni 
FROM ExecutionLog3 
WHERE ItemAction = 'Render'
GROUP BY ItemPath 
ORDER BY pocetSpusteni DESC

--nejaktivnejsi uzivatele
SELECT UserName, COUNT(*) pocetSpusteni 
FROM ExecutionLog3 
WHERE ItemAction = 'Render'
GROUP BY UserName
ORDER BY pocetSpusteni DESC

--nejaktivnejsi uzivatele podle reportu
SELECT ItemPath, UserName, COUNT(*) pocetSpusteni 
FROM ExecutionLog3 
WHERE ItemAction = 'Render'
GROUP BY ItemPath, UserName
ORDER BY ItemPath, pocetSpusteni DESC

--reporty s chybou
SELECT ItemPath, UserName, TimeStart
FROM ExecutionLog3 
WHERE Status <> 'rsSuccess'
ORDER BY TimeStart DESC

--reporty s dlouhym nacitanim dat
SELECT ItemPath, UserName, TimeStart,TimeDataRetrieval
FROM ExecutionLog3 
WHERE ItemAction = 'Render'
ORDER BY TimeDataRetrieval DESC

--parametry spusteni
;WITH parametry(ItemPath, UserName, TimeStart, parametry) AS (
	SELECT ItemPath, UserName, TimeStart, CAST('<root><parametr><nazev>'+REPLACE(REPLACE(CAST([Parameters] AS VARCHAR(MAX)),'=','</nazev><hodnota>'),'&','</hodnota></parametr><parametr><nazev>')+'</hodnota></parametr></root>' AS XML) parametry
	FROM ExecutionLog3 
	WHERE ItemAction = 'Render' AND ISNULL(CAST([Parameters] AS NVARCHAR(MAX)),'') <> '' 
)
SELECT ItemPath, 
	UserName, 
	TimeStart, 
	T.p.value('nazev[1]','VARCHAR(50)') parametr, 
	T.p.value('hodnota[1]','VARCHAR(50)') hodnota
FROM parametry
CROSS APPLY parametry.nodes('/root/parametr') AS T(p)

Indexed view

Mějme špatně navrženou tabulku

CREATE TABLE testpohledu (id INT IDENTITY(1,1),data NVARCHAR(50))
GO

--naplníme daty
DECLARE @counter INT
SET @counter = 0
WHILE @counter < 100000
BEGIN
	INSERT INTO testpohledu(data)
	SELECT CHAR(ROUND(RAND()*25,2)+65)+cast(round(RAND()*10000,0) as varchar(30))
	SET @counter += 1
END

A pak mějme proceduru, které prohledává testpohledu.data a hledá číslo, nehledíc na první znak.

SELECT * FROM testpohledu WHERE SUBSTRING(data,2,5) = '9601'

Jenže tohle hledání je pomalé. I kdybychom na sloupci data měli index, tak by nám byl k ničemu, protože index je seřazený od prvního znaku. Co teď? Zákazník si stěžuje na pomalost, přidat vypočtený a oindexovaný sloupec nemůžeme, protože kdoví kde všude programátoři narvali * a co by se tím všechno rozbilo…

Zbývá indexovaný pohled

CREATE VIEW v_testpohledu WITH SCHEMABINDING
AS
SELECT id,data,CAST(SUBSTRING(data,2,5) AS VARCHAR(30)) subst FROM dbo.testpohledu
GO

CREATE UNIQUE CLUSTERED INDEX ix_v_testpohledu ON v_testpohledu(subst,data,id)
GO

Podle teorií by tohle samo o sobě mělo stačit, ale nestačí…
EDIT: Takže podle teorií tohle funguje u verze 2008 jen v edici Enterprise .

SELECT * FROM testpohledu WHERE SUBSTRING(data,2,5) = '9601'
--logical reads 348

SELECT id, data FROM v_testpohledu WHERE subst = '9601'
--logical reads 348

SELECT id, data  FROM v_testpohledu WITH (NOEXPAND) WHERE subst = '9601'
--logical reads 3

Pomohlo až přepsání procky pro pro použití pohledu a hint NOEXPAND, který přinutil server použít index seek na indexu ix_v_testpohledu a nečíst zdrojovou tabulku.