SQL Server object overview (tables, index, etc) tutorial

[Anonymous]

Since I could not find any downloadable PowerBI reports that give insight into objects (like tables, views, indexes etc) in our SQL Server database I made one myself. At the moment it only contains tables, but you can add any objects yourself. I thought I'd share it with you. I cannot find an option to attach the reports so you have to make it yourself. But I'll explain the process and give the code for some views that are needed for the data. You need the rights to deploy and read those views otherwise it won't work. I'm from Holland so you'll see some Dutch language. 

This is the report we use. Notice it contains different instances and different databases:

For the data you have to make a seperate view in *all* databases and *all* instances you want to show. We use linked servers to other instances, and through those linked servers we connect to these views. In every database we have a schema "beheer" (like "management" in Dutch) where we store these views. So for every database in every instance you have to make this view:

CREATE VIEW [Beheer].[objectoverzicht] AS
SELECT @@SERVICENAME as instance,
       db_name() as [database],
       s.Name AS [schema],
       t.NAME AS tabel,
       p.rows AS rijen,
       (SUM (a.total_pages) * 😎 / 1024 AS totaal_mb,
       (SUM (a.used_pages) * 😎 / 1024 AS gebruikt_mb
  FROM sys.tables t
       INNER JOIN sys.schemas s
          ON s.schema_id = t.schema_id
       INNER JOIN sys.indexes i
          ON t.OBJECT_ID = i.object_id
       INNER JOIN sys.partitions p
          ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
       INNER JOIN sys.allocation_units a
          ON p.partition_id = a.container_id
 WHERE t.NAME NOT LIKE 'dt%' AND t.is_ms_shipped = 0 AND i.OBJECT_ID > 255
GROUP BY t.Name, s.Name, p.Rows

Then you make a "master view" that combines the other ones (note that on the other instances I also made master views) (samengevoegd means something like "combined" in Dutch):

CREATE VIEW [Beheer].[objectoverzicht_samengevoegd]
AS
SELECT *
	FROM Datamart.beheer.objectoverzicht
UNION ALL
SELECT *
	FROM Datastore.beheer.objectoverzicht
UNION ALL
SELECT *
	FROM DWH.beheer.objectoverzicht
UNION ALL
SELECT *
	FROM more.views
UNION
SELECT *
	FROM [linked server 1].metadata.beheer.objectoverzicht_samengevoegd
UNION
SELECT *
	FROM [linked server 2].metadata.beheer.objectoverzicht_samengevoegd

Finally, import the data from the master view into PowerBI and if you are a basic user you can make the reports in no time, there are no special formulas you have to make (well maybe some simple DAX) and the report can be made with the default visualisations. I do recommend using the Chicklet slicer for the buttons.

[v-qiuyu-msft]

Hi @Anonymous,

Thanks for your sharing.

Best Regards,
Qiuyun Yu

[Anonymous]

Since I could not find any downloadable PowerBI reports that give insight in scheduled job/jobstep/package run times on our SQL Server I made some myself. I thought I'd share it with you. I cannot find an option to attach the reports so you have to make those yourself. But I'll explain the process and give the code for some views that are needed for the data. You need the rights to deploy and read those views otherwise it won't work. Here are some screenshots of the reports we use. I'm from Holland so you'll see some Dutch language. Doorlooptijd / dlt = run time in Dutch.

Top level info about scheduled jobs/jobsteps/packages, only recent data (last 3 days): 

A bit more detail about total run times per day and the objects with the top run times:

First view, job and jobstep information:

CREATE VIEW [Beheer].[doorlooptijden_jobs_jobsteps]
AS
	SELECT h.server                                                                                                                                      
	,      CASE h.step_id WHEN 0 THEN 'Job'
	                             ELSE 'Jobstep' END                                                                                                          type
	,      j.name                                                                                                                                         AS job
	,      step_name                                                                                                                                      AS stap
	,      cast(cast(h.run_date AS varchar) + ' ' + format (h.run_time, '00:00:00') AS datetime)                                                          AS start
	,      CASE WHEN h.run_duration < 0 THEN '00:00:00'
	                                    ELSE format (h.run_duration, '00:00:00') END                                                                      AS doorlooptijd
	,      CASE WHEN CAST(h.run_duration AS int) > 235959 THEN 0 -- doorlooptijden groter dan 23:59:59 passen niet in een datetime
	                                                      ELSE DATEDIFF(second,0,cast(cast(format (h.run_duration, '00:00:00') AS time) AS datetime)) END AS doorlooptijd_sec
	,      CASE h.run_status WHEN 0 THEN 'Failed'
	                         WHEN 1 THEN 'Succeeded'
	                         WHEN 2 THEN 'Canceled'
	                         WHEN 3 THEN 'Retry'
	                                ELSE 'Onbekend' END                                                                                                      status
		FROM         msdb.dbo.sysjobs       j
				JOIN msdb.dbo.sysjobhistory h ON j.job_id = h.job_id

Second view, package information:

SELECT -- Overzicht van uitgevoerde packages volgens de catalog
  op.server_name                                                                                           AS server
, project_name                                                                                             AS project
, package_name                                                                                             AS package
, cast (MIN (exestats.start_time) AS DATETIME)                                                             AS start
, CONVERT(varchar, DATEADD(ss, datediff (ss, MIN (exestats.start_time), MAX (exestats.end_time)), 0), 108) AS doorlooptijd
, datediff (ss, MIN (exestats.start_time), MAX (exestats.end_time))                                        AS doorlooptijd_sec
, CASE op.status WHEN 1 THEN 'Created'
                 WHEN 2 THEN 'Running'
                 WHEN 3 THEN 'Canceled'
                 WHEN 4 THEN 'Failed'
                 WHEN 5 THEN 'Pending'
                 WHEN 6 THEN 'Ended unexpectedly'
                 WHEN 7 THEN 'Succeeded'
                 WHEN 8 THEN 'Stopping'
                 WHEN 9 THEN 'Completed'
                        ELSE 'Onbekend' END                                                                   status
	FROM         ssisdb.[internal].[executions]            exe     
			JOIN ssisdb.[internal].[operations]            op       ON exe.execution_id = op.operation_id
			JOIN ssisdb.[internal].[executable_statistics] exestats ON exe.execution_id = exestats.execution_id
GROUP BY exe.execution_id
,        exe.project_name
,        exe.package_name
,        op.server_name
,        op.status

Third view, combine the other views into one:

CREATE VIEW [Beheer].[doorlooptijden_pbi]
AS
	SELECT [type]                             
	, job + '  ' + stap COLLATE Latin1_General_CI_AS AS object
	,      [start]                            
	,      [doorlooptijd]                     
	,      [doorlooptijd_sec]                 
	,      [status]                           
	,      format(start,'dd')          AS dag
	,      format(start,'yyyy MM')   AS maand
		FROM [MetaData].[Beheer].[doorlooptijden_jobs_jobsteps]
		WHERE start >= cast(format(getdate()-180,'yyyyMM01') AS date)
	UNION ALL
	SELECT 'Package'                         AS type
	,project + ' ' + package as object
	,      [start]                          
	,      [doorlooptijd]                   
	,      [doorlooptijd_sec]               
	,      [status]                         
	,      format(start,'dd')        AS dag
	,      format(start,'yyyy MM') AS maand
		FROM [MetaData].[Beheer].[doorlooptijden_packages]
		WHERE start >= cast(format(getdate()-180,'yyyyMM01') AS date)

Finally, import the data from the last view into PowerBI and if you are a basic user you can make the reports in no time, there are no special formulas you have to make (well maybe simple DAX SUM(doorlooptijd_sec) or something) and the report can be made with the default visualisations. I do recommend using the Chicklet slicer for the buttons.