Transaction Isolation Level

By default, when you open a connection to sql server, its TRANSACTION ISOLATION LEVEL is READ COMMITTED. However, client applications can change the transaction isolation level for their sql connections, such as by running SET TRANSACTION ISOLATION LEVEL statement. When diagnosing sql blocking and deadlock, you must beware of transaction isolation level not being overlooked. Below script checks the transaction isolation level of all current running processes:
SELECT
r.session_id, s.transaction_isolation_level,
CASE s.transaction_isolation_level
WHEN 0 THEN 'Unspecified'
WHEN 1 THEN 'ReadUncommitted'
WHEN 2 THEN 'ReadCommitted'
WHEN 3 THEN 'Repeatable'
WHEN 4 THEN 'Serializable'
WHEN 5 THEN 'Snapshot' END AS transaction_isolation_level_Name,
OBJECT_NAME(t.objectid, t.dbid) AS object_name,
t.[text],
SUBSTRING(
t.[text],
(r.statement_start_offset / 2) + 1,
((
CASE
r.statement_end_offset
WHEN -1
THEN DATALENGTH(t.[text])
ELSE r.statement_end_offset
END - r.statement_start_offset) / 2) + 1) AS stmt,
r.start_time, r.status, r.command, DB_NAME(r.database_id) AS db_name,
s.login_name, s.host_name, s.program_name, s.total_elapsed_time
FROM sys.dm_exec_requests r
JOIN sys.dm_exec_sessions s ON s.session_id = r.session_id
CROSS APPLY sys.dm_exec_sql_text(r.[sql_handle]) t
WHERE r.status IN ('runnable', 'suspended', 'running')

If it's set by the client application, and you are not able to change the application code, then you can override the isolation level inside the stored procedure, given that it use stored procedure and you got the permission to alter it. E.g.
-- a sample stored procedure
CREATE PROCEDURE uspTestTranIsoLvl
    @set bit
AS
BEGIN
IF @set = 1
BEGIN
    SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
END
   
SELECT session_id, transaction_isolation_level,
CASE transaction_isolation_level
WHEN 0 THEN 'Unspecified'
WHEN 1 THEN 'ReadUncommitted'
WHEN 2 THEN 'ReadCommitted'
WHEN 3 THEN 'Repeatable'
WHEN 4 THEN 'Serializable'
WHEN 5 THEN 'Snapshot' END AS TRANSACTION_ISOLATION_LEVEL
FROM sys.dm_exec_sessions WHERE session_id = @@SPID;

END
GO

-- Demonstration
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

SELECT 'Before', session_id, transaction_isolation_level,
CASE transaction_isolation_level
WHEN 0 THEN 'Unspecified'
WHEN 1 THEN 'ReadUncommitted'
WHEN 2 THEN 'ReadCommitted'
WHEN 3 THEN 'Repeatable'
WHEN 4 THEN 'Serializable'
WHEN 5 THEN 'Snapshot' END AS TRANSACTION_ISOLATION_LEVEL
FROM sys.dm_exec_sessions WHERE session_id = @@SPID;

SELECT 'NOT set';
EXEC uspTestTranIsoLvl 0;

SELECT 'SET';
EXEC uspTestTranIsoLvl 1;

SELECT 'After', session_id, transaction_isolation_level,
CASE transaction_isolation_level
WHEN 0 THEN 'Unspecified'
WHEN 1 THEN 'ReadUncommitted'
WHEN 2 THEN 'ReadCommitted'
WHEN 3 THEN 'Repeatable'
WHEN 4 THEN 'Serializable'
WHEN 5 THEN 'Snapshot' END AS TRANSACTION_ISOLATION_LEVEL
FROM sys.dm_exec_sessions WHERE session_id = @@SPID;




 

Above demonstration shows that the transaction isolation level can be overridden inside stored procedure, and back to original after the stored procedure returned.
 

Increasing usability of Filtered Index

A filtered index is an optimized nonclustered index especially suited to cover queries that select from a well-defined subset of data. It uses a filter predicate to index a portion of rows in the table. A well-designed filtered index can improve query performance as well as reduce index maintenance and storage costs compared with full-table indexes.
In order to increase the chance that the query engine to employ the filtered indexes you created, make sure to include all the filtering column(s) of the index into its index key or included column list. Below example demonstrates why.


USE AdventureWorks2008
GO
-- Filtered Column in index key
DROP INDEX [Sales].[SalesOrderDetail].IX_1
GO
CREATE INDEX IX_1 ON [Sales].[SalesOrderDetail] (ProductID, SalesOrderID, OrderQty) WHERE OrderQty >= 2;
GO
SELECT ProductID, SalesOrderID FROM [Sales].[SalesOrderDetail] WHERE OrderQty >= 2        -- Use Filtered Index
SELECT ProductID, SalesOrderID FROM [Sales].[SalesOrderDetail] WHERE OrderQty >= 3        -- Use Filtered Index

-- Filtered Column NOT in index
DROP INDEX [Sales].[SalesOrderDetail].IX_1
GO
CREATE INDEX IX_1 ON [Sales].[SalesOrderDetail] (ProductID, SalesOrderID) WHERE OrderQty >= 2;
GO
SELECT ProductID, SalesOrderID FROM [Sales].[SalesOrderDetail] WHERE OrderQty >= 2        -- Use
SELECT ProductID, SalesOrderID FROM [Sales].[SalesOrderDetail] WHERE OrderQty >= 3        -- NOT use!

-- Filtered Column in INCLUDED
DROP INDEX [Sales].[SalesOrderDetail].IX_1
GO
CREATE INDEX IX_1 ON [Sales].[SalesOrderDetail] (ProductID, SalesOrderID) INCLUDE (OrderQty) WHERE OrderQty >= 2;
GO
SELECT ProductID, SalesOrderID FROM [Sales].[SalesOrderDetail] WHERE OrderQty >= 2        -- Use
SELECT ProductID, SalesOrderID FROM [Sales].[SalesOrderDetail] WHERE OrderQty >= 3        -- Use

By the way, the query optimizer won’t consider filtered indexes if you’re using local variables or parameterized SQL for the predicate that matches the filter. Dynamic SQL can help.

SQL Trace

SQL Trace, even it's a quite old feature, still loved by lot of database administrators due to its simplicity of creation. Rather than writing a sql script to call sp_trace_xxx system stored procedures, it can be created by using SQL Profiler to export a trace definition script, as below steps:

1. Open SQL Server Profiler;
2. Create a new trace;
3. Select a trace template, pick the required events, columns, and set filters as you wish;
4. Start the trace;
5. Stop the trace;
6. Click "File" menu -> "Export" -> "Script Trace Definition" -> "For SQL Server 2005 - 2014..."
7. Save the exported sql file.
The trace definition sql script will be something like below:

8. Replace the text "InsertFileNameHere" with a filename prefixed by a path, e.g., c:\MyFolder\MyTrace. The .trc extension will be appended to the filename automatically;
9. If you wanna use rollover files on the .trc trace output, change the second parameter value of sp_trace_create from 0 to 2. The default value of max_file_size is 5 MB;
10. Noted that trace definitions will be automatically removed in the case of SQL Server restart, so if you wanna the same trace survives server restart, you can create a stored procedure in the master database, which wrap up the above trace definition script, then execute sp_procoption to set that stored procedure for automatic execution every time SQL Server is started. You can check which stored procedure(s) in master database will be auto-executed by the following query using sysadmin login to run:
USE master
SELECT [name]
FROM sysobjects
WHERE type = 'P'
  AND OBJECTPROPERTY(id, 'ExecIsStartUp') = 1; 
11. Execute the trace definition script to create and start the trace.

After the trace is created, you can check the properties of a trace, including path of the output .trc file, is rollover, events, columns, and filters, by below queries:
-- view created traces
SELECT * FROM sys.traces
-- view trace events & columns
SELECT
     e.name AS Event_Name,
     c.name AS Column_Name
FROM fn_trace_geteventinfo(3) ei    -- replace the fn param value with the Trace ID u wanna check
JOIN sys.trace_events e ON ei.eventid = e.trace_event_id
JOIN sys.trace_columns c ON ei.columnid = c.trace_column_id
-- view trace filters
SELECT
     columnid,
    c.name AS Column_Name,
     logical_operator,
     comparison_operator,
     value
FROM fn_trace_getfilterinfo(3) fi    -- replace the fn param value with the Trace ID u wanna check
JOIN sys.trace_columns c ON fi.columnid = c.trace_column_id 

To see the trace output, you can open the .trc files by SQL Profiler, or you can use fn_trace_gettable which returns the content of one or more trace files in tabular form, e.g.
-- view trace output as table
SELECT *
FROM fn_trace_gettable('H:\SYSADMIN_AUDIT\sysadmin_audit_168.trc', default)    -- 1st param:initial file; 2nd param:no. of rollover files to be read (default=until last file) 

View Execution Plan for Multi Statement TVF, and why Inline TVF better

When you execute a Multi-statement table valued function (TVF), you will see an execution plan that looks something like this:

The optimizer always estimates a Multi-statement TVF has only a single row return, which is mostly not the truth. In order to see the detailed plan inside the Multi-statement TVF, you can run the following SQL statement to get it from the plan cache:
SELECT  deqp.query_plan, dest.text,
SUBSTRING(dest.text, (deqs.statement_start_offset / 2) + 1,
(deqs.statement_end_offset - deqs.statement_start_offset)
/ 2 + 1) AS actualstatement, deqs.creation_time
FROM    sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_query_plan(deqs.plan_handle) AS deqp
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
WHERE   deqp.objectid = OBJECT_ID('YourFunctionName');
The result will look like this:

Multi-statement TVF can be bad for performance due to the fact that it's treated like a table except there are no statistics available for SQL Server to base a good execution plan on - so it will estimate the function as returning a very small number of rows. If it returns a larger number of rows, then the plan generated could be a lot less than optimal.
Let's compare the showplan difference between Inline TVF and Multi-statement TVF. Below are two TVFs, one Inline and another Multi-statement:
CREATE FUNCTION udfInline
(   
    @id int
)
RETURNS TABLE
AS
RETURN
    SELECT * FROM Account
GO
CREATE FUNCTION udf_Multi
(
    @id int
)
RETURNS
@result TABLE
(
    AccountId int, UtcOffset int
)
AS
BEGIN
    INSERT @result SELECT * FROM Account
    RETURN
END
GO
The Inline TVF execution plan look like this:

For Inline TVF, the estimated number of rows are quite accurate. The actual underlying tables get brought into the main query and a better execution plan can be generated based on proper statistics. You'll notice that in this case, the execution plan will NOT have a mention of the function at all as it's basically just merged the function into the main query.

The Multi statement TVF execution plan look like this:

For Multi-statement TVF, SQL Server made the estimation as only one row. There's a great reference on it on MSDN: "But if you use multi-statement TVF, it’s treated as just like another table. Because there is no statistics available, SQL Server has to make some assumptions and in general provide low estimate. If your TVF returns only a few rows, it will be fine. But if you intend to populate the TVF with thousands of rows and if this TVF is joined with other tables, inefficient plan can result from low cardinality estimate."

How to fix Forwarded Records in a Heap

A heap is a table without a clustered index. Forwarded records are records in a heap that have grown too large for the page that it currently resides on. These types of records can only be found in heaps because tables with a clustered index keep the data sorted based on the clustered index. If a heap row gets updated and grows too big for it to stay on the same page, SQL Server will move it to a new page slot and leave a pointer at its original spot. Therefore, looking for a row will be more expensive afterwards. A lookup of the data row in a heap is no longer a direct access using the page and slot address. Instead of getting the data row the server might have to follow a forward pointer first.
You can temporarily fix forwarded records in heaps by rebuilding heaps. Note that rebuilding heaps was added to SQL Server starting with SQL Server 2008. You can’t rebuild heaps in SQL Server 2005 or lower. But creating a clustered index on it can avoid forward records permanently.
Below sql script list out all heaps in current database:
SELECT SCH.name + '.' + TBL.name AS TableName
FROM sys.tables AS TBL
     INNER JOIN sys.schemas AS SCH
         ON TBL.schema_id = SCH.schema_id
     INNER JOIN sys.indexes AS IDX
         ON TBL.object_id = IDX.object_id
            AND IDX.type = 0 -- = Heap
ORDER BY TableName;
You can then check the forwarded_record_count by using DMF sys.dm_db_index_physical_stats one by one table:
SELECT OBJECT_NAME(object_id) AS table_name, forwarded_record_count, avg_fragmentation_in_percent, page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID('<tableName>'), DEFAULT, DEFAULT, 'DETAILED');

Checking Buffer Pool Usage by Table/Index

In SQL Server, the data in table is stored in pages which has fixed size of 8 KB. Whenever there is a need of a page (for read or write) the page is first read from the disk and bought to memory location called "Buffer Pool". Below sql script can be used to check the buffer pool usage by table and index in the current database, this script calculates the usage by counting the no. of rows in sys.dm_os_buffer_descriptors. Each cached data page has one buffer descriptor. Buffer descriptors uniquely identify each data page that is currently cached in an instance of SQL Server.
 SELECT COUNT(*) AS cached_pages_count, COUNT(*) * 8 / 1024 AS cachedUsedMB,
obj.[object_id], obj.name AS tableName, idx.index_id, idx.name AS indexName
FROM sys.dm_os_buffer_descriptors AS bd
JOIN (
        SELECT p.[object_id], OBJECT_NAME(p.[object_id]) AS name, p.index_id, au.allocation_unit_id
        FROM sys.allocation_units AS au JOIN sys.partitions AS p ON au.container_id = p.hobt_id AND (au.[type] = 1 OR au.[type] = 3)
        UNION ALL
        SELECT p.[object_id], OBJECT_NAME(p.[object_id]) AS name, p.index_id, au.allocation_unit_id
        FROM sys.allocation_units AS au JOIN sys.partitions AS p ON au.container_id = p.[partition_id] AND au.[type] = 2
) AS obj ON bd.allocation_unit_id = obj.allocation_unit_id
JOIN sys.indexes AS idx ON idx.[object_id] = obj.[object_id] AND idx.index_id = obj.index_id
WHERE database_id = DB_ID()
GROUP BY obj.[object_id], obj.name, idx.index_id, idx.name
ORDER BY cached_pages_count DESC;

SQL Server Buffer Pool Extension

Introduced in SQL Server 2014, the buffer pool extension (BPE) provides the seamless integration of a nonvolatile random access memory (solid-state drive SSD) extension to the database engine buffer pool to significantly improve I/O throughput.
Data and index pages are read from disk into the buffer pool and modified pages (also known as dirty pages) are written back to disk. Memory pressure on the server and database checkpoints cause hot (active) dirty pages in the buffer cache to be evicted from the cache and written to mechanical disks and then read back into the cache. These I/O operations are typically small random reads and writes on the order of 4 to 16 KB of data. Small random I/O patterns incur frequent seeks, competing for the mechanical disk arm, increasing I/O latency, and reducing aggregate I/O throughput of the system. The typical approach to resolving these I/O bottlenecks is to add more DRAM, or alternatively, added high-performance SAS spindles.

The following list describes the benefits of the BPE feature.
 - Increased random I/O throughput
 - Reduced I/O latency
 - Increased transaction throughput
 - Improved read performance with a larger hybrid buffer pool
 - A caching architecture that can take advantage of present and future low-cost memory drives

The following illustration provides a high-level architectural overview of the buffer pool relative to other SQL Server components.

Below steps demonstrate how to enable BPE:

1. Check the currently configured value of the MAX SERVER MEMORY.
USE master
GO
EXEC sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE
GO
EXEC sp_configure 'max server memory (MB)'
GO

2. Configure the BPE buffer cache file. The minimum size is the size of MAX SERVER MEMORY. You should put this file in a SSD drive, e.g.
USE master
GO
ALTER SERVER CONFIGURATION
SET BUFFER POOL EXTENSION ON
  (FILENAME = '<SSD>:\SSDCACHE\SqlServerCache.BPE', SIZE = 32 GB);
GO

Best Practices:
Microsoft says that the BPE can be up to 32 times of the MAX SERVER MEMORY with SQL Server Enterprise, and up to 4 times with Standard Edition. But it recommends a ratio of 1:16 or less between the amount of memory and the size of the extension and cautions that a ratio of 1:4 to 1:8 could be optimal as a starting point.

Restore Log Backup Files in a Directory

Most production databases are running in full recovery model, there must be transaction log backup job scheduled to run periodically. In order to reduce possible data loss, log should be backup as frequent as possible, let's say every minute. More log backups mean more files. Rather than manually restore log backups through the GUI, one at a time, you need a script-based method to automatically restore all of the log files in a directory. Below script can be used to generate RESTORE LOG statements by reading a directory which transaction log backup files reside. The log backup file time must be expressed by its file name, e.g. with a YYYYMMDDhhmmss suffix.
USE Master;
GO 
SET NOCOUNT ON

-- 1 - Variable declaration
DECLARE @dbName sysname
DECLARE @backupPath NVARCHAR(500)
DECLARE @cmd NVARCHAR(500)
DECLARE @fileList TABLE (backupFile NVARCHAR(255))
DECLARE @lastFullBackup NVARCHAR(500)
DECLARE @lastDiffBackup NVARCHAR(500)
DECLARE @backupFile NVARCHAR(500)

-- 2 - Initialize variables
SET @dbName = '<UserDB>'
SET @backupPath = 'D:\LogBackup\UserDB\'

-- 3 - get list of files
SET @cmd = 'DIR /b ' + @backupPath

INSERT INTO @fileList(backupFile)
EXEC master.sys.xp_cmdshell @cmd

SELECT 'RESTORE LOG ' + @dbName + ' FROM DISK = N''' + @backupPath + backupFile + ''' WITH NORECOVERY'
FROM @fileList WHERE backupFile IS NOT NULL ORDER BY backupFile

Pause Mirroring to Speed up Large Data Update

Database mirroring transports the changes in the production database to a mirror database, either synchronously or asynchronously. Asynchronous database mirroring is supported only by SQL Server Enterprise edition. During large data update, the mirror commit overhead of a Synchronous mirroring session becomes significant. In order to speed up your data update process, you can change the operating mode to Asynchronous, if your sql server is Enterprise edition. But if it's Standard edition, you can Pause the mirror session. During bottlenecks, pausing can be useful to improve performance on the principal server. Below is a demonstration.

1. Pause the mirroring session.

 2. In “Database Mirroring Monitor”, also shows the mirroring session as Suspended.

3. Run some loading on the exposed principal database, to fill up the Unsent Log Queue:
SET NOCOUNT ON;
WHILE 1 = 1
BEGIN
        INSERT Table_1 VALUES (99, 'Test')
    DELETE Table_1 WHERE pk = 99
END

4. In this example, I accumulate 14Gb of unsent transaction log.

5. Now “Resume” the mirroring session.

6. As there’s quite a lot of unsent log in the queue, it takes time to synchronize.

7. Even the synchronous (high safety mode) mirroring session is synchronizing, you can still commit any new transactions WITHOUT any delay!

You should resume a paused session as soon as possible, because as long as a database mirroring session remains paused, the transaction log can NOT be truncated.

Notes of SQL Server AlwaysOn Availability Group

The AlwaysOn Availability Groups (AG) feature is a high-availability and disaster-recovery solution that provides an enterprise-level alternative to database mirroring. Introduced in SQL Server 2012, an availability group supports a failover environment for a discrete set of user databases, known as availability databases, that fail over together. An availability group supports a set of read-write primary databases and one to eight sets of corresponding secondary databases. Optionally, secondary databases can be made available for read-only access and/or some backup operations.

AG must be running on the Enterprise Edition of SQL Server 2012.

You can create any type of backup of a primary database. Alternatively, you can create log backups and copy-only full backups of secondary databases.

A given availability group can support up to three synchronous-commit availability replicas, including the current primary replica (i.e. 1 primary + 2 synchronous mode secondaries).

A WSFC Resource Group is created for every Availability Group that you create. The WSFC cluster monitors this resource group to evaluate the health of the primary replica.
The quorum for AG is based on all nodes in the WSFC cluster regardless of whether a given cluster node hosts any availability replicas.
In contrast to database mirroring, there is no witness role in AlwaysOn Availability Groups.

A Failover Cluster Instance (FCI) may be used together with an AG to enhance the availability of an availability replica. However, to prevent potential race conditions in the WSFC cluster, automatic failover of the availability group is NOT supported to or from an availability replica that is hosted on a FCI. SQL Server Failover Cluster Instances (FCIs) do NOT support automatic failover by availability groups, so any availability replica that is hosted by an FCI can only be configured for manual failover.

Do NOT use the Failover Cluster Manager to manipulate availability groups, for example:
Do NOT change any availability group properties, such as the possible owners.
Do NOT use the Failover Cluster Manager to fail over availability groups. You must use Transact-SQL or SQL Server Management Studio.

AG Failovers are NOT caused by database issues such as a database becoming suspect or corruption of a transaction log.
The primary replica sends transaction log records of each primary database to every secondary database. Every secondary replica caches the transaction log records and then applies them to its corresponding secondary database (hardens the log).
Data synchronization occurs between the primary database and each connected secondary database, independently of the other databases in an availability group. Therefore, a secondary database can be suspended or fail without affecting other secondary databases, and a primary database can be suspended or fail without affecting other primary databases.

Availability Modes:
- Asynchronous-commit mode, the primary replica commits transactions WITHOUT waiting for acknowledgement that an asynchronous-commit secondary replica has hardened the log.
- Synchronous-commit mode, before committing transactions, the primary replica waits for a synchronous-commit secondary replica to acknowledge that it has finished hardening the log.

Failover:
Synchronous-commit mode supports planned manual failover and automatic failover, if the target secondary replica is currently synchronized. The support for these forms of failover depends on the setting of the failover mode property on the failover partners. If failover mode is set to "manual" on either the primary or secondary replica, only manual failover is supported for that secondary replica. If failover mode is set to "automatic" on both the primary and secondary replicas, both automatic and manual failover are supported on that secondary replica. Automatic failover requires that both the primary replica and the target secondary replica are running under synchronous-commit mode with the failover mode set to "Automatic". In addition, the secondary replica must already be synchronized, have WSFC quorum, and meet the conditions specified by the flexible failover policy of the availability group. In Asynchronous-commit mode, the only form of failover is forced manual failover (with possible data loss), typically called forced failover.

An availability group listener is associated with a unique DNS name that serves as a virtual network name (VNN), one or more virtual IP addresses (VIPs), and a TCP port number.

The session-timeout period is an availability-replica property that determines how long connection with another availability replica can remain inactive before the AG connection is closed. The primary and secondary replicas ping each other to signal that they are still active. If no ping is received from the other replica within the session-timeout period, the replica times out. Its connection is closed, and the timed-out replica enters the DISCONNECTED state. Even if a disconnected replica is configured for synchronous-commit mode, transactions will not wait for that replica to resynchronize. The default session-timeout period for each availability replica is 10 seconds. Setting the value to less than 10 seconds creates the possibility of a heavily loaded system declaring a false failure. In the resolving role (during failover), the session-timeout period does not apply because pinging does not occur.

Automatic Page Repair:
Each availability replica tries to automatically recover from corrupted pages on a local database. If a secondary replica cannot read a page, the replica requests a fresh copy of the page from the primary replica. If primary replica cannot read a page, it broadcasts a request for a fresh copy to all the secondary replicas and gets the page from the first to respond.

AG Requirements:
1. The sql server instances that host availability replicas for a given AG must reside on separate nodes of a single WSFC cluster. A given instance can host only one availability replica per availability group. However, each instance can be used for many availability groups.

2. To enable Kerberos authentication:
- all sql server instances that host an availability replica for the AG must use the same SQL Server service account;
- domain administrator needs to manually register a Service Principal Name (SPN) with Active Directory on the SQL Server service account for the virtual network name (VNN) of the availability group listener.

3. All sql server instances that host availability replicas for an AG must use the same SQL Server Collation.

4. Enable the AlwaysOn Availability Groups feature on each sql server instance.

5. Each server instance requires a database mirroring endpoint. If a server instance that you select to host an availability replica is running under a domain user account and does not yet have a mirroring endpoint, the New Availability Group Wizard (or Add Replica to Availability Group Wizard) can create the endpoint and grant CONNECT permission to the server instance service account.

6. The user databases must be in the full recovery mode.

7. Possess at least one full database backup, required to initiate the full-recovery log chain.

8. The databases must NOT be configured for database mirroring.

Availability Mode:
If the primary replica is configured for asynchronous-commit mode, it does not wait for any secondary replica to write incoming transaction log records to disk (to harden the log).
If a given secondary replica is configured for asynchronous-commit mode, the primary replica does not wait for that secondary replica to harden the log.
If both the primary replica and a given secondary replica are both configured for synchronous-commit mode, the primary replica waits for the secondary replica to confirm that it has hardened the log (unless the secondary replica fails to ping the primary replica within the primary's session-timeout period). If primary's session-timeout period is exceeded by a secondary replica, the primary replica temporarily shifts into asynchronous-commit mode for that secondary replica. When the secondary replica reconnects with the primary replica, they resume synchronous-commit mode.
If a synchronous-commit secondary replica times out without confirming that it has hardened the log, the primary marks that secondary replica as failed. The connected state of the secondary replica changes to DISCONNECTED, and the primary replica stops waiting for confirmation from the secondary replica. This behavior ensures that a failed synchronous-commit secondary replica does not prevent hardening of the transaction log on the primary replica.

Asynchronous-Commit Mode with Only Forced Failover:
The only form of failover supported by asynchronous-commit mode is forced failover (with possible data loss). The failover target transitions to the primary role, and its copies of the databases become the primary databases. Any remaining secondary databases, along with the former primary databases, once they become available, are suspended until you manually resume them individually. Any transaction logs that the original primary replica had not yet sent to the former secondary replica are lost. This means that the new primary databases might be lacking recently committed transactions (data loss).

Synchronous-Commit Mode with Only Manual Failover:
When these replicas are connected and the database is synchronized, manual failover is supported. If the secondary replica goes down, the primary replica is unaffected. The primary replica runs exposed if NO any SYNCHRONIZED replicas exist (that is, without sending data to any secondary replica).

Synchronous-Commit Mode with Automatic Failover:
To configure an availability group for automatic failover, you need to set both the current primary replica and one secondary replica to synchronous-commit mode with automatic failover. Furthermore, for an automatic failover to be possible at a given time, this secondary replica must be synchronized with the primary replica (that is, the secondary databases are all synchronized), and the Windows Server Failover Clustering (WSFC) cluster must have quorum. If the primary replica becomes unavailable under these conditions, automatic failover occurs. The secondary replica switches to the role of primary, and it offers its database as the primary database.

Log Shipping Email Alert

As a DBA, you can use the Log Shipping Status report in SSMS to check the Log Shipping is running without any issues. However, it is not possible to do 24/7 monitoring using SSMS manually. By the way, some third party monitoring systems can be overkill. So I create this email alert by myself, which can be executed as an Agent Job periodically, let's say per 15 minutes. This monitor bases on the last_restored_latency column value of the system table log_shipping_monitor_secondary in msdb system database. In order to send email content in HTML table format, a tailor-made stored procedure [ConvertTableToHtml] is being used.

/* JOB BODY START */
/* Specify your alert threshold in minutes here */
DECLARE @alertDelayMinutes int = 15;

DECLARE @NewLineChar char(2) = CHAR(13) + CHAR(10);

DECLARE @recipients varchar(max) = 'peter.lee@lazybugstudio.com;'
DECLARE @importance varchar(6) = 'Normal';
DECLARE @subject nvarchar(255) = N'DR Log Shipping Delay Check - ' + CONVERT(varchar(50), GETDATE(), 120);
DECLARE @body nvarchar(max);

CREATE TABLE #t (
    secondary_database sysname,
    last_copied_date datetime,
    last_restored_date datetime,
    last_restored_latency int,
    last_restored_ago int
);
INSERT #t
SELECT
    secondary_database,
    last_copied_date,
    last_restored_date,
    last_restored_latency,
    DATEDIFF(minute, last_restored_date, GETDATE())
FROM log_shipping_monitor_secondary;

DECLARE @htmlTable varchar(max);
EXEC master.dbo.ConvertTableToHtml 'SELECT * FROM #t', @htmlTable OUTPUT;;

SET @body = @htmltable;

DECLARE @cnt int = (SELECT COUNT(1) FROM #t WHERE last_restored_latency >= @alertDelayMinutes OR last_restored_ago >= @alertDelayMinutes);

IF @cnt > 0
BEGIN
--    SET @recipients = 'dbateam@lazybugstudio.com;';
    SET @importance = 'High';
    SET @subject = 'ERROR: ' + @subject;
    SET @body += N'<br/><b>DR DB Long Delay!</b>';
END

EXEC msdb.dbo.sp_send_dbmail @recipients = @recipients , @subject = @subject, @body = @body, @importance = @importance, @body_format = 'HTML';

DROP TABLE #t;
/* JOB BODY END */

/* ConvertTableToHtml Stored Proc. */
CREATE PROCEDURE [dbo].[ConvertTableToHtml](
      @SqlQuery AS NVARCHAR(4000),
      @Html AS VARCHAR(MAX) OUTPUT
)
AS

      DECLARE @Header AS NVARCHAR(MAX) = ''
      DECLARE @Column AS NVARCHAR(MAX) = ''
      DECLARE @Query AS NVARCHAR(MAX)
      DECLARE @Css AS VARCHAR(MAX) = '
            <style type="text/css">

            table.gridtable {
                font-family: verdana,arial,sans-serif;
                font-size:11px;
                color:#333333;
                border-width: 1px;
                border-color: #666666;
                border-collapse: collapse;
            }

            table.gridtable th {
                border-width: 1px;
                padding: 8px;
                border-style: solid;
                border-color: #666666;
                background-color: #dedede;
            }

            table.gridtable td {
                border-width: 1px;
                padding: 8px;
                border-style: solid;
                border-color: #666666;
                background-color: #ffffff;
            }

            </style>
            '
BEGIN

      SET @Query = 'SELECT * INTO ##columns FROM ( ' + @SqlQuery + ') Temp'
      EXECUTE(@Query)

      SELECT @Column = @Column + 'ISNULL(' + QUOTENAME(name) +' ,'' '')' + ' AS TD, '
      FROM tempdb.SYs.columns
      WHERE object_id = OBJECT_ID('tempdb..##columns')
     
      SET  @Column = LEFT(@Column,LEN(@Column)-1)

      SELECT @Header = @Header + '<TH>' +  name + '</TH>'
      FROM tempdb.SYs.columns
      WHERE object_id = OBJECT_ID('tempdb..##columns')
     
      SET @Header = '<TR>' + @Header  + '</TR>'
     
      SET @Query = 'SET  @Html = (SELECT ' + @Column + ' FROM ( ' + @SqlQuery + ') AS TR
       FOR XML AUTO ,ROOT(''TABLE''), ELEMENTS)'

      EXECUTE SP_EXECUTESQL @Query,N'@Html VARCHAR(MAX) OUTPUT',@Html OUTPUT
      SET  @Html = @Css + REPLACE(@Html,'<TABLE>' ,'<TABLE  class="gridtable">' + @Header)

      DROP TABLE ##columns

END

GO

Optimizing Query Plans using SQL2014 Cardinality Estimator

The SQL Server Query Optimizer’s purpose is to find an efficient execution plan that fulfills a query request. It attempts this by assigning estimated costs to various query execution plan alternatives and then choosing the plan alternative with the lowest estimated cost. One key factor for determining operator cost is the estimation of rows that will be processed for each operator within a query execution plan. This row estimation process is commonly referred to as cardinality estimation (CE). SQL Server 2014 introduces the first major redesign of the SQL Server Query Optimizer CE process since version 7.0. Use of the new CE can result in an overall improvement in average query performance for a wide range of application workloads.

Estimates are calculated using input from statistics associated with objects referenced in the query. Statistics objects used for estimation can be associated with an index or they can exist independently. You can create statistics objects manually or the query optimization process can generate them automatically.

The database context (USE <whichDB?>;) of a SQL Server session determines the CE version. If you connect to a database that is set to the SQL Server 2014 database compatibility level, the query request will use the new CE. If the database compatibility level is for an earlier version, the legacy CE will be used. You can verify the compatibility level of a database by querying sys.databases.

SELECT [name],

[compatibility_level]

FROM sys.[databases];

To move a database to the SQL Server 2014 database compatibility level, alter the database compatibility level to the latest version, which is “120”. E.g.

USE [master];

GO

-- SQL Server 2014 compatibility level

ALTER DATABASE [AdventureWorks2012] SET COMPATIBILITY_LEVEL = 120;

GO

In addition to checking the database’s compatibility level, you can also verify which CE version was used for a specific query. To verify the CE version, inspect the query execution plan. E.g.

 In the Properties Window of the Root (leftmost) operator, for the CardinalityEstimationModelVersion attribute, value of 120 means that the new SQL Server 2014 CE functionality generated the plan. A value of 70, means that the legacy CE functionality generated the plan.

Use Query Hint RECOMPILE to Improve Cardinality Estimate

In order to generate execution plans, SQL Server makes estimations on number of rows to be affected by different operators. But sometimes even your query is simple enough and statistics are up-to-date, SQL Server may still makes bad estimation which is quite far away from the actual number of rows, especially if your table data is skewed (row distribution is not even) and you pass variable/parameter into the query (which is very common in stored procedures).
Let's see an example below:
 
DECLARE @ID int = (SELECT TOP 1 ID FROM CSAccount WHERE Username = 'awe001');
SELECT ID FROM CSAccount WHERE Parent = @ID;

In this example, the CSAccount table is skewed, most Parent values only have a few rows, but Parent 'awe001' have 128 rows.
Check the execution plan of the 2nd query, the Estimated Number of Rows and the Actual one are very different (2.95 vs 128).

It is because from the query optimizer perspective, it does not know the value of the @ID variable when the query is compiled and before query is executed, values of variables can only be determined during run time. In order to let the optimizer to use the run time variable value for query compilation, we can specify a query hint RECOMPILE. When compiling query plans, the RECOMPILE query hint uses the current values of any local variables in the query and, if the query is inside a stored procedure, the current values passed to any parameters. RECOMPILE is a useful alternative to creating a stored procedure that uses the WITH RECOMPILE clause when only a subset of queries inside the stored procedure, instead of the whole stored procedure, must be recompiled.

Let's see the effect of RECOMPILE query hint:

DECLARE @ID int = (SELECT TOP 1 ID FROM CSAccount WHERE Username = 'awe001');
SELECT ID FROM CSAccount WHERE Parent = @ID OPTION (RECOMPILE);

Now the estimation becomes accurate.

Accurate cardinality estimation plays an important role on generating execution plan. The whole structure of the execution plan will have huge difference if the estimation is far away, which can make the query runs very slow. So when you find a query/stored procedure runs slow, especially with a specific set of parameters, then you should check the actual execution plan of it, try to find any edges/operators inside the plan get bad cardinality estimations. If the table data is also skewed, then the query hint RECOMPILE may help.