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

카테고리

보미아빠, 석이 (441)
밥벌이 (16)
싸이클 (1)
일상 (1)
Total196,651
Today37
Yesterday46

달력

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

공지사항


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

최근에 달린 댓글

최근에 받은 트랙백

글 보관함