플랜 변경으로 인한 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