Objective:
The objective of
this POC is to understand the time delay or the time to get committed for
insert statement for the database which is configured with AlwaysOn synchronous
and async failover.
Note: Synchronous
commit for a insert/update query will first harden the transaction in the
secondary AG and based on the acknowledgement the primary will commit.
Server
configuration:
Test
Case:
1.
We are
going to create one database named Sync_POC_WithoutAG is a standalone DB which
is not involved in AlwaysOn.
2.
In the
second set we are going to create one database named Sync_POC which will be
involved in AlwaysOn with Synchoronus failover on the secondary server with a
readable copy.
3.
Both
(#1 and #2) DBs, we are going to create a table with same structure and insert
data.
4.
We are
planning to insert the data with different record counts.
5.
The
above insert will happen based on a loop and find the time difference between
both.
6.
The
same test has been observed for AlwaysOn AG with async mode.
Setup
AlwaysOn with Sync:
Setup AlwaysOn with Async:
Create table script:
CREATE TABLE [dbo].[Name](
[id]
[numeric](10, 0) IDENTITY(1,1) NOT NULL,
[name]
[varchar](50) NULL,
CONSTRAINT [PK_Name]
PRIMARY KEY CLUSTERED
(
[id]
ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE
= OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
Simulation query:
DECLARE @StartTime datetime,@EndTime datetime
SELECT @StartTime=GETDATE()
DECLARE @intLimit AS INT = 100000
DECLARE @intCounter AS INT = 1
WHILE @intCounter <= @intLimit
BEGIN
INSERT INTO Name VALUES ('Name1')
SET @intCounter = @intCounter + 1
END
SELECT @EndTime=GETDATE()
SELECT DATEDIFF(ms,@StartTime,@EndTime) AS [Duration in
milliseconds]
Sample observations for standalone DB:
Sample observations for AlwaysOn Sync DB:
Sample observations for AlwaysOn Async DB:
Analysis:
|
No of records inserted
|
Normal DB (milli secs)
|
Sync AG DB (milli secs)
|
Sync AG DB (milli secs) NIC card
|
Async AG DB (milli secs)
|
|
100
|
0.106
|
0.203
|
0.203
|
0.103
|
|
250
|
0.266
|
0.541
|
0.541
|
0.26
|
|
500
|
0.573
|
1.071
|
1.071
|
0.516
|
|
750
|
0.716
|
1.643
|
1.643
|
0.85
|
|
1000
|
0.971
|
1.963
|
1.963
|
1.3
|
|
2500
|
2.446
|
4.963
|
4.963
|
2.94
|
|
5000
|
4.746
|
12.276
|
5.41
|
|
|
7500
|
7.411
|
15.506
|
15.502
|
8.683
|
|
10000
|
9.696
|
20.813
|
20.811
|
11.26
|
|
25000
|
25.161
|
50.411
|
50.41
|
25.813
|
|
50000
|
50.523
|
107.312
|
107.31
|
53.97
|
|
75000
|
74.603
|
155.45
|
155.45
|
82.406
|
|
100000
|
98.99
|
214.82
|
214.81
|
108.016
|
Conclusion:
Time taken
= (Avg. time of Sync (or) Async AG DB /
Avg. time of Normal DB)
From the above analysis, the time taken to
do an insert in AlwaysOn Sync would be twice (2.125 milli-secs) the time as
compared to a standalone (or) normal DB.
Same way, to do an insert in AlwaysOn Async
would be bit time consuming (1.091 milli-secs) compared to the DB which is not part of AG.
Time taken for insert query:
Normal DB (without AlwaysOn enabled) = 1
milli sec
AlwaysOn Async = 1.1 milli sec
AlwaysOn Sync = 2.1 milli sec
AlwaysOn Sync with dedicated NIC card =
2.1 milli sec
Note:
All the above observations are based on the primary DB commit.
No comments:
Post a Comment