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
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)
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:
How many databases are offline or online:
Fig5. Database state
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.)
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.
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
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.