https://github.com/mRemoteNG/mRemoteNG/wiki/Common-External-Tool-Configurations
'2017/11'에 해당되는 글 4건
- 2017.11.23 mremoteng
- 2017.11.10 플랜 변경으로 인한 cpu 100% 를 막아보자
- 2017.11.07 datatype
- 2017.11.06 HADR_SYNC_COMMIT
플랜 변경으로 인한 cpu 100% 를 막아보자
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
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
HADR_SYNC_COMMIT
https://blogs.msdn.microsoft.com/sql_server_team/troubleshooting-high-hadr_sync_commit-wait-type-with-always-on-availability-groups/