sp_whoisactive2.txt

디스크 읽기 최대성능을 볼 때 쓸만함 

BACKUP DATABASE tt

TO DISK = 'NUL:'
,DISK   = 'NUL:'
,DISK   = 'NUL:'
,DISK   = 'NUL:'
,DISK   = 'NUL:'
,DISK   = 'NUL:'
,DISK   = 'NUL:'
,DISK   = 'NUL:'
 WITH NO_COMPRESSION
,BUFFERCOUNT = 2200

backup log tt to
disk ='NUL'
, disk = 'NUL'

1) call pattern 

jTDS 로 PreparedStatement 를 호출하면 sp_prepare sp_execute 를 호출한다. 

MS JDBC 는 sp_prepexec 로 위 두 단계를 하나로 줄여서 실행한다. 한마디로 서버 클라이언트간 round-trip 횟수를 줄여 성능을 높일 수 있다. connectionString 에 prepareSQL=2 라고 옵션을 주면 sp_executesql 로 동작해 한번에 돌릴 수 있고 parameter snipping 도 가능하다. 그런데, 이건 좀 다른 동작 방식이다. 항상 sql 이 날아가야 한다.  

2) parameter snipping

jTDS 로 PreparedStatement 를 사용하면 파라메터 스니핑을 사용하지 못하고 

JDBC 를 사용하면 파라메터 스니핑이 가능하다.....

그런데 감당하지 못하겠으면 SQL Server 레벨에서 disable 가능하다 

Parameter sniffing can be disabled using the documented and supported trace flag 4136. This trace flag is also supported for per-query use via the QUERYTRACEON hint. Both apply from SQL Server 2005 Service Pack 4 onward (and slightly earlier if you are willing to apply cumulative updates to Service Pack 3).

sql server 에서 파라메터 스니핑을 하는 경우(MS JDCB)

             <ParameterList>

              <ColumnReference Column="@P0" ParameterCompiledValue="(1)" ParameterRuntimeValue="(1)" />

            </ParameterList>

파라메터 스니핑을 못하는 경우 (jTDS)

             <ParameterList>

              <ColumnReference Column="@P0" ParameterRuntimeValue="(1)" />

            </ParameterList>

3) applicationIntent 기능을 MS JDBC 만 지원한다. 4.0 이상부터

고 가용성 기능을 쓰려면 JDBC 로 가야한다. 

기타)

unicode false 옵션은 주의해서 셋팅해라 

SQL Server Extended Events (xevent) are great to troubleshoot many issues including performance issues or other targeted scenarios.  But we keep seeing users misusing them to negatively impact their systems.

Here is a latest example.  We had a customer who called our support for an issue where the same query ran fast in test but ran slow in production.  In fact, the query ‘never’ finished in production in a sense that they waited for 30 minutes or more but couldn’t get it to finish.  But the same query finished in seconds in test.

The query was actually a large batch that contains over 90k lines of code with many statements with size of about 800k.   Our initial effort focused on comparing the differences between the two servers.  But there weren’t many differences.   We even eliminated database as a factor. They restored database from production to test and issue went away in test.

Through some troubleshooting, we discovered even parsing the query took a long time in production.    But we simply couldn’t figure out what was going on because the statements in the batch were very simple inserts.   So we took some user dumps and analyzed the call stacks.  Finally we realized that the XEvent was involved.   Every dump we got showed the server was producing XEvent.  It turned out their developers enabled the some xevents which can cause high overhead by accident inproduction.

So we got their Xevents being captured (screen shot below).  Among those, there were scan_started, scan_stopped, wait_info etc.   It was generate a million events every minute without anyone else running the system.  In addition to that, this customer captured sql_text for all the events. Basically the same 800k batch text would be captured for every event including wait_info etc

None of the events mentioned (scan_started, scan_stopped, wait_info ) are suited for long term capture.   For this specific scenario, it was wait_info that hurt them most (combined with sql_text being included).   Wait_info is produced whenever there is a scheduler yield or wait is finished.    Because customer’s batch is very large, SQL needs to play nice and yields frequently.  so the event gets triggered very frequently.  That’s why so many events were generated and overhead led to slowdown.

Demo (tested on SQL Server 2012, 2016)

create and enable Xevents

CREATE EVENT SESSION [test_xevent] ON SERVER
ADD EVENT sqlos.wait_info(
ACTION(package0.collect_system_time,sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.context_info,sqlserver.database_name,sqlserver.nt_username,sqlserver.plan_handle,sqlserver.query_hash,sqlserver.query_plan_hash,sqlserver.session_id,sqlserver.session_nt_username,sqlserver.sql_text)    ),
ADD EVENT sqlos.wait_info_external(
ACTION(package0.collect_system_time,sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.context_info,sqlserver.database_name,sqlserver.nt_username,sqlserver.plan_handle,sqlserver.query_hash,sqlserver.query_plan_hash,sqlserver.session_id,sqlserver.session_nt_username,sqlserver.sql_text)    )
ADD TARGET package0.event_counter,
ADD TARGET package0.event_file(SET filename=N’c:\temp\test_xevent.xel’)
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=ON,STARTUP_STATE=ON)
GO

alter event session [test_xevent] on server state = start

in SSMS, duplicate “INSERT INTO t VALUES  (null,null,null)” 50,000 times and try parsing the query.  it will take several minutes.  but if you disable the Xevent session, the entire batch will parse in seconds.

If you have to capture wait_info for large batch like this one, consider taking out sql_text.   It will be hard to look at the trace afterwards without it.  But hopefully, it is a controlled environment and you know which session you are troubleshooting and filter on that.

Jack Li |Senior Escalation Engineer | Microsoft SQL Server

twitter| pssdiag |Sql Nexus

https://blogs.msdn.microsoft.com/ssma/2011/02/07/mysql-to-sql-server-migration-how-to-use-ssma/

MySQL to SQL Server Migration: How to Use SSMA

February 7, 2011By Bill Ramos

Bill Ramos

Indigo Slate

Partner

12,865 Points 12 2 2

Recent Achievements

First Marked Answer First Helpful Vote Blog Party Starter

View Profile

29

• 0
• 0
• 0

mstsc shadow:2

ssd smart path

https://support.microsoft.com/en-us/kb/2160852

https://blogs.technet.microsoft.com/koalra/2012/01/15/30-windows-server-51/

http://www.overtop.co.kr/136

가용성 그룹에 대한 읽기 전용 라우팅 구성(SQL Server)
Configure Read-Only Routing for an Availability Group
https://msdn.microsoft.com/library/hh710054.aspx

읽기전용 라우팅을 언제 사용해야 적절할까는 고민할 부분이다.

[환경]

AG1㈜, AG2(보조), AG3(보조) 의 복제복이 존재한다.

[Action Plan]

A. 필수 구성 요소

가용성 그룹 수신기 있어야 한다.

읽기전용 보조 복제복이 있어야 한다.

B. 적용은 T-SQL 또는 PowerShell 로만 적용이 가능하다.

-- 1. 읽기전용 라우팅 설정 하기

/*

읽기전용이란 이름으로 정의하였기 때문에 보조복제본의 연결은 모두 ALL 이 아닌 READ_ONLY 이어야 한다.

아래 설정변경은 주복제본에서만 수행이 된다.

*/

use master

go

ALTER AVAILABILITY GROUP AGName

MODIFY REPLICA ON N'AG1' WITH (SECONDARY_ROLE(ALLOW_CONNECTIONS = READ_ONLY))

ALTER AVAILABILITY GROUP AGName

MODIFY REPLICA ON N'AG2' WITH (SECONDARY_ROLE(ALLOW_CONNECTIONS = READ_ONLY))

ALTER AVAILABILITY GROUP AGName

MODIFY REPLICA ON N'AG3' WITH (SECONDARY_ROLE(ALLOW_CONNECTIONS = READ_ONLY))

ALTER AVAILABILITY GROUP AGName

MODIFY REPLICA ON

N'AG1' WITH

(SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://AG1.overtop.local:1433'));

ALTER AVAILABILITY GROUP AGName

MODIFY REPLICA ON

N'AG2' WITH

(SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://AG2.overtop.local:1433'));

ALTER AVAILABILITY GROUP AGName

MODIFY REPLICA ON

N'AG3' WITH

(SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://AG3.overtop.local:1433'));

ALTER AVAILABILITY GROUP AGName

MODIFY REPLICA ON

N'AG1' WITH

(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('AG2','AG3','AG1')));

-- AG1 이 주 복제본일경우에 라우팅은 AG3 로 연결되며, AG3 가 접속이 안되는 경우에는 AG2 로 자동연결 된다.

ALTER AVAILABILITY GROUP AGName

MODIFY REPLICA ON

N'AG2' WITH

(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('AG3','AG1','AG2')));

ALTER AVAILABILITY GROUP AGName

MODIFY REPLICA ON

N'AG3' WITH

(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('AG1','AG2','AG3')));

GO

-- 2. 설정 확인하기.

SELECT * FROM sys.availability_read_only_routing_lists

GO

SELECT ag.name as "Availability Group", ar.replica_server_name as "When Primary Replica Is",

rl.routing_priority as "Routing Priority", ar2.replica_server_name as "RO Routed To",

ar.secondary_role_allow_connections_desc, ar2.read_only_routing_url

FROM sys.availability_read_only_routing_lists rl

inner join sys.availability_replicas ar on rl.replica_id = ar.replica_id

inner join sys.availability_replicas ar2 on rl.read_only_replica_id = ar2.replica_id

inner join sys.availability_groups ag on ar.group_id = ag.group_id

ORDER BY ag.name, ar.replica_server_name, rl.routing_priority

C. 라우팅 연결 테스트

-K , -d 옵션을 필수 입력해야 확인이 가능하다.
주 복제본에 따라 라우팅 순서를 정의할 수 있다. 위 코드 상에서 AG2가 주 복제복일경우에는 AG3 리턴한다.

sqlcmd -S Aglistener.overtop.local,62000 -E -K ReadOnly -d AGDB1 -Q "select @@servername"

[참고문서]

Modifying AlwaysOn Read Only Routing Lists
http://blogs.msdn.com/b/alwaysonpro/archive/2014/01/22/modifying-alwayson-read-only-routing-lists.aspx

192.168.137.5에 연결하는 중...

메시지 41158, 수준 16, 상태 3, 줄 65

로컬 가용성 복제본을 가용성 그룹 'poc_ag'에 조인하지 못했습니다. 작업 중 SQL Server 오류 41106이(가) 발생하여 작업이 롤백되었습니다. 자세한 내용은 SQL Server 오류 로그를 확인하십시오. 나중에 오류 원인을 해결한 후 ALTER AVAILABILITY GROUP JOIN 명령을 다시 시도하십시오.

192.168.137.5에서 연결을 끊는 중...

쿼럼 구성

클라이언트가 ad 도메인에 없을경우 

클라이언트의 host 에 도메인 이름을 명시적으로 쓰던지 

아래와 같이 리스너 구성을 명시적으로 ip 를 기술한다. 

ALTER AVAILABILITY GROUP poc_ag

MODIFY REPLICA ON N'mhv1' WITH (SECONDARY_ROLE(ALLOW_CONNECTIONS = READ_ONLY))

ALTER AVAILABILITY GROUP poc_ag

MODIFY REPLICA ON N'mhv2' WITH (SECONDARY_ROLE(ALLOW_CONNECTIONS = READ_ONLY))

ALTER AVAILABILITY GROUP poc_ag

MODIFY REPLICA ON N'chv1' WITH (SECONDARY_ROLE(ALLOW_CONNECTIONS = READ_ONLY))

ALTER AVAILABILITY GROUP poc_ag

MODIFY REPLICA ON

N'mhv1' WITH (SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://192.168.137.4:1433'));

ALTER AVAILABILITY GROUP poc_ag

MODIFY REPLICA ON

N'mhv2' WITH (SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://192.168.137.5:1433'));

ALTER AVAILABILITY GROUP poc_ag

MODIFY REPLICA ON

N'chv1' WITH (SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://192.168.137.6:1433'));

ALTER AVAILABILITY GROUP poc_ag

MODIFY REPLICA ON

N'mhv1' WITH

(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('chv1','mhv2','mhv1')));

-- AG1 이 주 복제본일경우에 라우팅은 AG3 로 연결되며, AG3 가 접속이 안되는 경우에는 AG2 로 자동연결 된다.

ALTER AVAILABILITY GROUP poc_ag

MODIFY REPLICA ON

N'mhv2' WITH

(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('chv1','mhv1','mhv2')));

ALTER AVAILABILITY GROUP poc_ag

MODIFY REPLICA ON

N'chv1' WITH

(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('mhv2','mhv1','chv1')));

GO

jdbc test

package com.company;
import java.sql.*;
public class Main {

    public static void RunQuery() throws ClassNotFoundException, SQLException, InterruptedException
    {

        String url = "jdbc:sqlserver://192.168.137.44;DatabaseName=cafedb;ApplicationIntent=ReadOnly";
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
        conn = DriverManager.getConnection(url, "test", "1234!@#$");
        stmt = conn.createStatement();
        rs = stmt.executeQuery("select @@servername ServerName");

        rs.next();
        System.out.println(rs.getString("ServerName"));
        Thread.sleep(500);

        rs.close();
        stmt.close();
        conn.close();
    }

    public static void main(String[] args)
    {
        Integer i = 0;
        while (1 == 1)
        {
            i++;
            try
            {
                Main.RunQuery();
                System.out.println(i);
            }
            catch(Exception e)
            {
                System.out.println(e.getMessage());
            }
        }
    }
}

10퍼센트 처리되었습니다.

20퍼센트 처리되었습니다.

30퍼센트 처리되었습니다.

40퍼센트 처리되었습니다.

50퍼센트 처리되었습니다.

60퍼센트 처리되었습니다.

70퍼센트 처리되었습니다.

80퍼센트 처리되었습니다.

90퍼센트 처리되었습니다.

100퍼센트 처리되었습니다.

파일 1에서 데이터베이스 'userDatabase', 파일 'userDatabase_Data'에 대해 0개의 페이지를 처리했습니다 .

파일 1에서 데이터베이스 'userDatabase', 파일 'userDatabase_Data2'에 대해 0개의 페이지를 처리했습니다 .

파일 1에서 데이터베이스 'userDatabase', 파일 'userDatabase_Data3'에 대해 0개의 페이지를 처리했습니다 .

파일 1에서 데이터베이스 'userDatabase', 파일 'userDatabase_GUEST'에 대해 0개의 페이지를 처리했습니다 .

파일 1에서 데이터베이스 'userDatabase', 파일 'userDatabase_z1'에 대해 0개의 페이지를 처리했습니다 .

파일 1에서 데이터베이스 'userDatabase', 파일 'userDatabase_Log'에 대해 234355개의 페이지를 처리했습니다 .

메시지 3456, 수준 16, 상태 1, 줄 1

페이지 (1:828275), 데이터베이스 'userDatabase'(데이터베이스 ID 5)에서 트랜잭션 ID (16:395253956)에 대한 로그 레코드 (3529369:96028:38)을(를) 다시 실행할 수 없습니다. 페이지: LSN = (3529369:95946:19), 유형 = 2. 로그: OpCode = 3, 컨텍스트 19, PrevPageLSN: (3529369:95244:5). 데이터베이스 백업에서 복원하거나 데이터베이스를 복구하십시오.

메시지 9004, 수준 16, 상태 6, 줄 1

데이터베이스 'userDatabase'의 로그를 처리하는 동안 오류가 발생했습니다. 가능하면 백업을 사용하여 복원하십시오. 백업이 없을 경우 로그를 다시 만들어야 합니다.

메시지 3013, 수준 16, 상태 1, 줄 1

RESTORE LOG이(가) 비정상적으로 종료됩니다.

내용을 보니 현재 처리중인 LSN (3529369:95946:19) 과 이전 LSN (3529369:95244:5) 이 맞지 않아 나는 에러인데....왜 이런 현상이 일어나는지는 이해할 수 없네...

테스트가 필요한 부분은 데이터베이스 커럽션이 있는지

차등백업 후 로그백업을 복구하면 에러없이 진행 가능한지 등이 있겠다. 

최후의 수단으로 CONTINUE_AFTER_ERROR 가 있을 수 있다. 

읽어볼만한 글

http://dba.stackexchange.com/questions/93473/unable-to-restore-error-3456

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/36dd0107-ceca-4d48-8523-3c5624c135b2/msg-3456-level-16-state-1-could-not-redo-log-record?forum=sqldatabaseengine

https://www.mssqltips.com/sqlservertip/3209/understanding-sql-server-log-sequence-numbers-for-backups/

복사본

shareimprove this answer

edited Feb 23 '15 at 22:02

answered Feb 23 '15 at 14:49 Thomas Stringer 30.2k562109

Problem

This tip describes SQL Server Log Sequence Numbers (LSNs) and how the sequence numbers link full, differential and transaction log backups.  We will look at sample backups and how these LSN values can be read from the backup files to determine the restore path.

Solution

This tip is the continuation from this tip, Different Ways to Restore a SQL Server Database, and utilizes the same database creation and backup scripts to explain how the SQL Server full, differential and transaction log backup chain is mapped between each backup type. If you want to follow along, please read this first tip and setup your database and backups.

RESTORE HEADERONLY

When restoring a database, the initial database RESTORE sequence must begin from a FULL database backup. A database RESTORE sequence cannot begin with a differential file backup or transaction log backup. When restoring databases there are four important LSNs: FirstLSN, LastLSN, CheckpointLSN and DatabaseBackupLSN.  These values can be retrieved from a SQL Server backup file using the RESTORE HEADERONLY command.

You can use RESTORE HEADERONLY to retrieve the backup header information for each backup file on disk as shown below.

USE [master]
RESTORE HEADERONLY FROM DISK = N'C:\Temp\F1.BAK'
RESTORE HEADERONLY FROM DISK = N'C:\Temp\T1.TRN'
RESTORE HEADERONLY FROM DISK = N'C:\Temp\T2.TRN'
RESTORE HEADERONLY FROM DISK = N'C:\Temp\D1.BAK'

I have collated the FirstLSN, LastLSN, CheckpointLSN and DatabaseBackupLSN for these database backup files (Database setup script) into an Excel spreadsheet as shown below. The full scripts to retrieve the LSNs from all backup files are found at the end of this tip.

"Column A" is the backup type performed at a point-in-time (i.e. F1 = first full backup, T3 = third transaction log backup, D2 = second differential backup).

Some attributes for the full database backup LSNs are:

•  The very first full database backup will always have a DatabaseBackupLSN of zero
•  The very first full database backup's FirstLSN will be the same as the CheckpointLSN

Some attributes of a differential database backup LSNs are:

• The DatabaseBackupLSN value for the differential backup identifies the full database backup that is required in order to apply the differential database backup
• The DatabaseBackupLSN value for the differential backup will match its base full database backup CheckpointLSN
• The CheckpointLSN maps to the CheckpointLSN of the first transaction log backup after the differential backup

Some attributes of a transaction log backup LSNs are:

• A LSN uniquely identifies every record in a transaction log backup
• The FirstLSN and CheckpointLSN of the first transaction log backup is also the first full database backup CheckpointLSN if the backup is taken when the database is idle and no replication is configured
• The transaction log LSN chain is not affected by a full or differential database backup
• LSN are sequential in nature. A higher LSN value indicates a later point in time

Understanding LSN Mapping Examples in SQL Server

Below are 3 sections that describe the LSN mapping for:

• Full database backup LSN to Transaction Log backup LSN
• Full database backup LSN to Differential database backup LSN
• Differential database backup LSN to Transaction Log backup LSN

Full database backup LSN to Transaction Log backup LSN

• FirstLSN identifies the first log record included in the backup
• LastLSN includes log records up to, but not including this LSN
• When planning which transaction log backup to use to roll forward, the LastLSN + 1 of the Full database backup will fall in between the FirstLSN and LastLSN of its subsequent transaction log backup
• In the example above, Full database backup LastLSN 34000000025600001 falls in between transaction log backup T1 FirstLSN 34000000016000100 and LastLSN 34000000028800000. Applying T1 after F1 will succeed, applying T2 or T3 after F1 will result in an error.
• A transaction log backup's LastLSN is the FirstLSN in the subsequent transaction log backup chain. In the example above, transaction log backup T1 LastLSN 34000000028800000 is the FirstLSN of transaction log backup T2, transaction log backup T2 LastLSN 34000000030400000 is the FirstLSN of transaction log backup T3 and so on

Full database backup LSN to Differential database backup LSN

• A differential database backup can only be applied ONCE to a restored full database backup that has a CheckpointLSN value that is equal to the differential backup DatabaseBackupLSN

Differential database backup LSN to Transaction Log backup LSN

• A differential backup LastLSN + 1 will be in between the FirstLSN and LastLSN of its subsequent transaction log backup
• In the example above, Full database backup LastLSN 34000000035200001 falls in between transaction log backup T3 FirstLSN 34000000030400000 and LastLSN 34000000036000000. Applying T3 after D1 will succeed, applying T4 or another transaction log backup will result in an error.

Script to Retrieve Backup LSNs

This is the full script to retrieve the LSNs for all database backup files created from this tip.

USE [master]
RESTORE HEADERONLY FROM DISK = N'C:\Temp\F1.BAK'
RESTORE HEADERONLY FROM DISK = N'C:\Temp\T1.TRN'
RESTORE HEADERONLY FROM DISK = N'C:\Temp\T2.TRN'
RESTORE HEADERONLY FROM DISK = N'C:\Temp\D1.BAK'
RESTORE HEADERONLY FROM DISK = N'C:\Temp\T3.TRN'
RESTORE HEADERONLY FROM DISK = N'C:\Temp\T4.TRN'
RESTORE HEADERONLY FROM DISK = N'C:\Temp\D2.BAK'
RESTORE HEADERONLY FROM DISK = N'C:\Temp\T5.TRN'
RESTORE HEADERONLY FROM DISK = N'C:\Temp\F2.BAK'
RESTORE HEADERONLY FROM DISK = N'C:\Temp\T6.TRN'
RESTORE HEADERONLY FROM DISK = N'C:\Temp\T7.TRN'
RESTORE HEADERONLY FROM DISK = N'C:\Temp\D3.BAK'
RESTORE HEADERONLY FROM DISK = N'C:\Temp\T8.TRN'

Next Steps

• Take a look at this tip that shows different ways to restore a SQL Server database -http://www.mssqltips.com/sqlservertip/3049/different-ways-to-restore-a-sql-server-database/
• Learn more about Log Sequence Numbers and Restore Planning - http://technet.microsoft.com/en-us/library/ms190729(v=sql.105).aspx