Gantt report for MS SQL Server

Wykres Gantt aktywności SQL Server

Written by on in Business Intelligence, Inspiration, Integration Services, MSSQL, R Programming, TIPS, TSQL

Wstęp

 

Narzędzi oraz sposobów monitorowania poszczególnych obiektów w SQL Server jest wiele i wiele zostało już na ten temat napisane. W tym poście chciałbym pokazać jeden ze sposobów graficznej analizy aktywności różnych obiektów działającej na serwerze Microsoft SQL Server, który – mam nadzieję – nie jest tak bardzo oczywisty. Idea tego postu to przygotowanie wykresu Gantt (https://pl.wikipedia.org/wiki/Diagram_Gantta), który zobrazuje aktywność uruchamianych procedur, pakietów SSIS oraz zadań SQL Server Agent. Taki wykres powinien okazać się przydatny do ogólnej analizy tego, co dzieje się na serwerze, ale również powinien pomóc dokładniej zrozumieć które zadania są wykonywane równocześnie i jakie zależności mogą występować. Graficzna prezentacja powinna ułatwić oraz przyspieszyć ewentualną analizę problemów. Rzeczony wykres zostanie przygotowany z wykorzystaniem języka R, natomiast dane zostaną przygotowane po stronie SQL Server jako procedura składowana.

Logi dla procedur składowanych

Śledzenie czasów wykonania procedur składowanych w Microsoft SQL Server nie jest rzeczą trywialną. Co prawda możemy skorzystać z widoku systemowego sys.dm_exec_procedure_stats, który udostępnia informacje o statystykach wykonywania procedur, natomiast należy pamiętać, że zawiera on informacje tylko o tych procedurach, które zostały załadowane do pamięci cache. W chwili, kiedy procedura jest usuwana z cache widok ten nie zwróci statystyk dla tej procedury. Innymi sposobami mogłoby być skonfigurowanie SQL Server Profiler, Extendend Events bądź wykorzystanie logów dodatkowych narzędzi do monitorowania Microsoft SQL Server. W przypadku hurtowni danych wydaje mi się jednak, że dobrym pomysłem jest stworzenie własnego mechanizmu do gromadzenia informacji o początku i zakończeniu wykonywania procedur. W przypadku dużych rozwiązań i dużej liczby skomplikowanych procedur dobrym pomysłem może być nawet rozbudowanie takiego mechanizmu oraz logowanie informacji ze znakiem czasowym dla kluczowych miejsc w środku procedur. Dla procesu ETL i dużych procedur logowanie takie nie spowoduje zauważalnego narzutu, a mogą okazać się bardzo pomocne podczas optymalizacji oraz monitorowania. Niezależnie od tego postu warto rozważyć implementację takich logów dla procesów ładowania hurtowni danych. Poniżej przykład takiego rozwiązania poczynając od tabeli, która posłuży do przechowywania logów:

CREATE TABLE [dbo].[spRunLog]
(
	[spRunLogID] [int] NOT NULL IDENTITY(1, 1),
	[packageRunID] [int] NULL,
	[spName] [varchar] (512) NOT NULL,
	[startTime] [datetime] NOT NULL,
	[endTime] [datetime] NULL,
	[spRunStatus] [varchar] (50) NULL,
	[regDate] [datetime] NOT NULL CONSTRAINT [DF_spRunLog_regDate] DEFAULT (getutcdate())
) 

ALTER TABLE [dbo].[spRunLog] ADD CONSTRAINT [PK_spRunLog] PRIMARY KEY CLUSTERED  ([spRunLogID]) 
GO
CREATE NONCLUSTERED INDEX [IX_spRunLog_PackageRunID] ON [dbo].[spRunLog] ([packageRunID]) 
GO
CREATE NONCLUSTERED INDEX [IX_spRunLog_startTime] ON [dbo].[spRunLog] ([startTime])

W przykładzie znajduje się dodatkowa kolumna packageRunID do której możemy zapisywać execution_id z pakietu SSIS. W tym przypadku zakładamy, że procedura jest wywoływana z pakietu SQL Server Integration Services i jako parametr przekazywany jest @packageRunID, który następnie pozwoli powiązać konkretne wykonanie procedury z konkretnym wykonaniem pakietu w logach SSISDB. Następnie możemy dodać dwie procedury, które będą odpowiedzialne za logowanie czasu startu i zakończenia procedury.

CREATE PROCEDURE [dbo].[spLogStart] 
	@spName varchar(512),
	@packageRunID int = NULL,
	@outputID int OUTPUT
AS
BEGIN
	SET NOCOUNT ON;
	
	insert into dbo.spRunLog (packageRunID, spName, startTime)
	values (@packageRunID, @spName, GETDATE())
		
	set @outputID = SCOPE_IDENTITY()
	
	Return @outputID
END
CREATE PROCEDURE [dbo].[spLogEnd] 
	@logID int
AS
BEGIN
	
	SET NOCOUNT ON;

	update dbo.spRunLog
	set endTime = GETDATE()
		,spRunStatus = 'OK'
	where spRunLogID = @logID
	
END

Dla każdej procedury wystarczy teraz dodać na początku oraz końcu kodu procedury odwołanie do nowo utworzonych procedur. Poniżej przykład:

CREATE PROCEDURE [dbo].[testSPLog]
   (
    @rowNumber BIGINT
   ,@packageRunID Int = NULL
   )
AS 

DECLARE @logID Int
  ,@procName Varchar(50) = OBJECT_NAME(@@procid)
 
BEGIN
  
  SET NOCOUNT ON

EXEC dbo.spLogStart @spName = @procName, @packageRunID = @packageRunID, @outputID = @logID OUTPUT 

SELECT 
TOP (@rowNumber)
a.number, b.number, c.number
FROM master.dbo.spt_values a
CROSS JOIN master.dbo.spt_values b
CROSS JOIN master.dbo.spt_values c

EXEC dbo.spLogEnd @logID = @logid  

END

Przykładowe wykonanie testowej procedury. Warto zauważyć, że pierwsze wywołanie spowoduje błąd, ponieważ parametr dla klauzuli TOP nie może być ujemny.

EXEC [dbo].[testSPLog] @rowNumber = -1, @packageRunID = NULL
EXEC [dbo].[testSPLog] @rowNumber = 9, @packageRunID = NULL
EXEC [dbo].[testSPLog] @rowNumber = 99999, @packageRunID = NULL
EXEC [dbo].[testSPLog] @rowNumber = 999999, @packageRunID = NULL

Dzięki zaimplementowanemu mechanizmu jesteśmy w stanie przygotować proste zapytanie oraz sprawdzić czas wykonywania procedur.

SELECT 
	DATEDIFF(SECOND, startTime, endTime) AS DurationSec
	,[spRunLogID]
	,[packageRunID]
	,[spName]
	,[startTime]
	,[endTime]
	,[procRunStatus]
	,[regDate] 
FROM dbo.spRunLog
ORDER BY starttime DESC

Powyższe wywołanie procedur zaloguje następujące informacje:

Oczywiście zarówno tabelę, jak i procedury z logowaniem można dowolnie rozbudowywać i dodać dodatkowe informacje, natomiast nawet w takiej formie powinny dostarczyć wielu cennych informacji. Na potrzeby tego postu oraz wykresu taki mechanizm dostarczy informacji o czasie wykonywania procedur.

Logi dla SQL Server Integration Services Packages

Czasy wykonywania pakietów w SQL Server Integration Services można przechwycić na kilka sposób. Moglibyśmy przygotować własny mechanizm logowania, użyć logowania dostępnego w SSIS i zapisywać informacje do tabeli sysssislog, natomiast najprostszym sposobem wydaje się przygotowanie zapytania na bazie SSISDB. Na przykład:

SELECT 
	ex.package_name, 
	ex.package_path,
	s.start_time, 
	s.end_time, 
	s.execution_duration, 
	CASE s.execution_result
		WHEN 0 then 'Success'
		WHEN 1 then 'Failure'
		WHEN 2 then 'Completion'
		WHEN 3 then 'Cancelled'
		ELSE 'Unknown'
	END AS ExecutionResult,
	e.execution_id,
	ex.executable_id,
	s.statistics_id
FROM [SSISDB].[catalog].[executable_statistics] s
INNER JOIN SSISDB.catalog.executions e ON s.execution_id = e.execution_id
INNER JOIN SSISDB.catalog.executables ex ON s.execution_id = ex.execution_id AND s.executable_id = ex.executable_id
where ex.package_path = '\Package'

Przykładowy wynik zapytania:

Zapytanie zwróci statystyki o czasie wykonania poszczególnych pakietów. Moglibyśmy również oczywiście dodać dodatkowe informacje dostępne w bazie SSISDB.

Logi dla SQL Server Agent

W przypadku SQL Server Agent również wystarczy przygotować stosunkowo proste zapytanie (tym razem do bazy msdb). Na przykład:

SELECT 
	j.job_id AS 'AgentJobID',
	j.name AS 'AgentJobName',
	j.version_number AS 'AgentJobVersion',
	s.step_id AS 'AgentStepID',
	s.step_name AS 'AgentStepName',
	s.subsystem AS 'AgentStepSubsystem',
	s.step_uid AS 'AgentStepUID',
	h.instance_id AS 'AgentRunInstanceID',
	CASE h.run_status 
		WHEN 0 THEN 'Failed'
		WHEN 1 THEN 'Succeeded'
		WHEN 2 THEN 'Retry'
		WHEN 3 THEN 'Canceled'
	END AS 'AgentRunRunStatus',
	msdb.dbo.agent_datetime(run_date, run_time) AS 'AgentStepCalendarDate',
	msdb.dbo.agent_datetime(run_date, run_time) AS 'AgentStepStartTime',
	DATEADD(SECOND, ((h.run_duration/1000000)*86400) + (((h.run_duration-((h.run_duration/1000000)*1000000))/10000)*3600) + (((h.run_duration-((h.run_duration/10000)*10000))/100)*60) + (h.run_duration-(h.run_duration/100)*100),  msdb.dbo.agent_datetime(run_date, run_time)) AS 'AgentStepEndTime',
	'Agent' AS 'AgentLogType'
FROM msdb.dbo.sysjobs j 
INNER JOIN msdb.dbo.sysjobsteps s 
	ON j.job_id = s.job_id
INNER JOIN msdb.dbo.sysjobhistory h 
	ON s.job_id = h.job_id 
	AND s.step_id = h.step_id

Przykładowy wynik zapytania:

Zapytanie to zwróci informacje o konktrenych “Job-ach” i ich zadaniach oraz dodatkowe informacje, które ułatwią ewentualną, dalszą analizę.

Procedura jako źródło danych do wykresu

Wykorzystując powyższe zapytania możemy wreszcie przygotować procedurę, która pobierze statystyki dla procedur składowanych, SSIS oraz SQL Server Agent i zwróci jako pojedynczy rezultat.

CREATE PROCEDURE dbo.GanttReport  
(
	@startDate DATE 
	,@endDate DATE 
	,@getSSISPackages BIT = 1
	,@getStoredProcedures BIT = 1
	,@getAgentJobs BIT = 1
	,@durationMin INT = 0
)

AS 

SET NOCOUNT ON

BEGIN 

	CREATE TABLE #results
	(
		[TaskName] [NVARCHAR](500) NULL,
		[Start] [DATETIME] NULL,
		[Duration] [INT] NULL,
		[Resource] [VARCHAR](20) NOT NULL,
		[AdditionalInfo] [VARCHAR](max) NULL
	) 

	IF @getAgentJobs = 1
	BEGIN 

		;WITH cte AS 
		(
			SELECT 
				j.job_id AS 'AgentJobID',
				j.name AS 'AgentJobName',
				j.version_number AS 'AgentJobVersion',
				s.step_id AS 'AgentStepID',
				s.step_name AS 'AgentStepName',
				s.subsystem AS 'AgentStepSubsystem',
				s.step_uid AS 'AgentStepUID',
				h.instance_id AS 'AgentRunInstanceID',
				CASE h.run_status 
					WHEN 0 THEN 'Failed'
					WHEN 1 THEN 'Succeeded'
					WHEN 2 THEN 'Retry'
					WHEN 3 THEN 'Canceled'
				END AS 'AgentRunRunStatus',
				msdb.dbo.agent_datetime(run_date, run_time) AS 'AgentStepCalendarDate',
				msdb.dbo.agent_datetime(run_date, run_time) AS 'AgentStepStartTime',
				DATEADD(SECOND, ((h.run_duration/1000000)*86400) + (((h.run_duration-((h.run_duration/1000000)*1000000))/10000)*3600) + (((h.run_duration-((h.run_duration/10000)*10000))/100)*60) + (h.run_duration-(h.run_duration/100)*100),  msdb.dbo.agent_datetime(run_date, run_time)) AS 'AgentStepEndTime',
				'Agent' AS 'AgentLogType'
			FROM msdb.dbo.sysjobs j 
			INNER JOIN msdb.dbo.sysjobsteps s 
				ON j.job_id = s.job_id
			INNER JOIN msdb.dbo.sysjobhistory h 
				ON s.job_id = h.job_id 
				AND s.step_id = h.step_id 
			WHERE 
				CONVERT(DATE,CONVERT(VARCHAR(20),run_date)) BETWEEN @startDate AND @endDate
		), cte2 AS 
		(
			SELECT
				a.AgentJobID ,
				a.AgentJobName ,
				a.AgentJobVersion ,
				a.AgentStepID ,
				a.AgentStepName ,
				a.AgentStepSubsystem ,
				a.AgentStepUID ,
				a.AgentRunInstanceID ,
				a.AgentRunRunStatus ,
				a.AgentStepCalendarDate ,
				a.AgentStepStartTime ,
				a.AgentStepEndTime ,
				a.AgentLogType,
				'Job ' + a.AgentJobName + ' ' + a.AgentStepName + ' (' + CONVERT(VARCHAR(100),a.AgentRunInstanceID) + ')' AS TaskName,
				DATEDIFF(SECOND, a.AgentStepStartTime, a.AgentStepEndTime) AS Duration,
				DATEDIFF(MINUTE, a.AgentStepStartTime, a.AgentStepEndTime) AS DurationMin,
				a.AgentStepStartTime AS 'Start',
				'Job' AS [Resource],
				'<br>' + 'AgentJobID' + ': ' + CONVERT(VARCHAR(100),ISNULL(a.AgentJobID,'') )
				+ '<br>' + 'AgentJobName' + ': ' + CONVERT(VARCHAR(100),ISNULL(a.AgentJobName ,''))
				+ '<br>' + 'AgentJobVersion' + ': ' + CONVERT(VARCHAR(100),ISNULL(a.AgentJobVersion,'') )
				+ '<br>' + 'AgentStepID' + ': ' + CONVERT(VARCHAR(100),ISNULL(a.AgentStepID ,''))
				+ '<br>' + 'AgentStepName' + ': ' + CONVERT(VARCHAR(100),ISNULL(a.AgentStepName,'') )
				+ '<br>' + 'AgentStepSubsystem' + ': ' + CONVERT(VARCHAR(100),ISNULL(a.AgentStepSubsystem,'') )
				+ '<br>' + 'AgentStepUID' + ': ' + ISNULL(CONVERT(VARCHAR(100),a.AgentStepUID) ,'')
				+ '<br>' + 'AgentRunInstanceID' + ': ' + CONVERT(VARCHAR(100),ISNULL(a.AgentRunInstanceID ,''))
				+ '<br>' + 'AgentRunRunStatus' + ': ' + CONVERT(VARCHAR(100),ISNULL(a.AgentRunRunStatus ,''))
				+ '<br>' + 'AgentStepStartTime' + ': ' + CONVERT(VARCHAR(100),ISNULL(a.AgentStepStartTime ,''))
				+ '<br>' + 'AgentStepEndTime' + ': ' + CONVERT(VARCHAR(100),ISNULL(a.AgentStepEndTime ,''))
				AS AdditionalInfo
			FROM cte a
		)
		INSERT INTO #results 
		(   
			TaskName ,
			Start ,
			Duration ,
			Resource ,
			AdditionalInfo
		)
		SELECT 
			TaskName, 
			Start, 
			Duration, 
			[Resource], 
			AdditionalInfo 
		FROM cte2

	END 

	IF @getSSISPackages = 1
	BEGIN 

	;WITH cte AS 
	(
		SELECT 
			ex.package_name, 
			ex.package_path,
			s.start_time, 
			s.end_time, 
			s.execution_duration, 
			CASE s.execution_result
				WHEN 0 then 'Success'
				WHEN 1 then 'Failure'
				WHEN 2 then 'Completion'
				WHEN 3 then 'Cancelled'
				ELSE 'Unknown'
			END AS ExecutionResult,
			e.execution_id,
			ex.executable_id,
			s.statistics_id
		FROM [SSISDB].[catalog].[executable_statistics] s
		INNER JOIN SSISDB.catalog.executions e ON s.execution_id = e.execution_id
		INNER JOIN SSISDB.catalog.executables ex ON s.execution_id = ex.execution_id AND s.executable_id = ex.executable_id
		where ex.package_path = '\Package'
		AND CONVERT(DATE,s.start_time) BETWEEN @startDate AND @endDate
	)
	INSERT INTO #results 
	(   
		TaskName ,
		Start ,
		Duration ,
		Resource ,
		AdditionalInfo
	)
	SELECT 
		a.package_name + ' ' + CONVERT(VARCHAR(25),a.execution_id) + ' ' + CONVERT(VARCHAR(25),a.statistics_id) AS TaskName,
		a.start_time,
		a.execution_duration AS Duration,
		'Package' AS [Resource],
		'<br>' + 'package_name' + ': ' + CONVERT(VARCHAR(500),ISNULL(a.package_name,''))
		+ '<br>' + 'package_path' + ': ' + CONVERT(VARCHAR(500),ISNULL(a.package_path,'')) 
		+ '<br>' + 'start_time' + ': ' + CONVERT(VARCHAR(50),ISNULL(a.start_time,'')) 
		+ '<br>' + 'end_time' + ': ' + CONVERT(VARCHAR(50),ISNULL(a.end_time,'')) 
		+ '<br>' + 'execution_duration' + ': ' + CONVERT(VARCHAR(50),ISNULL(a.execution_duration,'')) 
		+ '<br>' + 'ExecutionResult' + ': ' + CONVERT(VARCHAR(50),ISNULL(a.ExecutionResult,'')) 
		+ '<br>' + 'execution_id' + ': ' + CONVERT(VARCHAR(50),ISNULL(a.execution_id,'')) 
		+ '<br>' + 'executable_id' + ': ' + CONVERT(VARCHAR(50),ISNULL(a.executable_id,'')) 
		+ '<br>' + 'statistics_id' + ': ' + CONVERT(VARCHAR(50),ISNULL(a.statistics_id,'')) 
		AS AdditionalInfo
	FROM cte a

	END 

	IF @getStoredProcedures = 1
	BEGIN 

	;WITH cte AS 
	(
		SELECT 
			a.spRunLogID AS 'ProcProcRunLogID',
			a.packageRunID AS 'ProcPacakgeRunID',
			a.spName AS 'ProcProcName',
			a.spRunStatus AS 'ProcRunStatus',
			a.startTime AS 'ProcStartTime',
			a.endTime AS 'ProcEndTime',
			'Proc' AS 'ProcLogType',
			'Proc ' + a.spName + ' (' + CONVERT(VARCHAR(100),a.spRunLogID) + ')' AS TaskName,
			DATEDIFF(SECOND, a.startTime, a.endTime) AS Duration,
			DATEDIFF(MINUTE, a.startTime, a.endTime) AS DurationMin,
			a.startTime AS 'Start',
			'Proc' AS [Resource],
			  '<br>' + 'ProcProcRunLogID' + ': ' + CONVERT(VARCHAR(100),ISNULL(a.spRunLogID,''))
			+ '<br>' + 'ProcPacakgeRunID' + ': ' + CONVERT(VARCHAR(100),ISNULL(a.packageRunID,'') )
			+ '<br>' + 'ProcProcName' + ': ' + ISNULL(a.spName,'') 
			+ '<br>' + 'ProcRunStatus' + ': ' + ISNULL(a.spRunStatus ,'')
			+ '<br>' + 'ProcStartTime' + ': ' + CONVERT(VARCHAR(100),ISNULL(a.startTime,'') )
			+ '<br>' + 'ProcEndTime' + ': ' + CONVERT(VARCHAR(100),ISNULL(a.endTime,'') )
			+ '<br>' + 'Duration' + ': ' + ISNULL(CONVERT(VARCHAR(25),DATEDIFF(SECOND, a.startTime, a.endTime)),'')
			+ '<br>' + 'DurationMin' + ': ' + ISNULL(CONVERT(VARCHAR(25),DATEDIFF(MINUTE, a.startTime, a.endTime)),'')
			AS AdditionalInfo
		FROM dbo.spRunLog a
		WHERE CONVERT(DATE,a.startTime) BETWEEN @startDate AND @endDate
		AND a.spRunStatus = 'OK'
	)
	INSERT INTO #results 
	(   
		TaskName ,
		Start ,
		Duration ,
		Resource ,
		AdditionalInfo
	)
	SELECT 
		TaskName,
		Start,
		Duration,
		[Resource],
		AdditionalInfo
	FROM cte
	END 

	SELECT 
		CONVERT(BIGINT, 1000000 + ROW_NUMBER() OVER(ORDER BY Start ASC)) AS TaskName ,
		Start ,
		Duration ,
		Resource ,
		AdditionalInfo
	FROM #results
	WHERE Duration >= @durationMin

END

Procedura jest stosunkowo prosta i w dużym skrócie łączy dane z powyższych zapytań, natomiast wszelkie dodatkowe informacje, które są unikalne dla poszczególnych obiektów są konsolidowane do jednego atrybutu. Na potrzeby skryptu do generowania wykresu za pomocą ROW_NUMBER generowany jest unikalny identyfikator wiersza. Do procedury zostało dodane kilka parametrów:

  • @startDate – data początkowa logów, które powinny znaleźć się na wykresie
  • @endDate – data końcowa logów, które powinny znaleźć się na wykresie
  • @getSSISPackages – zwróć/nie zwracaj logów dla SSIS
  • @getStoredProcedures – zwróć/nie zwracaj logów dla procedur składowanych
  • @getAgentJobs – zwróć/nie zwracaj logów dla SQL Server Agent
  • @durationMin – dolny limit czasu trwania poszczególnego obiektu w sekundach. Ustawienie tego parametru na 60 spowoduje, że procedura zwróci tylko i wyłącznie te logi dla których czas wykonania był dłuższy niż 1 minuta (60 sekund)

Wykres

Powyższa procedura posłuży jako źródło danych dla wykresu. Wykres będzie generowany za pomocą skryptu R i zostanie w nim wykorzystane biblioteka plotly:

#install.packages("plotly")
#install.packages("RODBC")

## params:
pDebug = 0
pStartDate = '20170817'
pEndDate = '20170817'
pGetSSISPackages = '1'
pGetStoredProcedures = '1'
pGetAgentJobs = '1'
pGurationMin = '100'

library(plotly)
library(RODBC)

myConnectionString <- 'driver=SQL Server;server=localhost\\sql2016;database=Test;trusted_connection=true'
mySqlQuery <- paste("EXEC GanttReport @startDate = \'"
            ,  pStartDate 
            , '\', @endDate = \''
            , pEndDate
            , '\', @getSSISPackages = '
            , pGetSSISPackages
            , ', @getStoredProcedures = '
            , pGetStoredProcedures
            , ', @getAgentJobs = '
            , pGetAgentJobs
            , ', @durationMin = '
            , pGurationMin
         )

dbhandle <- odbcDriverConnect(myConnectionString)
res <- sqlQuery(dbhandle, mySqlQuery)
close(dbhandle)

res

## Read and order data
df <- res[order(-res$TaskName),]

# Convert to dates
#df$Start <- as.Date(df$Start, format = "yyyy-mm-dd HH:MM:SS.ssssss")
df$Start <- as.POSIXct(df$Start)

# Sample client name
client = "..."

{
  if (length(unique(df$Resource)) > 2)
  {
    cols <- RColorBrewer::brewer.pal(length(unique(df$Resource)), name = "Set3")
  }
  else if (length(unique(df$Resource)) == 2)
  {
    cols <- c("#FFFFB3", "#BEBADA")
  }
  else if (length(unique(df$Resource)) == 1)
  {
    cols <- c("#8DD3C7")
  }
  else 
  {
    
  }
}

# Choose colors based on number of resources
df$color <- factor(df$Resource, labels = cols)

# Initialize empty plot
p <- plot_ly()

# Each task is a separate trace
# Each trace is essentially a thick line plot
# x-axis ticks are dates and handled automatically

for(i in 1:(nrow(df) - 1)){
  p <- add_trace(p,
                 x = c(df$Start[i], df$Start[i] + df$Duration[i]),  # x0, x1
                 y = c(i, i),  # y0, y1
                 mode = "lines",
                 line = list(color = df$color[i], width = 10),
                 showlegend = F,
                 hoverinfo = "text",
                 
                 # Create custom hover text
                 
                 text = paste("Task: ", df$Task[i], "<br>",
                              "Start: ", df$Start[i], "<br>",
                              "End: ", df$Start[i] + df$Duration[i], "<br>",
                              "Duration: ", df$Duration[i], "seconds<br>",
                              "Resource: ", df$Resource[i], "<br>", 
                              df$AdditionalInfo[i]
                 ),
                 type="scatter"
                 
  )
  
  if (pDebug == 1)
  {
    print(i)
    print(df$Start[i])
    print(df$Start[i] + df$Duration[i])
    Sys.sleep(0.01)
  }
  
}


# Add information to plot and make the chart more presentable
m <- list(l=100, r=50, b=((nrow(df) * 20)-650), t=30, pad=1)
p <- layout(p,
            # Axis options:
            # 1. Remove gridlines
            # 2. Customize y-axis tick labels and show task names instead of numbers
            
            xaxis = list(showgrid = F, tickfont = list(color = "#e6e6e6")),
            
            yaxis = list(showgrid = F, tickfont = list(color = "#e6e6e6"),
                         tickmode = "array", tickvals = 1:nrow(df), ticktext = unique(df$Task),
                         domain = c(0, 0.99),
                         x="0"
                         
            ),
            
            # Annotations
            margin=m,
            autosize=F,
            annotations = list(
              # Add total duration and total resources used
              # x and y coordinates are based on a domain of [0,1] and not
              # actual x-axis and y-axis values
              
              list(xref = "paper", yref = "paper",
                   x = 0.90, y = 0.90,
                   text = paste0("Total Duration: ", sum(df$Duration), " seconds<br>",
                                 "Total Resources: ", length(unique(df$Resource)), "<br>"),
                   font = list(color = "#ffff66", size = 12),
                   ax = 0, ay = 0,
                   align = "left"),
              
              # Add client name and title on top
              
              list(xref = "paper", yref = "paper",
                   x = 0.1, y = 1, xanchor = "left",
                   text = paste0("Gantt Report (", nrow(res), " logs)"),
                   font = list(color = "#f2f2f2", size = 20, family = "Times New Roman"),
                   ax = 0, ay = 0,
                   align = "left")
            ),
            height=(nrow(df) * 20),
            width=2000,
            plot_bgcolor = "#333333",  # Chart area color
            paper_bgcolor = "#333333")  # Axis area color


p

Idea samego skryptu została zaczęrpnięta z http://moderndata.plot.ly/gantt-charts-in-r-using-plotly/ Kod został zmodyfikowany w następujących miejscach:

  • pobieranie dane z bazy danych
  • prezentowanie dane z dokładnością do 1 sekundy
  • obsługa różną ilości źródeł logów (resources)
  • dynamicznie ustalanie rozmiaru wykresu
  • zmiana wyglądu wykresu (marginesy, rozmiar obiektów)
  • debugowowanie

Przykładowy wykres wygenerowany przez powyższy skrypt może wyglądać następująco:

Jak widać na załączonym zrzucie, wykres przedstawia oś czasu oraz poszczególne obiekty, które były wykonywane na serwerze. Dzięki wykorzystaniu biblioteki plotly wykres jest dynamiczny oraz można dowolnie zaznaczać dany fragment (dany moment), aby lepiej przyjrzeć się obiektom, które wówczas były wykonywane, a po najechaniu na poszczególny obiekt jesteśmy w stanie zobaczyć jego szczegóły.

Wykres można eksportować do pliku HTML, a pełny przykład dostępny jest tutaj: ganttMSSQLServer
Wszystkie kody źródłowe oraz przykładowy wykres dostępny jest na GitHub: https://github.com/seequality/seequality_gantt_for_sqlserver

Slawomir Drzymala
Follow me on
Latest posts by Slawomir Drzymala (see all)

Leave a Reply