블로그 이미지
SQL Server VS. 석이 minsouk@hotmail.com MSSQL 쿼리성능 관련해 궁금한 사항이 있다면 언제나 누구나 TeamViewer + Line (네이버 japan 메신저) 에 minsouk1 추가 후 연락주세요~ 010-9967-0955 보미아빠

카테고리

보미아빠, 석이 (439)
밥벌이 (16)
싸이클 (1)
일상 (1)
Total195,101
Today53
Yesterday48

달력

« » 2017.11
      1 2 3 4
5 6 7 8 9 10 11
12 13 14 15 16 17 18
19 20 21 22 23 24 25
26 27 28 29 30    

공지사항

'보미아빠, 석이'에 해당되는 글 439건

  1. 2017.11.10 플랜 변경으로 인한 cpu 100% 를 막아보자
  2. 2017.11.07 datatype
  3. 2017.11.06 HADR_SYNC_COMMIT
  4. 2017.10.16 folder drive map
  5. 2017.10.13 Powershell - WinNT Provider
  6. 2017.08.24 vs shortcut
  7. 2017.07.06 cd 복사
  8. 2017.06.29 excel 암호풀기
  9. 2017.06.14 ssms 에러
  10. 2017.06.12 json

oltp 서버에서는 traceflag 4136을 추천한다. 

그러면, 최소한 가만히 있던 서버가 플랜이 바뀌어 cpu 를 100% 사용하는 일은 없어질것이다. 



-- ------------------------------------

-- 데이터 생성 시작 

-- ------------------------------------

use master

go


if db_id('plan_test') is not null

begin

alter database plan_test set single_user with rollback immediate 

drop database plan_test  

end 


create database plan_test

go


use plan_test 

go


if object_id('t_product') is not null

drop table t_product 

go


-- 샵2개 상품 각 10만개 , 샵 100만개 넣어도 됨 하고 싶으면...

with temp as

(

select 

top 200000 cast(row_number() over (order by (select 1)) as int) idx

, cast('contents other column' as char(400)) contentOtherCol

from sys.objects a1

cross join sys.objects a2

cross join sys.objects a3

cross join sys.objects a4

cross join sys.objects a5

)

select 

idx

, cast(abs(checksum(newid())) % 2 as int) shopid

, cast(abs(checksum(newid())) % 100000 as int) productid

, contentOtherCol

into t_product

from temp

go


create clustered index cl_t_product on t_product (shopid, productid) 

go


if object_id('t_img') is not null

drop table t_img 

go


-- 샵과 상품으로 연결되는 이미지 1만개 type 은 모두 1, 다른거 100만개 넣어도 됨. 하고 싶으면 ...

with temp as

(

select 

top 10000 cast(row_number() over (order by (select 1)) as int) idx

, cast('img other column' as char(400)) imgOtherCol

from sys.objects a1

cross join sys.objects a2

cross join sys.objects a3

cross join sys.objects a4

cross join sys.objects a5

)

select 

idx

, cast(abs(checksum(newid())) % 2 as int) shopid

, cast(abs(checksum(newid())) % 100000 as int) productid

, 1 as imgType

, imgOtherCol

into t_img

from temp

go


create clustered index cl_t_img on t_img (imgType, shopid, productid) 

go


-- ------------------------------------

-- 데이터 생성 끝 

-- ------------------------------------



-- ------------------------------------

-- 생성된 데이터 보기 시작 

-- ------------------------------------

dbcc show_statistics (t_product, cl_t_product)

dbcc show_statistics (t_img, cl_t_img)

select top 10 * from t_product 

select top 10 * from t_img 


-- ------------------------------------

-- 생성된 데이터 보기 끝

-- ------------------------------------


if object_id('a') is null

exec ('create proc a as select 1')

go


alter proc a

  @shopid int

, @productid int

as 

select top 1 *

from 

(

select top 1 shopid, productid

from t_img 

where 

shopid = @shopid 

and productid < @productid 

and imgType = 1 

order by productid desc 

) a

left join t_product b

on a.shopid = b.shopid 

and a.productid = b.productid 

go


-- ------------------------------------

-- 테스트 스크립트 시작 

-- ------------------------------------

set statistics io on 

-- 정상적인 경우 

exec a 1, 10000 -- 1번 shop 은 물건이 많고 상대적으로 많은 쿼리가 들어오고 있음 그러므로 첫 번째 컴파일 될 확율이 높음

exec a 3, 1 -- 이렇게 컴파일된 쿼리는 통계에 없는 변수가 들어와도 충분히 쓸만한 플랜임 


-- 첫 변수를 통계에 없는 shopid 를 넣어 컴파일 한 경우, 성능에 심각한 오류가 생김 

dbcc freeproccache 

exec a 3, 1 -- 3번 샵은 신규 샵으로 상품이 적음 3번 샵과 1번으로 쿼리가 들어오면 아주 좋은 플랜임 

exec a 1, 10000 -- 그러나 상품이 많은 1번 샵을 처리하기에는 부적합한 플랜임 

                -- 플랜 리뷰 및 설명 

-- ------------------------------------

-- 테스트 스크립트 끝

-- ------------------------------------


-- ------------------------------------

-- work around 1  (뷰 안의 변수가 쿼리 외부로 나오지 못하게 강제로 막음)

-- ------------------------------------

go

alter proc a

  @shopid int

, @productid int

as 

select top 1 *

from 

(

select top 1 shopid, productid % productid + productid as productid2 -- join 될 컬럼은 가공해 마지막 값만 = 비교가 일어날 수 있도록 바꾸어줌 

from t_img 

where 

shopid = @shopid 

and productid < @productid 

and imgType = 1 

order by productid desc -- sort 는 내부 컬럼을 그대로 이용해 인덱스를 이용한 소트를 사용 가능하게 만들어줌 

) a

left join t_product b

on a.shopid = b.shopid 

and a.productid2 = b.productid 

go


-- ------------------------------------

-- work around 2 (optimize for unknown 으로 histogram 을 보지 못하게 쿼리 하도록 함)

-- ------------------------------------

go

alter proc a

  @shopid int

, @productid int

as 

select top 1 *

from 

(

select top 1 shopid, productid

from t_img 

where 

shopid = @shopid 

and productid < @productid 

and imgType = 1 

order by productid desc 

) a

left outer join t_product b

on a.shopid = b.shopid 

and a.productid = b.productid 

option (OPTIMIZE FOR unknown)

go


-- ------------------------------------

-- work around 3 (querytraceon 4136을 이용해 ontimize for unknown 과 같은 효과를 query, instance 내 공통 적용 가능하게 만들 수 있음)

                 (database level 로 적용할 경우 2016부터... ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SNIFFING = OFF;)

(요즘 추세가 azure 가 나오면서 database level 로 옵션을 변경할 수 있게 바뀌고 있음) 

(https://www.mssqltips.com/sqlservertip/4245/sql-server-2016-database-scoped-configuration-options/)

-- ------------------------------------


alter proc a

  @shopid int

, @productid int

as 

select top 1 *

from 

(

select top 1 shopid, productid

from t_img 

where 

shopid = @shopid 

and productid < @productid 

and imgType = 1 

order by productid desc 

) a

left outer join t_product b

on a.shopid = b.shopid 

and a.productid = b.productid 

option (querytraceon 4136)

go


-- ------------------------------------

-- 다른 시나리오 

-- ------------------------------------


drop index t_product.cl_t_product 

create nonclustered index nc_t_product_01 on t_product (shopid) 



if object_id('c') is null

exec ('create proc c as select 1')

go


alter proc c

@shopid int

as 

declare @c varchar(max) =N''

select @c = contentOtherCol

from t_product                                                                                                                                                                                                                                                                                                                

where shopid = @shopid

select @c

go



alter proc c

@shopid int

as 

declare @c varchar(max) =N''

select @c = contentOtherCol

from t_product                                                                                                                                                                                                                                                                                                                

where shopid = @shopid

option (OPTIMIZE FOR unknown)

select @c

go


-- ------------------------------------

-- 테스트 스크립트 시작 

-- ------------------------------------


-- 문제없는 컴파일 순서 

exec c 1

exec c 3


-- 성능이 나빠지는 컴파일 순서 

-- 그러나 변수 3에 대해서는 플랜이 좋음 이 모든건 histogram 을 보고 컴파일하는 parameter sniffing 문제임 

exec c 3

exec c 1


-- ------------------------------------

-- 테스트 스크립트 끝

-- ------------------------------------



-- ------------------------------------

-- 다른 시나리오 

-- ------------------------------------

create nonclustered index nc_t_product_01 on t_product (productid) 

go


if object_id('b') is null

exec ('create proc b as select 1')

go


alter proc b

@productid int

as 

declare @c varchar(max) =N''

select @c = contentOtherCol

from t_product 

where productid < @productid 

select @c 

go


dbcc freeproccache 

set statistics io on 

set statistics time on 

set statistics profile off

go



alter proc b

@productid int

as 

declare @c varchar(max) =N''


select @c = contentOtherCol

from t_product 

where productid < @productid 

option (OPTIMIZE FOR unknown)

select @c 

go



exec b 1

exec b 100000

go



저작자 표시 비영리 변경 금지
신고
Posted by 보미아빠

datatype

분류없음 / 2017.11.07 15:24
SQL Server data type          CLR data type (SQL Server)    CLR data type (.NET Framework)  
varbinary                     SqlBytes, SqlBinary           Byte[]  
binary                        SqlBytes, SqlBinary           Byte[]  
varbinary(1), binary(1)       SqlBytes, SqlBinary           byte, Byte[] 
image                         None                          None

varchar                       None                          None
char                          None                          None
nvarchar(1), nchar(1)         SqlChars, SqlString           Char, String, Char[]     
nvarchar                      SqlChars, SqlString           String, Char[] 
nchar                         SqlChars, SqlString           String, Char[] 
text                          None                          None
ntext                         None                          None

uniqueidentifier              SqlGuid                       Guid 
rowversion                    None                          Byte[]  
bit                           SqlBoolean                    Boolean 
tinyint                       SqlByte                       Byte 
smallint                      SqlInt16                      Int16  
int                           SqlInt32                      Int32  
bigint                        SqlInt64                      Int64 

smallmoney                    SqlMoney                      Decimal  
money                         SqlMoney                      Decimal  
numeric                       SqlDecimal                    Decimal  
decimal                       SqlDecimal                    Decimal  
real                          SqlSingle                     Single  
float                         SqlDouble                     Double  

smalldatetime                 SqlDateTime                   DateTime  
datetime                      SqlDateTime                   DateTime 

sql_variant                   None                          Object  
User-defined type(UDT)        None                          user-defined type     
table                         None                          None 
cursor                        None                          None
timestamp                     None                          None 
xml                           SqlXml                        None


저작자 표시 비영리 변경 금지
신고
Posted by 보미아빠

HADR_SYNC_COMMIT

분류없음 / 2017.11.06 20:32

https://blogs.msdn.microsoft.com/sql_server_team/troubleshooting-high-hadr_sync_commit-wait-type-with-always-on-availability-groups/



Troubleshooting High HADR_SYNC_COMMIT wait type with Always On Availability Groups


HADR_SYNC_COMMIT indicates the time between when a transaction ready to commit in the primary replica, and all secondary synchronous-commit replicas have acknowledged the hardening of the transaction commit LSN in an AG. It means a transaction in the primary replica cannot be committed, until the primary replica received greater hardened LSNs from all secondary synchronous-commit replicas. If transactions in the primary replica are slower than usual, and HADR_SYNC_COMMIT is unusually long, it means there is some performance issue in at least one Primary-Secondary replica data movement flow, or at least one secondary replica is slow in log hardening.

This blog post drills down on all related monitoring metrics including performance counters and XEvents, and provides guidelines on how to utilize them to troubleshoot the root cause of the performance issue.

All the metrics explained below are listed as per the following log block movement sequence:

1.      A transaction is initialized in a primary replica

2.      Primary replica capture transaction logs and send to a secondary replica

3.      The secondary replica receives and harden log block and eventually send new hardened lsn to Primary.

Availability Group Performance Counters

Some of the performance counters included below are applicable to both the primary and secondary, and hence are included twice in the list below.

Perf CounterAG ReplicaSQL releaseExplanation and Usage
Log Bytes Flushed/secPrimarySQL 2012+Total number of log bytes flushed per second in the primary replica. It represents the volume of logs that are available to be captured in primary replica and sent to secondary replica(s).
Log Pool LogWriter Pushes/secPrimarySQL 2016+Total number of log bytes are captured by LogWriter mode. LogWriter mode was introduced in SQL Server 2016 and it directly captures logs from memory log cache instead of log file. When LogWriter mode is on, it means there is almost no lag between log flush and log capture.
Bytes Sent to Replica/secPrimarySQL 2012+Total number of bytes sent to the internal message Queue of a single secondary replica (for both replica and database level data). The internal in-memory message queue which holds the captured and formatted data that will be sent to the targeted secondary replica. By default, the data in the internal message queue is compressed for both sync and async secondary replicas in SQL12 and SQL14. But only for an async secondary replica from SQL16. When data compression is enabled, the data volume of this perf counter is less than the corresponding value in Log Bytes Flushed/sec.
Flow Control/secPrimarySQL 2012+Number of flow control initiated in the last second. AG has internal throttling gates to control data flow. When the number of sequential messages that has not be acknowledged by secondary replica exceeds the gate value, data flow will be paused until receiving more acknowledge messages from secondary replica. More details can be found in "Flow Control Gates" section in https://msdn.microsoft.com/en-us/library/dn135338(v=sql.120).aspx with one correction: Database level gate values are 1792 for x64 and 256 for x86 environments.
Flow Control Time (ms/sec)PrimarySQL 2012+Time in milliseconds messages waited on flow control in the last second.
Bytes Sent to Transport/secPrimarySQL 2012+Total bytes sent to transport (UCS) for the availability replica. It represents the volume of data dequeued for XmitQueue and sent to transport layer. Its values should be very close to "Bytes Sent to Replica/sec" with a very slight lag.
Bytes Received from Replica/secSecondarySQL 2012+Total bytes received from the primary replica. It represents the volume of data received from transport (UCS) before any processing in secondary replica. Its values should be very close to "Bytes Sent to Replica/sec" on primary, with a lag influenced by the current network latency between this primary-secondary replica pair.
Log Bytes Flushed/secSecondarySQL 2012+Total number of log bytes flushed per second in the secondary replica. When secondary receives new log packages from primary, it decompresses and hardens them before sending a progress message to primary for its new hardened lsn. The trend of this perf counter should align with the same perf counter in primary replica with some lags.
Bytes Sent to Replica/secSecondarySQL 2012+Total number of bytes sent to the XmitQueue for delivering to the primary replica. They are all AG control messages, and data volume is very low.
Flow Control/secSecondarySQL 2012+Number of flow control initiated in the last second. Although secondary replica only sends AG control messages to primary, it may hit transport level flow control when it has hundreds of databases, and primary replica cannot send acknowledge message in time.
Flow Control Time (ms/sec)SecondarySQL 2012+Time in milliseconds messages waited on flow control in the last second.
Bytes Sent to Transport/secSecondarySQL 2012+Total bytes sent to transport (UCS) for the availability replica.
Bytes Received from Replica/secPrimarySQL 2012+Total bytes received from the secondary replica.

In Perfmon, except "Log Bytes Flushed/sec" and "Log Pool LogWriter Pushes/sec" which is in SQLServer:Databases object, all other counters are in the SQLServer:Availability Replica object.

Network Performance Counters

There are a few performance counters in "Network Interface" object can be used for network performance monitoring:

Perf CounterDescription
Bytes Received/secShows the rate at which bytes are received over each network adapter. The counted bytes include framing characters. Bytes Received/sec is a subset of Network Interface\Bytes Total/sec.
Bytes Sent/secShows the rate at which bytes are sent over each network adapter. The counted bytes include framing characters. Bytes Sent/sec is a subset of Network Interface\Bytes Total/sec.
Bytes Total/secShows the rate at which bytes are sent and received on the network interface, including framing characters. Bytes Total/sec is the sum of the values of Network Interface\Bytes Received/sec and Network Interface\ Bytes Sent/sec.
Current BandwidthShows an estimate of the current bandwidth of the network interface in bits per second (BPS). For interfaces that do not vary in bandwidth or for those where no accurate estimation can be made, this value is the nominal bandwidth.

When a host machine has a network dedicated for SQL, the network "Bytes Sent/sec" should share the same trend as AG "Bytes Sent to Transport/sec" with slightly larger values in the primary replica. Same similarity is observed between network counter "Bytes Received/sec" and AG counter "Bytes Received from Replica/sec" on the secondary replica.

Please note that "Current Bandwidth" value is in BPS (bit per second). When comparing it to the "Bytes Total/sec", the "Current Bandwidth" value needs to be divided by 8 first. When "Bytes Total/sec" is consistently close or equal to ("Current Bandwidth"/8), the network bandwidth can be identified as the performance bottleneck.

Availability Group Extended Events

Although performance counters can present the overall performance in each AG data movement stage, they are not sufficient to find out which stage is slow when there is a performance issue because the data flow in AG is like a closed-loop system, the slowest node decides the final throughput of the system. When trying to study available performance counters after a performance issue has started, all perf counter values may have already been slowed down.

It will be useful to capture extended events and correlated them by common field(s) to track a single block log movement in the whole data flow. It will provide details for identifying which stage took most of time and narrowing down the root cause analysis scope.

Below is a list of extended events that can be evaluated for investigating performance issues related to high HADR_SYNC_COMMIT.

Event NameReplicaDescriptionSymptom Key & ValueCorrelation Key(s)
log_flush_startPrimaryOccurs when asynchronous log write startslog_block_id,

database_id

log_flush_completePrimaryOccurs when log write complete. It can happen after hadr_capture_log_block because of its asynchronous nature of writes.log_block_id,

database_id

hadr_log_block_compressionPrimaryOccurs when log is ready to be captured and log compression is enabled. (If log compression is not enabled, this XEvent is not logged in SQL12 and SQL14, but it is always logged from SQL16. For SQL16, check Boolean value of property is_compressed.)log_block_id,

database_id

hadr_capture_log_blockPrimaryOccurs right after primary has captured a log block.mode=1log_block_id, database_replica_id
hadr_capture_log_blockPrimaryOccurs right before primary enqueues the captured log block to an internal message Queue of DbMgrPartner. A DbMgrPartner maps a database in the remote replica. No processing operation between mode 1 and 2.mode=2log_block_id,

database_replica_id

hadr_capture_log_blockPrimaryOccurs after dequeuing a log block from the internal message Queue of DbMgrPartner and before sending to transport (UCS)mode=3log_block_id,

database_replica_id

hadr_capture_log_blockPrimaryOccurs after the dequeued message reaches Replica layer and before sending to transport (UCS). Only message routing actions between mode 3 and 4.mode=4log_block_id,

database_replica_id,

availability_replica_id

hadr_transport_receive_log_block_messageSecondaryOccurs when receiving new log block message from transport (UCS)mode=1log_block_id,

database_replica_id

hadr_transport_receive_log_block_messageSecondaryOccurs after enqueuing a new RouteMessageTask to DbMgrPartner for this new received log block. No process operations yet.mode=2log_block_id,

database_replica_id

hadr_log_block_decompressionSecondaryOccurs after decompressing log block buffer. Boolean value of "is_compressed" means the incoming log block buffer is compressed or not.log_block_id,

database_id

log_flush_startSecondaryOccurs when asynchronous log write startslog_block_id,

database_id

log_flush_completeSecondaryOccurs when log write completelog_block_id,

database_id

hadr_apply_log_blockSecondaryOccurs right after log block is flushed and new redo target LSN is calculated in secondarylog_block_id,

database_replica_id

hadr_send_harden_lsn_messageSecondaryOccurs when SyncLogProgressMsg with new hardened lsn is constructed and before pushing this message to the internal message Queue of the database's DbMgrPartner. At this point, the previous received log block has been flushed to disk, but the current log block may not.mode=1log_block_id (*Need to choose the immediate next log_block_id of this database),

hadr_database_id

hadr_send_harden_lsn_messageSecondaryOccurs when SyncLogProgressMsg is dequeued from the internal message Queue and before sending to transport (UCS)mode=2log_block_id (*Need to choose the immediate next log_block_id of this database),

hadr_database_id

hadr_send_harden_lsn_messageSecondaryOccurs after the dequeued SyncLogProgressMsg reaches Replica layer and before sending to transport (UCS). No processing operation between mode 2 and 3.mode=3log_block_id (*Need to choose the immediate next log_block_id of this database),

hadr_database_id

hadr_receive_harden_lsn_messagePrimaryOccurs when receiving new SyncLogProgressMsg which contains new hardened lsn of database in secondary replica from transport (UCS)mode=1log_block_id (*Need to choose the immediate next log_block_id of this database),

database_replica_id

hadr_receive_harden_lsn_messagePrimaryOccurs after enqueuing a new RouteSyncProgressMessageTask to DbMgrPartner for this new received SyncLogProgressMsg. No processing operations yet.mode=2log_block_id (*Need to choose the immediate next log_block_id of this database),

database_replica_id

hadr_db_commit_mgr_hardenPrimaryOccurs after minimal hardened lsn among primary replica and all synchronous-commit secondary replicas exceeds the lsn of the expected log block.wait_log_block,

database_id,

ag_database_id

hadr_db_commit_mgr_harden_still_waitingPrimaryOccurs when a committed lsn in primary has not been notified for the hardening from all synchronous-commit secondary replicas for more than 2 seconds. This Xevent will be logged every 2 seconds until logging hadr_db_commit_mgr_harden.

In normal cases, the harden wait time is in tens of millisecond range, and this XEvent is not logged.

wait_log_block,

database_id,

ag_database_id

*Regarding the comment "Need to choose the immediate next log_block_id of this database" for hadr_send_harden_lsn_message and hadr_receive_harden_lsn_message, it is because the ChangeApply side always returns a hardened lsn which is one block less than the currently received log block lsn. To ensure the primary replica received the expected hardened LSN from synchronous-commit secondary replica, the immediate next log block lsn is needed to trace the complete end-to-end logic flow for eventually reaching hadr_db_commit_mgr_harden.

To trace these, two XEvent queries are needed:

1.      Get the immediate next log block lsn which is greater than the value in property "wait_log_block" of Xevent hadr_db_commit_mgr_harden

Query in secondary replica (because that the max size of a log block is 60KB, and 1 log block unit represents 512 bytes, the [next log_block_id] will not be 120 more than the current log_block_id)

log_block_id > [current log_block_id] && log_block_id <= [current log_block_id] + 120

pick the first log_block_id from the query result as the [next log_block_id]

2.      Use current log block and next log blocks to get the end-to-end Xevent flow of a log block that contains the expected commit lsn.

Query in primary replica:

(name != hadr_receive_harden_lsn_message && log_block_id = [current log_block_id]) ||

(name == hadr_receive_harden_lsn_message && log_block_id = [next log_block_id]) ||

(name == hadr_db_commit_mgr_harden && wait_log_block = [current log_block_id])

Query in secondary replica:

(name != hadr_send_harden_lsn_message && log_block_id = [current log_block_id]) ||

(name == hadr_send_harden_lsn_message && log_block_id = [next log_block_id])

Screenshot for a sample XEvent flow in Primary replica (highlighted records):

        log_block_id (3925600606568) is got from property "wait_log_block" of Xevent hadr_db_commit_mgr_harden

Screenshot for a sample XEvent flow in Secondary replica (highlighted records):

Based on the "wait_log_block" value in a hadr_db_commit_mgr_harden, the full timeline of this log_block data movement sequence can be tracked back following above table by combining the captured Xevents from primary replica and all synchronous-commit secondary replicas.

Optionally, the "database_id" and "ag_database_id" (which is database replica id in AG) can be combined with "wait_log_block" for the related Xevent logs lookup.

In addition, the mapping among database_id, ag_database_id and replica_id can be found from the output of sys.dm_hadr_database_replica_states.

Troubleshooting

There are multiple possible reasons to cause HADR_SYNC_COMMIT to be unusually long. With above performance counters and XEvents, it is possible to narrow down the root cause. If none of the resources has performance issue, please involve Microsoft Customer Support Team for further investigation

Slow Disk IO

With AG Xevents, the duration between log_flush_start and log_flush_complete for the same log_block_id in the secondary replica should be long when there is a disk IO issue. Another way to look for this value is to check value (in millisecond) of property "duration" in log_flush_complete.

When a secondary replica of an AG has compatible hardware configuration as the primary replica (which is recommended in MSDN – section "Recommendations for Computers That Host Availability Replicas (Windows System)" in https://msdn.microsoft.com/en-us/library/ff878487.aspx), and the host computer of the secondary replica is dedicated to this AG, it is not expected to see secondary replica has disk IO issue before the primary replica hit it. One exception is that the secondary replica enables read-only access, and it receives IO intensive reporting workloads. Frequent log backup and copy backup can be another potential cause to be looked at.

High CPU

When a secondary replica of an AG has compatible hardware configuration as the primary replica, and its host computer is for this AG only, it is not expected to see Secondary replica hit CPU issue. But it is possible when there is a heavy reporting workload on a read-only enabled secondary replica.

In addition, log compression in primary replica and log decompression in secondary replica can be CPU heavy operations. If compression is enabled (based on XEvent hadr_log_block_compression is logged in primary, or property is_compressed of XEvent hadr_log_block_decompression is true or not), it can be a possible cause for high CPU in both primary and secondary replicas.  When hadr_log_block_compression is logged in primary replica with is_compressed=true, if the duration between log_flush_start and hadr_log_block_compression is long, while DISK IO is still fast, data compression can be identified as the cause. Similarly, the duration between hadr_transport_receive_log_block_message (mode=2) and hadr_log_block_decompression can be measured for the same detection.

When compression/decompression is identified to be the root cause of High CPU, for SQL Server 2012 and SQL Server 2014, disabling compression with TF1462 is a workaround option by sacrificing some network efficiency (bigger data packages to network). For SQL Server 2016 and above, disabling parallel compression (TF 9591) is another option.

Network Issues

After rooting out the disk IO and high CPU as the root cause of long HADR_SYNC_COMMIT, network performance needs to be checked.

With the "Network Performance Counters" mentioned above, the first thing is to check if "Bytes Total/sec" is close to "Current Bandwidth"/8 for the related Network Adapter in primary replica and all synchronous-commit secondary replicas. When any replicas show this situation, it means this network adapter has reached its network throughput capacity.

To check if the network throughput is mainly from AG data movement, the values of Network perf count "Bytes Sent/sec" and AG perf counter "Bytes Sent to Transport/sec" can be compared in Primary replica for a specific secondary replica as the sync partner, and the values of network perf counter "Bytes Received/sec" and AG perf counter "Bytes Received from Replica/sec" can be compared in Secondary replica. Their values and trend should be very close to each other.

XEvents can be applied here to examine the network latency. For the same log_block_id, the duration between hadr_capture_log_block (mode=4 in primary with the matching availability_replica_id to the secondary replica) and hadr_transport_receive_log_block_message (mode=1 in secondary) means the network latency for a log block to transport from primary to secondary replica. The duration between hadr_send_harden_lsn_message (mode=3 in secondary) and hadr_receive_harden_lsn_message (mode=1 in primary) represents the network latency for a log block lsn hardening message to move from secondary to primary.

Content Creator - Dong Cao, Principal Software Engineer, Data Platform Group, Microsoft

Sourabh Agarwal
Senior PM, SQL Server Tiger Team
Twitter LinkedIn
Follow us on Twitter: @mssqltiger | Team Blog: Aka.ms/sqlserverteam


저작자 표시 비영리 변경 금지
신고
Posted by 보미아빠

folder drive map

분류없음 / 2017.10.16 15:52

https://www.itworld.com/article/2694895/how-to-map-a-local-folder-to-a-drive-letter-in-windows.html


subst x: C:\Folder\Example

To remove a mapping:

subst x: /D

저작자 표시 비영리 변경 금지
신고
Posted by 보미아빠

http://learningpcs.blogspot.kr/2011/01/powershell-winnt-provider.html




I reimaged my Windows 7 machine and want to make a new account strictly to contain pen testing tools.  Wondering how I could do this the Powershell way I started looking around. Most everything I found related to domain user management, i.e., LDAP and ADSI.  In my case, I was not dealing with AD per se, but, rather the WinNT provider to add a local account.  I stumbled onto this post:
http://www.vistax64.com/powershell/173919-add-built-account-local-group-using-winnt-adsi-provider.html
where Shay Levy added this little nugget:
$group = [ADSI]"WinNT://$env:COMPUTERNAME/Administrators,group"
$group.add("WINNT://NT AUTHORITY/SYSTEM")
I thought okay, great, I've got something to work with.  My next step was MSDN to try and find some higher level info to work with.  This link came up on Google, but, it really didn't get me far:
http://msdn.microsoft.com/en-us/library/aa746534(v=VS.85).aspx
I then searched for [ADSI]"WinNT Powershell and got an old Scripting Guys post that got me messing around in the right direction:
http://blogs.technet.com/b/heyscriptingguy/archive/2008/03/11/how-can-i-use-windows-powershell-to-add-a-domain-user-to-a-local-group.aspx
As is much more eloquently noted in the post, if you try and pass the reference to a variable and run a Get-Member cmdlet against it, you don't get very far.
$group = [ADSI]"WinNT://$env:COMPUTERNAME/Administrato
rs,group"
$group | gm


TypeName: Microsoft.PowerShell.Commands.MemberDefinition

Name MemberType Definition
---- ---------- ----------
Equals Method bool Equals(System.Object obj)
GetHashCode Method int GetHashCode()
GetType Method type GetType()
ToString Method string ToString()
Definition Property System.String Definition {get;}
MemberType Property System.Management.Automation.PSMemberTypes MemberType {get;}
Name Property System.String Name {get;}
TypeName Property System.String TypeName {get;}
Now, if you use the PSBase reference, things start to open up a lot:
Likewise, if you go up one level and focus less on a specific group, but, rather, the machine itself, you see a lot of nice things to start playing with:
$machine = [ADSI]"WinNT://$env:COMPUTERNAME"
$machine | gm


TypeName: System.DirectoryServices.DirectoryEntry

Name MemberType Definition
---- ---------- ----------
ConvertDNWithBinaryToString CodeMethod static string ConvertDNWithBinaryToString(psobject deInstance, psobject dnWit...
ConvertLargeIntegerToInt64 CodeMethod static long ConvertLargeIntegerToInt64(psobject deInstance, psobject largeInt...
Division Property System.DirectoryServices.PropertyValueCollection Division {get;set;}
Name Property System.DirectoryServices.PropertyValueCollection Name {get;set;}
OperatingSystem Property System.DirectoryServices.PropertyValueCollection OperatingSystem {get;set;}
OperatingSystemVersion Property System.DirectoryServices.PropertyValueCollection OperatingSystemVersion {get;...
Owner Property System.DirectoryServices.PropertyValueCollection Owner {get;set;}
Processor Property System.DirectoryServices.PropertyValueCollection Processor {get;set;}
ProcessorCount Property System.DirectoryServices.PropertyValueCollection ProcessorCount {get;set;}
Throwing in the PSBase option, I get much more when I run the gm.
$machine.PSBase | gm


TypeName: System.Management.Automation.PSMemberSet

Name MemberType Definition
---- ---------- ----------
Disposed Event System.EventHandler Disposed(System.Object, System.EventArgs)
Close Method System.Void Close()
CommitChanges Method System.Void CommitChanges()
CopyTo Method adsi CopyTo(adsi newParent), adsi CopyTo(adsi newParent, string newName)
CreateObjRef Method System.Runtime.Remoting.ObjRef CreateObjRef(type requestedType)
DeleteTree Method System.Void DeleteTree()
Dispose Method System.Void Dispose()
Equals Method bool Equals(System.Object obj)
GetHashCode Method int GetHashCode()
GetLifetimeService Method System.Object GetLifetimeService()
GetType Method type GetType()
InitializeLifetimeService Method System.Object InitializeLifetimeService()
Invoke Method System.Object Invoke(string methodName, Params System.Object[] args)
InvokeGet Method System.Object InvokeGet(string propertyName)
InvokeSet Method System.Void InvokeSet(string propertyName, Params System.Object[] args)
MoveTo Method System.Void MoveTo(adsi newParent), System.Void MoveTo(adsi newParent, string n...
RefreshCache Method System.Void RefreshCache(), System.Void RefreshCache(string[] propertyNames)
Rename Method System.Void Rename(string newName)
ToString Method string ToString()
AuthenticationType Property System.DirectoryServices.AuthenticationTypes AuthenticationType {get;set;}
Children Property System.DirectoryServices.DirectoryEntries Children {get;}
Container Property System.ComponentModel.IContainer Container {get;}
Guid Property System.Guid Guid {get;}
Name Property System.String Name {get;}
NativeGuid Property System.String NativeGuid {get;}
NativeObject Property System.Object NativeObject {get;}
ObjectSecurity Property System.DirectoryServices.ActiveDirectorySecurity ObjectSecurity {get;set;}
Options Property System.DirectoryServices.DirectoryEntryConfiguration Options {get;}
Parent Property System.DirectoryServices.DirectoryEntry Parent {get;}
Password Property System.String Password {set;}
Path Property System.String Path {get;set;}
Properties Property System.DirectoryServices.PropertyCollection Properties {get;}
SchemaClassName Property System.String SchemaClassName {get;}
SchemaEntry Property System.DirectoryServices.DirectoryEntry SchemaEntry {get;}
Site Property System.ComponentModel.ISite Site {get;set;}
UsePropertyCache Property System.Boolean UsePropertyCache {get;set;}
Username Property System.String Username {get;set;}
As I began poking around I found lots of excellent information that would be good for something like the enumeration phase of a pen test, assuming you could get access to a machine via WinNT provider. It's also flat out useful to find out more about your machine. Here is a treasure trove of information:
($user.PSBase.children) | select * -First 1
UserFlags : {66051}
MaxStorage : {-1}
PasswordAge : {48034693}
PasswordExpired : {0}
LoginHours : {255 255 255 255 255 255 255 255 255 255 255 255 255 255 255 255 255 255 255 255 255}
FullName : {}
Description : {Built-in account for administering the computer/domain}
BadPasswordAttempts : {0}
LastLogin : {7/13/2009 10:53:58 PM}
HomeDirectory : {}
LoginScript : {}
Profile : {}
HomeDirDrive : {}
Parameters : {}
PrimaryGroupID : {513}
Name : {Administrator}
MinPasswordLength : {0}
MaxPasswordAge : {3628800}
MinPasswordAge : {0}
PasswordHistoryLength : {0}
AutoUnlockInterval : {1800}
LockoutObservationInterval : {1800}
MaxBadPasswordsAllowed : {0}
objectSid : {1 5 0 0 0 0 0 5 21 0 0 0 50 63 56 2 145 31 129 81 36 160 45 106 244 1 0 0}
AuthenticationType : Secure
Children : {}
Guid : {D83F1060-1E71-11CF-B1F3-02608C9E7553}
ObjectSecurity :
NativeGuid : {D83F1060-1E71-11CF-B1F3-02608C9E7553}
NativeObject : System.__ComObject
Parent : WinNT://WORKGROUP/MyMachine
Password :
Path : WinNT://WORKGROUP/MyMachine/Administrator
Properties : {UserFlags, MaxStorage, PasswordAge, PasswordExpired...}
SchemaClassName : User
SchemaEntry : System.DirectoryServices.DirectoryEntry
UsePropertyCache : True
Username :
Options :
Site :
Container :
If you don't think this tells you a lot about a given machine, I don't know what to tell you.

Pulling back from the hidden wealth of information just discovered and refocusing on the task at hand, I still needed to know how to add a new local user account.  I dug up another, perfect script:
http://stackoverflow.com/questions/383390/create-local-user-with-powershell-windows-vista
which threw out a function:
function create-account ([string]$accountName = "testuser") { 
$hostname = hostname 
$comp = [adsi] "WinNT://$hostname" 
$user = $comp.Create("User", $accountName) 
$user.SetPassword("Password1") 
$user.SetInfo() 
}
Seeing snippets of references I had already looked at--Create, SetPassword, SetInfo--I figured I would go with this and just get things setup. After thinking it through, however, I decided to add a little functionality and write my own function, mainly to enable the specification of UserFlags. If you have never worked with the UserFlags enumeration it can be referenced here:
http://msdn.microsoft.com/en-us/library/Aa772300
Here is the main segment worth focusing on as outlined in the typedef for this enum:
typedef enum {
ADS_UF_SCRIPT = 1, // 0x1
ADS_UF_ACCOUNTDISABLE = 2, // 0x2
ADS_UF_HOMEDIR_REQUIRED = 8, // 0x8
ADS_UF_LOCKOUT = 16, // 0x10
ADS_UF_PASSWD_NOTREQD = 32, // 0x20
ADS_UF_PASSWD_CANT_CHANGE = 64, // 0x40
ADS_UF_ENCRYPTED_TEXT_PASSWORD_ALLOWED = 128, // 0x80
ADS_UF_TEMP_DUPLICATE_ACCOUNT = 256, // 0x100
ADS_UF_NORMAL_ACCOUNT = 512, // 0x200
ADS_UF_INTERDOMAIN_TRUST_ACCOUNT = 2048, // 0x800
ADS_UF_WORKSTATION_TRUST_ACCOUNT = 4096, // 0x1000
ADS_UF_SERVER_TRUST_ACCOUNT = 8192, // 0x2000
ADS_UF_DONT_EXPIRE_PASSWD = 65536, // 0x10000
ADS_UF_MNS_LOGON_ACCOUNT = 131072, // 0x20000
ADS_UF_SMARTCARD_REQUIRED = 262144, // 0x40000
ADS_UF_TRUSTED_FOR_DELEGATION = 524288, // 0x80000
ADS_UF_NOT_DELEGATED = 1048576, // 0x100000
ADS_UF_USE_DES_KEY_ONLY = 2097152, // 0x200000
ADS_UF_DONT_REQUIRE_PREAUTH = 4194304, // 0x400000
ADS_UF_PASSWORD_EXPIRED = 8388608, // 0x800000
ADS_UF_TRUSTED_TO_AUTHENTICATE_FOR_DELEGATION = 16777216 // 0x1000000
} ADS_USER_FLAG_ENUM;
This script can give you a taste of a few others ways to play with the UserFlags enum and the -bor operator if you want to be more precise in your settings:
http://poshcode.org/685
As noted in this post there is a little usage of the binary comparison operators. You can get more detail (plus some examples) by typing Get-Help about_comparison operators in your shell.

Once I had all this set up I arrived at this script:
function Add-LocalUser
{
param(
[Parameter(Mandatory = $true, Position = 1)]
[String]
$UserName, 
[Parameter(Mandatory = $true, Position = 2)]
[String]
$Password, 
[Parameter(Mandatory = $false, Position = 3)]
[Int32]
$UserFlags,
[Parameter(Mandatory = $false, Position = 4)]
[String]
$ComputerName = $env:COMPUTERNAME
)

$comp = [adsi] "WinNT://$ComputerName"
$user = $comp.Create("User", $UserName)
$user.SetPassword($Password)
if($UserFlags)
{
$user.UserFlags = $UserFlags
}
$user.SetInfo()
}
To use this function I do this: 
Add-LocalUser User P@55w0RD (65536 + 64)
To add the user to a group, I can go back to the boiler plate code from the original Scripting Guys post and put this right where I need it.


저작자 표시 비영리 변경 금지
신고
Posted by 보미아빠

vs shortcut

분류없음 / 2017.08.24 22:29


<주석>-------------------------------------------------------------------
Ctrl+K, Ctrl+C 선택 영역 주석 처리 (.NET 2003, 2005)
Ctrl+K, Ctrl+U 선택 영역 주석 없앰 (.NET 2003, 2005) 
 

<
이동>
------------------------------------------------------------------------------

Ctrl + F2               현재 라인에 북마크 지정/해제

F2                       지정된 다음 북마크로 이동

Ctrl + Shift + F2      지정된 모든 북마크를 해제

Ctrl-K, Ctrl-H         바로가기 설정. ( 작업목록 창에서 확인가능 )

Ctrl-K,K                북마크 설정 / 해제

Ctrl-K,L                북마크 모두 해제

Ctrl-K,N                북마크 다음으로 이동

Ctrl-K,P                북마크 이전으로 이동

Ctrl-K,C                선택한 블럭을 전부 코멘트

Ctrl-K,U                선택한 블럭을 전부 언코멘트(코멘트 해제)

Ctrl + ] 또는 E        {괄호의 짝을 찾아줌

Ctrl + J, K              #ifdef  #endif의 짝을 찾아줌

Ctrl+ -, Ctrl+Shift+ -

현재 커서를 기억하는 Ctrl+F3(VS6에서), Ctrl+K,K(VS7에서와는 달리 사용자가 별도로 입력을 해주는건 없고단지 이전에 커서가 있었던곳으로 위 키를 누를 때마다 이동된다. (shift를 이용하면 역순)

 

Ctrl-F12                커서위치 내용의 선언(.h)으로 이동

F12                      커서위치 내용의 정의(.cpp)로 이동

Shift+Alt+F12         빠른기호찾기

 

Ctrl-Shift-G           #include "파일명파일로 바로 직접이동

F8                       After a build failure hit

Shift+F8            거꾸로

Ctrl + D                툴바의 찾기 Editbox로 이동 

 


<
편집>
------------------------------------------------------------------------------

Ctrl-F                   찾기 대화상자

Ctrl-H                  바꾸기 대화상자

Ctrl-Shift-F           파일들에서 찾기 대화상자

Ctrl-Shift-H           파일들에서 바꾸기 대화상자

Ctrl-G                  해당 줄로 가기 (별로 필요없음)

Ctrl-K,Ctrl-F          선택된 영역 자동 인덴트 (VS6 Alt-F8기능)

Ctrl-Shift-Spacebar             함수와매개변수설명이 안나올경우강제로 나오게

Ctrl+Alt+T

Ctrl+Spacebar       멤버목록 팝업창이 나타납니다

Ctrl+Shift+R           (키보드 레코딩)

             가끔 연속된 연속기만으로는 부족한경우가 있다.

             이때 Ctrl+Shift+R 을 누르고원하는 동작들을 수행후,

다시 Ctrl+Shift+R을 눌러 종료한다. 

이 중간동작을 원하는 위치에서 반복하고 싶다면

             Ctrl+Shift+P 를 누른다.

 

Ctrl+Shift+V           (히스토리 붙이기)

Ctrl-Z                  이전으로 되돌리기

Ctrl-Shift-Z           되돌렸다다시 복구하기

Ctrl + I                  문자열 입력점진적으로 문자열 찾기

Ctrl + F3               현재 커서에 있는 문자열 찾기

Ctrl+Shift+F3          거꾸로 찾기

F3                       찾은 문자열에 대한 다음 문자열 (Next Search)

Ctrl + H                 문자열 찾아 바꾸기 (Replace)

Ctrl + Left/Right     단어 단위로 이동

Ctrl+[Delete|Backspace] 단어 단위로 삭제

Ctrl + L                 한 라인을 클립보드로 잘라내기

Ctrl + Shift + L       한 라인을 삭제

Alt + Mouse          세로로 블록 설정하기 (마우스로)

Ctrl + Shift + F8      세로로 블록 설정하기 (키보드로),

취소할 때는 Esc키를 눌러야 함

블록설정>>Tab       선택된 블록의 문자열을 일괄적으로 들여쓰기(Tab)

블록설정>>Shift + Tab선택된 블록의 문자열을 일괄적으로 내어쓰기

Alt+F8>> [Tab|Shift + Tab]

                          들여쓰기 자동 조정

Ctrl + T                 현재 커서에 있는 변수/함수에 대한 Type

Tooltip 힌트 창에 나타남

Ctrl + Alt + T          멤버 변수/함수 목록에 대한 팝업 창이 나타남

Ctrl + Shift + T       공백/콤마/파이프/괄호 등을 기준으로

좌우 문자열을 Swap시킴

Ctrl + Shift + 8       문단기호 표시/감추기 :

Tab ^, Space .으로 표시

Ctrl + D                 툴바의 찾기 Editbox로 이동

Ctrl + Up/Down      커서는 고정시키고 화면만 스크롤 시키기

CTRL+SHIFT+T       커서 위치의 단어와 앞 단어가 서로 교체

ALT+SHIFT+T         커서 위치의 한줄과 윗줄이 서로 교환

Ctrl + Shift + U       소문자가 대문자로 둔갑.

Ctrl + U                 대문자를 소문자로 변경

Ctrl + Shift + F8      블럭설정

Ctrl + C, C                          That copies the current line.

Ctrl+K, Ctrl+C                      Automatically commented.

Ctrl+K, Ctrl+U                      Uncommented.

 


<
디버그/빌드>
-----------------------------------------------------------------------

F5                       디버그 시작

F9                       디버그 브렉포인트 지정/해제

Ctrl-F9                 현위치 설정된 브렉포인트 해제

Ctrl-Shift-F9         현재 소스파일에 지정된 모든 Breakpoint 해제

Shift-F5               디버그 빠져나오기

Ctrl-F10                커서가 있는곳까지 실행

Shift-F11                           현 함수를 빠져나감.

 

Shift+Ctrl+B           전체 빌드(프로젝트가 여러개있을경우 모두 빌드)

Alt+B, C                해당 프로젝트만 정리.

Alt+B, U                해당 프로젝트만 빌드.

Ctrl-F7                 현 파일만 컴파일    현 프로젝트만 빌드

Ctrl-F5                 프로그램 시작

Shift + F9                           디버그 모드에서 추가하고픈 변수나 등등

앞에 커서를 위치 시킨후 Shift+F9를 누르면

Watch Window에 자동으로 추가.

 


<
창관련>
----------------------------------------------------------------------------
Shift+Alt+Enter       전체 창 (토글 됨)

F4                       속성창 보여준다.

Ctrl+Alt+X                           리소스에디터 툴박스창

Ctrl+Alt+K              작업목록 창.

Ctrl + Tab                           Edit하고 있는 Child Window 간의 이동

Ctrl + F4               현재 Edit하고 있는 Child Window를 닫기

Ctrl-M, Ctrl-L         소스파일의 함수헤더만 보이기 (구현부는 감추고)

Ctrl-M, Ctrl-M        현재 커서가 위치한 함수를 접는다/편다. (토글 키)

Ctrl+R, Ctrl+R         Word Wrap

Ctrl+M+L               편집.전체개요표시숨기기

 

Ctrl+M+H           편집.선택영역숨기기

Ctrl+M+U           편집.현재숨기기중지

 

Alt + F7                Project Setting



저작자 표시 비영리 변경 금지
신고
Posted by 보미아빠

cd 복사

분류없음 / 2017.07.06 00:38

imgBurn

DVDFabPasskey 8


cd2mp3 : CDex


저작자 표시 비영리 변경 금지
신고
Posted by 보미아빠

excel 암호풀기

분류없음 / 2017.06.29 17:38

http://m.blog.naver.com/sindong14/220408019666

헐~ 이렇게 쉬울수가....2016까지 다 풀립니다. (테스트 완료)

저작자 표시 비영리 변경 금지
신고
Posted by 보미아빠

ssms 에러

분류없음 / 2017.06.14 14:05

https://connect.microsoft.com/VisualStudio/feedback/details/1171345/exception-when-opening-editor

 

del %temp%

 

저작자 표시 비영리 변경 금지
신고
Posted by 보미아빠

json

분류없음 / 2017.06.12 13:41

http://devluna.blogspot.kr/2016/05/newtonsoftjson-c-net-json-parsing.html

http://www.csharpstudy.com/Data/Json-jsonnet.aspx

저작자 표시 비영리 변경 금지
신고
Posted by 보미아빠

최근에 달린 댓글

최근에 받은 트랙백

글 보관함