thread 완벽 정리!
http://www.albahari.com/threading/
위 sql 을 받아서 주석중 a 한개만 지우면 read i/o 가 1602 에서 13 으로 줄어듭니다.
반면, while 문이 아닌 구간은 그렇게 큰 영향이 없습니다.
위 파일은 주석이 훨씬 많은데도 i/o 에 별 영향이 없습니다.
위 현상은 sql 2012 2016 등에서 재현 가능합니다.
EXEC master.dbo.xp_create_subdir 'd:\XE'
CREATE EVENT SESSION [SQLTAG_BASELINE_01_B_2012] ON SERVER
ADD EVENT sqlos.WAIT_INFO(
WHERE ([package0].[greater_than_equal_uint64]([duration],(3)))),
ADD EVENT sqlos.WAIT_INFO_EXTERNAL(
WHERE ([package0].[greater_than_equal_uint64]([duration],(3)))),
ADD EVENT sqlserver.RPC_COMPLETED(SET collect_statement=(1)
ACTION(sqlserver.query_hash,sqlserver.sql_text)
WHERE ([package0].[greater_than_equal_uint64]([duration],(3000)))),
ADD EVENT sqlserver.SP_STATEMENT_COMPLETED(SET collect_object_name=(1),collect_statement=(1)
ACTION(sqlserver.query_hash,sqlserver.sql_text)
WHERE ([package0].[greater_than_equal_int64]([duration],(3000)))),
ADD EVENT sqlserver.SQL_BATCH_COMPLETED(SET collect_batch_text=(1)
ACTION(sqlserver.query_hash,sqlserver.sql_text)
WHERE ([package0].[greater_than_equal_uint64]([duration],(3000)))),
ADD EVENT sqlserver.SQL_STATEMENT_COMPLETED(SET collect_parameterized_plan_handle=(0),collect_statement=(1)
ACTION(sqlserver.query_hash,sqlserver.sql_text)
WHERE ([package0].[greater_than_equal_int64]([duration],(3000))))
ADD TARGET package0.event_file(SET filename=N'D:\XE\SQLTAG_BASELINE_01_B_2012.XEL',max_file_size=(100),max_rollover_files=(1000))
WITH (MAX_MEMORY=10240 KB,EVENT_RETENTION_MODE=ALLOW_MULTIPLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=5 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=PER_CPU,TRACK_CAUSALITY=ON,STARTUP_STATE=ON)
GO
CREATE EVENT SESSION [SQLTAG_BLOCKED_REPORTS_03_R_2012] ON SERVER
ADD EVENT sqlserver.BLOCKED_PROCESS_REPORT(
ACTION(package0.callstack,sqlos.cpu_id,sqlos.task_time,sqlserver.client_app_name,sqlserver.client_connection_id,sqlserver.client_hostname,sqlserver.client_pid,sqlserver.database_id,sqlserver.session_id,sqlserver.session_nt_username,sqlserver.sql_text,sqlserver.tsql_stack,sqlserver.username))
ADD TARGET package0.event_file(SET filename=N'D:\XE\SQLTAG_BLOCKED_REPORTS_03_R_2012.XEL',max_file_size=(100),max_rollover_files=(10))
WITH (MAX_MEMORY=10240 KB,EVENT_RETENTION_MODE=ALLOW_MULTIPLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=5 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=PER_CPU,TRACK_CAUSALITY=OFF,STARTUP_STATE=ON)
GO
CREATE EVENT SESSION [SQLTAG_ERROR_02_E_2012] ON SERVER
ADD EVENT sqlserver.ERROR_REPORTED(
ACTION(sqlos.task_time,sqlserver.client_app_name,sqlserver.client_connection_id,sqlserver.client_hostname,sqlserver.client_pid,sqlserver.database_id,sqlserver.database_name,sqlserver.is_system,sqlserver.nt_username,sqlserver.plan_handle,sqlserver.session_id,sqlserver.session_nt_username,sqlserver.sql_text,sqlserver.tsql_frame,sqlserver.tsql_stack,sqlserver.username)
WHERE ([PACKAGE0].[NOT_EQUAL_INT64]([ERROR_NUMBER],(8153)) AND [PACKAGE0].[NOT_EQUAL_INT64]([ERROR_NUMBER],(5707)) AND [ERROR_NUMBER]<>(5701) AND [ERROR_NUMBER]<>(8153)))
ADD TARGET package0.event_file(SET filename=N'D:\XE\SQLTAG_ERROR_02_E_2012.XEL',max_file_size=(100),max_rollover_files=(10))
WITH (MAX_MEMORY=10240 KB,EVENT_RETENTION_MODE=ALLOW_MULTIPLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=5 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=PER_CPU,TRACK_CAUSALITY=OFF,STARTUP_STATE=ON)
GO
ALTER EVENT SESSION [SQLTAG_BASELINE_01_B_2012]
ON SERVER
STATE = start;
ALTER EVENT SESSION [SQLTAG_BLOCKED_REPORTS_03_R_2012]
ON SERVER
STATE = start;
ALTER EVENT SESSION [SQLTAG_ERROR_02_E_2012]
ON SERVER
STATE = start;
EXEC SP_CONFIGURE 'show advanced options', 1
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC SP_CONFIGURE 'blocked process threshold (s)', 2
GO
RECONFIGURE WITH OVERRIDE
GO
https://catalog.update.microsoft.com/v7/site/Search.aspx?q=SQL%20SERVER%202012%20SP3%20CU1
Everyone has probably heard that Windows Server 2012will add around 2,300 new Windows PowerShellcommands. Today, however, PowerShell still works with just a subset of the Windows Server management functions. One area that it doesn't directly touch is the ability to configure Windows Server networking and firewall functions, for which you need to use the venerable netsh commands. Even with the advent of PowerShell, netsh has continued to evolve, and it's particularly useful for configuring Windows Server Core.
Related: Network Configuration Tasks with Netsh
In this column, I'll show you 10 handy netsh commands you can use to query and configure your Windows Firewall settings. It's worth noting that you can call these netsh commands from within your PowerShell scripts.
10. Query firewall rules: One of the first things you'll probably need to use netsh for is to discover Windows Firewall's current configuration properties. You can query Windows Firewall settings using the following netsh command:
netsh advfirewall firewall show rule name=all
9. Enable and disable Windows Firewall: It's typically a best practice to leave Windows Firewall enabled, but sometimes when you're performing testing or setting up new applications, you need to turn Windows Firewall off for a period. The following commands illustrate how to turn Windows Firewall off and then back on:
netsh advfirewall set allprofiles state on netsh advfirewall set allprofiles state off
8. Reset Windows Firewall: If you make a mistake configuring Windows Firewall, you might want to use the following netsh command to reset it back to its default settings:
netsh advfirewall reset
7. Set logging: The default path for the Windows Firewall log files is \Windows\system32\LogFiles\Firewall\pfirewall.log. The netsh command below changes the location of the log file to the C:\temp directory:
netsh advfirewall set currentprofile logging filename "C:\temp\pfirewall.log"
6. Allow and prevent ping: You can use netsh to control how and if a given system responds to ping requests. The following two netsh commands show how you can block and then open Windows Firewall to ping requests:
netsh advfirewall firewall add rule name="All ICMP V4" dir=in action=block protocol=icmpv4 netsh advfirewall firewall add rule name="All ICMP V4" dir=in action=allow protocol=icmpv4
5. Enable and delete a port: One of the most common things you need to do with Windows Firewall is open ports that are used by different programs. The following examples show how to use netsh to create a rule to open and then close port 1433, which is used by Microsoft SQL Server:
netsh advfirewall firewall add rule name="Open SQL Server Port 1433" dir=in action=allow protocol=TCP localport=1433 netsh advfirewall firewall delete rule name="Open SQL Server Port 1433" protocol=tcp localport=1433
4. Enable a program: Another common task is opening Windows Firewall for a given program. The following example illustrates how to add a rule that enables Windows Live Messenger to work through Windows Firewall:
netsh advfirewall firewall add rule name="Allow Messenger" dir=in action=allow program="C:\programfiles\messenger\msnmsgr.exe"
3. Enable remote management: Another common requirement, especially when you're setting up new systems, is to enable remote management so that tools such as the Microsoft Management Console can connect to remote systems. To open Windows Firewall for remote management, you can use the following command:
netsh advfirewall firewall set rule group="remote administration" new enable=yes
2. Enable Remote Desktop Connection: One of the first things I do with most of the server systems I set up is enable Remote Desktop Connection for easy remote systems management. The following command shows how to use netsh to open Windows Firewall for Remote Desktop Connections:
netsh advfirewall firewall set rule group="remote desktop" new enable=Yes
1. Export and import firewall settings: After you get Windows Firewall configured, it's a good idea to export your settings so that you can easily reapply them later or import them into another system. In the following netsh commands, you can see how to export and then import your Windows Firewall configuration:
netsh advfirewall export "C:\temp\WFconfiguration.wfw" netsh advfirewall import "C:\temp\WFconfiguration.wfw"
Learn more: Netsh Command Syntax for the Netsh Firewall Context
netsh advfirewall firewall set rule name="Netlogon 서비스(NP-In)" new enable=yes
netsh advfirewall firewall set rule group="remote desktop" new enable=Yes
일단 원격 데이터 수집을 위해 리버스 하면서 수집한 정보를 조금 남겨둔다.
https://msdn.microsoft.com/en-us/library/aa371915(VS.85).aspx
The CounterData table contains a row for each counter that is collected at a particular time. There will be a large number of these rows.
The CounterData table defines the following fields:
The GUID, CounterID, and RecordIndex fields make up the primary key for this table.
SecondValueA 와 B
timestamp 값이다. 결국 1초 차이는 3312642 라는 systemfrequency 이고 TimeStamp 값에 1초마다 차곡 차곡 더해진다. 이 값을 32bit Value 2개에 나누어 담아서 secondValueA 를 하위값 secondValueB 를 상위값으로 기록 (만철이가 리버스 함)
countertype 별 timebasea
=DEC2BIN(MOD(QUOTIENT(A1,256^3),256),8)&DEC2BIN(MOD(QUOTIENT(A1,256^2),256),8)&DEC2BIN(MOD(QUOTIENT(A1,256^1),256),8)&DEC2BIN(MOD(QUOTIENT(A1,256^0),256),8)
timebasea 는 SystemFrequency 이거나 10000000 인데 countertype 에 종속된다. 자세한 컬럼은 위 그림을 참고한다.
virtual Machine 은 10000000 이다.
DefaultSclae 은 모두 0으로 두고 모니터링 할 때 선택한 카운터 배율조정을 선택해 모두 잘 나타낼 수 있도록 만들면 된다.
counterdata 의 multicount 는 모두 0으로 해도 이상없이 데이터가 출력된다.
workgroup 환경에서 원격서버의 데이터를 수집하기 위해서는 가장(Impersonation)을 통해 로그인 한다.
이제 perfmon extendedevent dmv 모두 수집할 수 있다.
일정 잡고 만들면 된다. 만드는건 쉽다........아~ 돌아버릴뻔~
norecovery 로 복구한 데이터베이스 mdf ldf 를 신규 서버로 이전해와
복구 하려고 할 때
메시지 4333, 수준 16, 상태 1, 줄 32
로그가 복원되지 않아 데이터베이스를 복구할 수 없습니다.
메시지 3013, 수준 16, 상태 1, 줄 32
RESTORE DATABASE이(가) 비정상적으로 종료됩니다.
norecovery 로 복구한 데이터베이스 mdf ldf 를 신규 서버로 이전해와
계속 transacion log 를 복구 하려고 할 때
mdf, ldf 파일에 권한이 없어도 날 수 있는 오류이다.
메시지가 그시기 하다.....
위 방식(SI 백업시 이런 시나리오가 됩니다.)으로 복구 할 때는
select * from sysaltfiles where dbid = 5
와
restore filelistonly from disk = ''
에서의 fileid 순서가 같아야 한다.
CREATE DATABASE [agtest2] ON PRIMARY
( NAME = N'agtest2', FILENAME = N'c:\temp\agtest2.mdf' , SIZE = 10000KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N'agtest2_log', FILENAME = N'c:\temp\agtest2_log.ldf' , SIZE = 10000KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO
USE [master]
GO
-- 파일 순서를 맞추어 주기 위해서
ALTER DATABASE [agtest2] ADD FILE ( NAME = N'agtest2_1', FILENAME = N'C:\Temp\agtest2_1.ndf' , SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP [PRIMARY]
GO
이런 식으로 fake db 를 2008 에서 만든 후 2014에 리커버리 한 다음
서버를 종료 파일 바꿔치기를 진행한다.