'T-SQL'에 해당되는 글 28건
- 2015.04.01 master SP 생성 -> DB별 view 인식 하게 하기
- 2013.03.13 월에 두번째/세번째 요일 구하기.
- 2012.11.02 T-SQL::동적 PIVOT 생성 3
- 2012.11.01 Function to return a range of dats - 날짜 범위 펑션
- 2012.07.26 T-SQL:: 인덱스 압축 예상 Size
- 2012.01.29 TSQL::월별 누적 건수를 구하기.
- 2011.10.05 T-SQL::테이블 확장속성
- 2011.10.03 T-SQL::create assembly
- 2011.08.14 SQLCLR Disk 사이즈 확인
- 2011.08.13 T-SQL:: TCP 포트 정보
- 2011.08.13 T-SQL:: INDEX-중복인덱스 제거. 1
- 2010.09.06 DBCC ShrinkFile
- 2010.07.19 [T-SQL] JOb 수행 시간 완료 계산
- 2010.06.15 Index::Defrag Script v4.0
- 2010.06.15 T_SQL::미 사용 Table
master에 Sp를 생성하고 모든 DB의 객체 view를 인식 할 수 있게 하는 방법.
이렇게 하면 동적 쿼리를 사용하지 않아도 됨.
exec sys.sp_MS_marksystemobject '{procedure name}'
어떤 사용자도 호출 할 수 있게 하려면 public에 권한 부여
grant execute on object to [public]
등록을 해지 하고 싶다면 ?
[SQL 2005 이전 버전 ]
sp_configure 'allow updates', 1
GO
RECONFIGURE WITH OVERRIDE
update sysobjects
set status = 1610612737,
base_schema_ver = 0
where name = '{procedure name}'
sp_configure 'allow updates', 0
GO
RECONFIGURE WITH OVERRIDE
[SQL 2005 이후 버전 ]
- sqlservr -m"SQLCMD" -s<<instance name>>
sqlcmd -A -S.\MSSQL2005
use <<databasename>>
go
update sys.sysschobjs set status = 917504 where name = <<table name>>
go
'T-SQL' 카테고리의 다른 글
| 월에 두번째/세번째 요일 구하기. (0) | 2013.03.13 |
|---|---|
| T-SQL::동적 PIVOT 생성 (3) | 2012.11.02 |
| Function to return a range of dats - 날짜 범위 펑션 (0) | 2012.11.01 |
| T-SQL:: 인덱스 압축 예상 Size (0) | 2012.07.26 |
업무를 하다보면 월 두번째, 세번째 특정 요일을 구해야 하는 경우가 있다.
여기서는 금요일로 하겠다.
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 '셋째 금'
'T-SQL' 카테고리의 다른 글
| master SP 생성 -> DB별 view 인식 하게 하기 (0) | 2015.04.01 |
|---|---|
| T-SQL::동적 PIVOT 생성 (3) | 2012.11.02 |
| Function to return a range of dats - 날짜 범위 펑션 (0) | 2012.11.01 |
| T-SQL:: 인덱스 압축 예상 Size (0) | 2012.07.26 |
원문 : 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;
*/
원하는 결과가 도출.
'T-SQL' 카테고리의 다른 글
| master SP 생성 -> DB별 view 인식 하게 하기 (0) | 2015.04.01 |
|---|---|
| 월에 두번째/세번째 요일 구하기. (0) | 2013.03.13 |
| Function to return a range of dats - 날짜 범위 펑션 (0) | 2012.11.01 |
| T-SQL:: 인덱스 압축 예상 Size (0) | 2012.07.26 |
http://www.mssqltips.com/sqlservertip/2800/sql-server-function-to-return-a-range-of-dates/By: Albert Hetzel 정리
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 |
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 |
출처:http://www.sqlservercentral.com/articles/T-SQL/76940/
- 고객수 월별 누적 건수를 구하거나 할때 이용할 수 있음.
'T-SQL' 카테고리의 다른 글
| Function to return a range of dats - 날짜 범위 펑션 (0) | 2012.11.01 |
|---|---|
| T-SQL:: 인덱스 압축 예상 Size (0) | 2012.07.26 |
| T-SQL::테이블 확장속성 (0) | 2011.10.05 |
| T-SQL::create assembly (0) | 2011.10.03 |
테이블 확장 속성 확인 쿼리
-- 테이블에 대한 속성 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 |
CREATE ASSEMBLY assembly_name [ AUTHORIZATION owner_name ] FROM { <client_assembly_specifier> | <assembly_bits> [ ,...n ] } [ WITH PERMISSION_SET = { SAFE | EXTERNAL_ACCESS | UNSAFE } ] [ ; ] <client_assembly_specifier> :: ='[\\computer_name\]share_name\[path\]manifest_file_name' | '[local_path\]manifest_file_name'<assembly_bits> :: = { varbinary_literal | varbinary_expression }
SQL Server에서 어셈블리에 액세스할 때 이 어셈블리에 부여된 코드 액세스 권한 집합을 지정합니다. 지정하지 않으면 기본적으로 SAFE가 적용됩니다.
SAFE를 사용하는 것이 좋습니다. SAFE는 가장 제한적인 권한 집합입니다. SAFE 권한을 사용하여 어셈블리에서 실행한 코드는 파일, 네트워크, 환경 변수 또는 레지스트리와 같은 외부 시스템 리소스에 액세스할 수 없습니다.
EXTERNAL_ACCESS를 사용하면 어셈블리에서 파일, 네트워크, 환경 변수 및 레지스트리와 같은 특정 외부 시스템 리소스에 액세스할 수 있습니다.
UNSAFE를 사용하면 어셈블리에서 SQL Server 인스턴스의 내부 리소스와 외부 리소스 모두에 제한 없이 액세스할 수 있습니다. UNSAFE 어셈블리 내에서 실행되는 코드는 비관리 코드를 호출할 수 있습니다.
SAFE 권한 설정은 SQL Server 인스턴스의 외부 리소스에 액세스하지 않고 계산 및 데이터 관리 태스크를 수행하는 어셈블리에 권장됩니다.
SQL Server 인스턴스의 외부 리소스에 액세스하는 어셈블리에는 EXTERNAL_ACCESS를 사용하는 것이 좋습니다. EXTERNAL_ACCESS 어셈블리는 SAFE 어셈블리 수준의 안정성 및 확장성 보호를 제공하지만 보안 측면에서는 UNSAFE 어셈블리와 유사합니다. 그 이유는 EXTERNAL_ACCESS 어셈블리의 코드에서 명시적으로 호출자를 가장하지 않는 한 기본적으로 코드가 SQL Server 서비스 계정으로 실행되고 이 계정을 통해 외부 리소스에 액세스하기 때문입니다. 따라서 EXTERNAL_ACCESS 어셈블리를 만드는 사용 권한은 SQL Server 서비스 계정에서 코드를 실행하도록 트러스트된 로그인에만 부여해야 합니다. 가장에 대한 자세한 내용은 CLR 통합 보안를 참조하십시오.
UNSAFE를 지정하면 어셈블리의 코드를 사용하여 SQL Server 프로세스 공간에서 모든 작업을 자유롭게 수행할 수 있게 되므로 SQL Server가 취약해질 가능성이 있습니다. UNSAFE 어셈블리는 SQL Server 또는 공용 언어 런타임 중 하나의 보안 시스템을 손상시킬 수도 있습니다. 따라서 UNSAFE 권한은 가장 높은 수준의 트러스트된 어셈블리에만 부여해야 합니다. sysadmin 고정 서버 역할의 멤버만 UNSAFE 어셈블리를 만들고 변경할 수 있습니다.
'T-SQL' 카테고리의 다른 글
| TSQL::월별 누적 건수를 구하기. (0) | 2012.01.29 |
|---|---|
| T-SQL::테이블 확장속성 (0) | 2011.10.05 |
| SQLCLR Disk 사이즈 확인 (0) | 2011.08.14 |
| T-SQL:: TCP 포트 정보 (0) | 2011.08.13 |
'T-SQL' 카테고리의 다른 글
| T-SQL::테이블 확장속성 (0) | 2011.10.05 |
|---|---|
| T-SQL::create assembly (0) | 2011.10.03 |
| T-SQL:: TCP 포트 정보 (0) | 2011.08.13 |
| T-SQL:: INDEX-중복인덱스 제거. (1) | 2011.08.13 |
--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 |
-- 퍼옴.
/*
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 |
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 |
SQL Server 2005 이상
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
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
--======================================= -- 해당 시간에 실행되는 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 |
SQL SERVER 2008 SP 2 이상
출처: http://sqlfool.com/category/sql-scripts/
인덱스 조각화를 얻고 실행하는 sp 이다.
SQLl 2005의 sp2 이상에서 가능하다.
1. 테이블 생성
인덱스 조각화를 얻고 실행하는 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 |
미사용 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 |
Prev
