'SQL Server 2005'에 해당되는 글 36건
- 2011.08.13 T-SQL:: TCP 포트 정보
- 2011.08.13 T-SQL:: INDEX-중복인덱스 제거. 1
- 2010.06.15 Index::Defrag Script v4.0
- 2010.06.07 dm_os_performance_counters , Server/Process Information
- 2010.06.04 WMI Providers_2
- 2010.06.04 WMI Providers 1
- 2010.06.04 SID및ID
- 2010.06.04 Service Broker::소개
- 2010.06.03 T-SQL::DB_Restore_move_to
- 2010.04.13 복제::지연된 명령어 확인
- 2010.04.05 SQL Server 2005 and 2008 Ranking Functions
- 2010.04.05 DMV::Index
- 2010.04.05 CPU 할당된 Task 상세
- 2010.04.04 admin::여러 TCP 포트에서 수신하도록 데이터베이스 엔진 구성
- 2010.03.28 Admin::SQL Server wait types
--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 |
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 |
2010. 6. 7. 23:51
dm_os_performance_counters , Server/Process Information
2010. 6. 7. 23:51 in Peformance Tuning
SQL SERVER 2005 이상
출처: http://www.sqlservercentral.com/scripts/Monitoring/68069/
This script displays data from the dynamic management view called: sys.dm_os_performance and presents it in a report. I recommend that you look closer to this view for all your statistical needs.
If you find other information that you want to include, just modify this script to reflect the additional information.
Hope you find this script useful.
-- SQL Doctor -- -- Welcome to the World of Databases -- -- By Rudy Panigas -- -- Code Name: usp_dba_SQL_Server_Stats -- Date of Creation: Aug 27, 2009 - version 1.0 -- Updated: Sept 3, 2009 - version 2.1 -- Comments: Code will produce a report with important information about your SQL server (2005/2008) and its processes -- USE [master] -- Doesn't have to be in master database. GO IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_dba_SQL_Server_Stats]') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].[usp_dba_SQL_Server_Stats] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROC [dbo].[usp_dba_SQL_Server_Stats] AS PRINT ' SQL 2005/2008 Server Statistics' PRINT ' -------------------------------' PRINT '' PRINT ' Displaying statistics of SQL server ' PRINT ' ' PRINT '...Buffer Statistics' PRINT ' ' SELECT [counter_name] AS 'Description' , [cntr_value] AS 'Current Value' from sys.dm_os_performance_counters WHERE ([object_name] LIKE '%Buffer Manager%'and [counter_name] = 'Buffer cache hit ratio') OR ([object_name] LIKE '%Buffer Manager%'and [counter_name] = 'Buffer cache hit ratio base') OR ([object_name] LIKE '%Buffer Manager%'and [counter_name] = 'Page lookups/sec') OR ([object_name] LIKE '%Buffer Manager%'and [counter_name] = 'Free pages') OR ([object_name] LIKE '%Buffer Manager%'and [counter_name] = 'Total pages') OR ([object_name] LIKE '%Buffer Manager%'and [counter_name] = 'Readahead pages/sec') OR ([object_name] LIKE '%Buffer Manager%'and [counter_name] = 'Page reads/sec') OR ([object_name] LIKE '%Buffer Manager%'and [counter_name] = 'Page writes/sec') OR ([object_name] LIKE '%Buffer Manager%'and [counter_name] = 'Page life expectancy') PRINT '...Memory Statistics' PRINT ' ' SELECT [counter_name] AS 'Description' , [cntr_value] AS 'Current Value' from sys.dm_os_performance_counters WHERE ([object_name] LIKE '%memory manager%'and [counter_name] = 'Connection Memory (KB)') OR ([object_name] LIKE '%memory manager%'and [counter_name] = 'Granted Workspace Memory (KB)') OR ([object_name] LIKE '%memory manager%'and [counter_name] = 'Lock Memory (KB)') OR ([object_name] LIKE '%memory manager%'and [counter_name] = 'Lock Blocks Allocated') OR ([object_name] LIKE '%memory manager%'and [counter_name] = 'Lock Owner Blocks Allocated') OR ([object_name] LIKE '%memory manager%'and [counter_name] = 'Lock Blocks') OR ([object_name] LIKE '%memory manager%'and [counter_name] = 'Lock Owner Blocks') OR ([object_name] LIKE '%memory manager%'and [counter_name] = 'Maximum Workspace Memory (KB)') OR ([object_name] LIKE '%memory manager%'and [counter_name] = 'Memory Grants Outstanding') OR ([object_name] LIKE '%memory manager%'and [counter_name] = 'Memory Grants Pending') OR ([object_name] LIKE '%memory manager%'and [counter_name] = 'Optimizer Memory (KB)') OR ([object_name] LIKE '%memory manager%'and [counter_name] = 'SQL Cache Memory (KB)') OR ([object_name] LIKE '%memory manager%'and [counter_name] = 'Target Server Memory (KB)') OR ([object_name] LIKE '%memory manager%'and [counter_name] = 'Total Server Memory (KB)') PRINT '...General Statistics' PRINT ' ' SELECT [counter_name] AS 'Description' , [cntr_value] AS 'Current Value' from sys.dm_os_performance_counters WHERE ([object_name] LIKE '%General Statistics%'and [counter_name] = 'Active Temp Tables') OR ([object_name] LIKE '%General Statistics%'and [counter_name] = 'Temp Tables Creation Rate') OR ([object_name] LIKE '%General Statistics%'and [counter_name] = 'Logins/sec') OR ([object_name] LIKE '%General Statistics%'and [counter_name] = 'Logouts/sec') OR ([object_name] LIKE '%General Statistics%'and [counter_name] = 'User Connections') OR ([object_name] LIKE '%General Statistics%'and [counter_name] = 'Logical Connections') OR ([object_name] LIKE '%General Statistics%'and [counter_name] = 'Transactions') OR ([object_name] LIKE '%General Statistics%'and [counter_name] = 'Mars Deadlocks') OR ([object_name] LIKE '%General Statistics%'and [counter_name] = 'HTTP Authenticated Requests') OR ([object_name] LIKE '%General Statistics%'and [counter_name] = 'Processes blocked') OR ([object_name] LIKE '%General Statistics%'and [counter_name] = 'Temp Tables For Destruction') OR ([object_name] LIKE '%General Statistics%'and [counter_name] = 'Event Notifications Delayed Drop') OR ([object_name] LIKE '%General Statistics%'and [counter_name] = 'Trace Event Notification Queue') OR ([object_name] LIKE '%General Statistics%'and [counter_name] = 'SQL Trace IO Provider Lock Waits') PRINT '...Locks Statistics' PRINT ' ' SELECT [counter_name] AS 'Description' , [cntr_value] AS 'Current Value' from sys.dm_os_performance_counters WHERE ([object_name] LIKE '%Locks%'and [counter_name] = 'Lock Timeouts/sec') OR ([object_name] LIKE '%Locks%'and [counter_name] = 'Number of Deadlocks/sec') OR ([object_name] LIKE '%Locks%'and [counter_name] = 'Lock Waits/sec') OR ([object_name] LIKE '%Locks%'and [counter_name] = 'Lock Wait Time (ms)') OR ([object_name] LIKE '%Locks%'and [counter_name] = 'Average Wait Time (ms)') OR ([object_name] LIKE '%Locks%'and [counter_name] = 'Average Wait Time Base') OR ([object_name] LIKE '%Locks%'and [counter_name] = 'Lock Timeouts (timeout > 0)/sec') PRINT '...Total for Locks Statistics' PRINT ' ' SELECT [counter_name] AS 'Description' , [cntr_value] AS 'Current Value' from sys.dm_os_performance_counters WHERE ([object_name] LIKE '%Locks%'and [counter_name] = 'Lock Requests/sec' and [instance_name] ='_Total') OR ([object_name] LIKE '%Locks%'and [counter_name] = 'Lock Timeouts/sec' and [instance_name] ='_Total') OR ([object_name] LIKE '%Locks%'and [counter_name] = 'Number of Deadlocks/sec' and [instance_name] ='_Total') OR ([object_name] LIKE '%Locks%'and [counter_name] = 'Lock Waits/sec' and [instance_name] ='_Total') OR ([object_name] LIKE '%Locks%'and [counter_name] = 'Lock Wait Time (ms)' and [instance_name] ='_Total') OR ([object_name] LIKE '%Locks%'and [counter_name] = 'Average Wait Time (ms)' and [instance_name] ='_Total') OR ([object_name] LIKE '%Locks%'and [counter_name] = 'Average Wait Time Base' and [instance_name] ='_Total') OR ([object_name] LIKE '%Locks%'and [counter_name] = 'Lock Timeouts (timeout > 0)/sec' and [instance_name] ='_Total') PRINT '...Temp DB Statistics' PRINT ' ' SELECT [counter_name] AS 'Description' , [cntr_value] AS 'Current Value' from sys.dm_os_performance_counters WHERE ([object_name] LIKE '%Databases%'and [counter_name] = 'Data File(s) Size (KB)' and [instance_name] ='tempdb') OR ([object_name] LIKE '%Databases%'and [counter_name] = 'Log File(s) Size (KB)' and [instance_name] ='tempdb') OR ([object_name] LIKE '%Databases%'and [counter_name] = 'Log File(s) Used Size (KB)' and [instance_name] ='tempdb') OR ([object_name] LIKE '%Databases%'and [counter_name] = 'Percent Log Used' and [instance_name] ='tempdb') OR ([object_name] LIKE '%Databases%'and [counter_name] = 'Active Transactions' and [instance_name] ='tempdb') OR ([object_name] LIKE '%Databases%'and [counter_name] = 'Transactions/sec' and [instance_name] ='tempdb') OR ([object_name] LIKE '%Databases%'and [counter_name] = 'Repl. Pending Xacts' and [instance_name] ='tempdb') OR ([object_name] LIKE '%Databases%'and [counter_name] = 'Repl. Trans. Rate' and [instance_name] ='tempdb') OR ([object_name] LIKE '%Databases%'and [counter_name] = 'Log Cache Reads/sec' and [instance_name] ='tempdb') OR ([object_name] LIKE '%Databases%'and [counter_name] = 'Log Cache Hit Ratio' and [instance_name] ='tempdb') OR ([object_name] LIKE '%Databases%'and [counter_name] = 'Log Cache Hit Ratio Base' and [instance_name] ='tempdb') OR ([object_name] LIKE '%Databases%'and [counter_name] = 'Bulk Copy Rows/sec' and [instance_name] ='tempdb') OR ([object_name] LIKE '%Databases%'and [counter_name] = 'Bulk Copy Throughput/sec' and [instance_name] ='tempdb') OR ([object_name] LIKE '%Databases%'and [counter_name] = 'Backup/Restore Throughput/sec' and [instance_name] ='tempdb') OR ([object_name] LIKE '%Databases%'and [counter_name] = 'DBCC Logical Scan Bytes/sec' and [instance_name] ='tempdb') OR ([object_name] LIKE '%Databases%'and [counter_name] = 'Shrink Data Movement Bytes/sec' and [instance_name] ='tempdb') OR ([object_name] LIKE '%Databases%'and [counter_name] = 'Log Flushes/sec' and [instance_name] ='tempdb') OR ([object_name] LIKE '%Databases%'and [counter_name] = 'Log Bytes Flushed/sec' and [instance_name] ='tempdb') OR ([object_name] LIKE '%Databases%'and [counter_name] = 'Log Flush Waits/sec' and [instance_name] ='tempdb') OR ([object_name] LIKE '%Databases%'and [counter_name] = 'Log Flush Wait Time' and [instance_name] ='tempdb') OR ([object_name] LIKE '%Databases%'and [counter_name] = 'Log Truncations' and [instance_name] ='tempdb') OR ([object_name] LIKE '%Databases%'and [counter_name] = 'Log Growths' and [instance_name] ='tempdb') OR ([object_name] LIKE '%Databases%'and [counter_name] = 'Log Shrinks' and [instance_name] ='tempdb') PRINT '...Totals of Database Statistics' PRINT ' ' SELECT [counter_name] AS 'Description' , [cntr_value] AS 'Current Value' from sys.dm_os_performance_counters WHERE ([object_name] LIKE '%Databases%'and [counter_name] = 'Data File(s) Size (KB)' and [instance_name] ='_Total') OR ([object_name] LIKE '%Databases%'and [counter_name] = 'Log File(s) Size (KB)' and [instance_name] ='_Total') OR ([object_name] LIKE '%Databases%'and [counter_name] = 'Log File(s) Used Size (KB)' and [instance_name] ='_Total') OR ([object_name] LIKE '%Databases%'and [counter_name] = 'Percent Log Used' and [instance_name] ='_Total') OR ([object_name] LIKE '%Databases%'and [counter_name] = 'Active Transactions' and [instance_name] ='_Total') OR ([object_name] LIKE '%Databases%'and [counter_name] = 'Transactions/sec' and [instance_name] ='_Total') OR ([object_name] LIKE '%Databases%'and [counter_name] = 'Repl. Pending Xacts' and [instance_name] ='_Total') OR ([object_name] LIKE '%Databases%'and [counter_name] = 'Repl. Trans. Rate' and [instance_name] ='_Total') OR ([object_name] LIKE '%Databases%'and [counter_name] = 'Log Cache Reads/sec' and [instance_name] ='_Total') OR ([object_name] LIKE '%Databases%'and [counter_name] = 'Log Cache Hit Ratio' and [instance_name] ='_Total') OR ([object_name] LIKE '%Databases%'and [counter_name] = 'Log Cache Hit Ratio Base' and [instance_name] ='_Total') OR ([object_name] LIKE '%Databases%'and [counter_name] = 'Bulk Copy Rows/sec' and [instance_name] ='_Total') OR ([object_name] LIKE '%Databases%'and [counter_name] = 'Bulk Copy Throughput/sec' and [instance_name] ='_Total') OR ([object_name] LIKE '%Databases%'and [counter_name] = 'Backup/Restore Throughput/sec' and [instance_name] ='_Total') OR ([object_name] LIKE '%Databases%'and [counter_name] = 'DBCC Logical Scan Bytes/sec' and [instance_name] ='_Total') OR ([object_name] LIKE '%Databases%'and [counter_name] = 'Shrink Data Movement Bytes/sec' and [instance_name] ='_Total') OR ([object_name] LIKE '%Databases%'and [counter_name] = 'Log Flushes/sec' and [instance_name] ='_Total') OR ([object_name] LIKE '%Databases%'and [counter_name] = 'Log Bytes Flushed/sec' and [instance_name] ='_Total') OR ([object_name] LIKE '%Databases%'and [counter_name] = 'Log Flush Waits/sec' and [instance_name] ='_Total') OR ([object_name] LIKE '%Databases%'and [counter_name] = 'Log Flush Wait Time' and [instance_name] ='_Total') OR ([object_name] LIKE '%Databases%'and [counter_name] = 'Log Truncations' and [instance_name] ='_Total') OR ([object_name] LIKE '%Databases%'and [counter_name] = 'Log Growths' and [instance_name] ='_Total') OR ([object_name] LIKE '%Databases%'and [counter_name] = 'Log Shrinks' and [instance_name] ='_Total') PRINT '...SQL Error Statistics' PRINT ' ' SELECT [instance_name] AS 'Description' , [cntr_value] AS 'Current Value' from sys.dm_os_performance_counters WHERE ([object_name] LIKE '%SQL Errors%'and [counter_name] = 'Errors/sec') OR ([object_name] LIKE '%SQL Errors%'and [counter_name] = 'Errors/sec') OR ([object_name] LIKE '%SQL Errors%'and [counter_name] = 'Errors/sec') OR ([object_name] LIKE '%SQL Errors%'and [counter_name] = 'Errors/sec') OR ([object_name] LIKE '%SQL Errors%'and [counter_name] = 'Errors/sec') PRINT '...SQL Statistics' PRINT ' ' SELECT [counter_name] AS 'Description' , [cntr_value] AS 'Current Value' from sys.dm_os_performance_counters WHERE ([object_name] LIKE '%SQL Statistics%'and [counter_name] = 'SQL Compilations/sec') OR ([object_name] LIKE '%SQL Statistics%'and [counter_name] = 'SQL Re-Compilations/sec') PRINT '...Plan Cache Statistics' PRINT ' ' SELECT [counter_name] AS 'Description' , [cntr_value] AS 'Current Value' from sys.dm_os_performance_counters WHERE ([object_name] LIKE '%Plan Cache%'and [counter_name] = 'Cache Hit Ratio' and [instance_name] ='_Total') OR ([object_name] LIKE '%Plan Cache%'and [counter_name] = 'Cache Hit Ratio Base' and [instance_name] ='_Total') OR ([object_name] LIKE '%Plan Cache%'and [counter_name] = 'Cache Pages' and [instance_name] ='_Total') OR ([object_name] LIKE '%Plan Cache%'and [counter_name] = 'Cache Object Counts' and [instance_name] ='_Total') OR ([object_name] LIKE '%Plan Cache%'and [counter_name] = 'Cache Objects in use' and [instance_name] ='_Total') PRINT '...Transactions Statistics' PRINT ' ' SELECT [counter_name] AS 'Description' , [cntr_value] AS 'Current Value' from sys.dm_os_performance_counters WHERE ([object_name] LIKE '%Transactions%') PRINT '...Wait Statistics. Average execution time (ms)' PRINT ' ' SELECT [counter_name] AS 'Description' , [cntr_value] AS 'Current Value' from sys.dm_os_performance_counters WHERE ([object_name] LIKE '%Wait Statistics%'and [instance_name] = 'Average execution time (ms)') -- PRINT '...Wait Statistics. Waits in progress' PRINT ' ' SELECT [counter_name] AS 'Description' , [cntr_value] AS 'Current Value' from sys.dm_os_performance_counters WHERE ([object_name] LIKE '%Wait Statistics%'and [instance_name] = 'Waits in progress') -- PRINT '...SQL Execution Statistics. Average execution time (ms)' PRINT ' ' SELECT [counter_name] AS 'Description' , [cntr_value] AS 'Current Value' from sys.dm_os_performance_counters WHERE ([object_name] LIKE '%Exec Statistics%'and [counter_name] = 'Extended Procedures') OR ([object_name] LIKE '%Exec Statistics%'and [counter_name] = 'DTC calls') OR ([object_name] LIKE '%Exec Statistics%'and [counter_name] = 'OLEDB calls') OR ([object_name] LIKE '%Exec Statistics%'and [counter_name] = 'Distributed Query') -- PRINT '...SQL Execution Statistics. Execution in progress' PRINT ' ' SELECT [counter_name] AS 'Description' , [cntr_value] AS 'Current Value' from sys.dm_os_performance_counters WHERE ([object_name] LIKE '%Exec Statistics' and [instance_name] ='Execs in progress') GO
'Peformance Tuning' 카테고리의 다른 글
| Storage Top 10 Best Practices (0) | 2011.01.16 |
|---|---|
| RML ReadTrace 분석에 필요한 이벤트 (0) | 2010.08.24 |
| 저장 프로시저 및 함수의 마법 깨트리기 #1 (0) | 2010.06.04 |
| 성능::ReadTrace 사용법 (0) | 2010.06.03 |
SQL SERVER 2005 이상
Microsoft Windows PowerShell and SQL Server 2005 WMI Providers - Part 2
이 글은 스프링노트에서 작성되었습니다.
'Common Admin' 카테고리의 다른 글
| DB파일사이즈 (1) | 2010.06.04 |
|---|---|
| DBCC 명령어 (0) | 2010.06.04 |
| WMI Providers (1) | 2010.06.04 |
| SID및ID (0) | 2010.06.04 |
SQL SERVER 2005 이상
Microsoft Windows PowerShell and SQL Server 2005 WMI Providers - Part 1
이 글은 스프링노트에서 작성되었습니다.
'Common Admin' 카테고리의 다른 글
| DBCC 명령어 (0) | 2010.06.04 |
|---|---|
| WMI Providers_2 (0) | 2010.06.04 |
| SID및ID (0) | 2010.06.04 |
| Admin::JOB 단계 설정시 option (0) | 2010.04.29 |
서버 수준 ID 번호
SQL Server 로그인을 만들면 ID와 SID가 할당됩니다. 두 번호는 sys.server_principals 카탈로그 뷰에 principal_id와 SID로 표시됩니다. ID(principal_id)는 로그인을 서버 내의 보안 개체로 식별하며 로그인이 생성될 때 SQL Server에서 할당합니다. 로그인을 삭제하면 해당 ID 번호가 재활용됩니다. SID는 로그인의 보안 컨텍스트를 식별하며 서버 인스턴스 내에서 고유합니다. SID의 원본은 로그인 생성 방법에 따라 달라집니다. Windows 사용자나 그룹에서 로그인을 만들면 원본 보안 주체의 Windows SID가 지정됩니다. Windows SID는 도메인 내에서 고유합니다. 인증서나 비대칭 키에서 SQL Server 로그인을 만들면 공개 키의 SHA-1 해시에서 파생된 SID가 할당됩니다. 로그인을 암호가 필요한 레거시 스타일 SQL Server 로그인으로 만들면 서버에서 SID를 생성합니다.
데이터베이스 수준 ID 번호
데이터베이스 사용자를 만들면 ID가 할당됩니다. ID를 서버 보안 주체에 매핑하면 SID(보안 ID)도 할당됩니다. 두 번호는 sys.database_principals 카탈로그 뷰에 principal_id와 SID로 표시됩니다. ID는 사용자를 데이터베이스 내의 보안 개체로 식별합니다. 데이터베이스 사용자를 삭제하면 해당 ID가 재활용됩니다. 데이터베이스 사용자에게 할당된 SID는 데이터베이스 내에서 고유합니다. SID의 원본은 데이터베이스 사용자 생성 방법에 따라 달라집니다. SQL Server 로그인에서 사용자를 만들면 해당 로그인의 SID가 지정됩니다. 인증서나 비대칭 키에서 사용자를 만들면 공개 키의 SHA-1 해시에서 SID가 파생됩니다. 로그인 없이 사용자를 만들면 해당 SID는 NULL이 됩니다.
최대 데이터베이스 사용자 수
최대 데이터베이스 사용자 수는 사용자 ID 필드의 크기에 의해 결정됩니다. 사용자 ID의 값은 0 또는 양의 정수여야 합니다. SQL Server 2000에서 사용자 ID는 16비트로 구성된 smallint로 저장되며 16비트 중 하나는 부호입니다. 이런 이유로 SQL Server 2000에서 최대 사용자 ID 수는 215 = 32,768입니다. SQL Server 2005에서는 사용자 ID가 32비트로 구성된 int로 저장되며 32비트 중 하나는 부호입니다. 이러한 추가 비트를 사용하여 231 = 2,147,483,648개의 ID 번호를 할당할 수 있습니다.
데이터베이스 사용자 ID는 다음 표에 설명된 것처럼 미리 할당된 범위로 나뉩니다.
| SQL Server 2000 ID | SQL Server 2005 ID | 할당 대상 |
|---|---|---|
|
0 |
0 |
public |
|
1 |
1 |
dbo |
|
2 |
2 |
guest |
|
3 |
3 |
INFORMATION_SCHEMA |
|
4 |
4 |
SYSTEM_FUNCTION_SCHEMA |
|
5 - 16383 |
5 - 16383 |
사용자, 별칭, 응용 프로그램 역할 |
|
16384 |
16384 |
db_owner |
|
16385 |
16385 |
db_accessadmin |
|
16386 |
16386 |
db_securityadmin |
|
16387 |
16387 |
db_ddladmin |
|
16389 |
16389 |
db_backupoperator |
|
16390 |
16390 |
db_datareader |
|
16391 |
16391 |
db_datawriter |
|
16392 |
16392 |
db_denydatareader |
|
16393 |
16393 |
db_denydatawriter |
|
16394 - 16399 |
16394 - 16399 |
예약되어 있습니다. |
|
16400 - 32767 |
역할 | |
|
16400 - 2,147,483,647 |
사용자, 역할, 응용 프로그램 역할, 별칭 |
이 글은 스프링노트에서 작성되었습니다.
'Common Admin' 카테고리의 다른 글
| WMI Providers_2 (0) | 2010.06.04 |
|---|---|
| WMI Providers (1) | 2010.06.04 |
| Admin::JOB 단계 설정시 option (0) | 2010.04.29 |
| admin::여러 TCP 포트에서 수신하도록 데이터베이스 엔진 구성 (0) | 2010.04.04 |
소개
Service Broker는 SQL Server에 큐 기능과 안정적인 메시징 기능을 제공합니다. Service Broker는 단일 SQL Server 인스턴스를 사용하는 응용 프로그램과 여러 인스턴스에 작업을 분산하는 응용 프로그램에서 모두 사용됩니다.
Service Broker는 단일 SQL Server 인스턴스 내에서 강력한 비동기 프로그래밍 모델을 제공합니다. 데이터베이스 응용 프로그램은 일반적으로 비동기 프로그래밍을 사용하여 대화형 응답 시간을 줄이고 전반적인 응용 프로그램 처리량을 늘립니다.
또한 Service Broker는 SQL Server 인스턴스 간에 안정적인 메시징을 제공합니다. Service Broker는 개발자가 서비스라고 하는, 독립적인 자체 포함 구성 요소에서 응용 프로그램을 작성하는 데 도움을 제공합니다. 이러한 서비스에서 제공하는 기능을 필요로 하는 응용 프로그램은 메시지를 사용하여 해당 서비스와 상호 작용합니다. Service Broker는 TCP/IP를 사용하여 인스턴스 간에 메시지를 교환합니다. Service Broker에는 네트워크에서의 무단 액세스를 차단하고 네트워크를 통해 전송되는 메시지를 암호화하는 기능이 있습니다.
역활
-
대화
- Service Broker는 메시지를 보내고 받는 기본적인 기능을 기반으로 설계되었습니다. 각 메시지는 안정적이고 지속적인 통신 채널인 대화의 일부분을 구성합니다. 각 메시지와 대화에는 개발자의 안정적인 응용 프로그램 작성을 돕기 위해 Service Broker가 강제 적용하는 특정 유형이 있습니다.
- 새 Transact-SQL 문을 통해 응용 프로그램에서 안정적으로 메시지를 보내고 받을 수 있습니다. 응용 프로그램에서는 관련된 작업 집합에 대한 이름인 서비스에 메시지를 보내고 내부 테이블 보기인 큐로부터 메시지를 받습니다.
- 같은 작업에 대한 메시지는 같은 대화에 속합니다. 각 대화 내에서 Service Broker는 응용 프로그램이 모든 메시지를 전송된 순서대로 각각 한 번씩만 받도록 합니다.
- 같은 대화를 대화그룹으로 연결할 수도 있습니다.
-
메세지 순서 지정 및 조정
- 큐가 통합되어 데이터베이스 유지 관리 운영에 Service Broker도 포함된다. 관리자가 일상적으로 수행해야 할 유지 관리 작업이 없습니다.
-
트랜잭션 비동기 프로그래밍
- 메세징은 트랜잭션 방식이므로 트랜잭션이 롤백 될 경우 트랜잭션의 모든 Service Broker 작업이 롤백된다.
- 비동기 프로그래밍을 통해 개발자는 큐를 사용하는 응용 프로그램을 쉽게 작성할 수 있습니다. 많은 데이터베이스 응용 프로그램에는 리소스가 허용될 때 수행할 작업에 대한 큐 역할을 하는 테이블이 포함되어 있습니다. 큐를 사용하면 데이터베이스가 사용 가능한 리소스를 효율적으로 사용하면서 대화형 사용자에 대한 응답성을 유지할 수 있습니다. Service Broker는 큐를 데이터베이스 엔진의 중요한 부분으로 제공합니다.
-
느슨하게 연결된 응용 프로그램에 대한 지원
- 느슨하게 연결된 응용 프로그램은 서로 독립적으로 메시지를 주고받는 여러 프로그램으로부터 구성됩니다.
- 이러한 응용 프로그램은 교환되는 메시지에 대해 동일한 정의를 포함하고 서비스 간의 상호 작용에 대해 전체적으로 동일한 구조를 정의해야 합니다.
- 응용 프로그램은 동시에 실행되거나 같은 SQL Server 인스턴스 내에서 실행되거나 구현 정보를 공유해야 할 필요는 없습니다.
- 응용프로그램은 대화내 다른 참가자의 물리적 위치 또는 구현을 알 필요가 없습니다.
Service Broker 구성요소
-
대화 구성요소
- 대화그룹, 대화 메세지, 런타임 구조를 구성한다.
- 대화 아키텍처 참조
-
서비스 정의 구성요소
- 응용프로그램에서 사용하는 대화의 기본 구조를 지정하는 디자인 타임 구성요소
- 메세지 유형, 대화 흐름, 데이터베이스 저장소 등 정의
- 서비스 아키텍처 참조
-
네트워크 및 구성 요소
- SQL Server 인스턴스 외부의 메세지 교화을 위한 인프라 정의
이 글은 스프링노트에서 작성되었습니다.
'Service Broker' 카테고리의 다른 글
| Sesrvice Broker::보안 (0) | 2010.06.04 |
|---|---|
| Service Broker::장점 (1) | 2010.06.04 |
| Service Broker::아키텍처 (0) | 2010.06.04 |
- --=============================================================
- -- 장비 이동하거나 DB 복원시 파일을 다른 디렉토리로 보관해야할때
- -- 파일들이 많은 DB의 경우 일일이 변경하기 어려움이 있어서 원본 기준으로 복원스크립트 생성하고
- -- 디렉토리 경로와 백업 파일 경로만 넣어주면 되게끔 생성 한다.
- --==============================================================
-
-
SET NOCOUNT ON
-
DECLARE @sql nvarchar(max)
-
DECLARE @sql_move nvarchar(3000)
-
DECLARE @move nvarchar(200)
-
DECLARE @backup_type char(1)
-
DECLARE @name sysname
-
SET @backup_type = 'L' --N
-
SET @sql_move = ''
-
DECLARE cur_restore CURSOR FOR
-
SELECT name FROM sys.databases WHERE database_id > 4 and NAME != 'LiteSpeedLocal' and state = 0
-
ORDER BY name
-
-
OPEN cur_restore
-
FETCH NEXT FROM cur_restore
-
INTO @name
-
-
WHILE @@FETCH_STATUS = 0
-
BEGIN
-
SET @sql = NULL
SET @sql_move = '' -
IF @backup_type = 'L'
-
BEGIN
-
SET @sql = 'exec master.dbo.xp_restore_database' + char(13)
-
+ '@database = ''' + @name + '''' + char(13)
-
+ ',@filename = ''''' + char(13)
-
+ ',@filenumber = 1' + char(13)
-
+ ',@with = ''REPLACE''' + char(13)
-
+ ',@with = ''NORECOVERY''' + char(13)
-
-
-
DECLARE cur_move CURSOR FOR
-
SELECT ',@with = ''MOVE ''''' +
-
name + ''''' TO N''''' +
-
filename + ''''''' '
-
FROM sys.sysaltfiles WHERE dbid = db_id(@name)
-
ORDER BY fileid
-
OPEN cur_move
-
FETCH NEXT FROM cur_move
-
INTO @move
-
-
WHILE @@FETCH_STATUS = 0
-
BEGIN
-
SET @sql_move = @sql_move + @move + char(13)
-
-
FETCH NEXT FROM cur_move
-
INTO @move
-
END
-
CLOSE cur_move
-
DEALLOCATE cur_move
-
-
-
END
-
ELSE IF @backup_type = 'N'
-
BEGIN
-
SET @sql = 'RESTORE DATABASE ' + @name + char(13)
-
+ 'FROM DISK =''''' + char(13)
-
+ 'WITH NORECOVERY' + char(13)
-
-
DECLARE cur_move CURSOR FOR
-
SELECT ',MOVE ''' + name + ''' TO ''' + filename + ''''
-
FROM sys.sysaltfiles WHERE dbid = db_id(@name)
-
ORDER BY fileid
-
OPEN cur_move
-
FETCH NEXT FROM cur_move
-
INTO @move
-
-
WHILE @@FETCH_STATUS = 0
-
BEGIN
-
SET @sql_move = @sql_move + @move + char(13)
-
-
FETCH NEXT FROM cur_move
-
INTO @move
-
END
-
CLOSE cur_move
-
DEALLOCATE cur_move
-
END
-
-
SET @sql = @sql + @sql_move + char(13)
-
-
print @sql
-
-
FETCH NEXT FROM cur_restore
-
INTO @name
-
END
-
-
CLOSE cur_restore
-
DEALLOCATE cur_restore
이 글은 스프링노트에서 작성되었습니다.
'T-SQL' 카테고리의 다른 글
| 데이터베이스 사이즈 (0) | 2010.06.04 |
|---|---|
| T-SQL::Removing Duplication Data (1) | 2010.06.03 |
| T-SQL::Convert hex value to String 32bit (0) | 2010.06.03 |
| T_SQL::CONSTRAINT조사 (1) | 2010.06.03 |
복제에 문제가 생길 때 보실 수 있는 내역입니다. 어제 이 방법으로 확인했습니다.
평소 문제가 없을때는 MSrepl_commands 조회시 top 을 주시는 것이 좋습니다.
-- 에러가나는agent에대한마지막으로실행된명령어정보확인
select agent_id, max(xact_seqno) as 'MaxTranNo', max(time) as 'Lasted'
from Msdistribution_history with (nolock)
where xact_seqno > 0x0 and agent_id = 29
group by agent_id
order by agent_id
1. 해당 게시명은 아는데 agent를 모를 경우 쉽게 알 수 있는 법은 배포 장비의 job중에 해당 게시의 배포 agent 명을 보면 뒤에 붙어 있는 숫자가 Agent 아이디 입니다.
아니면 select id, name , publication from MSdistribution_agents 하면 게시에 대한 agent를 확인 할 수 있습니다.
-- 마지막실행된이후명령어갯수확인
select top 10 sys.fn_sqlvarbasetostr(min(xact_seqno))
from MSrepl_commands as c with (nolock)
inner join MSarticles as a with (nolock) on c.article_id =a.article_id
inner join MSpublications as p with (nolock) on a.publication_id =
p.publication_id
where c.publisher_database_id = 2
and p.publication = '게시명'
and c.xact_seqno > 0x004BDF9200501314000C
--order by c.xact_seqno
2. 1번에서 실행된 agent에서 가장 마지막으로 실행된 xact_seqno 를 아셨으면 그 번호 이상인 명령이 무엇인지 확인하는 것입니다.
이때 MSrepl_commands 테이블만으로 조해 해보면 모든 게시자 DB에 여러 개의 게시가 있을 경우 다른 게시에 대한 것도 보이니 아티클로 아셔서 보시거나 윗 처럼 조인해서 보시면 됩니다.
-- 최종으로어떤명령어인지확인
exec sp_browsereplcmds
@xact_seqno_start = '0x004BDF9200501DD30018'
,@xact_seqno_end = '0x004BDF92005085BD0014'
,@publisher_database_id = 2
-- ,@agent_id = 29
3. 최종적으로 비슷한 xact_seqno가 동일한게 여러 개 보이면 범위주어서 찾아보시면 됩니다.
인자로 @agent_id 값을 넣으면 조회가 오래 걸리니 안 넣으시게 좋을 것 같습니다.
추가 ) 지연된 명령어 수 확인
exec sp_replmonitorsubscriptionpendingcmds
@publisher ='Enter publisher server name',
@publisher_db = 'Enter publisher database name',
@publication ='Enter name of publication',
@subscriber ='Enter subscriber server name',
@subscriber_db ='Enter subscriber database name',
@subscription_type ='0' --0 for push and 1 for pull
'Replication' 카테고리의 다른 글
| 복제::머지복제에러 (0) | 2010.06.04 |
|---|---|
| 복제::스냅숏 DB를 사용한 게시 초기화 (0) | 2010.06.04 |
| 복제::잘못 삭제 했을 경우 (0) | 2010.03.15 |
| 복제::트랜잭션 복제 배포agent 실행중 상태 모니터링 (0) | 2009.12.28 |
'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 |
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 : 사용자 쿼리별 업데이트 수
사용하지 않는 테이블 & 인덱스
-
-- unused tables & indexes.
-
DECLARE @dbid INT
-
SET @dbid = DB_ID('AdventureWorks')
-
-
SELECT OBJECT_NAME(IDX.object_id) as object_name,
-
IDX.name AS index_name,
-
CASE WHEN IDX.type = 1 THEN 'Clustered'
-
WHEN IDX.type = 2 THEN 'Non-Clustered'
-
ELSE 'Unknown' END Index_Type
-
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
-
WHERE OBJ.type IN ('U', 'V') AND DIS.object_id IS NULL
-
ORDER BY OBJECT_NAME(IDX.object_id), IDX.name
드물게 사용하는 인덱스
-
DECLARE @dbid INT
-
SET @dbid = DB_ID('AdventureWorks')
-
-
--- rarely used indexes appear first
-
SELECT OBJECT_NAME(DIS.object_id) as object_name,
-
IDX.name AS index_name, IDX.index_id,
-
CASE WHEN IDX.type = 1 THEN 'Clustered'
-
WHEN IDX.type = 2 THEN 'Non-Clustered'
-
ELSE 'Unknown' END Index_Type,
-
DIS.user_seeks, DIS.user_scans, DIS.user_lookups, DIS.user_updates
-
FROM sys.dm_db_index_usage_stats AS DIS
-
JOIN sys.indexes AS IDX ON DIS.object_id = IDX.object_id AND DIS.index_id = IDX.index_id
-
WHERE DIS.database_id = @dbid AND objectproperty(DIS.object_id,'IsUserTable') = 1
-
--AND DIS.user_updates > 0 AND DIS.user_seeks = 0
--AND DIS.user_scans = 0 AND DIS.user_lookups = 0 --(업데이트는 일어나는 사용되지 않은것, 관리 부담만 있다.)
-
ORDER BY (DIS.user_updates + DIS.user_seeks + DIS.user_scans + DIS.user_lookups ) asc
인덱스에 lock 이 걸려있는 지 확인
- declare @dbid int
- select @dbid = db_id()
- 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 쿼리 확인. 구분 이해하기
위에 부분은 실시간으로 처리되는 것이 아니라 누적된 결과를 보는 쿼리 이다. 실시간으로 락이 걸려있는 상태를 확이낳려면 프로시저를 생성하고 처리 결과를 보는것도 좋은 방법이다.
- 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 |
[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]
여러 TCP 포트에서 수신하도록 데이터베이스 엔진 구성
http://msdn.microsoft.com/ko-kr/library/ms189310.aspx
NUMA 노드에 TCP/IP 포트 매핑
http://msdn.microsoft.com/ko-kr/library/ms345346.aspx
소프트 NUMA를 사용하도록 SQL Server 구성
'Common Admin' 카테고리의 다른 글
| SID및ID (0) | 2010.06.04 |
|---|---|
| Admin::JOB 단계 설정시 option (0) | 2010.04.29 |
| Suspect 상태 해결 - SQL 2000 (1) | 2010.04.04 |
| Admin::DBA Check List (0) | 2010.04.04 |
'Common Admin' 카테고리의 다른 글
| Admin::VLDB성능관리#2 (0) | 2010.04.04 |
|---|---|
| Admin::VLDB성능관리#1 (0) | 2010.04.04 |
| Admin::DB 주의대상 일때. (0) | 2010.03.06 |
| SQL 2008-변경 내용 추적 (0) | 2009.12.28 |
Prev
