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 send you an email alert whenever SQL Server instance is started/restarted. Make sure to update the following lines in the script with your email address:

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

    SET @CriticalOperator = '<youremailaddresshere>'

ELSE

    SET @CriticalOperator = '<yourpageraddresshere'

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
EXEC msdb.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' AND category_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
GOTO EndSave
QuitWithRollback:
       IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO

This obviously would not alert you when the SQL Server goes down, which is what you would really want to monitor. However, I still have found this useful when SQL Server gets restarted or fails over because of some technical glitch in the network, OS, etc., or when someone, intentionally or not, restarts the SQL Server at unscheduled and inconvenient times. Since the SQL Server comes right back up, such events may not get caught by monitoring tools. These events can surely have negative consequences as they lead to connection resets, transaction rollbacks, or worse.

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_stats AS s

                        INNER JOIN sys.indexes AS i ON s.object_id = i.object_id

                                                       AND i.index_id = s.index_id

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

               GROUP BY OBJECT_NAME(s.object_id) WITH ROLLUP

             )

    SELECT  DB_NAME() DB ,

            work_load_cte.TableName ,

            work_load_cte.Reads ,

            work_load_cte.Writes ,

            CASE WHEN work_load_cte.Writes = 0 THEN NULL

                 ELSE work_load_cte.Reads / work_load_cte.Writes

            END AS 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

                  AND hdrs.replica_id = ar.replica_id THEN 0

             ELSE 1

        END is_primary ,

        adc.database_name ,

        agl.dns_name ,

        agl.port ,

        *

FROM    sys.availability_groups ag

        INNER JOIN sys.availability_replicas ar ON ag.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 JOIN sys.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 ON hdrs.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 JOIN sys.availability_group_listeners agl ON agl.listener_id = aglia.listener_id

Get SQL server database size, location and volume info using DMVs

SELECT * FROM sys.master_files AS f 
CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.file_id) v; 

Easy to use WMI commands to query windows system

WMI has been around for a very long while and it has grown significantly over the years. As of now though the WMI has been deprecated by Microsoft and the WMI and it's WMIC command line utility is superseded by Windows PowerShell for WMI!

But if you happened to have any legacy, old, outdated servers lying around that you cannot upgraded just yet or maybe ever, WMI is maybe the only option you may have available to remotely manage servers from the command line.

And the WMIC is a command line interface to the WMI (Windows Management Instrumentation).

To get started,  launch the command shell (CMD.EXE).

You can run WMIC utility interactively, from the WMIC prompt,  or non-interactively. Once you get little comfortable with it, I think you would use the non interactive method more often.

But for learning purpose, lets start WMIC in interactive mode by typing WMIC on the command prompt and hit enter. it should now display the WMIC command promot,  wmic:root\cli&gt;


Here on I am just listing some informational commands that you can type in and view the results show up on your display:

Type /? and hit enter.  This would display all available WMIC commands reference.

To get information on the CPU on local computer

WMIC CPU

OR

In interactive WMIC session:

To run a WMI command on a remote computer

WMIC /NODE:<SERVERNAME> CPU

OR

In interactive session:

To get information on the OS

wmic os

To display only SELECT fields from the output:

wmic diskdrive get name, caption, mediatype, status, size, systemname, totalheads, totaltracks

And here are some examples to get information on the logical disks:

wmic logicaldisk
wmic logicaldisk get caption, deviceid, drivetype, filesystem, status, volumename
wmic logicaldisk where deviceid="c:" get caption, deviceid, drivetype, filesystem, status, volumename

Notice the use of where clause with one the output fields to filter the results.

Get information on a service:

wmic service where name="mssqlserver" get name, caption, startmode, state, status

Wait a minute, what happened? I know I have SQL Server installed on this machine so what the hack?

The reason is simple, all my sql instances are named instances i.e. there is no default instance of SQL on this machine, which would have service name mssqlserver. So, I am going to modify my where condition like this to get all services with the "sql" anywhere in it's path name:

wmic service where "PathName like '%sql%'" get name, caption, startmode, state, status

That's better!

Here are few others that I have found useful at times.

wmic pagefile
wmic pagefileset
wmic memcache
wmic memphysical

And finally, here are some links for additional information:

Using Windows Management Instrumentation Command-line:

https://learn.microsoft.com/en-us/previous-versions/windows/it-pro/windows-server-2003/cc779482(v=ws.10)

WMIC - Take Command-line Control over WMI:

https://learn.microsoft.com/en-us/previous-versions/windows/it-pro/windows-2000-server/bb742610(v=technet.10)

Windows PowerShell for WMI:

https://learn.microsoft.com/en-us/powershell/scripting/learn/ps101/07-working-with-wmi?view=powershell-7.3

SQL query to shrink all log files on sql instance

USE master

exec sp_MSforeachdb '

use [?]
 print ''?''
 print cast(databasepropertyex(''?'', ''Updateability'') as varchar(200))

 if databasepropertyex(''?'', ''Updateability'') = ''READ_WRITE ''

   dbcc shrinkfile(2,1)

'

T-SQL Developer Interview Questions with Answers

What SQL command do you use to pull and filter data from a table ?  

SELECT [Column List] FROM [Table Name] WHERE [Condition = TRUE]

OR

SELECT * FROM [Table Name] WHERE [Condition = TRUE]

How do you update data (what command do  you use)

Syntax to update data without join:

UPDATE TableA SET Field1 = X

WHERE Condition = True

Syntax to update data using a join:

UPDATE TableA SET TableA.Field2 = TableB.Field2

FROM TableA 

INNER JOIN TableB ON TableB.Field1 = TableA.Field1

or (alias solution)

UPDATE a SET a.Field2 = b.Field2

FROM TableA AS a

INNER JOIN TableB AS b ON b.Field1=a.Field1

What is the Syntax to insert data in a table

INSERT [INTO] TableA [()] SELECT Field1 [, FieldN] FROM TableB WHERE Condition = True

INSERT [INTO] TableA [()] SELECT Constant

INSERT [INTO] TableA [()] VALUES (…)

INSERT [INTO] TableA [()] EXEC sp

SELECT Field1, Field2, … INTO [TableName] FROM Table WHERE Condition = True

What is the identity property?

Auto incremental numeric value, gets populated automatically by SQL Server

When inserting a record into a table having an identity column, how do you capture the newly-generated number?  

You can use @@IDENTITY, IDENT_CURRENT(‘Table Name’) or  SCOPE_IDENTITY() functions

How would you capture the last identity value created by your database session (assuming other connections are actively inserting records into the same table)?    

SCOPE_IDENTITY() function

How can one insert data into a field declared as having the identity property?

Identity fields are populated automatically and you are not allowed to specify what that value should be. However there is a way where you could force a specific value manually . 

Turn the identity insert on first

SET IDENTITY_INSERT TableA ON

Perform the insert

Turn off the identity insert

SET IDENTITY_INSERT TableA OFF

Syntax to remove record(s) from a table 

DELETE FROM TableA WHERE Condition = True

DELETE FROM a FROM TableA AS a

INNER JOIN TableB AS b ON b.Field1 = a.Field1

There are other ways and variations to the above syntax

You can also use TRUNCATE command that wipes out everything from the table.

What is the difference between the DELETE statement (to empty a table) and the TRUNCATE TABLE statement?

DELETE statement is a logged operation whereas the TRUNCATE TABLE statement is a minimally logged operation (data page deallocations are recorded in the log).

TRUNCATE TABLE statement performs faster than the DELETE statement.

TRUNCATE TABLE cannot be used on tables that are referenced by a foreign key constraint.

TRUNCATE TABLE will not activate a trigger because it is not logged; however, the DELETE statement may fire a trigger.

TRUNCATE TABLE will reset a table’s identity value back to its seed (usually 1).

DELETE statement is DML whereas TRUNCATE TABLE is DDL.

Different Joins and Their Purpose

Inner –Match rows from 2 tables based on the same values in the matching fields

Left Outer – All rows from left table and those records that match in right table.  If not match, then null.

Right Outer – All rows from right table and those records that match in left table.  If no match, then null.

Full Outer – All rows from left and right tables.  Any non matching rows will contain a NULL in either the left or right tables.

Cross – Cartesian product.  Left table record is returned with all records from right table.

Natural – Not implemented in SQL Server but it is in the ANSI standard. It basically amounts to a syntax short cut when you have your foreign and primary constraints set up.

Theta – A through D represent Equi-Joins which are a subset of Theta Joins that use the equality comparison. However you can join with othe non-equality 

Boolean operators. …. FROM TABLEA A JOIN TABLEB B ON A.ID > B.ID. 

WHERE clause purpose?  

To filter only the records of interest.

HAVING clause purpose?  

To filter the aggregation of interest.

What keyword(s) allow 2 datasets to be returned as a single dataset?  For example, query 1 pulls data from a table while another query pulls data from another table.  

UNION and UNION ALL clauses

And what is the difference between UNION and UNION ALL?  

UNION automatically performs a DISTINCT operation – ensuring unique records in a dataset.  UNION ALL returns a dataset AS-IS.

What is aliasing?  

Substituting a short-hand name for a table after defining it.

Why alias tables?  

To make code less verbose, yet more readable.  You may have to join to the same table multiple times.

What is a correlated sub-query?  

correlated sub-queries references same table in its sub query

How would you determine records that are missing when comparing 2 tables:

NOT IN subquery solution

SELECT a.*

FROM TableA

WHERE PKID NOT IN ( SELECT PKID FROM TableB )

LEFT OUTER JOIN solution

SELECT a.*

FROM TableA a

LEFT OUTER JOIN TableB b ON b.PKID=a.PKID

WHERE b.PKID IS NULL

NOT EXISTS correlated subquery solution

SELECT a.*

FROM TableA a

WHERE NOT EXISTS ( SELECT NULL FROM TableB WHERE PKID = a.PKID )

What is the difference between CROSS APPLY and OUTER APPLY in SQL Server?  

CROSS APPLY returns only rows from the outer table that produce a result set from the table-valued function. OUTER APPLY returns both rows that produce a result set, and rows that do not, with NULL values in the columns produced by the table-valued function.

What looping constructs are available in T-SQL?

WHILE Loop

Cursors Pros

Allows row-by-row processing

Cursors Cons

Poor performance

Best Practice – Avoid them if at all possible.  95% of the time, a set-oriented approach can be implemented with much better results.

What is a derived table?  

Select statement defined as its own separate table.   

what is syntax to define a derived table?

SELECT *

FROM (

SELECT Field1, Field2

FROM TableA

WHERE [Condition=TRUE] ) AS a

What is a benefit of using derived tables as compared to declaring and populating a temp table/ table variable?  

Performance because it uses existing indexes on referenced tables as compared to a temp table that needs to have indexes created on it before using it.

What is a CTE?  

It’s a more readable form of a derived table that can be referenced multiple times within the same query.  Syntax is:

CTEs can have recursive qualities.  

<CTE Name> [(Column1, Column2, ColumnN)] AS

(

<CTE Query Anchor Member SELECT statement>

UNION ALL

 <CTE Query Recursive Member SELECT statement>

)

SELECT *

FROM <CTE Name>

What is the difference between a temp table and a table variable?  

Table variables don’t support check constraints, default values, and computed columns cannot reference user-defined functions.  They don’t allow named constraints, table truncation, the overriding of the identity property.  They cannot be created on-the-fly using SELECT … INTO …

A common misconception is that Temp tables are physically stored in TempDB whereas table variables solely reside in memory.  This is not the case.  Both can be stored in memory, unless memory is insufficient.  In that case both can be flushed to disk in TempDB.

Table variables are good for storing small datasets (about 100 records or so) – not large unless it is indexed via primary key or unique constraint that is proven to be used in subsequent queries.  

A table variable limits recompiling events and avoids the statistics manager; whereas temp tables have statistics performed to help the optimizer determine the most efficient way of referencing the data. 

At what point (if any) do you determine to use a temp table over a table variable?  

It depends.  Performance tests ultimately will prove or disprove the best solution of a temp table vs. table variable.

What is dynamic SQL?  

A string that is assembled and executed at run-time.

How is dynamic SQL invoked?

EXECUTE  (T-SQL Command String)

EXECUTE sp_ExecuteSQL  @Stmt, @Params (declare), @Param1 (value)…

What is the difference between the above commands?  EXECUTE runs a statement in Ad-Hoc fashion, requiring compiler to operate.  

Sp_ExecuteSQL attempts to reuse query plans and therefore may skip compilation (provided a query plan exists).

When using sp_ExecuteSQL, do referenced tables need to have their database name fully qualified?  

YES.  

What happens if the database name is not fully qualified?  

Then the query is treated like an ad-hoc query – requiring the use of the compiler.

What conditions are appropriate to use a dynamic SQL solution?  

A query statement that must change to support a specific request; otherwise, multiple similar queries would need to be created statically.  For example, a requirement may state that a query handle up to 3 filtering conditions in a WHERE clause.  Each condition may be supplied by itself or in conjunction with others.

What functions types available?

User Defined Inline (returns a table of data), syntactically referenced as a table and achieves the functionality of a parameterized view

User Defined Multistatement table-valued functions

User Defined Scalar (returns a single data value)

Why is it preferable to NOT use the function?  

Because the function is called for EACH record, the performance will be slower than if one were to write a CASE statement or a concatenated string (with the + sign).

what is a database trigger?

Essentially a special stored procedure installed on a table and is invoked by INSERT, UPDATE and/or DELETE actions taken on rows within the table.

What 2 special tables are present during the execution of a trigger?  

Inserted & Deleted.