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

카테고리

보미아빠, 석이 (443)
밥벌이 (16)
싸이클 (1)
일상 (1)
Total202,134
Today16
Yesterday117

달력

« » 2018.02
        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      

공지사항

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

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

최근에 달린 댓글

최근에 받은 트랙백

글 보관함