Catch the Lead SQL Blocker - the real culprit of system slowness

Your client made an urgent call to you, said that the software system is hang. You check the CPU, RAM, I/O, and network, in all production servers, all seems healthy. You try to access the software system, but it's inaccessible. In this case, I suggest you to check the SQL Server, there's a free tool that I love so much called Idera SQL Check. If you see the Processes window just like a spider web, then you find the cause - SQL process blocking.

It is very common to deal with blocking scenario and there may be a blocking chain means one process (aka SPID) is blocking second one and second one is blocking third one and so on. It's really difficult to track the root of blocking chain by simply running the system stored procedure sp_who2 or from SSMS activity monitor if there are number of SPIDs which are involved. So here I wish to share a stored procedure that I wrote which will help you to find out the root SPID and all associated details, in order to help you to further troubleshoot and make prompt decision (may be you will consider to kill the lead blocker for a quick fix for the incident).

USE [master]
GO

CREATE PROC [dbo].[ViewProcessBlocking]
AS
BEGIN
SET NOCOUNT ON;

SELECT
s.spid, Blockingspid = s.blocked, DatabaseName = DB_NAME(s.dbid),
s.program_name, s.loginame, s.hostname, s.login_time, s.last_batch, s.waittime, s.waitresource,
ObjectName = OBJECT_NAME(objectid, s.dbid), Definition = CAST(text AS VARCHAR(MAX))
INTO #Processes
FROM sys.sysprocesses s
CROSS APPLY sys.dm_exec_sql_text (sql_handle)
WHERE
s.spid > 50
;
WITH Blocking(spid, Blockingspid, DB, BlockingObject, [Blocking Statement / Definition], program, login, host, login_time, last_batch, waittime_ms, waitresource, RowNo, LevelRow)
AS
(
SELECT
s.spid, s.Blockingspid, s.DatabaseName, s.ObjectName, s.Definition,
s.program_name, s.loginame, s.hostname, s.login_time, s.last_batch, s.waittime, s.waitresource,
ROW_NUMBER() OVER(ORDER BY s.spid),
0 AS LevelRow
FROM
#Processes s
JOIN #Processes s1 ON s.spid = s1.Blockingspid
WHERE
s.Blockingspid = 0
UNION ALL
SELECT
r.spid, r.Blockingspid, r.DatabaseName, r.ObjectName, r.Definition,
r.program_name, r.loginame, r.hostname, r.login_time, r.last_batch, r.waittime, r.waitresource,
d.RowNo,
d.LevelRow + 1
FROM
#Processes r
JOIN Blocking d ON r.Blockingspid = d.spid
WHERE
r.Blockingspid > 0
)
SELECT *
INTO #BlockTree
FROM Blocking
ORDER BY RowNo, LevelRow

-- Top HEAD
SELECT TOP 1 'Oldest HEAD may need to KILL' AS OldestHeadMayKill, 'KILL ' + CAST(spid AS varchar(10)) AS KillStmt,
* INTO #TopHead FROM #BlockTree WHERE LevelRow = 0 ORDER BY last_batch
SELECT * FROM #TopHead
SELECT COUNT(DISTINCT spid) AS [Total no. of affected process], CAST(MAX(waittime_ms) / 1000.0 / 60.0 AS decimal(38, 1)) AS [Longest Wait Minute(s)] FROM #BlockTree
-- All blocker(s) & blocked
SELECT DISTINCT
spid, Blockingspid, DB, BlockingObject, [Blocking Statement / Definition], program, [login], host, login_time, last_batch, waittime_ms, waitresource
FROM #BlockTree

DROP TABLE #TopHead
DROP TABLE #BlockTree
DROP TABLE #Processes

END

GO


Run this stored procedure:
EXEC master..ViewProcessBlocking
The result will be like this:

It shows you the lead blocker SPID, what SQL its running, how many processes are being blocked and how long it is. There's also a KILL statement to kill that culprit. As your blocking scenario may be caused by multiple lead blockers, you many need to kill and run ViewProcessBlocking multiple times in order to settle down the incident. Remind that it's just a quick fix to kill the lead blocker(s), as a DBA you must further investigate the problem. You can refer to my older post to enable the tracking events of blocked process and deadlock.

Why NOLOCK is a really, really bad idea

The NOLOCK / READ UNCOMMITTED hint is much more dangerous than its name suggests. And that it why most people who don’t understand the problem, recommend it. It creates “incredibly hard to reproduce” bugs. The type that often destroy your end-users confidence in your product & your company.

What many people think NOLOCK is doing
Most people think the NOLOCK hint just reads rows & doesn’t have to wait till others have committed their updates. If someone is updating, that is OK. If they’ve changed a value then 99.999% of the time they will commit, so it’s OK to read it before they commit. If they haven’t changed the record yet then it saves me waiting, its like my transaction happened before theirs did. But it's absolutely wrong!

The Problem
Those that know better will often point to the fact that NOLOCK allows dirty reads. Data that has not been committed can, and will, be returned. In straight terms, an insert, update, or delete that is in process will considered for the result set, regardless of the state of the transaction. This can mean returning rows from an insert that may potentially rollback, or returning rows that are being deleted.

What about times a query is returning rows that you know for certain are not being modified? Suppose that you are updating rows for one client and need to return rows for a second client. In this case, will the use of NOLOCK be safe? The data isn’t being modified for the second client, so you might assume that the returning that data won’t have an opportunity for dirty data. Unfortunately, this assumption is incorrect.

Same Data is Read Twice
There are rare occasions when the same data can be read twice when using the read-uncommitted isolation level or nolock hint. To illustrate this issue we have to give a little background first. Clustered Indexes are created on SQL Server tables to physically order the data within the table based on the Cluster Key. The leaf pages of the index contain the data pages which contains the actual data for the table. Data pages can hold 8K worth of data.
Scenario: You have an ETL process that will Extract all records from a table, perform some type of transformation, and then load that data into another table. There are two types of scans that occur in SQL Server to read data: allocation scans and range scans. Range scans occur when you have a specific filter (where clause) for the data you are reading, and an index can be used to help seek out those specific records. When you do not have a filter, an allocation scan is used to scan all of the data pages that have been allocated to that table. Pending you are not doing any type of sort operations, your data will read the data pages in the order as it finds them on the disk. For simplicity, let’s assume there is no fragmentation so your data pages are in order 1-10. So far your process has read pages 1-6. Remember your NOLOCK process is not requesting shared (S) locks so you are not blocking other users. Meanwhile, another process begins which inserts records into your table. This process attempts to insert records onto Page 3, but the page is full and the record will not fit. As a result the page has to be split and half of the records will remain on Page 3 and the other records will be moved to a new page which will be page 11. Your process has already read the data that was on Page 3, but now half of that data has been moved to page 11. As a result, as your process continues it will read Page 11 which contains data that has already been read. If there is no type of checks on the destination table, you will end up with bad duplicate data.

Solution
One of the the main concerns that people have with locking, is the blocking that is associated with two users trying to access the same locked resource. As an alternative to using NOLOCK, try using READ_COMMITTED_SNAPSHOT / SNAPSHOT isolation level instead. Through this, data readers won’t block writers; which will reduce the amount of lock blocking on your data platform.

Conclusion
SQL Server is a very complex enterprise database solution with many options and flags that can be changed to alter the behavior of SQL Server. Although many of these options have a justified use, it is important to understand the risks that are associated with changing these options. The read-uncommitted isolation level and nolock table hint are no exception to this rule. Generally it is best practice to stick with the default isolation level and refrain from using table/query hints unless it is absolutely necessary and the solution has been thoroughly tested. Using read-uncommitted and nolock should be the EXCEPTION and not the RULE.

MSSQL 2012 server failure results in Identity gaps

In SQL Server 2012, the database engine changes its mechanism for generating Identity values. Prior to SQL Server 2012, identity have no any cache in memory. SQL Server 2012 introduces cache in identity, leading to gaps will be resulted after server failover (ref.: Failover or Restart Results in Reseed of Identity). Below is the cache values for different data types.

type	identity
TINYINT	10
SMALLINT	100
INT	1000
BIGINT	10000
NUMERIC	10000

You can do a simple test to demonstrate how a server failure will result a gap in identity values:
IF OBJECT_ID(N'dbo.T1' , N'U') IS NOT NULL DROP TABLE dbo.T1;
GO
CREATE TABLE dbo.T1 (keycol INT IDENTITY);
GO
INSERT INTO dbo.T1 DEFAULT VALUES;
GO 2
SELECT IDENT_CURRENT(N'dbo.T1');
The result is 2.
To force an unclean termination of the SQL Server process, open Task Manager (Ctrl+Shift+Esc), right-click the SQL Server process, and choose End task.
Next, start the SQL Server using SQL Server Configuration Manager.
Then query the current identity value again:
SELECT IDENT_CURRENT(N'dbo.T1');
The result is 1001.

SEQUENCE Basics

The SEQUENCE statement introduced in SQL Server 2012 brings the ANSI SQL 2003 standard method of generating IDs. Unlike IDENTITY, which is a table property, SEQUENCE is an independent schema bound object. Different tables and objects can share the same SEQUENCE object. Below is the syntax of CREATE SEQUENCE:

CREATE SEQUENCE [schema_name.]sequence_name
[AS [built_in_integer_type | user-defined_integer_type]]
[START WITH constant]
[INCREMENT BY constant]
[{MINVALUE [constant]} | {NO MINVALUE}]
[{MAXVALUE [constant]} | {NO MAXVALUE}]
[CYCLE | {NO CYCLE}]
[{CACHE [constant]} | {NO CACHE}]
[;]

A sequence can be defined as any integer type, default is bigint.
START WITH specifies the first value returned by the sequence object, which must be within the minimum and maximum values, default is the minimum value if its an ascending sequence and the maximum if its an descending sequence.
INCERMENT BY specifies the value used to increment (or decrement if negative) the value of the sequence for each call to the NEXT VALUE FOR. If the increment is positive, the sequence is ascending; if the increment is negative, the sequence is descending. The default is 1.
MINVALUE specifies the minimum value. The default is the minimum value of the data type.
MAXVALUE specifies the maximum value. The default is the maximum value of the data type.
CYCLE specifies whether the sequence should restart from the minimum value (or maximum for descending sequence) or throw an exception when its minimum or maximum value is exceeded. The default is NO CYCLE. Note that cycling restarts from the minimum or maximum value, not from the start value.
The CACHE option is used to increase the performance of a sequence object by minimizing the number of disk IOs that are required to generate sequence numbers. If you didn't specify any CACHE nor NO CACHE when creating a sequence, SQL Server will take care it for you. I am not going to explain too much about it, further details are provided in the BOL.
Let's create a simple sequence and play with it.
CREATE SEQUENCE Simple_Seq
AS INTEGER
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 9
NO CYCLE;
Then try to get values from it a few times...
SELECT NEXT VALUE FOR Simple_Seq;
GO 9
SELECT NEXT VALUE FOR Simple_Seq;
After you hit 9 and then invoke the next value, you will get this error message.
Msg 11728, Level 16, State 1, Line 1
The sequence object 'Simple_Seq' has reached its minimum or maximum value. Restart the sequence object to allow new values to be generated.
You can restart the sequence by using the ALTER SEQUENCE statement:
ALTER SEQUENCE Simple_Seq RESTART;
Also if you specified the CYCLE option when creating the sequence, the sequence number will be recycled by itself.
CREATE SEQUENCE Cycle_Seq
AS INTEGER
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 9
CYCLE;
SELECT NEXT VALUE FOR Cycle_Seq;
GO 9
SELECT NEXT VALUE FOR Cycle_Seq;

As I said, a SEQUENCE is an independent schema bound object which can be shared by different tables. Below two tables both use the same sequence as the default value of its primary key.
-- restart the sequence
ALTER SEQUENCE Cycle_Seq RESTART
GO
CREATE TABLE A (
id int DEFAULT NEXT VALUE FOR Cycle_Seq PRIMARY KEY,
n VARCHAR(50)
);
GO
CREATE TABLE B (
id int DEFAULT NEXT VALUE FOR Cycle_Seq PRIMARY KEY,
n VARCHAR(50)
);
GO
INSERT A (n) VALUES ('mouse');
INSERT B (n) VALUES ('Metal');
INSERT A (n) VALUES ('cow');
INSERT A (n) VALUES ('tiger');
INSERT B (n) VALUES ('wood');
SELECT * FROM A
SELECT * FROM B

Result:

Transparent Data Encryption (TDE)

Transparent Data Encryption (TDE) provides the ability to encrypt an entire database and to have the encryption be completely transparent to the applications that access the database. TDE encrypts the data stored in both the database's data file (.mdf) and log file (.ldf) using either Advanced Encryption Standard (AES) or Triple DES (3DES) encryption. In addition, any backups of the database are encrypted. This protects the data while it's at rest as well as provides protection against losing sensitive information if the backup media were lost or stolen. The code below demonstrates how to enable TDE on a sample database named UserDB.
--Check if the Database Master Key already present.
USE master;
GO
SELECT * FROM sys.symmetric_keys;
--Drop the existing Master Key.
USE master;
GO
BEGIN TRY
DROP MASTER KEY;
END TRY
BEGIN CATCH
PRINT 'Master Key NOT exists.';
END CATCH
GO
--Create Master Key in master database.
USE master;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'master key password';
GO
--Create Server Certificate in the master database.
USE master;
GO
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'master key password';
CREATE CERTIFICATE SQL_TDE_CERT WITH SUBJECT = 'SQL TDE CERT';
GO
--Create User Database Encryption Key.
USE UserDB;
GO
CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_128 ENCRYPTION BY SERVER CERTIFICATE SQL_TDE_CERT;
GO
--Check Database Encryption Key created.
--The tempdb system database will also be encrypted
--if any other database on the instance of SQL Server is encrypted by using TDE.
SELECT DB_NAME(database_id) AS database_name, * FROM sys.dm_database_encryption_keys;
--Enabling Transparent Database Encryption for the User Database.
USE master;
GO
ALTER DATABASE UserDB SET ENCRYPTION ON;
GO
--Check User Database is encrypted.
SELECT name, is_encrypted FROM sys.databases;
--Backup Master Key to file.
USE master;
GO
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'master key password';
BACKUP MASTER KEY TO FILE = 'D:\MSSQL_TDE_KEYS\MasterKey.mtk' ENCRYPTION BY PASSWORD = 'master key password';
GO
--Backup Server Certificate.
USE master;
GO
BACKUP CERTIFICATE SQL_TDE_CERT TO FILE = 'D:\MSSQL_TDE_KEYS\ServerCert.cer'
WITH PRIVATE KEY ( FILE = 'D:\MSSQL_TDE_KEYS\PrivateKey.pvk', ENCRYPTION BY PASSWORD = 'master key password');
GO

Replace XML data in SQL Server

Using the XML modify method is the most efficient way to modify XML data stored in xml type variable or column. This method takes an XML DML statement to insert, replace, or delete nodes from XML data. Here is an example of using XML DML to replace the value of an attribute in XML documents stored in a xml type column.

DECLARE @old_tkr nvarchar(35), @new_tkr nvarchar(35)
-- SET @old_tkr = 'ABC'
-- SET @new_tkr = 'XYZ'
;WITH XMLNAMESPACES(DEFAULT 'http://schemas.guosen.com.hk/sqlserver/2014/04/guosen-archive/AnalystReportMetadata')
UPDATE AnalystReportMetaData SET
metadataXml.modify('replace value of (/analystreport/stocks/stock[@code=sql:variable("@old_tkr")]/@code)[1] with sql:variable("@new_tkr")')
WHERE metadataXml.exist('/analystreport/stocks/stock[@code=sql:variable("@old_tkr")]') = 1

Sample XML:
<analystreport xmlns="http://schemas.guosen.com.hk/sqlserver/2014/04/guosen-archive/AnalystReportMetadata">
  <stocks>
    <stock guosenNum="1617" code="1302 HK" countryCode="CN" marketCode="HK"></stock>
  </stocks>
</analystreport>

Linked Server options: RPC and RPC Out

A linked server is a mechanism that allows a query to be submitted on one server and then have all or part of the query redirected and processed on another SQL Server instance, and eventually have the results set sent back to the original server to be returned to the client. If the linked server is defined as an instance of SQL Server, remote stored procedures can be executed. In order to enable remote procedure call, you must enable the relevant linked server option.
By the way, there're two options with similar names, RPC, and RPC Out. Do you need both of them or only one of them set to True?
The first RPC setting is mainly for a legacy feature called Remote Server. You probably will NOT be using remote servers in SQL Server 2005 -SQL Server 2014 versions. Its default is false.
The second RPC Out setting is very relevant to linked servers on SQL Server 2005 -SQL Server 2014 versions. It enables remote procedure call to the linked server instance. Its default is false.
You can try to create a linked server, then run the following remote procedure call:
EXEC [linkedserver].master.dbo.sp_helpdb
These kind of remote stored procedure calls will be blocked unless RPC OUT is set to True.
Msg 7411, Level 16, State 1, Line 1
Server 'linkedserver' is not configured for RPC.
You can set it to True in the linked server's properties (a right click menu in from the Linked Server Name in SQL Server Management Studio).

Then you should able to run the remote procedure call now.
Summary:
1. The RPC option is NOT relevant, so just keep it set to false.
2. The RPC Out option is required for enabling remote procedure call, so set it to true if required.