Easy management with customized dashboards

SQL Operations Studio is a standalone application that lets you put frequently used queries into a dashboard for a quick overview of all your databases. The software is lightweight and uses very little resources, so it can effortlessly run next to more demanding tools like SQL Server Management Studio.

Using queries on your databases to get information such as available disk size, isn’t new. Before, you could manually run scripts on each database to extract statistics and then combine them. However, if you need to check this data on a daily basis, it becomes quite cumbersome and time-consuming. After a one-time setup per query, Operations Studio runs them for you and presents the results in clear graphs on a dashboard. Want to see the status of available disk size the next day too? Just refresh. With SQL Operations Studio you can now get insight into your databases with a push of the button.

 

Common graphs to use in SQL Operations Studio

We’ve tinkered with queries and creating a dashboard in SQL Operations Studio. Below you’ll find a couple of useful graphs to include with the needed code. Keep in mind, though that not every graph works for every query.

 

Available disk size

Query:

SELECT DISTINCT SUBSTRING(volume_mount_point, 1, 1) as drive_letter,

(total_bytes/1024/1024 - available_bytes/1024/1024) as usedsize_mb,

available_bytes/1024/1024 as available_size_mb

FROM sys.master_files AS f

CROSS APPLY

sys.dm_os_volume_stats(f.database_id, f.file_id)

CROSS APPLY

sys.dm_io_virtual_file_stats(f.database_id, f.file_id)

Graph:

Fig1. Okay for a pie chart

 

 

If we convert this pie chart to a bar chart however, we get a distorted bar chart, and a very imprecise view of the data.

Fig2. Not okay for a bar chart.jpeg

 

And if you try to add even more values, like wait stats, things go terribly wrong:

Fig3. Whoops

 

How many databases are offline or online:

Fig5. Database state

Script:

select d.state_desc,

count(d.state_desc) as state

from   sys.databases d

where replica_id is NULL

group by d.state_desc

How many logins have sysadmin rights

Fig6. Sysadmins <-> NonSysAdmins  (and yes, my security policy sucks.)

Script:

SELECT (select count(*) from sys.syslogins where  sysadmin <> 1)   as amount_of_logins_not_sysadmin,
			(select count(sysadmin)  from sys.syslogins where  sysadmin = 1) as amount_of_sysadmins

How many jobs have been scheduled or need to be scheduled

Fig 7. Looks like some jobs need to be scheduled.

Script:

SELECT (select count(s.name) FROM   msdb..sysschedules s 
inner join msdb..sysjobschedules a on s.schedule_id = a.schedule_id 
inner join msdb..sysjobs j on a.job_id = j.job_id ) as amount_of_jobs_scheduled,

(select (select count(job_id) from msdb..sysjobs)  - 
(select count(s.name) FROM   msdb..sysschedules s 
inner join msdb..sysjobschedules a on s.schedule_id = a.schedule_id 
inner join msdb..sysjobs j on a.job_id = j.job_id )) as amount_of_jobs_not_scheduled

Get InstanceInformation

Fig 8. InstanceInformation

This one is a bit trickier. The script is bigger and the widget and chart are customized, since the default settings could not display the graph. We created the count chart using create insight and tweaked the JSON settings.