Architecture Reads

Zen of Architecture
Amazon Architecture
Front Line Internet Analytics at Amazon.com
A/B testing at Amazon and Microsoft
Beyond A/B testing: hypothesis testing for startups
Software Testing Cage Match: Amazon.com vs. Microsoft 
Amazon recommendations system
Amazon Retail Analytics
Speed vs. Certainty in A/B Testing
The Canonical Cloud Architecture
Google Architecture
How Google Serves Data from Multiple Datacenters
Improving Global Application Performance, continued: GSLB with EC2
What The Heck Is Cloud Computing?
What The Heck Is Cloud Computing? – A Brief Re-Look

Virtual Stress-free Testing in the Cloud

View more PowerPoint from guest2e9c5f40

Happy Learning!!!

Blocking Vs Deadlocking

Recently I took a session with Roji on Blocking Vs Deadlocking. Couple of notes I am sharing it here..

Blocking - Blocking is a state when a transaction require a lock on a resource on which an incompatible lock is already placed by another transaction. Decreases transactional throughput, increases wait time

Locking - SQL Server ensures Transactional Consistency using two ways.
1. Versioning – Using Snapshot Isolation Levels
2. Locking – Implementing short lived transactional locks on resources. Provides consistency. Prevents conflict
Lock Types

• Shared(S) – Indicates resource is being read by process
• Update (U) – Indicates process is going to update resource. Only one update lock can exist at a time for a resource. It gets converted to X Lock while updating
• Exclusive(X) – Prevent multiple process attempting on same resource
• Intent – Used to Establish Lock Hierarchy.
• Schema – Prevent DML when DDL is executing
• Key Range – Protects range of key during Serializable operations

Lock Hierarchy
To update a particular row below is the lock hierarchy

• Database - Shared Lock
• Table - IX
• Page - IX
• ROW  - X

Lock Escalation Results as below

• Database - Shared Lock
• Table - X Lock

Deadlocking

• Deadlock is a blocking situation where two transactions are holding locks and waiting to acquire lock on the resource held by the other transaction
• Deadlocks are automatically caught by SQL Server Database Engine. Once detected, the Database Engine chooses one of the transactions as the deadlock victim and lets the other transaction complete its process.
• Default search interval is 5 seconds. 

SQL Puzzles - Interesting Ones

Question#1: When a composite index is created on a table. Lets see the scenarios of Select clauses with the columns and see whether index is being used
Table TestTable
Columns - A,B,C,D
Index - IX_ABC
Lets see three SELECT queries based on indexed columns and check whether index is being used

STEP 1
IF OBJECT_ID ('TestTable1') IS NOT NULL DROP TABLE TestTable1
CREATE TABLE TestTable1
(
    Number INT IDENTITY(1,1) PRIMARY KEY,
    A VARCHAR(20) NOT NULL,
    B VARCHAR(20) NOT NULL,
    C VARCHAR(20) NOT NULL,
    D VARCHAR(20) NOT NULL
)
STEP 2
DECLARE @I INT
SET @I = 100
WHILE 1 = 1
    BEGIN
       SET @I = @I + 1
       INSERT INTO TestTable1(A,B,C,D)
       VALUES ((CONVERT(CHAR(5),@I)+'A'),(CONVERT(CHAR(5),@I)+'B'),
       (CONVERT(CHAR    (5),@I)+'C'),(CONVERT(CHAR(5),@I)+'D'))
       IF @I=10000
            BREAK;
       END

STEP 3
CREATE INDEX IX_ABC ON TestTable1(A,B,C)

Queries and Index Used
SELECT Number FROM TestTable1 WHERE A = '1000 A' AND B = '1000 B' AND C = '1000 C'
INDEX SEEK

SELECT Number FROM TestTable1 WHERE C = '1000 C'
INDEX SCAN

SELECT Number FROM TestTable1 WHERE C = '1000 C' AND B = '1000 B'
INDEX SCAN

SELECT Number FROM TestTable1 WHERE A = '1000 A' AND C = '1000 C'
INDEX SEEK

SELECT Number FROM TestTable1 WHERE A = '1000 A' AND B = '1000 B'
INDEX SEEK

SELECT Number FROM TestTable1 WHERE A = '1000 A'
INDEX SEEK

Hint: As long as your search arguement has the index columns and the same order A,B,C or A,B or A it will use the Index IX_ABC
Question#2 - What is the output for below query
Query
SELECT top 10 'abc' from sys.objects
WHERE 1>2
HAVING 1<2;
Answer abc
Reference - Please refer link

Query
SELECT 'No rows'
WHERE 1>2
HAVING 1<2;
Answer No Rows

Question#3 - What is output for below query
use tempdb
CREATE TABLE A(ID INT IDENTITY(1,1), Name VARCHAR(10))
CREATE TABLE B(ID INT IDENTITY(1,1), Name VARCHAR(10))

INSERT INTO A(Name)
VALUES('1')
INSERT INTO B(Name)
VALUES('1'),('_1'),('%1'),('[%]1'),('[_]1')

Select * from A JOIN B
ON 'x'+A.Name LIKE B.Name

Have fun...Happy Reading....