블로그 이미지
불쥐의 눈으로 본세상 ㏈ª ☞ β┖υΕJini.κR

카테고리

분류 전체보기 (234)
By β┖υΕJini (131)
㏈ª By β┖υΕJini (103)
Total302,468
Today16
Yesterday15
SQL2k5 의 update statistics 문서화 되지 않은 옵션

SQL Server 2005의 update statistics에 문서화 되지 않은 추가적인 설정 값(pagecount,rowcount)이 추가 되었습니
다.
문서화 되지 않은 옵션이기 때문에 일반적인 서비스를 제공하는 환경에서는 사용하시면 다른 문제가 발생될 가능성
이 충분이 있습니다.
그러기에 테스트 용도로만 사용하시기 바랍니다.

아래는 SQL Server 2000 및 2005에 대해서 통계 정보에 대해서 쿼리의 실행계획이 어떻게 변경이 되는지 알아보고 있
습니다.
여기서 말하는 통계정보는 행수 와 페이지수를 말합니다.

/*==================SQL Sever 2005
Select @@version
Microsoft SQL Server 2005 - 9.00.2040.00 (Intel X86)
Mar 13 2006 11:20:51
Copyright (c) 1988-2005 Microsoft Corporation
Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
*/

Use Tempdb

--Drop Table t
Create table t(a int identity , b int )

Insert into t valueS(@@identity)
Insert into t valueS(@@identity)
Insert into t valueS(@@identity)
Insert into t valueS(@@identity)
Insert into t valueS(@@identity)

select id,indid,dpages,rowcnt,rows from sysindexes where id = object_id('t')
/*
id          indid  dpages      rowcnt               rows       
----------- ------ ----------- -------------------- -----------
1255675521  0      1           5                    5
*/

SET STATISTICS PROFILE ON

--행이 많이 있지 않기때문에 일반적인 SORT작업으로 DISTINCT를 수행 하였음.
select distinct(b) from t
5 1 select distinct(b) from t
5 1   |--Sort(DISTINCT ORDER BY:([tempdb].[dbo].[t].[b] ASC))
5 1        |--Table Scan(OBJECT:([tempdb].[dbo].[t]))
EstimateRows
5.0
5.0
5.0

--문서화 되지 않는 rowcount,pagecount 옵션을 사용해서 많은 행,페이지수를 설정함.
update statistics t with rowcount = 1000000000, pagecount = 90000;

--추가적인 시스템 통계정보가 생성됨.
select name,id,indid,dpages,rowcnt,rows from sysindexes where id = object_id('t')
/*
name                            id          indid  dpages      rowcnt               rows       
----------------------- ------ ----------- -------------------- -----------
NULL                       1255675521  0      90000       1000000000           1000000000
_WA_Sys_00000002_4AD81681    1255675521  2      0           0                    0
*/

--추가된 통계 정보 제거
DROP STATISTICS t._WA_Sys_00000002_4AD81681

--통계 정보에 많은 행이 있기 때문에 SORT가 아닌 hash로 연산 작업을 함.
select distinct(b) from t --option(recompile)
5 1 select distinct(b) from t option(recompile)
5 1   |--Hash Match(Aggregate, HASH:([tempdb].[dbo].[t].[b]), RESIDUAL:([tempdb].
[dbo].[t].[b] = [tempdb].[dbo].[t].[b]))
5 1        |--Table Scan(OBJECT:([tempdb].[dbo].[t]))
--예상행 수
EstimateRows
31622.777
31622.777
31622.777
63245.555
63245.555
1.0E+9

/*==================SQL Sever 2000
Select @@version
Microsoft SQL Server  2000 - 8.00.2187 (Intel X86)
Mar  9 2006 11:38:51
Copyright (c) 1988-2003 Microsoft Corporation
Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
*/
Use Tempdb

--Drop Table t
Create table t(a int identity , b int )

Insert into t valueS(@@identity)
Insert into t valueS(@@identity)
Insert into t valueS(@@identity)
Insert into t valueS(@@identity)
Insert into t valueS(@@identity)


select id,indid,dpages,rowcnt,rows from sysindexes where id = object_id('t')
/*
id          indid  dpages      rowcnt               rows       
----------- ------ ----------- -------------------- -----------
811149935   0      1           5                    5
*/

SET STATISTICS PROFILE ON

--역시 SORT로 DISTINCT작업을 수행함.
select distinct(b) from t
/*
5 1 select distinct(b) from t
5 1   |--Sort(DISTINCT ORDER BY:([t].[b] ASC))
5 1        |--Table Scan(OBJECT:([tempdb].[dbo].[t]))

EstimateRows
5.0
5.0
5.0
*/

EXEC sp_configure 'allow updates',1
RECONFIGURE WITH OVERRIDE

--UPDATE STATISTICS의 rowcount/pagecount 가 지원이 되지 않아 바로 업데이트
update sysindexes SET
dpages = 90000,
Rowcnt = 1000000000
Where id = object_id('t') AND Indid = 0

--해당 값이 변한 것을 확인
select name,id,indid,dpages,rowcnt,rows from sysindexes where id = object_id('t')
/*
name                            id          indid  dpages      rowcnt               rows       
----------------------- ------ ----------- -------------------- -----------
NULL                       1255675521  0      90000       1000000000           1000000000
_WA_Sys_b_30592A6F     11149935   2      0           0                    0
*/

--추가된 통계 정보 제거
DROP STATISTICS t._WA_Sys_b_30592A6F

--역시 처음과 같은 실행계획으로 풀리지 않았음.
select distinct(b) from t
/*
5 1 select distinct(b) from t
5 1   |--Parallelism(Gather Streams)
5 4        |--Sort(DISTINCT ORDER BY:([t].[b] ASC))
5 4             |--Parallelism(Repartition Streams, PARTITION COLUMNS:([t].[b]))
5 4                  |--Table Scan(OBJECT:([tempdb].[dbo].[t]))

EstimateRows(예상 행)
2.721337E+8
2.721337E+8
2.721337E+8
1.0E+9
1.0E+9
*/

결론 :
아시다시피 SQL Server 2000, 2005는 통계정보 및 기반 정보를 기반으로 실행계획을 만드는 CBO를 가지고 있습니다.
이러한 통계 정보가 잘못 또는 부족할 경우 실행계획이 변경되는 것을 보다 직관적으로 볼 수 있으며, 이 통계정보가
얼마나 중요한지 확인 할 수 있습니다.

참고)
테스트 머신은 일반 1CPU를 가지고 있는 머신이며 위 Parallelism연산자는 SQL서버의 시작 옵션 중 -P4를 추가 하
여 논리적인 UMS를 4개로 만들어서 보여지고 있습니다.
물리적인 프로세스로 인해 병렬처리를 테스트 하지 못할때 필요한 옵션입니다. 그러나 성능에 대한 문제는...

참고 자료)
UPDATE STATISTICS undocumented options
http://blogs.msdn.com/queryoptteam/archive/2006/07/21/674350.aspx
Posted by ㏈ª ☞ β┖υΕJini.κR

댓글을 달아 주세요

MS-SQL 트랜젝션 처리시 아래와 같이 사용 한다.

한 프로시져에서 여러개의 구문을 실행시 @@error

Begin Transaction
1) Insert 실행
2) Insert 실행

IF(@@Error <> 0)
  RollBack Transaction
ELSE
  Commit Transaction


@@Error의 값은 명령이 실핼할 때마다 업데이트가 되기 때문에 1)번은 실패, 2)번은 성공을 했다면 Commit이 발생하게 된다. -- 즉 1번이 실행이 실패된 상태로 인서트가 발생된다.
Begin Transaction
1) Insert 실행 --> 실패
@@Error 값은 1
2) Insert 실행 --> 성공
@@Error 값은 0

IF(@@Error <> 0) -- 최종적으로 @@Error값이 0이므로 Transaction은 Commit이 된다.
  RollBack Transaction
ELSE
  Commit Transaction


1. Declare @CntError Int

Begin Transaction
    Declare @CntError int
    SET @CntError = 0

    1) Insert 실행
    IF(@@Error <> 0)
        SET @CntError=@CntError+1

    2) Insert 실행
    IF(@@Error <> 0)
        SET @CntError=@CntError+1

    IF(@CntError>0)
        RollBack Transaction
    ELSE
        Commit Transaction


2. IF @@error<>0 GOTO _RollbackLabel

Begin Transaction
    1) Insert 실행
    IF(@@Error <> 0) GOTO _QuitTran

    2) Insert 실행
    IF(@@Error <> 0) GOTO _QuitTran

COMMIT TRAN
GOTO _SaveTran

_QuitTran:
----------------------------------------------------
IF @@TRANCOUNT > 0
BEGIN
    ROLLBACK TRAN
END
RETURN
----------------------------------------------------

_SaveTran:
----------------------------------------------------
RETURN
----------------------------------------------------
GO
Posted by ㏈ª ☞ β┖υΕJini.κR

댓글을 달아 주세요

1.  Database size 늘리기(3가지)

    1) 자동 증가하도록 Database 옵션 설정.

        size, maxsize, filegrowth 옵션을 사용해서 해야한다.

    2) 데이터 파일의 사이즈 확장

        alter Database MyDB

        modify file(name=testdata1, size=20mb)

        sp_helpDB Mydb

    3) 데이터 파일 추가

        alter Database mydb

        add file(name=testdata3,

                    filename='c:\dat\testdata3.ndf',

                    size=1,

                    maxsize=10,

                    filegrowth=1)

        to filegroup UserGroup1            -- 그룹 지정

2.  Database size 줄이기

   1) 자동 축소기능 : Database 옵션에서 자동축소를 선택하여주면된다.

                               sql server 가 매 30분마다 체크를 해서 Database size를 줄일수있다.

    2) DBCC shrinkdatabase 옵션

        a. 지정한 % 만큼의 빈공간이 남은 상태로 줄여준다.

            처음 만들어질 당시의 size보다 작게 줄일수는 없다.

            ex)  DBCC shrinkdatabase (MyDB,25)  -- 빈공간 25% 남기고 줄여라

                   Database가 10m 실제 사용한것이 6m 25% 빈공간만 남기고

                   나머지는 os에 환원시 계산 방법 (실제사용데이타*100/100-25=8)

        b. notruncate 옵션을 이용할 경우 데이터를 전부앞으로 옮기고 뒤 공간은 남겨둔다.

            ex) DBCC shrinkdatabase(Mydb, notruncate)

        c. truncateonly 옵션을 이용할 경우

            ex) DBCC shrinkdatabase(Mydb, truncateonly)

                 조각모음은 하지않고 뒷쪽의 빈공간만 제거하여 os에 환원을 해준다.

    3) DBCC shrinkfile

         Database 를 이루고 있는 파일의 사이즈를 직접 줄이는 방법

         처음 Database 가 만들어질 당시의 사이즈보다 작게 줄일수 있다.

         ex) DBCC shrinkfile (testdata3, 10)

               DBCC shrinkfile (testdata3, emptyfile)

Posted by ㏈ª ☞ β┖υΕJini.κR

댓글을 달아 주세요

오라클 관련 튜닝 컨설팅 회사에서 말한거라고 하네요... 도움이 될수 있으니 참고 하세요...


1. 좌변을 가공하지 말라 (DB column을 가공하지 말고 치환시켜 상수(변수)부분을 가공하라.
        원리
        1. 인뎃스 컬럼은 비교되시 전에 변형이 일어나면 인덱스를 사용할 수 없다.
        2. 부정형(Not, <>)으로 조건을 기술한 경우에도 인덱스를 사용하지 않는다.
        3. 인덱스 컬럼이 NULL로 비교되면 사용될수 없다.
        4. 옵티마이져가 필요에 따라 상기 적용원칙을 준수했음에도 불구하고 특정 인덱스의 사용을 취사 선택함으로써 사용되지 않는 인덱스가 생길수 있다.
       
        예1)
        기존)
        select dept,
               ename,
               sal
        from   emp
        where  substr(job, 1, 4) = 'SALE'
       
        해결책)
        select dept,
               ename,
               sal
        from   emp
        where  job like 'SALE%'
       
        예2)
        기존)
        select empno,
               ename,
               job
        from   emp
        where  sal * 12 = 35000000
              
        해결책)
        select empno,
               ename,
               job
        from   emp
        where  sal = 35000000/12
       
        예3)
        기존)
        select empno,
               ename,
               job
        from   emp
        where  to_char(hiredate, 'YYYYMMDD') = '20050809'
       
        해결책)
        select empno,
               ename,
               job
        from   emp
        where  hiredate = to_date('20050809', 'YYYYMMDD')
       
       
        예4)
        기존)
        select empno,
               ename,
               job
        from   emp
        where  nvl(job, 'X') = 'CLERK'
       
        해결책)
        select empno,
               ename,
               job
        from   emp
        where  job = 'CLERK'
       
        예5)
        기존)
        select empno,
               ename,
               job
        from   emp
        where  job||dept = 'CLERK10'      
       
        해결책)
        select empno,
               ename,
               job
        from   emp
        where  job = 'CLERK'
        and    dept = '10'
       
        예6)
        기존)
        select *
        from   tab1
        where  col1 || col2 = :FLD
       
        해결책)
        select *
        from   tab1
        where  col1 = substr(:FLD, 1, 3)
        and    col2 = substr(:FLD, 4, 2)



2. 데이터 존재 유무를 확인하기 위해 count(*) ... cnt > 0 를 사용하지 말 것.
        기존)
        select count(*)
        from   tbl_point
        where  user_id = ''
       
        해결책)
        SELECT  1 AS cnt 
        WHERE   EXISTS (select 'X'
                        from   tbl_point
                        where  user_id = 'locusty')
                       
3. Decode 또는 Case를 사용시에 새끼를 증손자 이상 낳치 마라. (decode(decode...))

        case when (
                   case when (
                              case when ( ) then end ) then end) then end as col2
                             
이렇게 증손자 이상으로 들어가면 연산자 개산에 cost가 발생해서 처리 속도가 떨리집니다.


4. Union 을 사용할 때 다시 한번 Union all로 사용할 수 없는지 확인하라.
    상식적으로 알고 있는 부분.
    -union all은 두개의 결과값에서 중복되는 결과값까지 출력
    -union은 두개의 결과값에서 중복되는 결과값을 제거하고 결과값을 출력
       
    내부적인 처리 절차.
    -union은 중복되는 결과값을 제거를 하기위해 내부적으로 sort가 발생하면서 distinct가 발생
   
    우리가 알아야 하는 부분은 눈으로 보이는 부분이 아니라 내부적인 처리 알고리즘입니다.
   
5. In Line View 또는 out of temp를 사용할 때 진정 필요한 In Line View인지를 확인하라.
   
    in line view 예제)   
    select *
    from   dept t1, (select col1, col2 from emp where dept_no) t2
    where  condition1, condition1
   
    이 부분은 옵티마이져의 원리를 아셔야 알수 있는 내용입니다.
   
    질의 처리 단계와 옵티마이저의 역활
    Query --> Parse --> Query Rewrite --> Query Optimization --> QEP Generation --> Query Excution --> Result
   
    sql이 실행이 되면 위와 같은 순서로 결과를 생성합니다.
    위 순서에서 'Query Rewrite' 단계에서 서브질의와 뷰의 병합이 수행됩니다.
   
    예1)
    create view vw_emp
    as
    select *
    from   emp
    where  deptno = 10;
   
    sql문)
    select empno
    from   vw_emp
    where  empno > 11910;
   
    'Query Rewrite' 단계
    select empno
    from   emp
    where  deptno = 10
    and    empno > 11910;
   
    line view도 동일한 과정을 수행합니다.
   
6. 조인 SQL일 경우 집합의 복제(카테시안곱)를 제외하고는
    연결고리 Relation 상 1:M 이든, M:1이든, 1:1 이든 어느 한쪽은 반드시 1 인지 확인하고
    함부로 Outer Join을 하지말고 Outer join이 자주 나타난다면 설계자(모델러)에게
    다시 한번 확인하라.
   
    카테시안 곱이란?
   
    table1 : (1, 2, 3, 4)
    table2 : (2, 3)
   
    1)정상적인 연결고리에서의 결과값 :
    -row(1) --> 2, 2  
    -row(2) --> 2, 2
   
    2)카테시안 곱의 결과값(연결고리 불량) :
    -row(1) --> 2, 1
    -row(2) --> 2, 2
    -row(3) --> 2, 3
    -row(4) --> 2, 4
    -row(5) --> 3, 1
    -row(6) --> 3, 2
    -row(7) --> 3, 3
    -row(8) --> 3, 4
   
    outer join 이란?
    -outer join은 두 테이블 간의 관계에서 발생하는 조인 방식으로써 한쪽 테이블에 데이터가 없더라도 원래의 테이블 정보는 나오도록 할때 사용을 합니다.
    이때 문제는 outer join이 걸리는 테이블은 무조건 Full table scan이 발생을 합니다.
    보통의 DB 모델링에서 문제가 없이 진행이 되었다면 1:m, 1:1 관계로 모든 ERD가 생성이 됩니다.
    그런데 코딩/개발 단계에서 outer join이 자주 발생을 한다는 것은 설계상의 문제가 있다는 것을 말합니다.
   
7. 결과에만 만족하지 말고 실행계획(Execution plan)에 관심을 가지고 절차형 로직(If Then Else)을 버리고 집합적 하나의 SQL로 임무를 완수토록 하라.
   
    모든 query는 작성 후 실행계획을 확인해서 개발자가 원하는 형태로 수행이 되었는지 확인을 해 봐야 합니다.
    인덱스가 생성이 되어 있고 정상적인 연결고리로 조인이 이루어져도 CBO(Cost Base Optimize) DBMS에서는 옵티마이저에 의해 인덱스 취사가 발생할수도 있으며
    개발자 실수로 인덱스 컬럼의 가공이 발생해서 인덱스를 사용을 할수 없는 경우도 발생할수 있습니다.
    기본 사항으로 실행계획은 확인하시고 개발해야 합니다.

Posted by ㏈ª ☞ β┖υΕJini.κR

댓글을 달아 주세요

[쿼리]
- 실행: Ctrl+E 또는 F5
- 실행 취소: Alt+Break
- 쿼리 구문 분석, 검사: Ctrl+F5

[DB]
- DB 연결: Ctrl+O
- DB 연결 끊기 및 하위 창 닫기: Ctrl+F4
- DB 개체 정보: Alt+F1
- DB 선택: Ctrl+U

[편집]
- 창 내용 지우기: Ctrl+Shift+Del

- 주석 달기(블럭): Ctrl+Shift+C
- 주석 제거(블럭): Ctrl+Shift+R

- 복사(블럭): Ctrl+Ins 또는 Ctrl+C
- 잘라내기(블럭): Shift+Del 또는 Ctrl+X
- 붙여넣기(블럭): Shift+Insert 또는 Ctrl+V

- 줄 내용 삭제: Ctrl+Del
- 줄 삭제: Ctrl+Y

- 찾기: Ctrl+F
- 다음 찾기: F3
- 줄 번호로 이동: Ctrl+G

- 들여쓰기(블럭): Tab
- 내어쓰기(블럭): Shift+Tab

- 소문자로 변환(블럭): Ctrl+Shift+L
- 대문자로 변환(블럭): Ctrl+Shift+U

- 바꾸기: Ctrl+H
- 모두 선택: Ctrl+A
- 실행 취소: Ctrl+Z

[창]
- 쿼리와 결과 창 사이 전환: F6 또는 Shift+F6
- 창 선택기: Ctrl+W
- 새 쿼리 창 열기: Ctrl+N
- 창 내용 저장: Ctrl+S

- 개체 브라우저 표시(토글): F8
- 개체 검색: F4

[결과]
- 결과 창 표시(토글): Ctrl+R

- 쿼리 실행 후, 표 형태로 결과 표시: Ctrl+D
- 쿼리 실행 후, 텍스트 형식으로 결과 표시: Ctrl+T
- 쿼리 실행 후, 파일로 결과 저장: Ctrl+Shift+F

- 쿼리 실행 후, 실행 계획 표시(토글): Ctrl+K
- 쿼리 실행 후, 클라이언트 통계 표시(토글): Ctrl+Shift+S
- 서버 추적 표시(토글): Ctrl+Shift+T

- 결과 창 크기 조절: Ctrl+B

[튜닝]
- 예상 실행 계획 표시: Ctrl+L
- 인덱스 튜닝 마법사: Ctrl+I

[템플릿]
- 템플릿 삽입: Ctrl+Shift+Insert
- 템플릿 매개 변수 바꾸기: Ctrl+Shift+M

[책갈피]
- 모든 책갈피 지우기: Ctrl+Shift+F2
- 책갈피 삽입, 제거(토글): Ctrl+F2
- 다음 책갈피로 이동: F2
- 이전 책갈피로 이동: Shift+F2

[도움말]
- QA 도움말: F1
- 선택한 T-SQL 문에 대한 도움말: Shift+F1

[기타]
- 옵션: Ctrl+Shift+O
- 인쇄: Ctrl+P

Posted by ㏈ª ☞ β┖υΕJini.κR

댓글을 달아 주세요

use pubs
go

select table_name = convert(varchar(30), min(o.name)), table_size = ltrim(str(sum(reserved) * 8192 / 1024.,15,0)
+ '' KB'')
from sysindexes i inner join sysobjects o on (o.id = i.id)
where i.indid in (0, 1, 255) and o.xtype = ''U''
group by i.id
go

이러한 방법으로 쿼리하시면 됩니다.

가끔씩 사용되는 루틴이라면 아래와 같이 master db에 프로시져로 만들어 등록시킨후

--drop proc sp_sqler_getTableSize
create proc sp_sqler_getTableSize @dbname sysname
as
   declare @stmt nvarchar(4000)
   set @stmt = 'USE ' + @dbname + ';
                select table_name = convert(varchar(30), min(o.name)), table_size = ltrim(str(sum(reserved) *
8192 / 1024.,15,0) + '' KB'')
                from sysindexes i inner join sysobjects o on (o.id = i.id)
                where i.indid in (0, 1, 255) and o.xtype = ''U''
                group by i.id order by sum(reserved) desc'

   exec sp_executesql @stmt
go


exec sp_sqler_getTableSize 'pubs'

Posted by ㏈ª ☞ β┖υΕJini.κR

댓글을 달아 주세요

-- 1.저장프로시져별 실행수 뽑기
select db_name(st.dbid) DBName
,object_schema_name(st.objectid,dbid) SchemaName
,object_name(st.objectid,dbid) StoredProcedure
,sum(qs.execution_count) Execution_count
from sys.dm_exec_cached_plans cp
join sys.dm_exec_query_stats qs on cp.plan_handle=qs.plan_handle
cross apply sys.dm_exec_sql_text(cp.plan_handle)st
where DB_Name(st.dbid) is not null and cp.objtype = 'proc'
group by DB_Name(st.dbid),object_schema_name(objectid,st.dbid),object_name(objectid,st.dbid)
order by sum(qs.execution_count) desc

--2. CPU소모량이 많은 저장프로시져 뽑기
select db_name(st.dbid) DBName
,object_schema_name(st.objectid,dbid) SchemaName
,object_name(st.objectid,dbid) StoredProcedure
,sum(qs.execution_count) Execution_count
,sum(qs.total_worker_time) total_cpu_time
,sum(qs.total_worker_time) / (sum(qs.execution_count) * 1.0) avg_cpu_time
from sys.dm_exec_cached_plans cp join sys.dm_exec_query_stats qs on cp.plan_handle=qs.plan_handle
cross apply sys.dm_exec_sql_text(cp.plan_handle) st
where db_name(st.dbid) is not null and cp.objtype='proc'
group by db_name(st.dbid), object_schema_name(objectid,st.dbid), object_name(objectid,st.dbid)
order by sum(qs.total_worker_time) desc

--3. IO량이 많은 저장프로시져 뽑기
select db_name(st.dbid) DBName
,object_schema_name(objectid,st.dbid) SchemaName
,object_name(objectid,st.dbid) StoredProcedure
,sum(execution_count) execution_count
,sum(qs.total_physical_reads+qs.total_logical_reads+qs.total_logical_writes) total_IO
,sum(qs.total_physical_reads+qs.total_logical_reads+qs.total_logical_writes) / sum
(execution_count) avg_total_IO
,sum(qs.total_physical_reads) total_physical_reads
,sum(qs.total_physical_reads) / (sum(execution_count) * 1.0) avg_physical_read
,sum(qs.total_logical_reads) total_logical_reads
,sum(qs.total_logical_reads) / (sum(execution_count) * 1.0) avg_logical_read
,sum(qs.total_logical_writes) total_logical_writes
,sum(qs.total_logical_writes) / (sum(execution_count) * 1.0) avg_logical_writes
from sys.dm_exec_query_stats qs cross apply sys.dm_exec_sql_text(qs.plan_handle) st
join sys.dm_exec_cached_plans cp on qs.plan_handle = cp.plan_handle
where db_name(st.dbid) is not null and cp.objtype = 'proc'
group by db_name(st.dbid),object_schema_name(objectid,st.dbid), object_name(objectid,st.dbid)
order by sum(qs.total_physical_reads+qs.total_logical_reads+qs.total_logical_writes) desc


--4. 처리시간이 긴 저장프로시져 뽑기
select db_name(st.dbid) DBName
,object_schema_name(objectid,st.dbid) SchemaName
,object_name(objectid,st.dbid) StoredProcedure
,sum(execution_count) execution_count
,sum(qs.total_elapsed_time) total_elapsed_time
,sum(qs.total_elapsed_time) / sum(execution_count) avg_elapsed_time
from sys.dm_exec_query_stats qs cross apply sys.dm_exec_sql_text(qs.plan_handle)st
join sys.dm_exec_cached_plans cp on qs.plan_handle = cp.plan_handle
where db_name(st.dbid) is not null and cp.objtype = 'proc'
group by db_name(st.dbid),object_schema_name(objectid,st.dbid),object_name(objectid,st.dbid)
order by sum(qs.total_elapsed_time) desc
Posted by ㏈ª ☞ β┖υΕJini.κR

댓글을 달아 주세요

한때 누구보다고 SQL 에 자신 있다는 생각을 가진적이 있었다.. 하지만 현 필라넷 정원혁 상무님 강의를
들은후 그 마음을 사라지고 말았다.

데이터베이에서 인덱스가 얼마나 중요한지를 깨닫게 되었다.

/*
sp_name : usp_allReindex

sp_Explanation : 디비 서버의 모든 테이블에 대해 DBCC DBREINDEX를 실행합니다.

Input Parameters : None

Output Parameters : None

Usage : exec usp_allReindex
*/

Create PROCEDURE usp_allReindex
AS

-- 변수 선언
DECLARE @SQLString varchar(300), @dbname varchar(30), @tblname varchar(30)

SET NOCOUNT ON

-- 테이블 리스트 저장 테이블
CREATE TABLE #tables
(
    tblname varchar(30)
)

-- 한 서버의 디비 목록을 위한 커서 시작
DECLARE cur_dbList CURSOR
FOR
SELECT name FROM master..sysdatabases WHERE name NOT IN ('master','model','msdb','tempdb') ---- (1)

OPEN cur_dbList
FETCH cur_dbList INTO @dbname

WHILE @@fetch_status = 0
BEGIN
    TRUNCATE TABLE #tables
    SET @SQLString = 'insert into #tables select name from ' + @dbname + '..sysobjects where type = ''U'''
    EXEC (@SQLString)

    -- 각 디비의 테이블 목록을 위한 커서 시작
    DECLARE cur_tblList CURSOR
    FOR
    SELECT tblname FROM #tables

    OPEN cur_tblList
    FETCH cur_tblList INTO @tblname

    WHILE @@fetch_status = 0
    BEGIN
        SET @SQLString = 'DBCC DBREINDEX (''' + @dbname + '..' + @tblname + ''', '''', 90)' ---- (2)
        EXEC (@SQLString)
        FETCH cur_tblList INTO @tblname
    END

    CLOSE cur_tblList
    DEALLOCATE cur_tblList

    FETCH cur_dbList INTO @dbname
END

CLOSE cur_dbList
DEALLOCATE cur_dbList

DROP TABLE #tables


GO

Posted by ㏈ª ☞ β┖υΕJini.κR

댓글을 달아 주세요