Setting : Enable 'SQL CLR' Setting.

To check current "SQL CLR" setting:

sp_configure 'clr enabled'
go

To enable "SQL CLR":

use master
go
sp_configure 'clr enabled', 1
go
reconfigure
go

Below screenshot shows values before "SQL CLR" is enabled & after:

Knowledge : About "SQL CLR" (Common Language Runtime)

About "SQL CLR":

• The Common Language Runtime (CLR) is the core of the Microsoft .NET Framework, providing the execution environment for all .NET code.
• CLR has been embedded with SQL Server from start of SQL Server 2005, so this is also called as "SQL CLR".
• The main goal of SQL CLR is to to provide better Reliability, Scalability, Security & Performance.
• "SQL CLR" supports Stored Procedures, Triggers, User-defined Functions, Table-valued Functions, User-defined aggregates, User-defined Types.
• When a user requests "SQL CLR" object for the first time the SQL will load .Net execution engine mscoree.dll into memory. Then the CLR makes request to "SQLOS" (SQL Operating System) for new threads and memory allocation. 
• SQLOS has all control over the SQL CLR requests / threads created. SQLOS can create new threads and also can suspend threads which idle for long time to release resources.
• Below is the high level architecture of "SQL CLR",

Properties of "T-SQL" vs "SQL CLR": 

Properties	Transact SQL	SQL CLR
Code Execution	Compiled	Interpreted
Code Model	Set Based	Procedural Based
Access to subset of the .NETFramework Base Class Libraries (BCL)	No	Yes
SQL Server Access	Direct Access	In-Process Provider
Support Complex Types	No	Yes
Parameters Support	Yes	Yes

When To Use "T-SQL" & "SQL CLR":

• For all basic CRUD (Create, Update, Read & Delete) operations it is highly recommended to use T-SQL. For all other performance intensive operations which use cursors, string/text manipulations, accessing external resources like acessing text/binary files etc; we should use SQL CLR.

Knowledge: Important SQL Server wait types and details.

Top 10 potential SQL Server waits:

Wait_Type	Description
CXPACKET	This wait type always occurs when parallelism happens, as the control thread in a parallel operation waits until all threads have completed.
ASYNC_NETWORK_IO	Occurs on network writes when the task is blocked behind the network. Verify that the client is processing data from the server.
BACKUPIO	Occurs when a backup task is waiting for data, or is waiting for a buffer in which to store data. This type is not typical, except when a task is waiting for a tape mount.
IO_COMPLETION	Occurs while waiting for I/O operations to complete. This wait type generally represents non-data page I/Os. Data page I/O completion waits appear as PAGEIOLATCH_* waits.
PAGEIOLATCH_SH	Occurs when a task is waiting on a latch for a buffer that is in an I/O request. The latch request is in Shared mode. Long waits may indicate problems with the disk subsystem.
PAGELATCH_EX	The classic cause of this wait type are from lots of concurrent threads inserting small rows into a clustered index with an identity value, leading to contention on the index leaf-level pages.
PAGEIOLATCH_EX	Occurs when a task is waiting on a latch for a buffer that is in an I/O request. The latch request is in Exclusive mode. Long waits may indicate problems with the disk subsystem.
WRITELOG	Occurs while waiting for a log flush to complete. Common operations that cause log flushes are checkpoints and transaction commits.
LCK_M_IX	This wait type occurs when a thread is waiting for a table or page IX lock so that a row insert or update can occur. It could be from lock escalation to a table X or S lock causing all other threads to wait to be able to insert/update.
SOS_SCHEDULER_YIELD	This wait type is most often associated with high signal wait rather than waits on a specific resource. If you observe this wait type persistently, investigate for other evidence that may confirm the server is under CPU pressure.

Setting : Verify and enable backup compression using T-SQL.

Backup compression was an added feature starting with SQL Server 2008 and this only comes with enterprise edition. But the later versions supports backup compression from standard editions.

Following are the steps to verify whether the backup compression is enabled or not and how to enable / disable it.

 To check current backup compression setting :

SELECT *
FROM sys.configurations
WHERE name = 'backup compression default' ;
GO

When Not Enabled :

When Enabled :

To Enable Backup Encryption:

 EXEC sp_configure 'backup compression default', 1 ;
 RECONFIGURE WITH OVERRIDE ;
 GO

To Disable Backup Encryption:

 EXEC sp_configure 'backup compression default', 0 ;
 RECONFIGURE WITH OVERRIDE ;
 GO

Monitor : How to check sysmail / dbmail events or logging information.

All events from the dbmail send / receive mail notifications are logged in a sys table (sysmail_event_log) of msdb database.  We can easily track down the exceptions on mails sending & receiving from this table.

For example if an SMTP server throws an exception of sending email it will be logged as an error in the table 'sysmail_event_log'. Details below,

Query:

    
USE msdb
GO
SELECT * FROM sysmail_event_log WHERE log_date > '2015-04-29 10:00'
GO
    

Results:

Exception Message: 

The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2015-04-29T10:36:34). Exception Message: Cannot send mails to mail server. (Insufficient system storage. The server response was: 4.3.1 Out of memory).)

Click here to redirect to msdn for more details on this.

Knowledge : Using 'MERGE' functionality to sync two different tables.

• 'MERGE' is a sql command introduced with SQL Server 2008 which makes ease of inserting & updating records between source and target tables. This is similar to a command in Oracle which is called 'UPSERT'. 
• Though it causes a little penalty in performance but its a very reliable and powerful command. Though it has slight impact in performance it is more powerful & robust command when compared to the traditional way of writing INSERTs, UPDATESs & DELETEs separately as with this command selextion happens only once.
• There are 3 separate matching clauses you can specify. The 3 matching clauses are:
• WHEN MATCHED
• WHEN [TARGET] NOT MATCHED
• WHEN SOURCE NOT MATCHED
• This command is so flexible with many options like you can specify constraints along with 'MATCHED' or 'UNMATCHED' clause as below,
• WHEN MATCHED AND order_id> 1234
• It is acceptable to have multiple instances of same matching clause without any restrictions.
• We can have $action function which outputs the type of action performed like INSERT, UPDATE or DELETE.
• This is very useful command to sync two or more tables,

Example:

CREATE TABLE temp_to_load_all_chnages(
  id INT,
  fname VARCHAR(10),
  lname VARCHAR(10`),
  dob datetime,
  action_taken VARCHAR(20),
  NEW_id INT,
  NEW_fname VARCHAR(10),
  NEW_lname VARCHAR(10`),
  NEW_dob datetime,
    );

MERGE sqlcheckpoint_target_db..customer_details AS target
    USING (SELECT 
    id,
    fname,
    lname,
    dob
FROM sqlcheckpoint_source_db..customer_details) AS source 
    ON (target.id = source.id)
    WHEN MATCHED THEN 
        
        UPDATE SET
  id = source.id
  ,fname=source.fname
  ,lname = source.lname
  ,dob = source.dob
        
 WHEN NOT MATCHED BY TARGET THEN 
     INSERT (
     id,
     fname,
     lname,
     dob
    )
     VALUES (
           source.id,
     source.fname,
     source.lname,
     source.dob
     )
   WHEN NOT MATCHED BY SOURCE THEN
   DELETE 
    OUTPUT deleted.*, $action, inserted.* INTO temp_to_load_all_chnages;

For syntax & more details please refer msdn link - click here to redirect to msdn