Continuing the series, Query #7 is SQL Server Services Info. It retrieves this information from the sys.dm_server_services DMV. Query 7 is shown in Figure 1.
Figure 1: Query #7 SQL Server Services Info
This query tells you the last time that the SQL Server Service started, which is very important information when it comes to interpreting the results of many of the other queries in this set. It also tells you the Service account, and the startup type for the Database Engine and SQL Server Agent, along with their startup types and process ID. It also tells you whether you have a SQL Server failover cluster instance (FCI).
Query #8 is SQL Server Agent Jobs. This query returns information about SQL Server Agent jobs from the sysjobs and syscategories tables in the msdb system database. This query gives you a good high-level view of all of the SQL Server Agent jobs on your instance. Query #8 is shown in Figure 2.
Figure 2: Query #8 SQL Server Agent Jobs
I like to focus on the JobOwner column, which should be sa rather than an individual login, whether the job is enabled or not, and whether there is a notify_email_operator specified.
I also like to see whether people are using jobs from the evil SQL Server Maintenance Plan Wizard that is built into SSMS. The SSMS Maintenance Plan Wizard (prior to SQL Server 2016) makes it far too easy for people to do dumb things, because they don’t know any better. For example, you can have SQL Server rebuild all the indexes in a database (whether they need it or not) and then update statistics (which is not necessary when you rebuild an index in SQL Server).
I think most instances will be in much better shape if they use Ola Hallengren’s SQL Server Maintenance Solution for their backups and routine SQL Server maintenance.