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

카테고리

보미아빠, 석이 (431)
밥벌이 (16)
싸이클 (1)
일상 (1)
Total180,046
Today14
Yesterday64

달력

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

공지사항


use t
go

if object_id ('tblx') is not null
drop table tblx
go

create table tblx
(idx int identity(1,1)
,a int
)
go


declare @a varchar(max)
set @a =''


-- 문제없음
set @a =
  '--' + replicate ('a', 7000)
+ char(10) + char(13)
+ 'insert into tblx (a) select 1'
exec (@a)


-- 에러 222222 를 넣었는데 22 만 들어감
set @a =
  '--' + replicate ('a', 7966)
+ char(10) + char(13)
+ 'insert into tblx (a) select 222222'
print @a
exec (@a)


-- 조용히 데이터 안들어감 
set @a =
  '--' + replicate ('a', 8000)
+ char(10) + char(13)
+ 'insert into tblx (a) select 3'
exec (@a)
go


-- 문제의 해결
set @a = 
convert(varchar(max), '')
+ '--' + replicate ('a', 8000)
+ char(10) + char(13)
+ 'insert into tblx (a) select 3'
exec (@a)
go


select * from tblx






유사한 오류는 다음과 같은것이 있습니다.

실제 오류는 아니지만 다음과 같은 계산이 안된다.

select power(2,31)-1
메시지 232, 수준 16, 상태 3, 줄 1
int 유형에 산술 오버플로 오류가 발생했습니다. 값 = 2147483648.000000

declare @a int, @b int, @c bigint
select @a = 2147483647, @b = 2147483647

set @c = @a + @b
select @c
메시지 8115, 수준 16, 상태 2, 줄 4
expression을(를) 데이터 형식 int(으)로 변환하는 중 산술 오버플로 오류가 발생했습니다.

--------------------
NULL

(1개 행이 영향을 받음)


신고

'밥벌이' 카테고리의 다른 글

varchar(max) 주의사항  (9) 2010.11.26
OPTIMIZE FOR ( @variable_name { UNKNOWN | = literal_constant } [ , ...n ] )  (0) 2010.11.19
민석이의 waitstat 모니터링  (0) 2010.11.18
부하 데이터생성  (0) 2010.11.18
Array Insert  (1) 2010.11.17
이 쿼리의 결과는 뭘까요?  (6) 2010.11.04
Posted by 보미아빠
UNKNOWN

Specifies that the query optimizer use statistical data instead of the initial value to determine the value for a local variable during query optimization.

OPTIMIZE FOR UNKNOWN

Instructs the query optimizer to use statistical data instead of the initial values for all local variables when the query is compiled and optimized, including parameters created with forced parameterization. For more information about forced parameterization, see Forced Parameterization.

If OPTIMIZE FOR @variable_name = literal_constant and OPTIMIZE FOR UNKNOWN are used in the same query hint, the query optimizer will use the literal_constant that is specified for a specific value and UNKNOWN for the remaining variable values. The values are used only during query optimization, and not during query execution.

해석을 하자면 변수가 많고 몇개는 로컬변수로 인식하게 하고, 몇개는 특정문자로 컴파일 하게 하고 싶다면 두 힌트를 모두 기술 할 수 있다.

<query_hint > ::=
{ { HASH | ORDER } GROUP
  | { CONCAT | HASH | MERGE } UNION
  | { LOOP | MERGE | HASH } JOIN
  | FAST number_rows
  | FORCE ORDER
  | MAXDOP number_of_processors
  | OPTIMIZE FOR ( @variable_name { UNKNOWN | = literal_constant } [ , ...n ] )
  | OPTIMIZE FOR UNKNOWN
  | PARAMETERIZATION { SIMPLE | FORCED }
  | RECOMPILE
  | ROBUST PLAN
  | KEEP PLAN
  | KEEPFIXED PLAN
  | EXPAND VIEWS
  | MAXRECURSION number
  | USE PLAN N'xml_plan'| TABLE HINT ( exposed_object_name  [ , <table_hint> [ [, ]...n ] ] )<table_hint> ::=

예전에 통계 히스토그램을 쓰지 않기 위해 프로시저 안에서 변수 할당해 놓고 다시 변수로 프로시저의 입력값을 넣었다가 로컬 변수를 조건절로 사용하곤 했다.

다시 말하지만 비즈니스와 데이터를 안다면, 힌트를 반드시 명시 하는게 좋다. unknown 이라는 힌트는 조금 동적인 힌트다. 왜냐하면, 평균 density 에 따라 변하기 때문이다. 그러므로, 완벽하게 어떤 인덱스, 어떤 조인 방법, 어떤 group 방법, 어떤 sort 등등을 명시하고, 필요한 쿼리에 대해서는 keepfixed plan 을 명시하는게 좋다.

유사한 이슈 중, 비동계 통계 업데이트는 이 모든 것을 제외하고 반드시 enable 시켜두는것이 좋다.


처음에 unknown 이 나왔을때 local variable 과 같다는 BOL 설명이 없었던것 같았다. 개편 되면서 추가 되지 않았을까 생각한다. 지금은 명확하게 local variable 과 같다고 되어 있으니 편하게 쓰면 될 듯 하다.

오늘도 즐거운 하루 되세요~







신고

'밥벌이' 카테고리의 다른 글

varchar(max) 주의사항  (9) 2010.11.26
OPTIMIZE FOR ( @variable_name { UNKNOWN | = literal_constant } [ , ...n ] )  (0) 2010.11.19
민석이의 waitstat 모니터링  (0) 2010.11.18
부하 데이터생성  (0) 2010.11.18
Array Insert  (1) 2010.11.17
이 쿼리의 결과는 뭘까요?  (6) 2010.11.04
Posted by 보미아빠

-- +++++++++++++++++++++++++++++++++++++++++++++++++++++
-- WAIT 조사용 프로시저 생성
-- +++++++++++++++++++++++++++++++++++++++++++++++++++++

USE MASTER
GO

IF OBJECT_ID ('AP_GET_WAITSTATS_1') IS NOT NULL
DROP PROC AP_GET_WAITSTATS_1
GO

CREATE PROC AP_GET_WAITSTATS_1
  @TYPE INT = 6
, @GROUP_MIN INT = 3
, @LOGINAME NVARCHAR(256) = NULL
, @NAME NVARCHAR(256) = NULL
, @HOSTNAME NVARCHAR(256) = NULL
, @PROGRAM_NAME NVARCHAR(256) = NULL
, @SQL_HANDLE BINARY(20) = NULL
, @START_TIME SMALLDATETIME = NULL
, @END_TIME SMALLDATETIME = NULL
, @SPID INT = NULL
AS

IF @TYPE <> 6 BEGIN

 ; WITH DATA AS
 (SELECT CONVERT(SMALLDATETIME,FLOOR((CAST(D AS FLOAT) * 24 * 60) / @GROUP_MIN ) * @GROUP_MIN / 24 / 60, 108) TIMEX, LOGINAME, NAME, HOSTNAME, PROGRAM_NAME, SQL_HANDLE, LASTWAITTYPE
   FROM ##TBLWAITS
  WHERE SQL_HANDLE = CASE WHEN @SQL_HANDLE IS NULL THEN SQL_HANDLE ELSE @SQL_HANDLE END
    AND LOGINAME = CASE WHEN @LOGINAME IS NULL THEN LOGINAME ELSE @LOGINAME END
    AND NAME = CASE WHEN @NAME IS NULL THEN NAME ELSE @NAME END
    AND HOSTNAME = CASE WHEN @HOSTNAME IS NULL THEN HOSTNAME ELSE @HOSTNAME END
    AND PROGRAM_NAME = CASE WHEN @PROGRAM_NAME IS NULL THEN PROGRAM_NAME ELSE @PROGRAM_NAME END
    AND SPID = CASE WHEN @SPID IS NULL THEN SPID ELSE @SPID END
    AND D BETWEEN CASE WHEN @START_TIME IS NULL THEN '19750523' ELSE @START_TIME END AND CASE WHEN @END_TIME IS NULL THEN '20790606' ELSE @END_TIME END )
 , PER_WAITTYPE AS
 (SELECT TIMEX, LASTWAITTYPE VAL, COUNT(*) CNT FROM DATA GROUP BY TIMEX, LASTWAITTYPE WITH ROLLUP)
 , PER_LOGINAME AS
 (SELECT TIMEX, LOGINAME, COUNT(*) CNT FROM DATA GROUP BY TIMEX, LOGINAME WITH ROLLUP)
 , PER_DATABASE AS
 (SELECT TIMEX, NAME, COUNT(*) CNT FROM DATA GROUP BY TIMEX, NAME WITH ROLLUP)
 , PER_HOSTNAME AS
 (SELECT TIMEX, HOSTNAME, COUNT(*) CNT FROM DATA GROUP BY TIMEX, HOSTNAME WITH ROLLUP)
 , PER_PROGRAM_NAME AS
 (SELECT TIMEX, PROGRAM_NAME, COUNT(*) CNT FROM DATA GROUP BY TIMEX, PROGRAM_NAME WITH ROLLUP)
 , PER_SQL_HANDLE_RAW AS
 (SELECT TIMEX, SQL_HANDLE, COUNT(*) CNT FROM DATA GROUP BY TIMEX, SQL_HANDLE WITH ROLLUP)
 , PER_SQL_HANDLE AS
 (SELECT TIMEX, CAST(SQL_HANDLE AS SQL_VARIANT) SQL_HANDLE, CNT, QT.TEXT FROM PER_SQL_HANDLE_RAW A OUTER APPLY SYS.DM_EXEC_SQL_TEXT(A.SQL_HANDLE) QT
 )
 SELECT *
   FROM (SELECT * FROM PER_WAITTYPE WHERE @TYPE = 1
   UNION ALL SELECT * FROM PER_LOGINAME WHERE @TYPE = 2
   UNION ALL SELECT * FROM PER_DATABASE WHERE @TYPE = 3
   UNION ALL SELECT * FROM PER_HOSTNAME WHERE @TYPE = 4
   UNION ALL SELECT * FROM PER_PROGRAM_NAME WHERE @TYPE = 5) A
  ORDER BY CASE WHEN TIMEX IS NULL THEN '20790606' ELSE TIMEX END , CNT
 OPTION (RECOMPILE)
END

IF @TYPE = 6 BEGIN
 ; WITH DATA AS
 (SELECT CONVERT(SMALLDATETIME,FLOOR((CAST(D AS FLOAT) * 24 * 60) / @GROUP_MIN ) * @GROUP_MIN / 24 / 60, 108) TIMEX, LOGINAME, NAME, HOSTNAME, PROGRAM_NAME, SQL_HANDLE, LASTWAITTYPE
   FROM ##TBLWAITS
  WHERE SQL_HANDLE = CASE WHEN @SQL_HANDLE IS NULL THEN SQL_HANDLE ELSE @SQL_HANDLE END
    AND LOGINAME = CASE WHEN @LOGINAME IS NULL THEN LOGINAME ELSE @LOGINAME END
    AND NAME = CASE WHEN @NAME IS NULL THEN NAME ELSE @NAME END
    AND HOSTNAME = CASE WHEN @HOSTNAME IS NULL THEN HOSTNAME ELSE @HOSTNAME END
    AND PROGRAM_NAME = CASE WHEN @PROGRAM_NAME IS NULL THEN PROGRAM_NAME ELSE @PROGRAM_NAME END
    AND SPID = CASE WHEN @SPID IS NULL THEN SPID ELSE @SPID END
    AND D BETWEEN CASE WHEN @START_TIME IS NULL THEN '19750523' ELSE @START_TIME END AND CASE WHEN @END_TIME IS NULL THEN '20790606' ELSE @END_TIME END )
 , PER_SQL_HANDLE AS
 (SELECT TIMEX, SQL_HANDLE, COUNT(*) CNT FROM DATA GROUP BY TIMEX, SQL_HANDLE WITH ROLLUP )
 SELECT A.*, SQL_TEXT
   FROM PER_SQL_HANDLE A
   LEFT JOIN ##TBLSQLTEXT B
  ON B.SQL_HANDLE = A.SQL_HANDLE
  ORDER BY CASE WHEN TIMEX IS NULL THEN '20790606' ELSE TIMEX END , CNT
END
GO

IF OBJECT_ID('AP_PUT_WAITSTATS_1') IS NOT NULL
DROP PROC AP_PUT_WAITSTATS_1
GO

CREATE PROC AP_PUT_WAITSTATS_1
@MIN INT = 10
AS

SET NOCOUNT ON
-- SCRIPT BY MINSOUK KIM (SQL SERVER MVP SINCE 2006)
-- DEBUG
-- DECLARE @MIN INT
-- SET @MIN = 1
-- END OF DEBUG
DECLARE @END_TIME DATETIME

SELECT @END_TIME = DATEADD(MINUTE, ABS(@MIN), GETDATE())
SELECT @END_TIME

BEGIN TRY
 DROP TABLE ##TBLWAITS
END TRY BEGIN CATCH END CATCH  

BEGIN TRY
 DROP TABLE ##TBLSQLTEXT
END TRY BEGIN CATCH END CATCH  

SELECT * INTO ##TBLWAITS
  FROM (SELECT TOP 0 CAST(S.LOGINAME AS NVARCHAR(256)) LOGINAME
    , CAST(D.NAME AS NVARCHAR(256)) NAME
    , CAST(S.HOSTNAME AS NVARCHAR(256)) HOSTNAME
    , CAST(S.PROGRAM_NAME AS NVARCHAR(256)) PROGRAM_NAME , S.SQL_HANDLE
    , S.SPID, CONVERT(SMALLINT, S.WAITTYPE) WAITTYPE
    , S.LASTWAITTYPE, S.ECID, S.WAITTIME , S.BLOCKED, GETDATE() D
    FROM MASTER..SYSDATABASES AS D WITH(NOLOCK)
    , MASTER..SYSPROCESSES AS S WITH(NOLOCK)
   WHERE S.CMD<>'AWAITING COMMAND'
     AND S.CMD NOT LIKE '%BACKUP%'
     AND S.CMD NOT LIKE '%RESTORE%'
     AND S.SPID>50
     AND S.SPID<>@@SPID
     AND S.DBID = D.DBID 
   ORDER BY S.SPID
    , S.ECID ASC) A

SELECT * INTO ##TBLSQLTEXT
  FROM (SELECT TOP 0 S.SQL_HANDLE, S.STMT_START, S.STMT_END, CAST('' AS VARCHAR(8000)) SQL_TEXT
    FROM MASTER..SYSDATABASES AS D WITH(NOLOCK)
    , MASTER..SYSPROCESSES AS S WITH(NOLOCK)
   WHERE S.CMD<>'AWAITING COMMAND'
     AND S.CMD NOT LIKE '%BACKUP%'
     AND S.CMD NOT LIKE '%RESTORE%'
     AND S.SPID>50
     AND S.SPID<>@@SPID
     AND S.DBID = D.DBID 
   ORDER BY S.SPID
    , S.ECID ASC) A

WHILE (GETDATE() < @END_TIME) BEGIN
 INSERT INTO ##TBLWAITS
 SELECT S.LOGINAME, D.NAME, S.HOSTNAME, S.PROGRAM_NAME, S.SQL_HANDLE
   , S.SPID, CONVERT(SMALLINT, S.WAITTYPE) WAITTYPE
   , S.LASTWAITTYPE, S.ECID, S.WAITTIME , S.BLOCKED, GETDATE() D
   FROM MASTER..SYSDATABASES AS D WITH(NOLOCK)
   , MASTER..SYSPROCESSES AS S WITH(NOLOCK)
  WHERE S.CMD<>'AWAITING COMMAND'
    --AND S.CMD NOT LIKE '%BACKUP%'
    --AND S.CMD NOT LIKE '%RESTORE%'
    AND S.SPID>50
    AND S.SPID<>@@SPID
    AND S.DBID = D.DBID 
  ORDER BY S.SPID
   , S.ECID ASC

 INSERT INTO ##TBLSQLTEXT (SQL_HANDLE, STMT_START, STMT_END, SQL_TEXT)
 SELECT A.*, C.TEXT
   FROM (SELECT S.SQL_HANDLE, S.STMT_START, S.STMT_END
     FROM MASTER..SYSDATABASES AS D WITH(NOLOCK)
     , MASTER..SYSPROCESSES AS S WITH(NOLOCK)
    WHERE S.CMD<>'AWAITING COMMAND'
      AND S.CMD NOT LIKE '%BACKUP%'
      AND S.CMD NOT LIKE '%RESTORE%'
      AND S.SPID>50
      AND S.SPID<>@@SPID
      AND S.DBID = D.DBID) A
   LEFT JOIN ##TBLSQLTEXT B
  ON B.SQL_HANDLE = A.SQL_HANDLE
  CROSS APPLY SYS.DM_EXEC_SQL_TEXT(A.SQL_HANDLE) C
  WHERE B.SQL_HANDLE IS NULL

 WAITFOR DELAY '00:00:00.100'
END
GO

 

 


-- +++++++++++++++++++++++++++++++++++++++++++++++++++++
-- WAIT 조사용 프로시저 돌리기 
-- +++++++++++++++++++++++++++++++++++++++++++++++++++++

EXEC MASTER.DBO.AP_PUT_WAITSTATS_1 10

-- +++++++++++++++++++++++++++++++++++++++++++++++++++++
-- 다른 세션에서 다음을 확인 한다.
-- +++++++++++++++++++++++++++++++++++++++++++++++++++++

-- 구조 확인용
-- SELECT TOP 10 * FROM ##TBLWAITS
-- SELECT TOP 10 * FROM ##TBLSQLTEXT

 


---- 어떤 WAIT  이 있나?
--EXEC MASTER.DBO.AP_GET_WAITSTATS_1 @TYPE =1
--EXEC MASTER.DBO.AP_GET_WAITSTATS_1 @TYPE =2
--EXEC MASTER.DBO.AP_GET_WAITSTATS_1 @TYPE =3
--EXEC MASTER.DBO.AP_GET_WAITSTATS_1 @TYPE =4
--EXEC MASTER.DBO.AP_GET_WAITSTATS_1 @TYPE =5
--EXEC MASTER.DBO.AP_GET_WAITSTATS_1 @TYPE =6

---- 디테일 정보 확인
--EXEC MASTER.DBO.AP_GET_WAITSTATS_1
--   @TYPE = 1
-- , @GROUP_MIN = 5
-- --, @LOGINAME = 'NB10202-PC\Administrator'
-- --, @NAME = 'TEMPDB'
-- --, @HOSTNAME = 'NB10202-PC'                                                                                                                 
-- --, @PROGRAM_NAME = 'Microsoft SQL Server Management Studio - 쿼리'
-- --, @SQL_HANDLE = 0x01000100FAE6651BF09DF1050000000000000000
-- --, @START_TIME = '2010-01-06 04:21:00'
-- --, @END_TIME = '2010-01-06 04:24:00'

---- +++++++++++++++++++++++++++++++++++++++++++++++++++++
---- 클리어
---- +++++++++++++++++++++++++++++++++++++++++++++++++++++
--BEGIN TRY
-- DROP TABLE ##TBLWAITS
--END TRY BEGIN CATCH END CATCH  

--BEGIN TRY
-- DROP TABLE ##TBLSQLTEXT
--END TRY BEGIN CATCH END CATCH 


돌린 결과는 다음과 같다.

 

프로파일러(링크를 따라가면, 오른쪽 상단에 자세한 DOC 문서거 첨부되어 있습니다. )

http://cafe.naver.com/sqlmvp/68

DMV

http://cafe.naver.com/sqlmvp/688

XEvents (2012 버전부터 쓸만 합니다. 2008R2 버전까지는 RPC 에서의 SQL_TEXT Action 을 캡처하지 못해 쓸만하지 못합니다. )

http://cafe.naver.com/sqlmvp/3775

http://cafe.naver.com/sqlmvp/3398

ERROR 찾기

http://cafe.naver.com/sqlmvp/626

 

 

 

신고

'밥벌이' 카테고리의 다른 글

varchar(max) 주의사항  (9) 2010.11.26
OPTIMIZE FOR ( @variable_name { UNKNOWN | = literal_constant } [ , ...n ] )  (0) 2010.11.19
민석이의 waitstat 모니터링  (0) 2010.11.18
부하 데이터생성  (0) 2010.11.18
Array Insert  (1) 2010.11.17
이 쿼리의 결과는 뭘까요?  (6) 2010.11.04
Posted by 보미아빠

부하 데이터생성

밥벌이 / 2010.11.18 16:05

if object_id('sm_cpu_pfmnc_log') is not null
drop table sm_cpu_pfmnc_log
go

create table sm_cpu_pfmnc_log
(
     host_id              CHARACTER VARYING(13)
,     log_ocr_ymdt         datetime
,     cpu_idx              SMALLINT
,     used_rto             NUMERIC(20,2)
,     idel_rto             NUMERIC(20,2)
,     user_rto             NUMERIC(20,2)
,     sys_rto              NUMERIC(20,2)
,     nice_rto             NUMERIC(20,2)
,     irq_rto              NUMERIC(20,2)
,     softirq_rto          NUMERIC(20,2)
,     io_wait_rto          NUMERIC(20,2)
,     prv_mde_exec_tm_rto  NUMERIC(20,2)
,     dly_pcd_call_tm_rto  NUMERIC(20,2)
)

--select * from sm_cpu_pfmnc_log

;with nums as (
select 0 a union all select 1 union all select 2 union all select 3 union all select 4 union all
select 5 union all select 6 union all select 7 union all select 8 union all select 9
)
, mm as (select a.a+b.a*10 mm from nums a ,nums b where a.a+b.a*10 < 60)
, hh as (select a.a+b.a*10 hh from nums a ,nums b where a.a+b.a*10 < 24)
, hostname as (select a.a+b.a*10+c.a*100+d.a*1000 hostname from nums a, nums b, nums c, nums d where a.a+b.a*10+c.a*100+d.a*1000 < 2)
insert into sm_cpu_pfmnc_log
select right('000000000000000'+cast(hostname as varchar(100)), 13) host_id
  , dateadd(hh, hh, dateadd(mi, mm , getdate())) log_ocr_ymdt
  , hh % 8 cpu_idx
  , cast(abs(checksum(newid()))/3.33 as numeric(20,2)) used_rto
  , cast(abs(checksum(newid()))/3.33 as numeric(20,2)) idel_rto
  , cast(abs(checksum(newid()))/3.33 as numeric(20,2)) user_rto
  , cast(abs(checksum(newid()))/3.33 as numeric(20,2)) sys_rto
  , cast(abs(checksum(newid()))/3.33 as numeric(20,2)) nice_rto
  , cast(abs(checksum(newid()))/3.33 as numeric(20,2)) irq_rto
  , cast(abs(checksum(newid()))/3.33 as numeric(20,2)) softirq_rto
  , cast(abs(checksum(newid()))/3.33 as numeric(20,2)) io_wait_rto
  , cast(abs(checksum(newid()))/3.33 as numeric(20,2)) prv_mde_exec_tm_rto
  , cast(abs(checksum(newid()))/3.33 as numeric(20,2)) dly_pcd_call_tm_rto
  from hostname, hh, mm
order by 2

-- 아래와 같이 파일로 생성
select right(cast(used_rto as varchar(20)),5), * from sm_cpu_pfmnc_log order by log_ocr_ymdt
-- 생성된 파일을 array insert 하기로 함

신고
Posted by 보미아빠

Array Insert

밥벌이 / 2010.11.17 18:26
46배 빨라지면 쓸만한가?






Begin tran Commit tran 은 빼고 Test 하시길 바랍니다.

alter database t set recovery simple with rollback immediate
go

if object_id ('tblx') is not null
drop table tblx
go

create table tblx
(c1 int
,c2 int
,c3 int
,c4 int
,c5 int
,c6 int
,c7 int
,c8 int
,c9 int
,c10 int
,c11 int
,c12 int
,c13 int
,c14 int
,c15 int
)
go

declare
  @total_loop_count int = 100000
 ,@current_loop_count int = 0
 ,@array_insert int = 0
 ,@start_time datetime2 = getdate()
 ,@end_time datetime2 = getdate()

begin tran
while (1=1) begin
 if @array_insert = 1 begin
  insert into tblx values
       (1,1,1,1,1,1,1,1,1,1,1,1,1,1,1)
        , (1,1,1,1,1,1,1,1,1,1,1,1,1,1,1)
        , (1,1,1,1,1,1,1,1,1,1,1,1,1,1,1)
        , (1,1,1,1,1,1,1,1,1,1,1,1,1,1,1)
        , (1,1,1,1,1,1,1,1,1,1,1,1,1,1,1)
        , (1,1,1,1,1,1,1,1,1,1,1,1,1,1,1)
        , (1,1,1,1,1,1,1,1,1,1,1,1,1,1,1)
        , (1,1,1,1,1,1,1,1,1,1,1,1,1,1,1)
        , (1,1,1,1,1,1,1,1,1,1,1,1,1,1,1)
        , (1,1,1,1,1,1,1,1,1,1,1,1,1,1,1)
  set @current_loop_count += 10
  print @current_loop_count
  if @current_loop_count >= @total_loop_count break;
 end else begin
  insert into tblx values
       (1,1,1,1,1,1,1,1,1,1,1,1,1,1,1)
  set @current_loop_count += 1
  print @current_loop_count
  if @current_loop_count >= @total_loop_count break;
 end
end
commit tran

select datediff(second, @start_time, getdate()) '입력시간 sec'

신고

'밥벌이' 카테고리의 다른 글

민석이의 waitstat 모니터링  (0) 2010.11.18
부하 데이터생성  (0) 2010.11.18
Array Insert  (1) 2010.11.17
이 쿼리의 결과는 뭘까요?  (6) 2010.11.04
insert select 로 인해 플랜 공간이 소모됩니다.  (0) 2010.10.28
insert 속도를 높여보자  (1) 2010.10.18
Posted by 보미아빠

select *
  from tblx a
  left join tbly b
    on a.idx = b.idx
   and a.idx in (1,2)
   and b.col1 = 10


이거라고 생각하는가?



if object_id('tblx') is not null
drop table tblx
go

if object_id('tbly') is not null
drop table tbly
go

create table tblx
(idx int
,col1 int
,col2 int
)
go

insert into tblx values (1,10,100)
insert into tblx values (2,20,200)
insert into tblx values (3,30,300)
insert into tblx values (4,40,400)
insert into tblx values (5,50,500)

go

create table tbly
(idx int
,col1 int
,col2 int
)
go

insert into tbly values (1,10,100)
insert into tbly values (2,20,200)
insert into tbly values (3,30,300)
go

내가 위 질문을 했을때 10명에 1명도 답을 못하더라....... 이는 sql join 의 on 과 where 를 아직도 이해하지 못하고 마구 쓰는 사람이 대부분 이라는 것이다. 심심하면 당신이 일하는 회사의 sql 팀장에게 문의해 봐라....모르는 사람도 있을듯 하다.

create database l
go

use l
go
if object_id ('tblx') is not null
drop table tblx
go

create table tblx
(idx int
,c1 int
,c2 int
)
go

if object_id ('tbly') is not null
drop table tbly
go

create table tbly
(idx int
,c1 int
,c2 int
)
go

insert into tblx values (1,1,1),(2,2,1),(3,null,1),(4,4,null)
insert into tbly values (1,1,1),(2,2,1),(4,4,null)
go

--exec sp_dbcmptlevel l, 80

select *
  from tblx a, tbly b
 where a.idx *= b.idx
   and (b.idx = 1 or b.idx is null)
  
이건 이렇게 풀 수 있다.

select *
  from tblx a
  left join tbly b
    on a.idx = b.idx
   and (b.idx = 1 or b.idx is null)

다음의 경우를 생각해 보자.
   
select *
  from tblx a
  left join tbly b
    on a.idx = b.idx
 where b.idx = 1
    or b.idx is null
   
위 쿼리를 tsql 조인으로 해보라 결과가 나오지 않는다.

select *
  from tblx a, tbly b
 where a.idx *= b.idx
   and (b.idx = 1 or b.idx is null)

이렇게 풀 수 밖에 없다. 이건 불편하다.

select *
  from (select a.*, b.idx bidx, b.c1 bc1, b.c2 bc2
    from tblx a, tbly b
   where a.idx *= b.idx) a
 where bidx = 1 or bidx is null
     
     
정의를 내려보면 다음과 같다.

- ansi left outer join 에서 on 절에는 join 조건을 기술하고 where 조건은 필터를 표시한다. 그러므로 어떠한 경우에도 명확하게 표현할 수 있다.  

 

- tsql *= (left join) 은 driving table 의 조건은 filter 조건이고, drived 되는 테이블의 조건은 join 조건으로만 풀려 drived 되는 테이블을 필터로 풀 수 없는 문제가 있게된다. 이러한 불편을 해소하기 위해 tsql *= (outer join) 을 sql 서버에서 차기 버전부터 지원하지 않기로 한 것이다. work around 로 tsql *= 에서 inline view 를 같이 쓰면 drived 되는 테이블의 filter 조건을 명시 할 수 있다.

 

- 따라서 이것을 누구처럼 먼저하고 저것을 먼저하고 라고 해석하면 안된다.  


신고
Posted by 보미아빠

아래와 같이 해결 가능하다.
(이건 모 sql 모니터링 업체에서 질문한 내용입니다.)

if object_id ('tblx') is not null
drop table tblx
go

create table tblx
(idx int)
go

insert into tblx (idx) select 1 union all select 2
go
insert into tblx (idx) select 2 union all select 3
go
--syscacheobjects sql
--insert into tblx (idx) select 1 union all select 2

insert into tblx values(1),(2)
go
insert into tblx values(2),(3)
go
--syscacheobjects sql
--(@1 int,@2 int)INSERT INTO [tblx] values(@1),(@2)


dbcc freeproccache
select * from master.dbo.syscacheobjects

신고
Posted by 보미아빠


로킹 시스템 DB 아키텍팅이라는 새로운 업무를 진행중이다. 
어떻게 하면 가장 빠르게 데이터를 넣을 수 있고,
어떻게 하면 가장 빠르게 데이터를 인출 할 수 있을 것인가에 대한 고민이다.

먼저 데이터 삽입에 대해서는 이러한 방법이 테스트의 대상이 될 듯 하다.

방법1
insert into tblx values (1,'minsouk')
go
insert into tblx values (2,'eunkyoung')
go
insert into tblx values (3,'bomi')
go

방법2
begin tran
   insert into tblx values (1,'minsouk')
   go
   insert into tblx values (2,'eunkyoung')
   go
   insert into tblx values (3,'bomi')
   go
commit tran

방법3

if object_id('usp_insert_tblx') is null
exec ('create proc usp_insert_tblx as select 1')
go

alter proc usp_insert_tblx
 @idx int
,@cname varchar(200)
as
insert into tblx values(@idx, @cname)
go

exec usp_insert_tblx 1, 'minsouk'
go
exec usp_insert_tblx 2, 'eunkyoung'
go
exec usp_insert_tblx 3, 'bomi'
go

방법4
begin tran
   exec usp_insert_tblx 1, 'minsouk'
   go
   exec usp_insert_tblx 2, 'eunkyoung'
   go
   exec usp_insert_tblx 3, 'bomi'
   go
commit tran

방법5
sqlcli를 테이블 변수 이용

방법6
insert into tblx values (1,'minsouk'), (2,'eunkyoung'), (3,'bomi')

글을 읽고있는 당신은 무엇이 가장 빠르다고 생각되는가?
더 빠르게 넣을 수 있는 방법이 있다고 생각하는가?
단 쿼리문의 변경과 insert 하는 방법론으로만 이야기를 한정 하고자 한다.
로그 디스크를 어떻게 구성하고 등등은 2부로 하자.

신고
Posted by 보미아빠

sp_reset_connection 이 무엇을 하는가? 또 무엇을 하지 못하는가?

sp_reset_connection resets the following aspects of a connection:

It resets all error states and numbers (like @@error)
It stops all EC's (execution contexts) that are child threads of a parent EC executing a parallel query
It will wait for any outstanding I/O operations that is outstanding
It will free any held buffers on the server by the connection
It will unlock any buffer resources that are used by the connection
It will release all memory allocated owned by the connection
It will clear any work or temporary tables that are created by the connection
It will kill all global cursors owned by the connection
It will close any open SQL-XML handles that are open
It will delete any open SQL-XML related work tables
It will close all system tables
It will close all user tables
It will drop all temporary objects
It will abort open transactions
It will defect from a distributed transaction when enlisted
It will decrement the reference count for users in current database; which release shared database lock
It will free acquired locks
It will releases any handles that may have been acquired
It will reset all SET options to the default values
It will reset the @@rowcount value
It will reset the @@identity value
It will reset any session level trace options using dbcc traceon()

sp_reset_connection will NOT reset:
Security context, which is why connection pooling matches connections based on the exact connection string
If you entered an application role using sp_setapprole, since application roles can not be reverted

Note: Pasting the content as I do not want it to be lost in the ever transient web

logon trigger 의 발동

sp_reset_connection 이 발생되면, logon trigger 도 동작하게 된다 예전에는 sp_reset_connection 을 호출 하지 않을 방법이 있었지만, 해당 옵션은 버전이 올라가면서 제거 되었다. logon trigger 는 시간으로도 접근제어가 가능하도록 디자인 되어 있어 sp_reset_connection 이 발생 할때마다 체크 되도록 되어 있다. (by design)

linked server 연결에서 logon trigger 가 문제인데, sp_reset_connection 을 호출 하지 않게 할 수 없나?

있다. 다음과 같이 다른 방식으로 연결하면 사용하지 않는다.

-- 기존 전통적인 방식
EXEC master.dbo.sp_addlinkedserver @server = N'TEST1', @srvproduct=N'SQL Server'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'TEST1',@useself=N'False',@locallogin=NULL,@rmtuser=N'sqler',@rmtpassword='!1234'


-- login logout 을 하지 않기 위해서 아래와 같이 odbc 를 사용합니다.
EXEC master.dbo.sp_addlinkedserver @server = N'test2', @srvproduct=N' ', @provider=N'MSDASQL', @provstr=N'Driver={SQL Server};Database=master;Server=10.1.1.1;UID=sqler;PWD=!1234;'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'test2',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL


while (1=1) begin
 waitfor delay '00:00:00.500'
 select top 1  * from test1.master.dbo.sysprocesses -- 이넘은 login logout 을 계속 합니다.
 --select top 1  * from test2.master.dbo.sysprocesses -- 이건 login logout 을 하지 않습니다.
end


참고자료
http://msdn.microsoft.com/en-us/library/aa172690(v=SQL.80).aspx

다음은 같은 event 로 잡은 프로파일링 데이터 이다.

test1 의 프로파일링
sp_reset_connection 이 발생하고 eventsubclass 에 pooled 라고 찍혀 있다.
여담이지만, sp_reset_connection 은 connection pooling 이 되고 있을때만 발생한다. 이걸보고 login logout 이 생긴다고 pooling 을 사용하지 못한다라고 말하는이가 있거나 오해가 없길 바란다.


test2 의 프로파일링
sp_reset_connection 호출이 없다.


msdasql 을 쓸때 tempdb 나 기타 reset 해야 할 부분이 잘 되는가는 만세가 테스트 하기로 함....
test 할 때 set ansi_nulls default on off 같은걸로 테스트 하면 금방 할끼야~ 리셋 안되야 정상이지 안불렀는데...ㅡ.ㅡ
목적에 따라서 잘 쓰시길 바랍니다.

 

신고
Posted by 보미아빠

SQL Server I/O Basic

밥벌이 / 2010.10.11 13:09


http://technet.microsoft.com/en-us/library/cc966500.aspx
http://technet.microsoft.com/en-us/library/cc917726.aspx
http://blogs.technet.com/b/josebda/archive/2009/03/31/sql-server-2008-i-o-performance.aspx

 

Microsoft SQL Server IO Internals.pptx



읽기의 물리적인 단위는 extents 이고 논리적인 읽기의 단위는 page 이다. 쓰기의 물리적인 단위는 page 이다.
그리고, 823,824 등등 오류의 정의도 볼 수 있다.

Object ID

This is the ID of the object to which the page is assigned within the schema of the database. A page can be assigned to only a single object. When the page is read from disk, the object ID is checked on the page. If the object ID does not match the expected object ID, SQL Server will generate Error 605.

SQL Server often performs its writes on page-sized, 8-KB, or larger boundaries.

Extents

SQL Server generally (except for nonmixed extents) allocates space an extent at a time. An extent is eight 8-KB pages, or 64 KB. SQL Server often performs reads in extent-sized (64 KB or 128 KB) boundaries as well.

신고
Posted by 보미아빠

최근에 달린 댓글

최근에 받은 트랙백

글 보관함

티스토리 툴바