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

카테고리

보미아빠, 석이 (441)
밥벌이 (16)
싸이클 (1)
일상 (1)
Total196,518
Today21
Yesterday61

달력

« » 2017.12
          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
31            

공지사항

prepare in...

분류없음 / 2017.12.09 11:00
PreparedStatement statement = connection.prepareStatement("Select * from test where field in (?)");
Array array = statement.getConnection().createArrayOf("VARCHAR", new Object[]{"A1", "B2","C3"});
statement.setArray(1, array);
ResultSet rs = statement.executeQuery();


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

mremoteng

분류없음 / 2017.11.23 10:23

https://github.com/mRemoteNG/mRemoteNG/wiki/Common-External-Tool-Configurations

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

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 보미아빠

최근에 달린 댓글

최근에 받은 트랙백

글 보관함