2013. 3. 13. 21:55

월에 두번째/세번째 요일 구하기.


업무를 하다보면  월 두번째, 세번째 특정 요일을 구해야 하는 경우가 있다. 

여기서는 금요일로 하겠다. 


declare @dt_getdate date

set @dt_getdate = '2013-03-13'

select @dt_getdate

 

select DATEADD(wk, DATEDIFF(wk,0, dateadd(dd,6-datepart(day,@dt_getdate),@dt_getdate) ), 4)


위 쿼리는 해당 월의 첫번째 금요일을 알려준다, 

허나 2013년 3월의 경우  3/1일이 금요일 이긴 하지만 주로 처리 되면 2월의 마지막 주가 되서 3/1일이 아닌 

3/8일이 결과가 나온다. 


이럴 경우, 두번째 금요일, 세번째 금요일도 원하는 결과가 아니다. 



그래서 , 구하려고 하는 달의 다음날 첫 일의 금요일을 찾아 거꾸로 찾는 방법을 택했다. 


-- 다음달의 첫번째 날짜를 구하자.

declare @dt_getdate date , @dt_next_month_day date

set @dt_getdate = '2013-02-13'

--set @dt_getdate = dateadd(mm,1,@dt_getdate)

select @dt_getdate

 

set @dt_next_month_day = CONVERT(VARCHAR(10),DATEADD(dd,-(DAY(DATEADD(mm,1,@dt_getdate))-1),DATEADD(mm,1,@dt_getdate)),121)

select @dt_next_month_day

if Datepart(dw, @dt_next_month_day )= 6

   select dateadd(dd,-21,@dt_next_month_day) as '둘째 ', dateadd(dd,-14, @dt_next_month_day) as '셋째 '

else

   select dateadd(dd,-21,dateadd(dd, 6- datepart(dw,@dt_next_month_day ), @dt_next_month_day) ) as '둘째 ',

               dateadd(dd,-14,dateadd(dd, 6- datepart(dw,@dt_next_month_day ), @dt_next_month_day) ) as '셋째 '





2012. 11. 2. 09:58

T-SQL::동적 PIVOT 생성

원문 : http://www.mssqltips.com/sqlservertip/2783/script-to-create-dynamic-pivot-queries-in-sql-server/

 

 

동적으로 PIVOT을 만들 수 있는 방법입니다.

아직도 case 문이 익숙하긴 하지만, 가끔은 오히려 더 복잡해지는 case 문 때문에 PIVOT을 사용해야 한다고 느껴질 때가 있습니다.

 

아직 PIVOT 익숙하지는 않은데 하다보니 컬럼 고정이 되서 불편한 점이 있는데 이 방법으로 사용하면 좋을 것 같네요.

 

PIVOT 은 행 형태의 반환을 열로 만들어 주는 것입니다.

 

아래로 테스트할 데이터를 생성 합니다.


CREATE TABLE dbo.Products
(
  ProductID INT PRIMARY KEY,
  Name      NVARCHAR(255) NOT NULL UNIQUE
  /* other columns */
);


INSERT dbo.Products VALUES
(1, N'foo'),
(2, N'bar'),
(3, N'kin');


CREATE TABLE dbo.OrderDetails
(
  OrderID INT,
  ProductID INT NOT NULL
    FOREIGN KEY REFERENCES dbo.Products(ProductID),
  Quantity INT
  /* other columns */
);


INSERT dbo.OrderDetails VALUES
(1, 1, 1),
(1, 2, 2),
(2, 1, 1),
(3, 3, 1);

 

SELECT p.Name, Quantity = SUM(o.Quantity)
  FROM dbo.Products AS p
  INNER JOIN dbo.OrderDetails AS o
  ON p.ProductID = o.ProductID
  GROUP BY p.Name;

 

결과는 아래 처럼 행으로 나 옵니다.  그러나 열로 표시하고 싶어요. 하면 PIVOT 처리 합니다.

 

 

 

SELECT p.[foo], p.[bar], p.[kin]
FROM
(
  SELECT p.Name, o.Quantity
   FROM dbo.Products AS p
   INNER JOIN dbo.OrderDetails AS o
   ON p.ProductID = o.ProductID
) AS j
PIVOT
(
  SUM(Quantity) FOR Name IN ([foo],[bar],[kin])
) AS p;

 

 

원하는 결과가 나왔습니다. ~

 

 

다른 데이터를 추가 해 보죠.

INSERT dbo.Products SELECT 4, N'blat';
INSERT dbo.OrderDetails SELECT 4,4,5;

 

그리고 나서 다시 윗 쿼리를 실행 해 보면 결과는 동일하게 3개의 데이터의 집계만 나타납니다.

당연한 결과 입니다. 지금 추가된 blat는 보이지 않습니다. PIVOT는 집계해서 보여줄 컬럼을 명시해야 하기 때문입니다.   쿼리를 그럼 고쳐야 하는데 좀 더 쉽게 동적으로 할 수 있게 아래는 도와 줍니다.

 

DECLARE @columns NVARCHAR(MAX), @sql NVARCHAR(MAX);


SET @columns = N'';

SELECT @columns += N', p.' + QUOTENAME(Name)   -- += 는 컬럼을 합쳐서 한 문자로 만들어 주죠.
FROM (SELECT p.Name FROM dbo.Products AS p
  INNER JOIN dbo.OrderDetails AS o
  ON p.ProductID = o.ProductID
 GROUP BY p.Name) AS x;

print @columns


SET @sql = N'
 SELECT ' + STUFF(@columns, 1, 2, '') + '
 FROM
 (
   SELECT p.Name, o.Quantity
    FROM dbo.Products AS p
    INNER JOIN dbo.OrderDetails AS o
    ON p.ProductID = o.ProductID
 ) AS j
 PIVOT
 (
   SUM(Quantity) FOR Name IN ('
   + STUFF(REPLACE(@columns, ', p.[', ',['), 1, 1, '')
   + ')
 ) AS p;';

PRINT @sql;
EXEC sp_executesql @sql;

 

-- @sql의 문은 컬럼을 만들어서 PIVOT 를 생성해 줍니다.

 

/*결과)
 SELECT p.[foo], p.[bar], p.[kin], p.[blat]
 FROM
 (
   SELECT p.Name, o.Quantity
    FROM dbo.Products AS p
    INNER JOIN dbo.OrderDetails AS o
    ON p.ProductID = o.ProductID
 ) AS j
 PIVOT
 (
   SUM(Quantity) FOR Name IN ([foo],[bar],[kin],[blat])
 ) AS p;

*/

 

원하는 결과가 도출.

 

2012. 11. 1. 22:17

Function to return a range of dats - 날짜 범위 펑션

http://www.mssqltips.com/sqlservertip/2800/sql-server-function-to-return-a-range-of-dates/

By:    정리

 

SQL SERVER 2005 이상

 

일자별  sum, max 등의 집계 쿼리를 할 때 일자의 범위가 모두 포함되기어 표현되어지기를 원할 때가 있다.

그러나 실제 데이터는 일자 범위에 데이터가 없을 수도 있고 있을 수도 있다. 이럴때 어떻게 해야 할까??

 

물론, row_number 함수를 사용하여 corss join을 사용하면 가능하다. 그러나 이런 쿼리는 복잡하고 원하는 결과를 나타내지 않을 수도 있다.

 

여기서는 테이블 함수를 사용하는 방법으로 해결 합니다.

이 함수는 CTE를 사용하여 반복적으로 날짜를 반환 해 준다. (@Increment 옵션에 따라서)

 

 

DROP FUNCTION [DateRange]
GO
CREATE FUNCTION [dbo].[DateRange]
(     
      @Increment              CHAR(1),   --  일/주/달 여부
      @StartDate              DATETIME,  -- 시작 날짜
      @EndDate                DATETIME   -- 종료 날짜
)
RETURNS  
@SelectedRange    TABLE 
(IndividualDate DATETIME)
AS
BEGIN
	 ;WITH cteRange (DateRange) AS (
            SELECT @StartDate
            UNION ALL
            SELECT 
                  CASE
                        WHEN @Increment = 'd' THEN DATEADD(dd, 1, DateRange)
                        WHEN @Increment = 'w' THEN DATEADD(ww, 1, DateRange)
                        WHEN @Increment = 'm' THEN DATEADD(mm, 1, DateRange)
                  END
            FROM cteRange
            WHERE DateRange <= 
                  CASE
                        WHEN @Increment = 'd' THEN DATEADD(dd, -1, @EndDate)
                        WHEN @Increment = 'w' THEN DATEADD(ww, -1, @EndDate)
                        WHEN @Increment = 'm' THEN DATEADD(mm, -1, @EndDate)
                  END)
          
      INSERT INTO @SelectedRange (IndividualDate)
      SELECT DateRange
      FROM cteRange
      OPTION (MAXRECURSION 3660);
      RETURN
END
GO

실행을 해 보면 아래와 같다.

SELECT IndividualDate FROM DateRange('d', '2012-11-01', '2012-11-10')
SELECT IndividualDate FROM DateRange('w', '2012-11-01', '2012-11-10')
SELECT IndividualDate FROM DateRange('m', '2012-11-01', '2013-01-10')

 

 

 

 

자 그럼 이제 집계하고자 하는 데이터와 조인해서 사용해 보자

임시 테이블을 사용해서 해당 날짜의 제품이 판매된 정보를 입력 하였다. 아래 데이터에서 봤을 경우 11/1 ~ 11/10 사이에

매출은 이틀만 발생 하였다.

 

CREATE TABLE #temp (orderDate DATETIME, orderInfo VARCHAR(50))
INSERT INTO #temp VALUES ('2012-11-01','2 copies of SQL Server 2008')
INSERT INTO #temp VALUES ('2012-11-05','6 copies of SQL Server 2008 R2')
INSERT INTO #temp VALUES ('2012-11-05','10 copies of SQL Server 2012')


SELECT a.IndividualDate , b.orderDate, b.orderInfo
FROM DateRange('d', '2012-11-01', '2012-11-10') as a
LEFT JOIN #temp as b on a.IndividualDate = b.orderDate


결과에서 알 수 있듯이, 매출이 발생하지 않은 일자도 나열 되어 표시된다. 이럴 경우 매출이 없는 일자를 찾기도 쉽다.

 

 

 

'T-SQL' 카테고리의 다른 글

월에 두번째/세번째 요일 구하기.  (0) 2013.03.13
T-SQL::동적 PIVOT 생성  (3) 2012.11.02
T-SQL:: 인덱스 압축 예상 Size  (0) 2012.07.26
TSQL::월별 누적 건수를 구하기.  (0) 2012.01.29
2012. 7. 26. 12:54

T-SQL:: 인덱스 압축 예상 Size

SQL Server 2008 이후 

인덱스 압축 했을 때 예상 사이즈 조회

  exec sp_estimate_data_compression_savings  
   @schema_name = 'dbo'
  ,@object_name = '테이블명'
  ,@index_id = 1
  ,@partition_number = null, @data_compression = 'page'  -- 압축 방법


'T-SQL' 카테고리의 다른 글

T-SQL::동적 PIVOT 생성  (3) 2012.11.02
Function to return a range of dats - 날짜 범위 펑션  (0) 2012.11.01
TSQL::월별 누적 건수를 구하기.  (0) 2012.01.29
T-SQL::테이블 확장속성  (0) 2011.10.05
2011. 10. 5. 13:01

T-SQL::테이블 확장속성


테이블 확장 속성 확인 쿼리

-- 테이블에 대한 속성
SELECT object_name(major_id) as table_name, value
FROM sys.extended_properties 
WHERE  minor_id = 0
ORDER BY major_id

-- 컬럼 확장 속성
declare @tb_nm sysname
set @tb_nm = 'SF_COMMENT' 

SELECT object_name(major_id) as table_name, c.name ,value
FROM sys.extended_properties as p with(nolock)
	inner join sys.columns as c with(nolock) on p.major_id = c.object_id and p.minor_id = c.column_id
WHERE   p.class = 1 and p.minor_id != 0 and p.major_id = OBJECT_ID(@tb_nm)
order by p.major_id, p.minor_id


SELECT   *
FROM   ::fn_listextendedproperty (NULL, 'user', 'dbo', 'table', @tb_nm, 'column', default)

'T-SQL' 카테고리의 다른 글

T-SQL:: 인덱스 압축 예상 Size  (0) 2012.07.26
TSQL::월별 누적 건수를 구하기.  (0) 2012.01.29
T-SQL::create assembly  (0) 2011.10.03
SQLCLR Disk 사이즈 확인  (0) 2011.08.14
2011. 8. 13. 23:52

T-SQL:: TCP 포트 정보


--SQL 2000/2005 Version
 
set nocount on
go
DECLARE @SqlPort Nvarchar(10)
DECLARE @instance_name Nvarchar(30)
DECLARE @reg_key Nvarchar(500)
Declare @value_name Nvarchar(20)
 
if left(CAST(SERVERPROPERTY('ProductVersion')AS sysname),1) = '9'
BEGIN
 
 
select @instance_name = CAST(SERVERPROPERTY('instancename')AS sysname)
 
if @instance_name is NULL
BEGIN
set @reg_key = 'SOFTWARE\Microsoft\MSSQLServer\MSSQlServer\SuperSocketNetLib\Tcp'
END
ELSE BEGIN
set @reg_key = 'SOFTWARE\Microsoft\Microsoft SQL Server\' + @instance_name + '\MSSQLServer\SuperSocketNetLib\Tcp'
END
 
EXEC master..xp_regread @rootkey='HKEY_LOCAL_MACHINE',
@key=@reg_key, @value_name='TcpPort',
@value=@SqlPort output
 
select CAST(SERVERPROPERTY('ServerName')AS sysname) as ServerName, @SqlPort as Port
 
END
 
 
if left(CAST(SERVERPROPERTY('ProductVersion')AS sysname),1) = '8'
BEGIN
 
Create table #Port_2000 (value nvarchar(20),Data nVarchar(10))
insert into #Port_2000 exec master..xp_instance_regread 'HKEY_LOCAL_MACHINE', 'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\Supersocketnetlib\tcp', 'tcpPort'
select @SqlPort = Data from #Port_2000
select CAST(SERVERPROPERTY('ServerName')AS sysname) as ServerName, @SqlPort as Port
drop table #Port_2000
 
END

'T-SQL' 카테고리의 다른 글

T-SQL::create assembly  (0) 2011.10.03
SQLCLR Disk 사이즈 확인  (0) 2011.08.14
T-SQL:: INDEX-중복인덱스 제거.  (1) 2011.08.13
DBCC ShrinkFile  (0) 2010.09.06
2011. 8. 13. 23:49

T-SQL:: INDEX-중복인덱스 제거.

-- 퍼옴.
 
/*
 
 Find Duplicate Indexes
 
 This script returns the Duplicate indexes on any database
 I use 2 User defined Functions
 Compatible with SQLServer 2000 (I used sp3)
 It won't work in SQL Server 7.0 because It uses user-defined functions and a Memory.
 
 
 Created by G.R. Preethiviraj Kulasingham
pkulasingham@virtusa.com
 Written on  : February  20, 2003
 Modified on : May 18, 2004
Additional code written to remove duplicate entries.
Additional code to include index properties.
*/
 
IF EXISTS(Select id from sysobjects Where id = object_id('dbo.GetColID'))
   DROP FUNCTION dbo.getColID
GO
 
Create Function dbo.getColID (@TableID int, @indid int)
/*
Parameters:
@TableID: ID of the Table
@IndID  : ID of the Index
Returns All the Columns (ID) for the given index in string format seperated by '&'sign. '-' is added for descending keys
*/
 
Returns Varchar(200)
As
BEGIN
     Declare @SQL varchar(200)
     Set @SQL =''
     Select @SQL= @SQL +'&'+  convert(varchar(7),((indexkey_property(id, indid, keyno, 'isdescending')*-2)+1)* colid)   from sysindexkeys
     Where id =@Tableid and indid=@Indid
     Order by id, indid, Keyno
     IF LEN(@SQL)>1
          Select @SQL = @SQL +'&'
     Return @SQL
END
GO
 
 
IF EXISTS(Select id from sysobjects Where id = object_id('dbo.GetColList'))
   DROP FUNCTION dbo.getColList
 
GO
 
Create Function dbo.getColList (@Tableid int, @indid int)
/*
Parameters:
@TableID: ID of the Table
@IndID  : ID of the Index
Returns Index Key (column names) for the given index in string format seperated by commas.
*/
 
Returns Varchar(8000)
As
BEGIN
     Declare @SQL varchar(8000)
     Set @SQL =''
     Select @SQL= @SQL +', '+ INDEX_Col(User_name(ObjectProperty(@TableID, 'Ownerid'))+'.'+Object_name(@TableID), @indid, keyno)+
     Case indexkey_property(id, indid, keyno, 'isdescending') When 1 Then '(Desc)' Else '' end   from sysindexkeys
     Where id =@Tableid and indid=@Indid
     Order by id, indid, Keyno
     IF LEN(@SQL)>2
          Select @SQL = SUbString(@SQL, 3, LEN(@SQL)-2)
     Return @SQL
END
GO
 
 
/*
@TempTable is used to store the keys in long string format
*/
 
Declare @TempTable Table (
ID int ,
Indid int,
ColID Varchar(200),
Status int
)
 
Declare @Duplicates Table (
LineID int Identity(1,1),
ID int ,
hasClustered char(3) not null default('NO'),
Indid1 int,
ColID1 Varchar(200),
Status1 int,
Desc1 varchar(200),
IndID2 int,
ColID2 Varchar(200),
Status2 int,
Desc2 varchar(100)
)
 
Insert into @TempTable 
Select Id, indid, dbo.GetColid(id, indid), status  from Sysindexes
where (status & 64)=0 
order by id
Delete @TempTable Where ColID=''
 
 
Insert into @Duplicates (ID, IndID1, ColID1, Desc1, Status1, IndID2, ColID2, desc2, status2 )
Select A.ID, A.IndID,  A.ColID, '', A.status, B.IndID,  B.ColID, '', B.status
from @Temptable A, @TempTable B
Where A.id = b.id and a.indid<>b.indid and
     a.colid like b.colid
+'%'
 
 
--This part removes the duplicate entries.
 
Delete @Duplicates Where LineID In (
Select A.LineID from @Duplicates A, @Duplicates B
Where A.ID = B.ID and A.IndID1= B.IndID2 and A.IndID2= B.IndID1 and  A.LineID>B.LineID)
Delete @Duplicates Where LineID In (
Select A.LineID from @Duplicates A, @Duplicates B
Where A.ID = B.ID and  A.IndID1 = B.IndID2 )
 
-- Identify the index properties
 
Update @Duplicates Set Desc1 =CASE status1 & 16 WHEN 0 THEN 'Nonclustered' ELSE 'Clustered' END
Update @Duplicates Set Desc2 =CASE status2 & 16 WHEN 0 THEN 'Nonclustered' ELSE 'Clustered' END
 
 
 
Declare @Desc varchar(20), @Number int
Declare spt_Vals Cursor 
FOR
Select name, number  from master.dbo.spt_Values
Where type ='I' and number in (1,2, 4, 32, 2048, 4096) 
Order by number
Open  spt_vals
FETCH Next from spt_vals into @Desc, @Number
WHILE @@FETCH_STATUS=0
  BEGIN
Update @Duplicates Set Desc1 = Desc1 + ', '+ @Desc 
where status1 & @number <>0
Update @Duplicates Set Desc2 = Desc2 + ', '+ @Desc 
where  status2 & @number <>0
FETCH Next from spt_vals into @Desc, @Number
 END
 
CLOSE spt_Vals
DEALLOCATE spt_vals
Update @Duplicates Set  Desc1 = replace(Desc1, 'unique, primary key', 'primary key'), 
Desc2 = replace(Desc2, 'unique, primary key', 'primary key')
 
Update @Duplicates Set  Desc1 = replace(Desc1, 'unique, unique key', 'unique key'), 
Desc2 = replace(Desc2, 'unique, unique key', 'unique key')
 
-- Identify whether table has clustered index
Update @Duplicates Set HasClustered = 'YES' Where id in (
Select id From sysIndexes Where IndId=1)
 
--Update @Duplicates Set HasClustered = 'NO' Where id in (
--Select id From sysIndexes Where IndId=0)
 
 
 
Select User_name(ObjectProperty(A.ID, 'Ownerid'))+'.'+Object_name(a.id) 'Table Name', HasClustered,
IA.Name 'Index 1', dbo.GetColList(A.id, A.indid1) 'Keys of Index 1',  A.Desc1 'Desc 1',
IB.Name 'Index 2', dbo.GetColList(A.id, A.indid2) 'Columns of Index 2', A.Desc2 'Desc 2'
from @Duplicates A, SysIndexes IA, Sysindexes IB
Where IA.id =A.id and IA.indId = A.IndID1 and IB.ID = A.ID and IB.indId=A.IndID2
order by User_name(ObjectProperty(A.ID, 'Ownerid'))+'.'+Object_name(a.id)
 
/*
 
GO
DROP FUNCTION dbo.getColList
 
GO
DROP FUNCTION dbo.getColID
GO
*/

'T-SQL' 카테고리의 다른 글

SQLCLR Disk 사이즈 확인  (0) 2011.08.14
T-SQL:: TCP 포트 정보  (0) 2011.08.13
DBCC ShrinkFile  (0) 2010.09.06
[T-SQL] JOb 수행 시간 완료 계산  (0) 2010.07.19
2011. 4. 15. 13:23

T-SQL:: Default Trace로 DB 증가량 확인

SQL SERVER 2000 이상

EventClass  : 92, 93, 94, 95 번은 DB File의 증가하거나 Shrink 하는 이벤트 임.
Default Trace가 되고 있는 SQL Server에서는  해당 이벤트를 수집하고 있으며, 증가/축소되는 사이즈를 확인할 수 있다.

begin try  
if (select convert(int,value_in_use) from sys.configurations where name = 'default trace enabled' ) = 1 
begin 
declare @curr_tracefilename varchar(500) ; 
declare @base_tracefilename varchar(500) ; 
declare @indx int ;

select @curr_tracefilename = path from sys.traces where is_default = 1 ; 
set @curr_tracefilename = reverse(@curr_tracefilename);
select @indx  = patindex('%\%', @curr_tracefilename) ;
set @curr_tracefilename = reverse(@curr_tracefilename) ;
set @base_tracefilename = left( @curr_tracefilename,len(@curr_tracefilename) - @indx) + '\log.trc' 


select  @@SERVERNAME as server_name
,	    (dense_rank() over (order by StartTime desc))%2 as l1
,       convert(int, EventClass) as EventClass
,       DatabaseName
,       Filename
,       (Duration/1000) as Duration
,       StartTime
,       EndTime
,       (IntegerData*8.0/1024) as ChangeInSize 
from ::fn_trace_gettable( @base_tracefilename, default ) 
where EventClass >=  92      and EventClass <=  95        and ServerName = @@servername 
order by StartTime desc ;   
end     else    
select -1 as l1, 0 as EventClass, 0 DatabaseName, 0 as Filename, 0 as Duration, 0 as StartTime, 0 as EndTime,0 as ChangeInSize 
end try 
begin catch 
select -100 as l1
,       ERROR_NUMBER() as EventClass
,       ERROR_SEVERITY() DatabaseName
,       ERROR_STATE() as Filename
,       ERROR_MESSAGE() as Duration
,       1 as StartTime, 1 as EndTime,1 as ChangeInSize 
end catch


'Monitoring' 카테고리의 다른 글

TEMPDB의 페이지 사용량 측정  (0) 2012.08.13
모니터링::Index 생성 & Rebuild 진행 상황  (0) 2011.12.06
Admin::Tempdb 의 작업, 모니터링  (0) 2011.01.30
T_SQL::SP_WHO2 + DBCC  (0) 2010.06.03
2010. 9. 6. 10:13

DBCC ShrinkFile


SET NOCOUNT ON
DECLARE @shrinkUnitSize INT, @TotalSizeMB INT, @filename varchar(50), @start_time datetime, @idx int
SET @shrinkUnitSize = 500
SELECT @TotalSizeMB = size/128, @filename= name FROM master..sysaltfiles where dbid=db_id('dba') and fileid =1

SELECT @TotalSizeMB AS CurrentSizeMB, GETDATE() AS StartTime

SET @idx = 1
WHILE (@TotalSizeMB > 300000)-- 현재사이즈가GB보다클때줄이는작업(file사이즈를GB아래로조정하는작업)
BEGIN 
         SET @start_time = GETDATE()
         SET @TotalSizeMB = @TotalSizeMB - @shrinkUnitSize        
         DBCC SHRINKFILE (@filename, @TotalSizeMB) WITH NO_INFOMSGS 

--         SELECT @idx as idx, @TotalSizeMB as TotalSizeMB, DATEDIFF(s, @start_time, getdate()) as duration
         
         PRINT 'idx = ' + CAST(@idx AS varchar(10)) + ', totalSizeMB = ' + CAST(@TotalSizeMB AS varchar(10)) + ', duration = ' + CAST(DATEDIFF(s, @start_time, getdate()) AS varchar(10))

              if DATEDIFF(s, @start_time, getdate() ) < 10 -- 줄어드는시간이10초이하로떨어지면5GB단위로줄임
                    SET @shrinkUnitSize = 5000                          
              
         SET @idx = @idx + 1
END

SELECT @TotalSizeMB AS ShrinkSizeMB, GETDATE() AS EndTime
GO

'T-SQL' 카테고리의 다른 글

T-SQL:: TCP 포트 정보  (0) 2011.08.13
T-SQL:: INDEX-중복인덱스 제거.  (1) 2011.08.13
[T-SQL] JOb 수행 시간 완료 계산  (0) 2010.07.19
Index::Defrag Script v4.0  (0) 2010.06.15
2010. 7. 19. 14:50

[T-SQL] JOb 수행 시간 완료 계산

SQL Server 2005 이상

1. JOB의 수행 완료 시간 계산



SELECT  a.name,  b.run_date, convert(varchar(4),run_time/10000) + ':' + convert(varchar(4),run_time/100%100) + ':' + convert(varchar(4),run_time%100) as run_time
		,convert(varchar(4),run_duration/10000) + ':' + convert(varchar(4),run_duration/100%100) + ':' + convert(varchar(4),run_duration%100) as run_duration
		,convert(int, (run_duration/10000 * 3600 ) + ((run_duration/100%100) * 60) + (run_duration%100 )) as duration_sec
		,convert(DATETIME, RTRIM(run_date)) +  (run_time * 9 + run_time % 10000 * 6 + run_time % 100 * 10) / 216e4 as start_date
		,dateadd(ss, convert(int, (run_duration/10000 * 3600 ) + ((run_duration/100%100) * 60) + (run_duration%100 ))
					,CONVERT(DATETIME, RTRIM(run_date)) +  (run_time * 9 + run_time % 10000 * 6 + run_time % 100 * 10) / 216e4 ) as end_date
FROM msdb.dbo.sysjobs as a with (nolock)
	inner join msdb.dbo.sysjobhistory as b with (nolock)  on a.job_id = b.job_id
where 
	--and b.job_id = ''
	b.run_date >= 20100709 and b.run_date < 20100719
	and b.step_id = 0
order by b.run_date, b.run_time



2. JOB 스케줄 조회



select
    j.job_id
,   J.name
,   S.name as '일정'
,	CASE S.freq_type WHEN 1 THEN '한번만'
			WHEN 4 THEN '매일'
			WHEN 8 THEN '매주'
			WHEN 16 THEN '매월'
			WHEN 32 THEN '매월 상대적'
			WHEN 64 THEN 'SQL Server Agent가 시작될때 실행' 
	END as freq_type 
,   s.freq_interval
,   CASE S.freq_subday_type
        WHEN 1 THEN '지정된시간'
        WHEN 2 THEN '초'
        WHEN 4 THEN '분'
        WHEN 8 THEN '시간'
    END AS freq_subday_type
,	S.freq_subday_interval         
,	right('00000' + cast(S.active_start_time as varchar), 6) as '시작시간'
,	right('00000' + cast(S.active_end_time as varchar), 6) as '끝시간'
,	S.active_start_date as 'job 시작일'
,	S.active_end_date as 'job 종료일'
from msdb.dbo.sysjobs J LEFT JOIN sysjobschedules as A on a.job_id = J.job_id
    JOIN msdb.dbo.sysschedules S WITH (NOLOCK) ON A.schedule_id = S.schedule_id
where J.enabled = 1
order by J.name, right('00000' + cast(S.active_start_time as varchar), 6) asc



3. 해당 시간에 실행되는 JOB 


--=======================================
-- 해당 시간에 실행되는  JOB 
-- =======================================
select  J.name
	, S.last_run_date
	, convert(char(10), S.last_run_time, 114) as 'last_run_start_time'
	,S.last_run_time + S.last_run_duration as 'last_run_end_time', S.last_run_duration as 'duration_time'
from msdb.dbo.sysjobservers S with (nolock)
  inner join msdb.dbo.sysjobs J with (nolock) on S.job_id = J.job_id
where S.last_run_date = '20081202'
-- 작업 시간 입력
and ( (S.last_run_time between '000000' and '010000' ) or
	(S.last_run_time + S.last_run_duration >= '000000' and S.last_run_time + S.last_run_duration < '010000'))
	and J.enabled = 1
order by J.name


select j.job_id, j.name, h.step_id
	, sum(case when  h.run_status = 1 then 1 else 0 end) as '성공count'
	, sum(case when  h.run_status = 0 then 1 else 0 end) as '실패count'
	 ,max(h.run_date) as run_date, max(h.run_time) as run_time 
	, max((CONVERT ( DATETIME, RTRIM(run_date)) 
		+  ( run_time * 9 + run_time % 10000 * 6  + run_time % 100 * 10  + 25 * run_duration ) / 216e4  ) )  as 종료시간	 
from msdb.dbo.sysjobhistory as h with (nolock)
	join msdb.dbo.sysjobs as j with (nolock) on h.job_id = j.job_id
where run_date = '20100412'
	and run_time >= '210000'
group by j.job_id, j.name, h.step_id
order by j.job_id, h.step_id



'T-SQL' 카테고리의 다른 글

T-SQL:: INDEX-중복인덱스 제거.  (1) 2011.08.13
DBCC ShrinkFile  (0) 2010.09.06
Index::Defrag Script v4.0  (0) 2010.06.15
T_SQL::미 사용 Table  (0) 2010.06.15
2010. 6. 15. 06:51

Index::Defrag Script v4.0

SQL SERVER 2008 SP 2 이상


출처: http://sqlfool.com/category/sql-scripts/

인덱스 조각화를 얻고 실행하는 sp 이다.
SQLl 2005의 sp2 이상에서 가능하다.


1. 테이블 생성
/* Scroll down to the see notes, disclaimers, and licensing information */
 
DECLARE @indexDefragLog_rename      VARCHAR(128)
    , @indexDefragExclusion_rename  VARCHAR(128)
    , @indexDefragStatus_rename     VARCHAR(128);
 
SELECT @indexDefragLog_rename       = 'dba_indexDefragLog_obsolete_' + CONVERT(VARCHAR(10), GETDATE(), 112)
    , @indexDefragExclusion_rename  = 'dba_indexDefragExclusion_obsolete_' + CONVERT(VARCHAR(10), GETDATE(), 112)
    , @indexDefragStatus_rename     = 'dba_indexDefragStatus_obsolete_' + CONVERT(VARCHAR(10), GETDATE(), 112);
 
IF Exists(SELECT [OBJECT_ID] FROM sys.tables WHERE [name] = 'dba_indexDefragLog')
    EXECUTE SP_RENAME dba_indexDefragLog, @indexDefragLog_rename;
 
IF Exists(SELECT [OBJECT_ID] FROM sys.tables WHERE [name] = 'dba_indexDefragExclusion')
    EXECUTE SP_RENAME dba_indexDefragExclusion, @indexDefragExclusion_rename;
 
IF Exists(SELECT [OBJECT_ID] FROM sys.tables WHERE [name] = 'dba_indexDefragStatus')
    EXECUTE SP_RENAME dba_indexDefragStatus, @indexDefragStatus_rename;
Go
 
CREATE TABLE dbo.dba_indexDefragLog
(
      indexDefrag_id    INT IDENTITY(1,1)   Not Null
    , databaseID        INT                 Not Null
    , databaseName      NVARCHAR(128)       Not Null
    , objectID          INT                 Not Null
    , objectName        NVARCHAR(128)       Not Null
    , indexID           INT                 Not Null
    , indexName         NVARCHAR(128)       Not Null
    , partitionNumber   SMALLINT            Not Null
    , fragmentation     FLOAT               Not Null
    , page_count        INT                 Not Null
    , dateTimeStart     DATETIME            Not Null
    , dateTimeEnd       DATETIME            Null
    , durationSeconds   INT                 Null
    , sqlStatement      VARCHAR(4000)       Null
    , errorMessage      VARCHAR(1000)       Null
 
    CONSTRAINT PK_indexDefragLog_v40
        PRIMARY KEY CLUSTERED (indexDefrag_id)
);
 
PRINT 'dba_indexDefragLog Table Created';
 
CREATE TABLE dbo.dba_indexDefragExclusion
(
      databaseID        INT                 Not Null
    , databaseName      NVARCHAR(128)       Not Null
    , objectID          INT                 Not Null
    , objectName        NVARCHAR(128)       Not Null
    , indexID           INT                 Not Null
    , indexName         NVARCHAR(128)       Not Null
    , exclusionMask     INT                 Not Null
        /* 1=Sunday, 2=Monday, 4=Tuesday, 8=Wednesday, 16=Thursday, 32=Friday, 64=Saturday */
 
    CONSTRAINT PK_indexDefragExclusion_v40
        PRIMARY KEY CLUSTERED (databaseID, objectID, indexID)
);
 
PRINT 'dba_indexDefragExclusion Table Created';
 
CREATE TABLE dbo.dba_indexDefragStatus
(
      databaseID        INT
    , databaseName      NVARCHAR(128)
    , objectID          INT
    , indexID           INT
    , partitionNumber   SMALLINT
    , fragmentation     FLOAT
    , page_count        INT
    , range_scan_count  BIGINT
    , schemaName        NVARCHAR(128)   Null
    , objectName        NVARCHAR(128)   Null
    , indexName         NVARCHAR(128)   Null
    , scanDate          DATETIME        
    , defragDate        DATETIME        Null
    , printStatus       BIT             DEFAULT(0)
    , exclusionMask     INT             DEFAULT(0)
 
    CONSTRAINT PK_indexDefragStatus_v40
        PRIMARY KEY CLUSTERED(databaseID, objectID, indexID, partitionNumber)
);
 
PRINT 'dba_indexDefragStatus Table Created';
2. 프로시저 생성
--===================
-- proc
--===================
 
IF OBJECTPROPERTY(OBJECT_ID('dbo.dba_indexDefrag_sp'), N'IsProcedure') = 1
BEGIN
    DROP PROCEDURE dbo.dba_indexDefrag_sp;
    PRINT 'Procedure dba_indexDefrag_sp dropped';
END;
Go
 
CREATE PROCEDURE dbo.dba_indexDefrag_sp
 
    /* Declare Parameters */
      @minFragmentation     FLOAT           = 10.0  
        /* in percent, will not defrag if fragmentation less than specified */
    , @rebuildThreshold     FLOAT           = 30.0  
        /* in percent, greater than @rebuildThreshold will result in rebuild instead of reorg */
    , @executeSQL           BIT             = 1     
        /* 1 = execute; 0 = print command only */
    , @defragOrderColumn    NVARCHAR(20)    = 'range_scan_count'
        /* Valid options are: range_scan_count, fragmentation, page_count */
    , @defragSortOrder      NVARCHAR(4)     = 'DESC'
        /* Valid options are: ASC, DESC */
    , @timeLimit            INT             = 720 /* defaulted to 12 hours */
        /* Optional time limitation; expressed in minutes */
    , @DATABASE             VARCHAR(128)    = Null
        /* Option to specify a database name; null will return all */
    , @tableName            VARCHAR(4000)   = Null  -- databaseName.schema.tableName
        /* Option to specify a table name; null will return all */
    , @forceRescan          BIT             = 0
        /* Whether or not to force a rescan of indexes; 1 = force, 0 = use existing scan, if available */
    , @scanMode             VARCHAR(10)     = N'LIMITED'
        /* Options are LIMITED, SAMPLED, and DETAILED */
    , @minPageCount         INT             = 8 
        /*  MS recommends > 1 extent (8 pages) */
    , @maxPageCount         INT             = Null
        /* NULL = no limit */
    , @excludeMaxPartition  BIT             = 0
        /* 1 = exclude right-most populated partition; 0 = do not exclude; see notes for caveats */
    , @onlineRebuild        BIT             = 1     
        /* 1 = online rebuild; 0 = offline rebuild; only in Enterprise */
    , @sortInTempDB         BIT             = 1
        /* 1 = perform sort operation in TempDB; 0 = perform sort operation in the index's database */
    , @maxDopRestriction    TINYINT         = Null
        /* Option to restrict the number of processors for the operation; only in Enterprise */
    , @printCommands        BIT             = 0     
        /* 1 = print commands; 0 = do not print commands */
    , @printFragmentation   BIT             = 0
        /* 1 = print fragmentation prior to defrag; 
           0 = do not print */
    , @defragDelay          CHAR(8)         = '00:00:05'
        /* time to wait between defrag commands */
    , @debugMode            BIT             = 0
        /* display some useful comments to help determine if/where issues occur */
 
AS
/*********************************************************************************
    Name:       dba_indexDefrag_sp
 
    Author:     Michelle Ufford, http://sqlfool.com
 
    Purpose:    Defrags one or more indexes for one or more databases
 
    Notes:
 
    CAUTION: TRANSACTION LOG SIZE SHOULD BE MONITORED CLOSELY WHEN DEFRAGMENTING.
             DO NOT RUN UNATTENDED ON LARGE DATABASES DURING BUSINESS HOURS.
 
      @minFragmentation     defaulted to 10%, will not defrag if fragmentation 
                            is less than that
 
      @rebuildThreshold     defaulted to 30% as recommended by Microsoft in BOL;
                            greater than 30% will result in rebuild instead
 
      @executeSQL           1 = execute the SQL generated by this proc; 
                            0 = print command only
 
      @defragOrderColumn    Defines how to prioritize the order of defrags.  Only
                            used if @executeSQL = 1.  
                            Valid options are: 
                            range_scan_count = count of range and table scans on the
                                               index; in general, this is what benefits 
                                               the most from defragmentation
                            fragmentation    = amount of fragmentation in the index;
                                               the higher the number, the worse it is
                            page_count       = number of pages in the index; affects
                                               how long it takes to defrag an index
 
      @defragSortOrder      The sort order of the ORDER BY clause.
                            Valid options are ASC (ascending) or DESC (descending).
 
      @timeLimit            Optional, limits how much time can be spent performing 
                            index defrags; expressed in minutes.
 
                            NOTE: The time limit is checked BEFORE an index defrag
                                  is begun, thus a long index defrag can exceed the
                                  time limitation.
 
      @database             Optional, specify specific database name to defrag;
                            If not specified, all non-system databases will
                            be defragged.
 
      @tableName            Specify if you only want to defrag indexes for a 
                            specific table, format = databaseName.schema.tableName;
                            if not specified, all tables will be defragged.
 
      @forceRescan          Whether or not to force a rescan of indexes.  If set
                            to 0, a rescan will not occur until all indexes have
                            been defragged.  This can span multiple executions.
                            1 = force a rescan
                            0 = use previous scan, if there are indexes left to defrag
 
      @scanMode             Specifies which scan mode to use to determine
                            fragmentation levels.  Options are:
                            LIMITED - scans the parent level; quickest mode,
                                      recommended for most cases.
                            SAMPLED - samples 1% of all data pages; if less than
                                      10k pages, performs a DETAILED scan.
                            DETAILED - scans all data pages.  Use great care with
                                       this mode, as it can cause performance issues.
 
      @minPageCount         Specifies how many pages must exist in an index in order 
                            to be considered for a defrag.  Defaulted to 8 pages, as 
                            Microsoft recommends only defragging indexes with more 
                            than 1 extent (8 pages).  
 
                            NOTE: The @minPageCount will restrict the indexes that
                            are stored in dba_indexDefragStatus table.
 
      @maxPageCount         Specifies the maximum number of pages that can exist in 
                            an index and still be considered for a defrag.  Useful
                            for scheduling small indexes during business hours and
                            large indexes for non-business hours.
 
                            NOTE: The @maxPageCount will restrict the indexes that
                            are defragged during the current operation; it will not
                            prevent indexes from being stored in the 
                            dba_indexDefragStatus table.  This way, a single scan
                            can support multiple page count thresholds.
 
      @excludeMaxPartition  If an index is partitioned, this option specifies whether
                            to exclude the right-most populated partition.  Typically,
                            this is the partition that is currently being written to in
                            a sliding-window scenario.  Enabling this feature may reduce
                            contention.  This may not be applicable in other types of 
                            partitioning scenarios.  Non-partitioned indexes are 
                            unaffected by this option.
                            1 = exclude right-most populated partition
                            0 = do not exclude
 
      @onlineRebuild        1 = online rebuild; 
                            0 = offline rebuild
 
      @sortInTempDB         Specifies whether to defrag the index in TEMPDB or in the
                            database the index belongs to.  Enabling this option may
                            result in faster defrags and prevent database file size 
                            inflation.
                            1 = perform sort operation in TempDB
                            0 = perform sort operation in the index's database 
 
      @maxDopRestriction    Option to specify a processor limit for index rebuilds
 
      @printCommands        1 = print commands to screen; 
                            0 = do not print commands
 
      @printFragmentation   1 = print fragmentation to screen;
                            0 = do not print fragmentation
 
      @defragDelay          Time to wait between defrag commands; gives the
                            server a little time to catch up 
 
      @debugMode            1 = display debug comments; helps with troubleshooting
                            0 = do not display debug comments
 
    Called by:  SQL Agent Job or DBA
 
    ----------------------------------------------------------------------------
    DISCLAIMER: 
    This code and information are provided "AS IS" without warranty of any kind,
    either expressed or implied, including but not limited to the implied 
    warranties or merchantability and/or fitness for a particular purpose.
    ----------------------------------------------------------------------------
    LICENSE: 
    This index defrag script is free to download and use for personal, educational, 
    and internal corporate purposes, provided that this header is preserved. 
    Redistribution or sale of this index defrag script, in whole or in part, is 
    prohibited without the author's express written consent.
    ----------------------------------------------------------------------------
    Date        Initials	Version Description
    ----------------------------------------------------------------------------
    2007-12-18  MFU         1.0     Initial Release
    2008-10-17  MFU         1.1     Added @defragDelay, CIX_temp_indexDefragList
    2008-11-17  MFU         1.2     Added page_count to log table
                                    , added @printFragmentation option
    2009-03-17  MFU         2.0     Provided support for centralized execution
                                    , consolidated Enterprise & Standard versions
                                    , added @debugMode, @maxDopRestriction
                                    , modified LOB and partition logic  
    2009-06-18  MFU         3.0     Fixed bug in LOB logic, added @scanMode option
                                    , added support for stat rebuilds (@rebuildStats)
                                    , support model and msdb defrag
                                    , added columns to the dba_indexDefragLog table
                                    , modified logging to show "in progress" defrags
                                    , added defrag exclusion list (scheduling)
    2009-08-28  MFU         3.1     Fixed read_only bug for database lists
    2010-04-20  MFU         4.0     Added time limit option
                                    , added static table with rescan logic
                                    , added parameters for page count & SORT_IN_TEMPDB
                                    , added try/catch logic and additional debug options
                                    , added options for defrag prioritization
                                    , fixed bug for indexes with allow_page_lock = off
                                    , added option to exclude right-most partition
                                    , removed @rebuildStats option
                                    , refer to http://sqlfool.com for full release notes
*********************************************************************************
    Example of how to call this script:
 
        Exec dbo.dba_indexDefrag_sp
              @executeSQL           = 1
            , @printCommands        = 1
            , @debugMode            = 1
            , @printFragmentation   = 1
            , @forceRescan          = 1
            , @maxDopRestriction    = 1
            , @minPageCount         = 8
            , @maxPageCount         = Null
            , @minFragmentation     = 1
            , @rebuildThreshold     = 30
            , @defragDelay          = '00:00:05'
            , @defragOrderColumn    = 'page_count'
            , @defragSortOrder      = 'DESC'
            , @excludeMaxPartition  = 1
            , @timeLimit            = Null;
*********************************************************************************/																
SET NOCOUNT ON;
SET XACT_Abort ON;
SET Quoted_Identifier ON;
 
BEGIN
 
    BEGIN Try
 
        /* Just a little validation... */
        IF @minFragmentation IS Null 
            Or @minFragmentation Not Between 0.00 And 100.0
                SET @minFragmentation = 10.0;
 
        IF @rebuildThreshold IS Null
            Or @rebuildThreshold Not Between 0.00 And 100.0
                SET @rebuildThreshold = 30.0;
 
        IF @defragDelay Not Like '00:[0-5][0-9]:[0-5][0-9]'
            SET @defragDelay = '00:00:05';
 
        IF @defragOrderColumn IS Null
            Or @defragOrderColumn Not In ('range_scan_count', 'fragmentation', 'page_count')
                SET @defragOrderColumn = 'range_scan_count';
 
        IF @defragSortOrder IS Null
            Or @defragSortOrder Not In ('ASC', 'DESC')
                SET @defragSortOrder = 'DESC';
 
        IF @scanMode Not In ('LIMITED', 'SAMPLED', 'DETAILED')
            SET @scanMode = 'LIMITED';
 
        IF @debugMode IS Null
            SET @debugMode = 0;
 
        IF @forceRescan IS Null
            SET @forceRescan = 0;
 
        IF @sortInTempDB IS Null
            SET @sortInTempDB = 1;
 
 
        IF @debugMode = 1 RAISERROR('Undusting the cogs and starting up...', 0, 42) WITH NoWait;
 
        /* Declare our variables */
        DECLARE   @objectID                 INT
                , @databaseID               INT
                , @databaseName             NVARCHAR(128)
                , @indexID                  INT
                , @partitionCount           BIGINT
                , @schemaName               NVARCHAR(128)
                , @objectName               NVARCHAR(128)
                , @indexName                NVARCHAR(128)
                , @partitionNumber          SMALLINT
                , @fragmentation            FLOAT
                , @pageCount                INT
                , @sqlCommand               NVARCHAR(4000)
                , @rebuildCommand           NVARCHAR(200)
                , @dateTimeStart            DATETIME
                , @dateTimeEnd              DATETIME
                , @containsLOB              BIT
                , @editionCheck             BIT
                , @debugMessage             NVARCHAR(4000)
                , @updateSQL                NVARCHAR(4000)
                , @partitionSQL             NVARCHAR(4000)
                , @partitionSQL_Param       NVARCHAR(1000)
                , @LOB_SQL                  NVARCHAR(4000)
                , @LOB_SQL_Param            NVARCHAR(1000)
                , @indexDefrag_id           INT
                , @startDateTime            DATETIME
                , @endDateTime              DATETIME
                , @getIndexSQL              NVARCHAR(4000)
                , @getIndexSQL_Param        NVARCHAR(4000)
                , @allowPageLockSQL         NVARCHAR(4000)
                , @allowPageLockSQL_Param   NVARCHAR(4000)
                , @allowPageLocks           INT
                , @excludeMaxPartitionSQL   NVARCHAR(4000);
 
        /* Initialize our variables */
        SELECT @startDateTime = GETDATE()
            , @endDateTime = DATEADD(MINUTE, @timeLimit, GETDATE());
 
        /* Create our temporary tables */
        CREATE TABLE #databaseList
        (
              databaseID        INT
            , databaseName      VARCHAR(128)
            , scanStatus        BIT
        );
 
        CREATE TABLE #processor 
        (
              [INDEX]           INT
            , Name              VARCHAR(128)
            , Internal_Value    INT
            , Character_Value   INT
        );
 
        CREATE TABLE #maxPartitionList
        (
              databaseID        INT
            , objectID          INT
            , indexID           INT
            , maxPartition      INT
        );
 
        IF @debugMode = 1 RAISERROR('Beginning validation...', 0, 42) WITH NoWait;
 
        /* Make sure we're not exceeding the number of processors we have available */
        INSERT INTO #processor
        EXECUTE XP_MSVER 'ProcessorCount';
 
        IF @maxDopRestriction IS Not Null And @maxDopRestriction > (SELECT Internal_Value FROM #processor)
            SELECT @maxDopRestriction = Internal_Value
            FROM #processor;
 
        /* Check our server version; 1804890536 = Enterprise, 610778273 = Enterprise Evaluation, -2117995310 = Developer */
        IF (SELECT SERVERPROPERTY('EditionID')) In (1804890536, 610778273, -2117995310) 
            SET @editionCheck = 1 -- supports online rebuilds
        ELSE
            SET @editionCheck = 0; -- does not support online rebuilds
 
        /* Output the parameters we're working with */
        IF @debugMode = 1 
        BEGIN
 
            SELECT @debugMessage = 'Your selected parameters are... 
            Defrag indexes with fragmentation greater than ' + CAST(@minFragmentation AS VARCHAR(10)) + ';
            Rebuild indexes with fragmentation greater than ' + CAST(@rebuildThreshold AS VARCHAR(10)) + ';
            You' + CASE WHEN @executeSQL = 1 THEN ' DO' ELSE ' DO NOT' END + ' want the commands to be executed automatically; 
            You want to defrag indexes in ' + @defragSortOrder + ' order of the ' + UPPER(@defragOrderColumn) + ' value;
            You have' + CASE WHEN @timeLimit IS Null THEN ' not specified a time limit;' ELSE ' specified a time limit of ' 
                + CAST(@timeLimit AS VARCHAR(10)) END + ' minutes;
            ' + CASE WHEN @DATABASE IS Null THEN 'ALL databases' ELSE 'The ' + @DATABASE + ' database' END + ' will be defragged;
            ' + CASE WHEN @tableName IS Null THEN 'ALL tables' ELSE 'The ' + @tableName + ' table' END + ' will be defragged;
            We' + CASE WHEN Exists(SELECT TOP 1 * FROM dbo.dba_indexDefragStatus WHERE defragDate IS Null)
                And @forceRescan <> 1 THEN ' WILL NOT' ELSE ' WILL' END + ' be rescanning indexes;
            The scan will be performed in ' + @scanMode + ' mode;
            You want to limit defrags to indexes with' + CASE WHEN @maxPageCount IS Null THEN ' more than ' 
                + CAST(@minPageCount AS VARCHAR(10)) ELSE
                ' between ' + CAST(@minPageCount AS VARCHAR(10))
                + ' and ' + CAST(@maxPageCount AS VARCHAR(10)) END + ' pages;
            Indexes will be defragged' + CASE WHEN @editionCheck = 0 Or @onlineRebuild = 0 THEN ' OFFLINE;' ELSE ' ONLINE;' END + '
            Indexes will be sorted in' + CASE WHEN @sortInTempDB = 0 THEN ' the DATABASE' ELSE ' TEMPDB;' END + '
            Defrag operations will utilize ' + CASE WHEN @editionCheck = 0 Or @maxDopRestriction IS Null 
                THEN 'system defaults for processors;' 
                ELSE CAST(@maxDopRestriction AS VARCHAR(2)) + ' processors;' END + '
            You' + CASE WHEN @printCommands = 1 THEN ' DO' ELSE ' DO NOT' END + ' want to print the ALTER INDEX commands; 
            You' + CASE WHEN @printFragmentation = 1 THEN ' DO' ELSE ' DO NOT' END + ' want to output fragmentation levels; 
            You want to wait ' + @defragDelay + ' (hh:mm:ss) between defragging indexes;
            You want to run in' + CASE WHEN @debugMode = 1 THEN ' DEBUG' ELSE ' SILENT' END + ' mode.';
 
            RAISERROR(@debugMessage, 0, 42) WITH NoWait;
 
        END;
 
        IF @debugMode = 1 RAISERROR('Grabbing a list of our databases...', 0, 42) WITH NoWait;
 
        /* Retrieve the list of databases to investigate */
        INSERT INTO #databaseList
        SELECT database_id
            , name
            , 0 -- not scanned yet for fragmentation
        FROM sys.databases
        WHERE name = IsNull(@DATABASE, name)
            And [name] Not In ('master', 'tempdb')-- exclude system databases
            And [STATE] = 0 -- state must be ONLINE
            And is_read_only = 0;  -- cannot be read_only
 
        /* Check to see if we have indexes in need of defrag; otherwise, re-scan the database(s) */
        IF Not Exists(SELECT TOP 1 * FROM dbo.dba_indexDefragStatus WHERE defragDate IS Null)
            Or @forceRescan = 1
        BEGIN
 
            /* Truncate our list of indexes to prepare for a new scan */
            TRUNCATE TABLE dbo.dba_indexDefragStatus;
 
            IF @debugMode = 1 RAISERROR('Looping through our list of databases and checking for fragmentation...', 0, 42) WITH NoWait;
 
            /* Loop through our list of databases */
            WHILE (SELECT COUNT(*) FROM #databaseList WHERE scanStatus = 0) > 0
            BEGIN
 
                SELECT TOP 1 @databaseID = databaseID
                FROM #databaseList
                WHERE scanStatus = 0;
 
                SELECT @debugMessage = '  working on ' + DB_NAME(@databaseID) + '...';
 
                IF @debugMode = 1
                    RAISERROR(@debugMessage, 0, 42) WITH NoWait;
 
               /* Determine which indexes to defrag using our user-defined parameters */
                INSERT INTO dbo.dba_indexDefragStatus
                (
                      databaseID
                    , databaseName
                    , objectID
                    , indexID
                    , partitionNumber
                    , fragmentation
                    , page_count
                    , range_scan_count
                    , scanDate
                )
                SELECT
                      ps.database_id AS 'databaseID'
                    , QUOTENAME(DB_NAME(ps.database_id)) AS 'databaseName'
                    , ps.OBJECT_ID AS 'objectID'
                    , ps.index_id AS 'indexID'
                    , ps.partition_number AS 'partitionNumber'
                    , SUM(ps.avg_fragmentation_in_percent) AS 'fragmentation'
                    , SUM(ps.page_count) AS 'page_count'
                    , os.range_scan_count
                    , GETDATE() AS 'scanDate'
                FROM sys.dm_db_index_physical_stats(@databaseID, OBJECT_ID(@tableName), Null , Null, @scanMode) AS ps
                Join sys.dm_db_index_operational_stats(@databaseID, OBJECT_ID(@tableName), Null , Null) AS os
                    ON ps.database_id = os.database_id
                    And ps.OBJECT_ID = os.OBJECT_ID
                    and ps.index_id = os.index_id
                    And ps.partition_number = os.partition_number
                WHERE avg_fragmentation_in_percent >= @minFragmentation 
                    And ps.index_id > 0 -- ignore heaps
                    And ps.page_count > @minPageCount 
                    And ps.index_level = 0 -- leaf-level nodes only, supports @scanMode
                GROUP BY ps.database_id 
                    , QUOTENAME(DB_NAME(ps.database_id)) 
                    , ps.OBJECT_ID 
                    , ps.index_id 
                    , ps.partition_number 
                    , os.range_scan_count
                OPTION (MaxDop 2);
 
                /* Do we want to exclude right-most populated partition of our partitioned indexes? */
                IF @excludeMaxPartition = 1
                BEGIN
 
                    SET @excludeMaxPartitionSQL = '
                        Select ' + CAST(@databaseID AS VARCHAR(10)) + ' As [databaseID]
                            , [object_id]
                            , index_id
                            , Max(partition_number) As [maxPartition]
                        From ' + DB_NAME(@databaseID) + '.sys.partitions
                        Where partition_number > 1
                            And [rows] > 0
                        Group By object_id
                            , index_id;';
 
                    INSERT INTO #maxPartitionList
                    EXECUTE SP_EXECUTESQL @excludeMaxPartitionSQL;
 
                END;
 
                /* Keep track of which databases have already been scanned */
                UPDATE #databaseList
                SET scanStatus = 1
                WHERE databaseID = @databaseID;
 
            END
 
            /* We don't want to defrag the right-most populated partition, so
               delete any records for partitioned indexes where partition = Max(partition) */
            IF @excludeMaxPartition = 1
            BEGIN
 
                DELETE ids
                FROM dbo.dba_indexDefragStatus AS ids
                Join #maxPartitionList AS mpl
                    ON ids.databaseID = mpl.databaseID
                    And ids.objectID = mpl.objectID
                    And ids.indexID = mpl.indexID
                    And ids.partitionNumber = mpl.maxPartition;
 
            END;
 
            /* Update our exclusion mask for any index that has a restriction on the days it can be defragged */
            UPDATE ids
            SET ids.exclusionMask = ide.exclusionMask
            FROM dbo.dba_indexDefragStatus AS ids
            Join dbo.dba_indexDefragExclusion AS ide
                ON ids.databaseID = ide.databaseID
                And ids.objectID = ide.objectID
                And ids.indexID = ide.indexID;
 
        END
 
        SELECT @debugMessage = 'Looping through our list... there are ' + CAST(COUNT(*) AS VARCHAR(10)) + ' indexes to defrag!'
        FROM dbo.dba_indexDefragStatus
        WHERE defragDate IS Null
            And page_count Between @minPageCount And IsNull(@maxPageCount, page_count);
 
        IF @debugMode = 1 RAISERROR(@debugMessage, 0, 42) WITH NoWait;
 
        /* Begin our loop for defragging */
        WHILE (SELECT COUNT(*) 
               FROM dbo.dba_indexDefragStatus 
               WHERE (
                           (@executeSQL = 1 And defragDate IS Null) 
                        Or (@executeSQL = 0 And defragDate IS Null And printStatus = 0)
                     )
                And exclusionMask & POWER(2, DATEPART(weekday, GETDATE())-1) = 0
                And page_count Between @minPageCount And IsNull(@maxPageCount, page_count)) > 0
        BEGIN
 
            /* Check to see if we need to exit our loop because of our time limit */        
            IF IsNull(@endDateTime, GETDATE()) < GETDATE()
            BEGIN
                RAISERROR('Our time limit has been exceeded!', 11, 42) WITH NoWait;
            END;
 
            IF @debugMode = 1 RAISERROR('  Picking an index to beat into shape...', 0, 42) WITH NoWait;
 
            /* Grab the index with the highest priority, based on the values submitted; 
               Look at the exclusion mask to ensure it can be defragged today */
            SET @getIndexSQL = N'
            Select Top 1 
                  @objectID_Out         = objectID
                , @indexID_Out          = indexID
                , @databaseID_Out       = databaseID
                , @databaseName_Out     = databaseName
                , @fragmentation_Out    = fragmentation
                , @partitionNumber_Out  = partitionNumber
                , @pageCount_Out        = page_count
            From dbo.dba_indexDefragStatus
            Where defragDate Is Null ' 
                + CASE WHEN @executeSQL = 0 THEN 'And printStatus = 0' ELSE '' END + '
                And exclusionMask & Power(2, DatePart(weekday, GetDate())-1) = 0
                And page_count Between @p_minPageCount and IsNull(@p_maxPageCount, page_count)
            Order By + ' + @defragOrderColumn + ' ' + @defragSortOrder;
 
            SET @getIndexSQL_Param = N'@objectID_Out        int OutPut
                                     , @indexID_Out         int OutPut
                                     , @databaseID_Out      int OutPut
                                     , @databaseName_Out    nvarchar(128) OutPut
                                     , @fragmentation_Out   int OutPut
                                     , @partitionNumber_Out int OutPut
                                     , @pageCount_Out       int OutPut
                                     , @p_minPageCount      int
                                     , @p_maxPageCount      int';
 
            EXECUTE SP_EXECUTESQL @getIndexSQL
                , @getIndexSQL_Param
                , @p_minPageCount       = @minPageCount
                , @p_maxPageCount       = @maxPageCount
                , @objectID_Out         = @objectID OUTPUT
                , @indexID_Out          = @indexID OUTPUT
                , @databaseID_Out       = @databaseID OUTPUT
                , @databaseName_Out     = @databaseName OUTPUT
                , @fragmentation_Out    = @fragmentation OUTPUT
                , @partitionNumber_Out  = @partitionNumber OUTPUT
                , @pageCount_Out        = @pageCount OUTPUT;
 
            IF @debugMode = 1 RAISERROR('  Looking up the specifics for our index...', 0, 42) WITH NoWait;
 
            /* Look up index information */
            SELECT @updateSQL = N'Update ids
                Set schemaName = QuoteName(s.name)
                    , objectName = QuoteName(o.name)
                    , indexName = QuoteName(i.name)
                From dbo.dba_indexDefragStatus As ids
                Inner Join ' + @databaseName + '.sys.objects As o
                    On ids.objectID = o.object_id
                Inner Join ' + @databaseName + '.sys.indexes As i
                    On o.object_id = i.object_id
                    And ids.indexID = i.index_id
                Inner Join ' + @databaseName + '.sys.schemas As s
                    On o.schema_id = s.schema_id
                Where o.object_id = ' + CAST(@objectID AS VARCHAR(10)) + '
                    And i.index_id = ' + CAST(@indexID AS VARCHAR(10)) + '
                    And i.type > 0
                    And ids.databaseID = ' + CAST(@databaseID AS VARCHAR(10));
 
            EXECUTE SP_EXECUTESQL @updateSQL;
 
            /* Grab our object names */
            SELECT @objectName  = objectName
                , @schemaName   = schemaName
                , @indexName    = indexName
            FROM dbo.dba_indexDefragStatus
            WHERE objectID = @objectID
                And indexID = @indexID
                And databaseID = @databaseID;
 
            IF @debugMode = 1 RAISERROR('  Grabbing the partition count...', 0, 42) WITH NoWait;
 
            /* Determine if the index is partitioned */
            SELECT @partitionSQL = 'Select @partitionCount_OUT = Count(*)
                                        From ' + @databaseName + '.sys.partitions
                                        Where object_id = ' + CAST(@objectID AS VARCHAR(10)) + '
                                            And index_id = ' + CAST(@indexID AS VARCHAR(10)) + ';'
                , @partitionSQL_Param = '@partitionCount_OUT int OutPut';
 
            EXECUTE SP_EXECUTESQL @partitionSQL, @partitionSQL_Param, @partitionCount_OUT = @partitionCount OUTPUT;
 
            IF @debugMode = 1 RAISERROR('  Seeing if there are any LOBs to be handled...', 0, 42) WITH NoWait;
 
            /* Determine if the table contains LOBs */
            SELECT @LOB_SQL = ' Select @containsLOB_OUT = Count(*)
                                From ' + @databaseName + '.sys.columns With (NoLock) 
                                Where [object_id] = ' + CAST(@objectID AS VARCHAR(10)) + '
                                   And (system_type_id In (34, 35, 99)
                                            Or max_length = -1);'
                                /*  system_type_id --> 34 = image, 35 = text, 99 = ntext
                                    max_length = -1 --> varbinary(max), varchar(max), nvarchar(max), xml */
                    , @LOB_SQL_Param = '@containsLOB_OUT int OutPut';
 
            EXECUTE SP_EXECUTESQL @LOB_SQL, @LOB_SQL_Param, @containsLOB_OUT = @containsLOB OUTPUT;
 
            IF @debugMode = 1 RAISERROR('  Checking for indexes that do not allow page locks...', 0, 42) WITH NoWait;
 
            /* Determine if page locks are allowed; for those indexes, we need to always rebuild */
            SELECT @allowPageLockSQL = 'Select @allowPageLocks_OUT = Count(*)
                                        From ' + @databaseName + '.sys.indexes
                                        Where object_id = ' + CAST(@objectID AS VARCHAR(10)) + '
                                            And index_id = ' + CAST(@indexID AS VARCHAR(10)) + '
                                            And Allow_Page_Locks = 0;'
                , @allowPageLockSQL_Param = '@allowPageLocks_OUT int OutPut';
 
            EXECUTE SP_EXECUTESQL @allowPageLockSQL, @allowPageLockSQL_Param, @allowPageLocks_OUT = @allowPageLocks OUTPUT;
 
            IF @debugMode = 1 RAISERROR('  Building our SQL statements...', 0, 42) WITH NoWait;
 
            /* If there's not a lot of fragmentation, or if we have a LOB, we should reorganize */
            IF (@fragmentation < @rebuildThreshold Or @containsLOB >= 1 Or @partitionCount > 1)
                And @allowPageLocks = 0
            BEGIN
 
                SET @sqlCommand = N'Alter Index ' + @indexName + N' On ' + @databaseName + N'.' 
                                    + @schemaName + N'.' + @objectName + N' ReOrganize';
 
                /* If our index is partitioned, we should always reorganize */
                IF @partitionCount > 1
                    SET @sqlCommand = @sqlCommand + N' Partition = ' 
                                    + CAST(@partitionNumber AS NVARCHAR(10));
 
            END
            /* If the index is heavily fragmented and doesn't contain any partitions or LOB's, 
               or if the index does not allow page locks, rebuild it */
            ELSE IF (@fragmentation >= @rebuildThreshold Or @allowPageLocks <> 0)
                And IsNull(@containsLOB, 0) != 1 And @partitionCount <= 1
            BEGIN
 
                /* Set online rebuild options; requires Enterprise Edition */
                IF @onlineRebuild = 1 And @editionCheck = 1 
                    SET @rebuildCommand = N' Rebuild With (Online = On';
                ELSE
                    SET @rebuildCommand = N' Rebuild With (Online = Off';
 
                /* Set sort operation preferences */
                IF @sortInTempDB = 1 
                    SET @rebuildCommand = @rebuildCommand + N', Sort_In_TempDB = On';
                ELSE
                    SET @rebuildCommand = @rebuildCommand + N', Sort_In_TempDB = Off';
 
                /* Set processor restriction options; requires Enterprise Edition */
                IF @maxDopRestriction IS Not Null And @editionCheck = 1
                    SET @rebuildCommand = @rebuildCommand + N', MaxDop = ' + CAST(@maxDopRestriction AS VARCHAR(2)) + N')';
                ELSE
                    SET @rebuildCommand = @rebuildCommand + N')';
 
                SET @sqlCommand = N'Alter Index ' + @indexName + N' On ' + @databaseName + N'.'
                                + @schemaName + N'.' + @objectName + @rebuildCommand;
 
            END
            ELSE
                /* Print an error message if any indexes happen to not meet the criteria above */
                IF @printCommands = 1 Or @debugMode = 1
                    RAISERROR('We are unable to defrag this index.', 0, 42) WITH NoWait;
 
            /* Are we executing the SQL?  If so, do it */
            IF @executeSQL = 1
            BEGIN
 
                SET @debugMessage = 'Executing: ' + @sqlCommand;
 
                /* Print the commands we're executing if specified to do so */
                IF @printCommands = 1 Or @debugMode = 1
                    RAISERROR(@debugMessage, 0, 42) WITH NoWait;
 
                /* Grab the time for logging purposes */
                SET @dateTimeStart  = GETDATE();
 
                /* Log our actions */
                INSERT INTO dbo.dba_indexDefragLog
                (
                      databaseID
                    , databaseName
                    , objectID
                    , objectName
                    , indexID
                    , indexName
                    , partitionNumber
                    , fragmentation
                    , page_count
                    , dateTimeStart
                    , sqlStatement
                )
                SELECT
                      @databaseID
                    , @databaseName
                    , @objectID
                    , @objectName
                    , @indexID
                    , @indexName
                    , @partitionNumber
                    , @fragmentation
                    , @pageCount
                    , @dateTimeStart
                    , @sqlCommand;
 
                SET @indexDefrag_id = SCOPE_IDENTITY();
 
                /* Wrap our execution attempt in a try/catch and log any errors that occur */
                BEGIN Try
 
                    /* Execute our defrag! */
                    EXECUTE SP_EXECUTESQL @sqlCommand;
                    SET @dateTimeEnd = GETDATE();
 
                    /* Update our log with our completion time */
                    UPDATE dbo.dba_indexDefragLog
                    SET dateTimeEnd = @dateTimeEnd
                        , durationSeconds = DATEDIFF(SECOND, @dateTimeStart, @dateTimeEnd)
                    WHERE indexDefrag_id = @indexDefrag_id;
 
                END Try
                BEGIN Catch
 
                    /* Update our log with our error message */
                    UPDATE dbo.dba_indexDefragLog
                    SET dateTimeEnd = GETDATE()
                        , durationSeconds = -1
                        , errorMessage = Error_Message()
                    WHERE indexDefrag_id = @indexDefrag_id;
 
                    IF @debugMode = 1 
                        RAISERROR('  An error has occurred executing this command! Please review the dba_indexDefragLog table for details.'
                            , 0, 42) WITH NoWait;
 
                END Catch
 
                /* Just a little breather for the server */
                WAITFOR Delay @defragDelay;
 
                UPDATE dbo.dba_indexDefragStatus
                SET defragDate = GETDATE()
                    , printStatus = 1
                WHERE databaseID       = @databaseID
                  And objectID         = @objectID
                  And indexID          = @indexID
                  And partitionNumber  = @partitionNumber;
 
            END
            ELSE
            /* Looks like we're not executing, just printing the commands */
            BEGIN
                IF @debugMode = 1 RAISERROR('  Printing SQL statements...', 0, 42) WITH NoWait;
 
                IF @printCommands = 1 Or @debugMode = 1 
                    PRINT IsNull(@sqlCommand, 'error!');
 
                UPDATE dbo.dba_indexDefragStatus
                SET printStatus = 1
                WHERE databaseID       = @databaseID
                  And objectID         = @objectID
                  And indexID          = @indexID
                  And partitionNumber  = @partitionNumber;
            END
 
        END
 
        /* Do we want to output our fragmentation results? */
        IF @printFragmentation = 1
        BEGIN
 
            IF @debugMode = 1 RAISERROR('  Displaying a summary of our action...', 0, 42) WITH NoWait;
 
            SELECT databaseID
                , databaseName
                , objectID
                , objectName
                , indexID
                , indexName
                , partitionNumber
                , fragmentation
                , page_count
                , range_scan_count
            FROM dbo.dba_indexDefragStatus
            WHERE defragDate >= @startDateTime
            ORDER BY defragDate;
 
        END;
 
    END Try
    BEGIN Catch
 
        SET @debugMessage = Error_Message() + ' (Line Number: ' + CAST(Error_Line() AS VARCHAR(10)) + ')';
        PRINT @debugMessage;
 
    END Catch;
 
    /* When everything is said and done, make sure to get rid of our temp table */
    DROP TABLE #databaseList;
    DROP TABLE #processor;
    DROP TABLE #maxPartitionList;
 
    IF @debugMode = 1 RAISERROR('DONE!  Thank you for taking care of your indexes!  :)', 0, 42) WITH NoWait;
 
    SET NOCOUNT OFF;
    RETURN 0
END


4. 사용법
 Exec dbo.dba_indexDefrag_sp
              @executeSQL           = 0
            , @printCommands        = 1
            , @DATABASE             ='search'
            --, @tableName            =
            , @debugMode            = 1
            , @printFragmentation   = 1
            , @forceRescan          = 1
            , @maxDopRestriction    = 1
            , @minPageCount         = 8
            , @maxPageCount         = Null
            , @minFragmentation     = 1
            , @rebuildThreshold     = 30
            , @defragDelay          = '00:00:05'
            , @defragOrderColumn    = 'page_count'
            , @defragSortOrder      = 'DESC'
            , @excludeMaxPartition  = 1
            , @timeLimit            = Null;



'T-SQL' 카테고리의 다른 글

DBCC ShrinkFile  (0) 2010.09.06
[T-SQL] JOb 수행 시간 완료 계산  (0) 2010.07.19
T_SQL::미 사용 Table  (0) 2010.06.15
데이터베이스 사이즈  (0) 2010.06.04
2010. 6. 15. 06:27

T_SQL::미 사용 Table


미사용 Table 목록


SELECT  idx.object_id, OBJECT_NAME(IDX.object_id) as object_name,
        max(ps.row_count) as row_count,
        max(obj.create_date) as create_date,
        max(obj.modify_date) as modify_date
FROM sys.dm_db_index_usage_stats  AS DIS
       RIGHT OUTER JOIN sys.indexes AS IDX  ON DIS.object_id = IDX.object_id AND DIS.index_id = IDX.index_id
       JOIN sys.objects AS OBJ  ON IDX.object_id = OBJ.object_ID
       JOIN sys.dm_db_partition_stats as PS ON ps.object_id = OBJ.object_id
WHERE  OBJ.type IN ('U', 'V') AND DIS.object_id IS NULL
GROUP BY idx.object_id
ORDER BY OBJECT_NAME(IDX.object_id)

'T-SQL' 카테고리의 다른 글

[T-SQL] JOb 수행 시간 완료 계산  (0) 2010.07.19
Index::Defrag Script v4.0  (0) 2010.06.15
데이터베이스 사이즈  (0) 2010.06.04
T-SQL::Removing Duplication Data  (1) 2010.06.03
2010. 4. 5. 00:06

SQL Server 2005 and 2008 Ranking Functions

'T-SQL' 카테고리의 다른 글

T-SQL::Convert hex value to String 32bit  (0) 2010.06.03
T_SQL::CONSTRAINT조사  (1) 2010.06.03
DMV::Index  (0) 2010.04.05
T-SQL::특정 objects 찾기  (0) 2010.04.04
2010. 4. 5. 00:01

DMV::Index



SQL SERVER 2005 이상

SQL 2005는 DMV를 통해서 실시간으로 인덱스의 사용빈도, 변경 횟수, lock 현황, missing 된 인덱스 정보를 제공한다.

이 자료를 바탕으로 튜닝과 관리 모니터링이 가능하다.

DMV의 정보는 마지막으로 SQL 인스턴스가 시작된 후로 누적된 값이다.

 사용하고 있는 INDEX 무엇인가?

 DDL 문을 통해 인덱스를 생성하게 되면 인덱스가 카탈로그는 업데이트 된다.

 그렇다고 이 인덱스가 "USE" 사용되는 것은 아니다.  인덱스가 select, insert, update가 될 때 sys.dm_db_index_usage_stats 에서 정보를 찾을 수 있다.

 마지막으로 SQL SERVER를 방금 재 시작 했다면 sys.dm_db_index_usage_stats 정보를 찾을 수 없을 수도 있다.

 

user_seeks : 사용자 쿼리별 검색(Seek) 수입니다.

user_scans : 사용자 쿼리별 검색(Scan) 수입니다.

user_lookups :  사용자 쿼리별 책갈피 조회 수입니다.

user_updates :  사용자 쿼리별 업데이트 수

 

사용하지 않는 테이블 & 인덱스

  1. -- unused tables & indexes.

  2. DECLARE @dbid INT

  3. SET @dbid = DB_ID('AdventureWorks')

  4.  

     

  5. SELECT OBJECT_NAME(IDX.object_id) as object_name,

  6.        IDX.name AS index_name,

  7.        CASE WHEN IDX.type = 1 THEN 'Clustered'

  8.           WHEN IDX.type = 2 THEN 'Non-Clustered'

  9.           ELSE 'Unknown' END Index_Type

  10. FROM sys.dm_db_index_usage_stats  AS DIS

  11.       RIGHT OUTER JOIN sys.indexes AS IDX  ON DIS.object_id = IDX.object_id AND DIS.index_id = IDX.index_id

  12.       JOIN sys.objects AS OBJ  ON IDX.object_id = OBJ.object_ID

  13. WHERE  OBJ.type IN ('U', 'V') AND DIS.object_id IS NULL

  14. ORDER BY OBJECT_NAME(IDX.object_id), IDX.name

     

 

드물게 사용하는 인덱스

 

  1. DECLARE @dbid INT

  2. SET @dbid = DB_ID('AdventureWorks')

  3.  

  4. --- rarely used indexes appear first

  5. SELECT OBJECT_NAME(DIS.object_id) as object_name,

  6.        IDX.name AS index_name, IDX.index_id,

  7.        CASE WHEN IDX.type = 1 THEN 'Clustered'

  8.           WHEN IDX.type = 2 THEN 'Non-Clustered'

  9.           ELSE 'Unknown' END Index_Type,

  10.         DIS.user_seeks, DIS.user_scans, DIS.user_lookups, DIS.user_updates

  11. FROM sys.dm_db_index_usage_stats AS DIS

  12.             JOIN sys.indexes AS IDX ON DIS.object_id = IDX.object_id AND DIS.index_id = IDX.index_id

  13. WHERE DIS.database_id = @dbid AND objectproperty(DIS.object_id,'IsUserTable') = 1

  14.             --AND DIS.user_updates > 0 AND DIS.user_seeks = 0

                 --AND DIS.user_scans = 0 AND DIS.user_lookups  = 0  --(업데이트는 일어나는 사용되지 않은것, 관리 부담만 있다.)

  15. ORDER BY (DIS.user_updates + DIS.user_seeks + DIS.user_scans + DIS.user_lookups ) asc

 

인덱스에 lock 이 걸려있는 지 확인

  1.  declare @dbid int
  2. select @dbid = db_id()
  3. Select dbid=database_id, objectname=object_name(s.object_id)
    , indexname=i.name, i.index_id       --, partition_number
    , row_lock_count, row_lock_wait_count
    , [block %]=cast (100.0 * row_lock_wait_count / (1 + row_lock_count) as numeric(15,2))
    , row_lock_wait_in_ms
    , [avg row lock waits in ms]=cast (1.0 * row_lock_wait_in_ms / (1 + row_lock_wait_count) as numeric(15,2))
    from sys.dm_db_index_operational_stats (@dbid, NULL, NULL, NULL) s,   sys.indexes i
    where objectproperty(s.object_id,'IsUserTable') = 1
    and i.object_id = s.object_id
    and i.index_id = s.index_id
    order by row_lock_wait_count desc

 

실시간 LOCK 확인 -- 다른 LOOK 쿼리 확인. 구분 이해하기

위에 부분은 실시간으로 처리되는 것이 아니라 누적된 결과를 보는 쿼리 이다. 실시간으로 락이 걸려있는 상태를 확이낳려면 프로시저를 생성하고 처리 결과를 보는것도 좋은 방법이다.

  1. create proc sp_block_info
    as
    set rowcount ON
    select t1.resource_type as [lock type]
                ,db_name(resource_database_id) as [database]
                ,t1.resource_associated_entity_id as [blk object]
                ,t1.request_mode as [lock req]                                          --- lock requested
                ,t1.request_session_id as [waiter sid]                                  --- spid of waiter
                ,t2.wait_duration_ms as [wait time]        
                ,(select text from sys.dm_exec_requests as r                           --- get sql for waiter
                            cross apply sys.dm_exec_sql_text(r.sql_handle)
                            where r.session_id = t1.request_session_id) as waiter_batch
                ,(select substring(qt.text,r.statement_start_offset/2,
                                        (case when r.statement_end_offset = -1
                                        then len(convert(nvarchar(max), qt.text)) * 2
                                        else r.statement_end_offset end - r.statement_start_offset)/2)
                            from sys.dm_exec_requests as r
                            cross apply sys.dm_exec_sql_text(r.sql_handle) as qt
                            where r.session_id = t1.request_session_id) as waiter_stmt    --- statement blocked
                 ,t2.blocking_session_id as [blocker sid]                                 -- spid of blocker
         ,(select text from sys.sysprocesses as p                                         --- get sql for blocker
                            cross apply sys.dm_exec_sql_text(p.sql_handle)
                            where p.spid = t2.blocking_session_id) as blocker_stmt
                from
                sys.dm_tran_locks as t1,
                sys.dm_os_waiting_tasks as t2
    where  t1.lock_owner_address = t2.resource_address
    set rowcount OFF
    go

 


 SEE ALSO : http://blogs.msdn.com/sqlcat/archive/2005/12/12/502735.aspx

'T-SQL' 카테고리의 다른 글

T_SQL::CONSTRAINT조사  (1) 2010.06.03
SQL Server 2005 and 2008 Ranking Functions  (0) 2010.04.05
T-SQL::특정 objects 찾기  (0) 2010.04.04
T-SQL::Attach  (1) 2009.12.01
2010. 4. 5. 00:00

CPU 할당된 Task 상세

 

[code sql] select -- object_name(sql_text.objectid) as 'SP명' ses.session_id , ses.host_name , ses.program_name , ses.client_interface_name , ses.login_name --, (case when sr.statement_end_offset = -1 -- then len(convert(nvarchar(max), sql_text.text)) * 2 -- else sr.statement_end_offset end - sr.statement_start_offset)/2) , * from sys.dm_os_schedulers as ss with (nolock) inner join sys.dm_os_tasks as st with (nolock) on ss.scheduler_id = st.scheduler_id inner join sys.dm_exec_requests as sr with (nolock) on st.request_id = sr.request_id inner join sys.dm_exec_sessions as ses with (nolock) on sr.session_id = ses.session_id cross apply sys.dm_exec_sql_text(sr.sql_handle) as sql_text where ss.scheduler_id = 24 -- cpu 번호 [/code]