How to get alerted anytime SQL Service is restarted?

You may and should have monitoring in place to monitor state of your servers, services, jobs, critical and not critical errors etc.

Here I just wanted to share a quick script to create a SQL Server agent job to alert you whenever SQL Server service is started/restarted.

If you have CMS setup, you could use it to deploy this across all SQL or most critical instances after tweaking it to meet your requirements

-- For SQL 2005 or up

-- Make sure it is running SQL 2005 or up

IF (@@microsoftversion / 0x1000000) & 0xff >= 9

   PRINT 'This server is running SQL Server 2005 or up.'

ELSE

BEGIN

   RAISERROR('ERROR: This server is running SQL 2000 or older version, exiting...',16, 1)

   return

END 

USE[msdb]

GO

EXECmsdb.dbo.sp_set_sqlagent_properties@alert_replace_runtime_tokens=1

GO

 SELECT NEWID()

USE[msdb]

GO

-- IF THE SERVER IS DEV OR TEST, CHANGE THE CRITICAL OPERATO FROM PAGE TO JUST EMAIL

DECLARE@CriticalOperator varchar(500)

IF (@@servername  like '%dev%' or @@servername like '%tst%')

    SET@CriticalOperator = '<youremailaddresshere>'

ELSE

    SET@CriticalOperator = '<yourpageraddresshere'

 

USE[msdb]

IF NOT EXISTS (select * from dbo.sysoperators where name = 'DBA - Critical')

       EXEC msdb.dbo.sp_add_operator @name=N'DBA - Critical',

                     @enabled=1,

                     @weekday_pager_start_time=90000,

                     @weekday_pager_end_time=180000,

                     @saturday_pager_start_time=90000,

                     @saturday_pager_end_time=180000,

                     @sunday_pager_start_time=90000,

                     @sunday_pager_end_time=180000,

                     @pager_days=0,

                     @email_address=@CriticalOperator,

                     @category_name=N'[Uncategorized]'

IF NOT EXISTS (select * from dbo.sysoperators where name = 'DBA - NonCritical')

       EXEC msdb.dbo.sp_add_operator @name=N'DBA - NonCritical',

                     @enabled=1,

                     @weekday_pager_start_time=80000,

                     @weekday_pager_end_time=180000,

                     @saturday_pager_start_time=80000,

                     @saturday_pager_end_time=180000,

                     @sunday_pager_start_time=80000,

                     @sunday_pager_end_time=180000,

                     @pager_days=62,

                     @email_address=N'<youremailaddresshere>',

                     @category_name=N'[Uncategorized]'

GO

USE[msdb]

GO

BEGIN TRANSACTION

DECLARE@ReturnCode INT

SELECT@ReturnCode = 0

IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'Database Maintenance' ANDcategory_class=1)

BEGIN

EXEC@ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'Database Maintenance'

IF (@@ERROR <> 0 OR@ReturnCode <> 0)GOTO QuitWithRollback

END

DECLARE@jobId BINARY(16)

IF  not EXISTS (SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = N'DBA - SQL Server Service Start Notification')

BEGIN

       PRINT 'CREATING JOB...'

       EXEC@ReturnCode =  msdb.dbo.sp_add_job @job_name=N'DBA - SQL Server Service Start Notification',

                     @enabled=1,

                     @notify_level_eventlog=0,

                     @notify_level_email=3,

                     @notify_level_netsend=0,

                     @notify_level_page=0,

                     @delete_level=0,

                     @description=N'No description available.',

                     @category_name=N'Database Maintenance',

                     @owner_login_name=N'sa',

                     @notify_email_operator_name=N'DBA - Critical', @job_id = @jobId OUTPUT

       IF (@@ERROR <> 0 OR@ReturnCode <> 0)GOTO QuitWithRollback

       /****** Object:  Step [dummy]    Script Date: 09/29/2010 10:54:23 ******/

       EXEC@ReturnCode = msdb.dbo.sp_add_jobstep  @job_name=N'DBA - SQL Server Service Start Notification', @step_name=N'dummy',

                     @step_id=1,

                     @cmdexec_success_code=0,

                     @on_success_action=1,

                     @on_success_step_id=0,

                     @on_fail_action=2,

                     @on_fail_step_id=0,

                     @retry_attempts=0,

                     @retry_interval=0,

                     @os_run_priority=0, @subsystem=N'TSQL',

                     @command=N'select @@version',

                     @database_name=N'master',

                     @flags=0

       IF (@@ERROR <> 0 OR@ReturnCode <> 0)GOTO QuitWithRollback

       EXEC@ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1

       IF (@@ERROR <> 0 OR@ReturnCode <> 0)GOTO QuitWithRollback

       EXEC@ReturnCode = msdb.dbo.sp_add_jobschedule  @job_name=N'DBA - SQL Server Service Start Notification', @name=N'When it starts',

                     @enabled=1,

                     @freq_type=64,

                     @freq_interval=0,

                     @freq_subday_type=0,

                     @freq_subday_interval=0,

                     @freq_relative_interval=0,

                     @freq_recurrence_factor=0,

                     @active_start_date=20100929,

                     @active_end_date=99991231,

                     @active_start_time=0,

                     @active_end_time=235959

       IF (@@ERROR <> 0 OR@ReturnCode <> 0)GOTO QuitWithRollback

       EXEC@ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'

       IF (@@ERROR <> 0 OR@ReturnCode <> 0)GOTO QuitWithRollback

END

ELSE

       PRINT 'JOB ALREADY EXISTS ON THIS SERVER'

COMMIT TRANSACTION

GOTOEndSave

QuitWithRollback:

       IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION

EndSave:

GO

How to find out whether your database is transactional, analytical or hybrid?

If you are a DBA, especially Infrastructure DBA, Production DBA or Operations DBA then you don't necessarily always know what type of application load is being handled by your SQL Servers.  In some cases you don't even need to know to do your job.

But if you are a performance engineer/DBA or one of your job description includes performance tuning and optimization then it is a very crucial piece of information.

Here is one of the queries I have used. 

SELECT  DB_NAME(vfs.database_id) dbname ,

              mf.name file_name,

              mf.type_desc file_type,

        CASE WHEN num_of_writes =0 THEN NULL 

             ELSE num_of_reads /num_of_writes END read_vs_writes,

        CASE WHEN num_of_bytes_written =0 THEN 

             NULL ELSE num_of_bytes_read /num_of_bytes_written END read_vs_writes_bytes ,

        (io_stall_read_ms + io_stall_write_ms ) /

             (num_of_reads + num_of_writes) avg_io_stall_ms,

        *

FROM    sys.dm_io_virtual_file_stats(NULL, NULL) vfs

        INNER JOIN sys.master_files mf ON mf.database_id = vfs.database_id

                                          AND mf.file_id = vfs.file_id

WHERE   mf.type_desc != 'LOG'

        AND DB_NAME(vfs.database_id) NOT IN ( 'master', 'msdb', 'tempdb', 'model' )

ORDER BY read_vs_writes DESC;

Please note that the sys.dm_io_virtual_file_stats is a DMV i.e. these data are reset every time the sql server instance is restarted and that is also the only way to reset the data from this DMV. However, you could take a snapshot of it over multiple intervals for comparison.


And here is a query that will show you read vs writes at the table level. You would want to be careful adding indexes to a table that has more writes than reads.

;

WITH    work_load_cte

          AS ( SELECT   TableName = OBJECT_NAME(s.object_id) ,

                        Reads = SUM(user_seeks +user_scans + user_lookups) ,

                        Writes = SUM(user_updates)

               FROM     sys.dm_db_index_usage_statsAS s

                        INNER JOIN sys.indexesAS i ON s.object_id = i.object_id

                                                       AND i.index_id = s.index_id

               WHERE    OBJECTPROPERTY(s.object_id, 'IsUserTable') = 1

               GROUPBY OBJECT_NAME(s.object_id) WITH ROLLUP

             )

    SELECT  DB_NAME() DB ,

            work_load_cte.TableName ,

            work_load_cte.Reads ,

            work_load_cte.Writes ,

            CASEWHEN work_load_cte.Writes = 0 THEN NULL

                 ELSEwork_load_cte.Reads /work_load_cte.Writes

            ENDAS read_vs_writes

    FROM    work_load_cte

    ORDER BY read_vs_writes DESC;

Gather Always On Configuration using a DMV query

If you were like me, you would know your AG configuration by heart. However, what if you get busy with increasing responsibilities in other areas, projects etc.? Therefore, you do not just want to rely on your memory. On the other hand, maybe you want to simply collect snapshot of your configuration every now and then or have it documented before a major upgrades, for your junior DBAs, new hire etc...

Alternatively, maybe you just started working for a new employer or a new client. 

Whatever is your reason, here is a DMV query to gather the information.

---

SELECT  ag.name ag_name ,

        ar.replica_server_name replica_server ,

        CASE WHEN hdrs.is_primary_replica = 0

                  ANDhdrs.replica_id =ar.replica_id THEN0

             ELSE1

        ENDis_primary ,

        adc.database_name ,

        agl.dns_name ,

        agl.port ,

        *

FROM    sys.availability_groups ag

        INNER JOIN sys.availability_replicas ar ONag.group_id =ar.group_id

        INNER JOIN sys.availability_databases_cluster adc ON adc.group_id = ag.group_id

        INNER JOIN sys.availability_group_listeners agl ON agl.group_id = ag.group_id

        LEFT  JOIN sys.dm_hadr_database_replica_states hdrs ON hdrs.group_id = adc.group_id

                                                              AND hdrs.group_database_id = adc.group_database_id

                                                              AND hdrs.replica_id = ar.replica_id

ORDER BY ag.name , adc.database_name , is_primary DESC;

Check health and status of Availability Group using DMVs

-- Health and status of WSFC cluster. These two queries work only if the WSFC has quorum

SELECT * FROM sys.dm_hadr_cluster

SELECT * FROM sys.dm_hadr_cluster_members

-- Health of the AGs

SELECT ag.name agname, ags.* FROM sys.dm_hadr_availability_group_states ags INNER JOIN sys.availability_groups ag ON ag.group_id = ags.group_id

-- Health and status of AG replics from the WsFC perspective

SELECT ar.replica_server_name,harc.* FROM sys.dm_hadr_availability_replica_cluster_states harc INNER JOINsys.availability_replicas ar ON ar.replica_id = harc.replica_id

-- Health and status of AG replicas, run this on the primary replica. 

-- On secondary this will only show info for that instance

SELECT * FROM sys.dm_hadr_availability_replica_states 

-- Health and status of AG databases from the WSFC perspective

SELECT * FROM sys.dm_hadr_database_replica_cluster_states 

-- Health and status of AG databases, run this on the primary replica. 

-- On secondary this will only show info for that instance

SELECT  ag.name ag_name ,

        ar.replica_server_name ,

        adc.database_name ,

        hdrs.database_state_desc ,

        hdrs.synchronization_state_desc ,

        hdrs.synchronization_health_desc ,

        agl.dns_name ,

        agl.port

-- ,*

FROM    sys.dm_hadr_database_replica_states hdrs

        LEFT JOIN sys.availability_groups ag ONhdrs.group_id =ag.group_id

        LEFT  JOIN sys.availability_replicas ar ON ag.group_id = ar.group_id

                                                   AND ar.replica_id = hdrs.replica_id

        LEFT  JOIN sys.availability_databases_cluster adc ON adc.group_id = ag.group_id

                                                             AND adc.group_database_id = hdrs.group_database_id

        LEFT  JOIN sys.availability_group_listeners agl ON agl.group_id = ag.group_id

ORDER BY ag.name , adc.database_name

-- Health and status of AG listeners

SELECT agl.dns_name, agl.port, aglia.* FROM sys.availability_group_listener_ip_addresses aglia INNER JOINsys.availability_group_listeners agl ON agl.listener_id = aglia.listener_id