Bora.Choi

MCITP 자격증 취득 방법

[순서]

431 (MCTS, 기본 조건 ) -> 443 : Designing a Database Server Infrastructure by Using Microsoft SQL Server 2005

                                      444  : Optimizing and Maintaining a Database Administration Solution by Using Microsoft SQL Server 2005  (MCITP: Database Administrator)

                                 -> 441 : Designing Database Solutions by Using Microsoft SQL Server 2005

                                     442 :Designing and Optimizing Data Access by Using Microsoft SQL Server 2005 (MCITP: Database Developer)

http://blog.naver.com/da10316?Redirect=Log&logNo=70044945798 : 등록 방법

http://www.microsoft.com/learning/mcp/mcts/default.mspx : E러닝 교육 과정 

1. MCTS: SQL Server 2005

MCTS: SQL Server 2005 취득 방법

MCTS 지원자는 SQL Server 2005의 구현 및 유지 관리 능력을 검증하는 한 가지 시험을 통과해야 합니다. 관련 교육 리소스는 다음 표를 참조하십시오. 시험 날짜 및 주요 분야는 시험 준비 가이드를 참조하십시오.

2. MCITP: Database Developer

MCITP(Microsoft Certified IT Professional): Database Developer는 데이터베이스 설계자 및 개발자를 위한 프리미어 인증입니다. 이 자격 증명은 Microsoft SQL Server 2005를 사용하여 안정적인 보안 엔터프라이즈 데이터베이스 솔루션을 설계할 수 있는 능력을 입증합니다.

데이터베이스 개발자는 데이터베이스 저장소 개체 및 관계형 데이터베이스 모델(논리적 및 물리적)을 설계하고 구현합니다. 또한 사용자 정의 함수, 트리거, 저장 프로시저, Transact-SQL 또는 CLR을 사용하여 서버를 프로그래밍하며, SQL 쿼리를 사용하여 데이터를 검색 또는 수정하거나 쿼리를 조정 및 최적화합니다. 데이터베이스 개발자는 일반적으로 중규모 또는 대규모 조직에서 채용합니다.

MCITP: Database Developer 취득 방법

MCITP 지원자는 먼저 MCTS: SQL Server 2005 자격 증명(한 가지 시험) 요건을 갖추어야 합니다. 이 자격 증명을 취득한 후 두 가지 필수 시험을 통과하면 MCITP: Database Developer를 취득할 수 있습니다. 다음 표에서는 전체 시험 목록 및 관련 교육 리소스를 제공합니다. 시험 날짜 및 주요 분야는 시험 시행이 공지될 때 개별 시험 준비 가이드를 참조하십시오.

3. MCITP: Database Administrator

MCITP(Microsoft Certified IT Professional): Database Administrator는 데이터베이스 서버 관리자를 위한 프리미어 인증입니다. 이 인증은 1년 365일, 하루 24시간 동안 엔터프라이즈 비즈니스 솔루션을 유지할 수 있는 능력을 입증합니다.

데이터베이스 관리자는 Microsoft SQL Server를 설치하거나 구성하고, 데이터베이스 또는 다차원 데이터, 사용자 계정, 데이터베이스 가용성, 복구 및 보고를 관리하거나 유지합니다. 또한 보안 또는 서버 자동화를 설계하거나 구현하고 SQL Server 동작을 모니터링하고 문제를 해결합니다. 데이터베이스 관리자는 일반적으로 중규모 또는 대규모 조직에서 채용합니다

MCITP: Database Administrator 취득 방법

MCITP 지원자는 먼저 MCTS: SQL Server 2005 인증(한 가지 시험) 요건을 갖추어야 합니다. 이 인증을 취득한 후 두 가지 필수 시험을 통과하면 MCITP: Database Administrator를 취득할 수 있습니다.

MCDBA(Microsoft Certified Database Administrator)인 경우에는 두 가지 필수 시험 대신 한 가지 업그레이드 시험을 치르고 MCITP: Database Administrator 인증으로 업그레이드할 수 있습니다. 그러나 먼저 MCTS: SQL Server 2005 인증을 보유하고 있어야 합니다.

다음 표는 전체 시험 및 관련 교육 리소스 목록입니다. 시험 날짜 및 주요 분야는 시험 시행이 공지될 때 개별 시험 준비 가이드를 참조하십시오.

이 글은 스프링노트에서 작성되었습니다.

 출처: http://www.nextstep.co.kr/69

  참조:성능 향상을 위한 SQL Server 구성

이 글은 스프링노트에서 작성되었습니다.

 Profiler

•  서버 및 데이터베이스 동작 모니터, 교착상태수, 치명적인 오류, 저자으로시저 및 Transact-SQL  문 추적, 로그인 동작 확인
• SQL 프로필러 데이터를 SQL Server 테이블이나 파일에 캡처해서 분석가능
• 캡처한 이벤트를 단계별로 재성
•  SQL 프로필러는 일괄처리나 트랜잭션 시작과 같은 엔진 프로세스 이벤트를 추적할 수 있다.

템플릿

• 나만의 템플릿을 만들어서 필요한 부분만 보자.
• 이벤트 에서는 보안 감사/세션 정보는 제외해도 무관
• 경고>> Execution Warnigs 는 성능에 나쁜 영향을 줄때 프로시저 내에서 발생하는 모든 에러를 반환한다. 한번씩 잡아 보는것도 괜찮다.
• 기본1.tdf : 기본에 충실한 추적 템플릿

이벤트 범주

• 저장프로시저 범주 : 저장프로시저 생성으로 발생하는 이벤트 컬랙션
  •  이벤트 클래스와 열의 상관 관계, 해당 이벤트를 가져와야 특정 정보가 발생한다.
  • 

• TSQL: 클라이언트로부터 SQL Server 인스턴스로 전달되는 Transact-SQL 문을 실행함으로 생성되는 이벤트 클래스 컬렉션입니다.

• 참고) 둘다 Batch가 아니라 단건을 해야 NestLevel, ObejctID를 가져올 수 있다.  이게 Read80Trace에서 있어야 분석이 가능한것 같음.
• 잠금: 잠금을 얻거나 취소, 해제 시 생성되는 이벤트 클래스의 컬렉션입니다.
  •  잠금은 자주 발생하기 때문에 잠금 이벤트를 캡쳐하면 추적되는 서비스에 중대한 오버해드를 발생 할 수 있다.
  • 

Profiler 바로가기 키

파일/테이블 저장

추적파일을 테이블에 저장할 수 있다. 파일로 저장해서 쿼리로 문제가 되는 부분을 필터링해서 개체를 찾는데 도움이 된다.

• 분석하기

Profiler 스크립트로 실행하기

• 프로필러를 스크립트로 실행할 수 있다. UI로 실행하게 되면 아무래도 부하가 걸린다.
• 필요한 이벤트, 컬럼을 지정해서 가능하다. 쉽게 하기 위해서는 자주 쓰는 템플릿을 불러와서 파일>>추적스크립트를 실행해서 변환을 하면 스크립트가 생성된다.
• 추적은 다음과 같은 순서로 실행하면 된다.  
  • sp_trace_create: Trace를 생성한다.
  • sp_trace_setevent: 해당 Trace에 이벤트와 컬럼 명을 지정한다.
  • sp_trace_setfilter: 필터를 지정해서 원하는 값만 볼수 있다.
• 추적이 실행되고 나서 상태확인/중지 
  • fn_trace_getfilterinfo : TraceId 정보를 확인할 수 있음
  •  sp_trace_status @TraceId, 0 : 추적중지 ->   sp_trace_status @TraceId, 2 : 추적 삭제
•   스크립트 저장프로시저로 만들기 
  • sp_start_trace.sql
  • sp_stop_trace.sql

이 글은 스프링노트에서 작성되었습니다.

 sp_MSforeachtable

Overview

Microsoft provides two undocumented Stored Procedures that allow you to process through all tables in a database, or all databases in a SQL Server instance. The first Stored Procedure (SP), "sp_MSforeachtable," allows you to easily process some code against every table in a single database. The other SP, "sp_MSforeachdb," will execute a T-SQL statement against every database associated with the current SQL Server instance. Let me go through each of these Stored Procedures in a little more detail.

sp_MSforeachtable

The "sp_MSforeachtable" SP comes with SQL Server, but it is not documented in Books Online. This SP can be found in the "master" database and is used to process a single T-SQL command or a number of different T-SQL commands against every table in a given database. To demonstrate how this SP works and how I think it is simpler to use then coding a CURSOR let me go through an example.

Say I want to build a temporary table that will contain a series of records; one for each table in the database and where each row contains the table name and the row count for the given table. To do this you would want to run a command like, "select '<mytable>', count(*) from <mytable>" where "<mytable>" was replaced with every table in your database and insert the results into my temporary table. So now, let's look at how we might do this using a CURSOR and then using the undocumented SP "sp_MSforeachtable".

Here is my code for getting the row counts for each table in a database using a CURSOR:

use pubs
go
set nocount on 
declare @cnt int
declare @table varchar(128)
declare @cmd varchar(500) 
create table #rowcount (tablename varchar(128), rowcnt int)
declare tables cursor for
select table_name from information_schema.tables
   where table_type = 'base table'
open tables
fetch next from tables into @table
while @@fetch_status = 0
begin
  set @cmd = 'select ''' + @table + ''', count(*) from ' + @table
  insert into #rowcount exec (@cmd)
  fetch next from tables into @table
end
CLOSE tables 
DEALLOCATE tables
select top 5 * from #rowcount
    order by tablename
drop table #rowcount

Here is the output of my CURSOR example when the above code in run on my machine:

tablename       rowcnt      
-------------   ----------- 
authors         23
discounts       3
employee        43
jobs            8
pub_info        8

Now here is my code that produces similar results using the undocumented SP "sp_MSforeachtable":

use pubs
go 
create table #rowcount (tablename varchar(128), rowcnt int)
exec sp_MSforeachtable 
   'insert into #rowcount select ''?'', count(*) from ?'
select top 5 * from #rowcount
    order by tablename
drop table #rowcount

Here is the output from the above code when run on my machine:

tablename         rowcnt      
----------------- -----------
[dbo].[authors]   23
[dbo].[discounts  3
[dbo].[employee]  43
[dbo].[jobs]      14
[dbo].[pub_info]  8

As you can see both the CURSOR example and the "sp_MSforeachtable" code produce relatively the same results. Which one do you think is easier to read and code? Let's look a little closer at how to use the undocumented SP "sp_MSforeachtable".

Below is the syntax for calling the sp_MSforeachtable SP:

exec @RETURN_VALUE=sp_MSforeachtable @command1, @replacechar, @command2, 
  @command3, @whereand, @precommand, @postcommand

Where:

• @RETURN_VALUE - is the return value which will be set by "sp_MSforeachtable"
• @command1 - is the first command to be executed by "sp_MSforeachtable" and is defined as a nvarchar(2000)
• @replacechar - is a character in the command string that will be replaced with the table name being processed (default replacechar is a "?")
• @command2 and @command3 are two additional commands that can be run for each table, where @command2 runs after @command1, and @command3 will be run after @command2
• @whereand - this parameter can be used to add additional constraints to help identify the rows in the sysobjects table that will be selected, this parameter is also a nvarchar(2000)
• @precommand - is a nvarchar(2000) parameter that specifies a command to be run prior to processing any table
• @postcommand - is also a nvarchar(2000) field used to identify a command to be run after all commands have been processed against all tables

As you can see, there are quite a few options for the "sp_MSforeachtable" SP. Let's go through a couple of different examples to explore how this SP can be used to process commands against all the tables, or only a select set of tables in a database.

First let's build on our original example above and return row counts for tables that have a name that start with a "p." To do this we are going to use the @whereand parameter. Here is the code for this example:

use pubs
go
create table #rowcount (tablename varchar(128), rowcnt int)
exec sp_MSforeachtable 
 @command1 = 'insert into #rowcount select ''?'', 
              count(*) from ?',
 @whereand = 'and name like ''p%'''
select top 5 * from #rowcount
    order by tablename
drop table #rowcount

On my machine, the above code produced the following output:

tablename          rowcnt      
------------------ ----------- 
[dbo].[pub_info]   8
[dbo].[publishers] 8

By reviewing the code above, you can see I am now using the @command1, and the @whereand parameter. The @whereand parameter above was used to constrain the WHERE clause and only select tables that have a table name that starts with a "p." To do this I specified "and name like ''p%''" for the @whereand parameter. If you needed to have multiple constraints like all tables that start with "p," and all the tables that start with "a," then the @whereand parameter would look like this:

and name like ''p%'' or name like ''a%''

Note, that in the @command1 string in the above example there is a "?". This "?" is the default replacement character for the table name. Now if for some reason you need to use the "?" as part of your command string then you would need to use the @replacechar parameter to specify a different replacement character. Here is another example that builds on the above example and uses the "{" as the replacement character:

create table #rowcount (tablename varchar(128), rowcnt int)
exec sp_MSforeachtable 
 @command1 = 'insert into #rowcount select 
             ''Is the rowcount for table {?'', 
             count(*) from {',
 @replacechar = '{',
 @whereand = 'and name like ''p%'''
select tablename as question, rowcnt from #rowcount
    order by tablename
drop table #rowcount

Here is the output from this command on my machine:

question                                         rowcnt      
------------------------------------------------ ----------- 
Is the rowcount for table [dbo].[pub_info]?      8
Is the rowcount for table [dbo].[publishers]?    8      

There are two more parameters to discuss, @percommand, and @postcommand. Here is an example that uses both of these commands:

exec sp_MSforeachtable 
 @command1 = 'print ''Processing table ?''', 
 @whereand = 'and name like ''p%''',
 @precommand = 'Print ''precommand execution '' ',
 @postcommand = 'Print ''postcommand execution '' '

Here is the output from this command when run on my machine:

precommand execution 
Processing table [dbo].[pub_info]
Processing table [dbo].[publishers]
postcommand execution

As you can see, the "PRINT" T-SQL command associated with the "@precommand" parameter was only executed once, prior to processing through the tables. Whereas, the "@postcommmand" statement was executed after all the tables where processed, and was only executed once. Using the pre and post parameters would be useful if I had some processing I wanted done prior to running a command against eachnly executed once. clause and table, and/or I needed to do some logic after all tables where processed.

sp_MSforeachdb

The SP "sp_MSforeachdb" is found in the "master" database. This SP is used to execute a single T-SQL statement, like "DBCC CHECKDB" or a number of T-SQL statements against every database defined to a SQL Server instance. Here is the syntax for calling this undocumented SP:

exec @RETURN_VALUE = sp_MSforeachdb @command1, @replacechar, 
  @command2, @command3, @precommand, @postcommand

Where:

• @RETURN_VALUE - is the return value which will be set by "sp_MSforeachdb"
• @command1 - is the first command to be executed by "sp_MSforeachdb" and is defined as nvarchar(2000)
• @replacechar - is a character in the command string that will be replaced with the table name being processed (default replacechar is a "?")
• @command2 and @command3 are two additional commands that can be run against each database
• @precommand - is a nvarchar(2000) parameter that specifies a command to be run prior to processing any database
• @postcommand - is also an nvarchar(2000) field used to identify a command to be run after all commands have been processed against all databases.

The parameters for sp_MSforeachdb are very similar to sp_MSforeachtable. Therefore, there is no need to go over some of the parameters since they provide the same functionality as I described in the sp_MSforeachtable section above.

To show you how the "sp_MSforeachdb" SP works let me go through a fairly simple example. This example will perform a database backup, then a "DBCC CHECKDB" against each database. Here is the code to perform this:

declare @cmd1 varchar(500)
declare @cmd2 varchar(500)
declare @cmd3 varchar(500)
set @cmd1 =
   'if ''?'' <> ''tempdb'' print ''*** Processing DB ? ***'''
set @cmd2 = 'if ''?'' <> ''tempdb'' backup database ? to disk=''c:\temp\?.bak'''
set @cmd3 = 'if ''?'' <> ''tempdb'' dbcc checkdb(?)'
exec sp_MSforeachdb @command1=@cmd1, 
                    @command2=@cmd2,
                    @command3=@cmd3

Here you can see that I am really processing three different commands. The first command is just a "PRINT" statement so you can easily review the output by database. Remember how "sp_MSforeachtable" SP had a parameter where you could constrain what tables where processed, there is no parameter that provides this type of functionality in the "sp_MSforeachdb" SP. Since SQL Server does not allow the "tempdb" database to be backed up I needed a way to skip that database. This is why I have used the "IF" statement in each of the commands I processed. The second command (@command2) processes the databases backup, and the last command (@command3) will run a "DBCC CHECKDB" command against all databases except the "tempdb" database.

Comments on Using Undocumented SQL Server Stored Procedures

Some level of testing and care should be taken when using undocumented code from Microsoft. Since these SP's are not documented, it means that Microsoft might change this code with any new release or patch without notifying customers. Because of this, you need to thoroughly test any code you write that uses these undocumented SPs against all new releases of SQL Server. This testing should verify that your code still functions as it did in old releases.

Conclusion

As you can see, these undocumented Stored Procedures are much easier to use than using a CURSOR. Next time you need to iteratively run the same code against all tables, or all databases consider using these undocumented Stored Procedures. Although, remember these Stored Procedures are undocumented and therefore Microsoft may change their functionality at anytime.

이 글은 스프링노트에서 작성되었습니다.

데이터베이스 사이즈 측정

1. DB파일사이즈
2.  Estimating the size of Table

• (Heap 사이즈 혹은 클러스터 인덱스 사이즈) + 넌 클러스터 인덱스 사이즈의 합

• 사이즈계산법

• 사이즈 계산법으로 말고 sp_spaceused 에 datasize와 인덱스 사이즈를 보고 테이블 전체 사이즈를 아는것과 다른가?

  • sp_spaceused 프로시저 확인해 보기 : 확인해 보니 복잡하지 않고 윗 부분과 동일한 것 같음

  1.  --=====================================================
  2.  - 테이블 사이즈  

  3. --======================================================

  4. SELECT  

     1.   @reservedpages = SUM (reserved_page_count), 

     2.   @usedpages = SUM (used_page_count), 

     3.   @pages = SUM ( 

     4.    CASE 

     5.     WHEN (index_id < 2) THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count) 

     6.     ELSE lob_used_page_count + row_overflow_used_page_count 

     7.    END 

     8.    ), 

     9.   @rowCount = SUM ( 

     10.    CASE 

     11.     WHEN (index_id < 2) THEN row_count 

     12.     ELSE 0 

     13.    END 

     14.    ) 

     15.  FROM sys.dm_db_partition_stats 

     16.  WHERE object_id = @id; 

     17.  

     18. SELECT  

     19.   name = OBJECT_NAME (@id), 

     20.   rows = convert (char(11), @rowCount), 

     21.   reserved = LTRIM (STR (@reservedpages * 8, 15, 0) + ' KB'), 

     22.   data = LTRIM (STR (@pages * 8, 15, 0) + ' KB'), 

     23.   index_size = LTRIM (STR ((CASE WHEN @usedpages > @pages THEN (@usedpages - @pages) ELSE 0 END) * 8, 15, 0) + ' KB'), 

     24.   unused = LTRIM (STR ((CASE WHEN @reservedpages > @usedpages THEN (@reservedpages - @usedpages) ELSE 0 END) * 8, 15, 0) + ' KB')

      

이 글은 스프링노트에서 작성되었습니다.

 Collecting database usage information for free space and trending

I find it important in my environment to monitor the following database/database file level metrics:

• Server/Instance Name
• Database Name
• Database File Names (both logical and full physical path)
• File Size (In Megabytes)
• Database Status
• Recovery Mode
• Free Space (In Megabytes and Percent)

To collect this information I need to tap into the either the master.dbo.sysfiles system table in SQL 2000 or master.sys.sysfiles compatibility view in SQL 2005. I also need to make use of a few T-SQL functions at the DBA's disposal. First, let me present the query. Afterwards I'll explain the finer points.

CAST(sysfiles.size/128.0 - CAST(FILEPROPERTY(sysfiles.name, ' + '''' + 'SpaceUsed' + '''' + ' ) AS int)/128.0 AS int) AS FreeSpaceMB

The FreeSpaceMB calculation is simple once you understand what I explained above for the calculations associated with FileSize. To determine free space we need to know how much of the total file space is being consumed. This information is exposed via the FILEPROPERTY() function. FILEPROPERTY() expects the following parameters: file name, and property.

Expect a future tip on the various uses for FILEPROPERTY(), but for now we focus on the SpaceUsed property. This value is also stored in 8Kb pages, so the factor of 1024/8 (or 128) remains constant. Using the same basic rules we discussed above this is what the formula would look like this: Available Space in Mb = (File Size in Mb) - (Space Used in Mb). The formula in the query casts all variables as integer data types and converts the available values from pages to Mb.

I know that one of the major foundations of database normalization is not storing calculated values in a database. However, I like to be able to trend my metadata over time and with the complexity of the formulas I prefer to do all my thinking up-front. That being said, I don't just store the size information in the table and run calculations in my queries after the fact - I do my calculations directly in the query that populates the repository.

이 글은 스프링노트에서 작성되었습니다.

 DBCC 명령어, 문서와 되어 있거나 되어있지 않은것 정리

참조:  http://www.sql-server-performance.com/dbcc_commands.asp

DBCC MEMORYSTATUS

Lists a breakdown of how the SQL Server buffer cache is divided up, including buffer activity. Undocumented command, and one that may be dropped in future versions of SQL Server.

참고 자료 : http://support.microsoft.com/kb/271624

DBCC PROCCACHE

프로시저 캐시에 대한 정보를 테이블 형식으로 표시합니다.

DBCC CACHESTATS

개첵들의 현재 버퍼에 있는 캐쉬 정보, SQL 2000, 7.0 버전에 적용

1. Object Type Hit Ratio Object Count Avg Cost Avg Pages LW Object Count LW Avg Cost LW Avg Stay (ms) LW Avg Use Count
   Proc     0 0 0 0 0 0 0 0
   Prepared 0 0 0 0 0 0 0 0
   Adhoc    0 0 0 0 0 0 0 0
   ReplProc 0 0 0 0 0 0 0 0
   Trigger  0 0 0 0 0 0 0 0
   Cursor   0 0 0 0 0 0 0 0
   Exec Cxt 0 0 0 0 0 0 0 0
   View     0 0 0 0 0 0 0 0
   Default  0 0 0 0 0 0 0 0
   UsrTab   0 0 0 0 0 0 0 0
   SysTab   0 0 0 0 0 0 0 0
   Check    0 0 0 0 0 0 0 0
   Rule     0 0 0 0 0 0 0 0
   Summary  0 0 0 0 0 0 0 0

• Hit Ratio: Displays the percentage of time that this particular object was found in SQL Server's cache. The bigger this number, the better.
• Object Count: Displays the total number of objects of the specified type that are cached.
• Avg. Cost: A value used by SQL Server that measures how long it takes to compile a plan, along with the amount of memory needed by the plan. This value is used by SQL Server to determine if the plan should be cached or not.
• Avg. Pages: Measures the total number of 8K pages used, on average, for cached objects.
• LW Object Count, LW Avg Cost, WL Avg Stay, LW Ave Use: All these columns indicate how many of the specified objects have been removed from the cache by the Lazy Writer. The lower the figure, the better.

DBCC DROPCLEANBUFFERS

정확한 테스트를 할때 유용하게 사용되며 캐쉬의 버퍼를 깨끗하게 지운다. 그러나 dirty page를 처리하는것은 아니다. 단지 버퍼를 지우는 것이다.

버퍼 풀에서 빈 버퍼를 모두 제거합니다.

버퍼 풀에서 빈 버퍼를 삭제하려면 먼저 CHECKPOINT를 사용해 빈 버퍼 캐시를 만드십시오. 이 과정은 현재 데이터베이스에 대한 모든 커밋되지 않은 페이지를 디스크로 기록하고 버퍼를 비웁니다. 버퍼를 비운 후에 DBCC DROPCLEANBUFFERS 명령을 실행해 버퍼 풀에서 모든 버퍼를 제거할 수 있습니다.

 SQL 2000, 7.0 에서 사용

DBCC ERRORLOG

자주 sql 서버를 재 시작하지 않으면 서버 에러 로그 파일이 커지게 된다. 에러로그 파일 하나를 끝내고 다시 새로 시작하게 만듭니다. (순환시킴)

Job으로 작업을 걸어도 된다.  같은 작업을 하는 프로시저가 sp_cycle_errorlog 임

DBCC FLUSHPROCINDB

데이터베이스가 사용하는 특수한 저장 프로시저를 캐쉬에서 삭제한다. 모두 다 삭제하는것은 아니다.  데이터베이스 아이디를 입력해서 특정 데이터베이스만도 가능하다.

상세 내역은 How to Interact with SQL Server's Data and Procedure Cache 참조 하여 읽어본다.

DBCC INDEXDEFRAG

 인덱스 조각 모음.

온라인에서 사용가능하기 때문에 장시간의 테이블 lock을 걸지는 않는다. 해당 작업은 병렬로 처리 가능하지 않는다. 즉 한 테이블에 인덱스를 여러개 처리하라고 명령해도 한 인덱스 작업이 끝난 후에 다른 인덱스 조각모음을 실시한다.

2005에서는 ALTER INDEX 사용은 권고한다. 해당 명령어는 더 이상 사용하지 않는다.

1. DBCC INDEXDEFRAG (Database_Name, Table_Name, Index_Name)

DBCC FREEPROCCACHE

모든 프로시저를 캐쉬에서 삭제한다.  테스트 시작 전에 사용하면 유용하다.

DBCC OPENTRAN

An open transaction can leave locks open, preventing others from accessing the data they need in a database. This command is used to identify the oldest open transaction in a specific database.

1. DBCC OPENTRAN('database_name')

DBCC PAGE

Use this command to look at contents of a data page stored in SQL Server.

1. DBCC PAGE ({dbid|dbname}, pagenum [,print option] [,cache] [,logical])

DBCC PINTABLE & DBCC UNPINTABLE

By default, SQL Server automatically brings into its data cache the pages it needs to work with. These data pages will stay in the data cache until there is no room for them, and assuming they are not needed, these pages will be flushed out of the data cache onto disk. At some point in the future when SQL Server needs these data pages again, it will have to go to disk in order to read them again into the data cache for use. If SQL Server somehow had the ability to keep the data pages in the data cache all the time, then SQL Server's performance would be increased because I/O could be significantly reduced on the server.

The process of "pinning a table" is a way to tell SQL Server that we don't want it to flush out data pages for specific named tables once they are read in in the first place. This in effect keeps these database pages in the data cache all the time, which eliminates the process of SQL Server from having to read the data pages, flush them out, and reread them again when the time arrives. As you can imagine, this can reduce I/O for these pinned tables, boosting SQL Server's performance.

To pin a table, the command DBCC PINTABLE is used. For example, the script below can be run to pin a table in SQL Server:

1. DECLARE @db_id int, @tbl_id int
   USE Northwind
   SET @db_id = DB_ID('Northwind')
   SET @tbl_id = OBJECT_ID('Northwind..categories')
   DBCC PINTABLE (@db_id, @tbl_id)

이 기능은 SQL Server 버전 6.5에서 성능 개선을 위해 도입되었으나 DBCC PINTABLE은 아주 좋지 않은 부작용을 갖고 있습니다. 그 중에는 버퍼 풀 손상 가능성도 포함됩니다. DBCC PINTABLE은 더 이상 필요하지 않으며 추가적인 문제를 예방하기 위해 제거되었습니다. 이 명령의 구문은 계속 작동하지만 서버에 영향을 주지 않습니다.

DBCC REINDEX

Periodically (weekly or monthly) perform a database reorganization on all the indexes on all the tables in your database. This will rebuild the indexes so that the data is no longer fragmented. Fragmented data can cause SQL Server to perform unnecessary data reads, slowing down SQL Server's performance.

If you do a reorganization on a table with a clustered index, any non-clustered indexes on that same table will automatically be rebuilt.

Database reorganizations can be done scheduling SQLMAINT.EXE to run using the SQL Server Agent, or if by running your own custom script via the SQL Server Agent (see below).

Unfortunately, the DBCC DBREINDEX command will not automatically rebuild all of the indexes on all the tables in a database, it can only work on one table at a time. But if you run the following script, you can index all the tables in a database with ease.

1. DBCC DBREINDEX('table_name', fillfactor)

DBCC SHOWCONTIG 

인덱스에 대한 페이지 분할등 정보를 보여줌

 If the scan density is less than 75%, then you may want to reindex the tables in your database reindex 추천

1. DBCC SHOWCONTIG (Table_id, IndexID)

DBCC SHOW_STATISTICS

Used to find out the selectivity of an index. Generally speaking, the higher the selectivity of an index, the greater the likelihood it will be used by the query optimizer. You have to specify both the table name and the index name you want to find the statistics on.

1. DBCC SHOW_STATISTICS (table_name, index_name)

DBCC SQLMGRSTATS

Used to produce three different values that can sometimes be useful when you want to find out how well caching is being performed on ad-hoc and prepared Transact-SQL statements.

• Memory Used (8k Pages): If the amount of memory pages is very large, this may be an indication that some user connection is preparing many Transact-SQL statements, but it not un-preparing them.
• Number CSql Objects: Measures the total number of cached Transact-SQL statements.
• Number False Hits: Sometimes, false hits occur when SQL Server goes to match pre-existing cached Transact-SQL statements. Ideally, this figure should be as low as possible.

DBCC SQLPERF()

This command includes both documented and undocumented options. Let's take a look at all of them and see what they do.

DBCC UPDATEUSAGE

The official use for this command is to report and correct inaccuracies in the sysindexes table, which may result in incorrect space usage reports. Apparently, it can also fix the problem of unreclaimed data pages in SQL Server. You may want to consider running this command periodically to clean up potential problems. This command can take some time to run, and you want to run it during off times because it will negatively affect SQL Server's performance when running. When you run this command, you must specify the name of the database that you want affected.

DBCC UPDATEUSAGE는 테이블 또는 인덱스의 각 파티션에 대해 행, 사용된 페이지, 예약된 페이지, 리프 페이지 및 데이터 페이지의 개수를 수정합니다. 시스템 테이블에 부정확한 데이터가 없으면 DBCC UPDATEUSAGE는 데이터를 반환하지 않습니다. 부정확한 데이터를 검색 및 수정하고 WITH NO_INFOMSGS를 사용하지 않았으면 DBCC UPDATEUSAGE는 시스템 테이블에서 업데이트 중인 행과 열을 반환합니다.

1. DBCC UPDATEUSAGE ('databasename')

 DBCC TRACESTATUS

 추적 플래그 상태를 표시합니다.

이 글은 스프링노트에서 작성되었습니다.

Problem
In a previous tip, "Setting up Performance Monitor to always collect performance statistics" I wrote about how to collect performance monitor data,  but once you have the data then what do you do with it.  In this tip I will show you how I use Excel to analyze the data to help determine where your bottlenecks may be and also an easy way to create quick reports and charts for your SQL Servers.

Solution
Before we get started here are a couple of things you will need for this tip.

• Microsoft Excel 2007 - you also can use Excel 2003 or earlier version but for this tip, I used the latest version.
• Perfmon trace files at least one day in "csv" format. - if you have a file in "blg" format, you can easily convert it by using the "relog" tool. When I get a chance, I will write another tip about the relog tool and other tools that work well with Perfmon.  To collect data using Perfmon you can review this tip Setting up Performance Monitor to always collect performance statistics. 

Step 1: Open the csv file

Once you have collected the performance data you can open the csv file using Excel and you should see data similar to the following.

Step 2: Adjust the format

To allow easy reporting of the data there are a few things that I do to adjust the data.

• Replace server name with an empty string - it helps to make reading the counter names easier. In this case I am replacing "\\AREA51\" the name of my server to nothing. (This is optional, but recommended)

• Cell - A1: Replace "(PDH-CSV 4.0) (Eastern Standard Time)(300)" with "Time" (Optional, but recommended)
• Delete the second row - very often, the first data row has bad data
• Change COLUMN A cell format to "date time"

Final look before we start using it the data.

Step 3: Create PivotTable with PivotChart

• From the Insert menu select PivotTable and then select PivotChart as shown below

• Take the default settings and click "OK"

• After you select the above you will get a screen similar to the following. (to get a bigger workspace area you can close the "PivotChart Filter Pane")

Step 4: Let's generate our first graph

For this example we will look at CPU

• From the "PivotTable Field List" select "Time" and drag it into the "Axis Fields (Categories)" area
• From the "PivotTable Field List" select "Process(_Total)\% Process Time" and drag it into the "Values" area
• At this point you will have a graph similar to the one shown below

• You can now just select the chart and copy and paste it into a report, an email, Word document etc... as shown below

• If you want to change it from processor time to batch requests you can remove "Process(_Total)\% Process Time" and select "SQLServer:SQL Statistics\Batch Requests/sec" and you will get a chart like below

Next Steps

• There are many ways to extend this reporting to make it more useful for both short term and long term needs. In order to do that, it is easier to load the Perfmon data into SQL Server and use the power of SQL Server along with Excel to generate the reports.
• By using the "relog" tool, you can load the Perfmon data directly into SQL Server
• By using the "logman" tool, you can setup Perfmon to store the performance data directly to SQL Server
• To get you started you can download a sample CSV file here with a lot of performance counters
• Here are a few more examples of reports and charts you can create

Sample 1

Sample 2

Sample 3

Related Tips

• Click here for more articles on similar subjects

Forum Posts

• Discuss this tip: http://blogs.mssqltips.com/forums/t/640.aspx
• There are 0 comments for this tip

이 글은 스프링노트에서 작성되었습니다.

1. --===================================

1. -- SP_WHO2와명령어동시확인하기

1. --===================================

1. SELECT

1.        ISNULL(D.text, '') AS SQLStatement,

1.        object_name(D.objectid) AS 'sp_ame',

1.        A.Session_ID SPID,

1.        ISNULL(B.status,A.status) AS [Status],

1.        A.login_name AS [Login],

1.        ISNULL(A.host_name, '  .') AS HostName,

1.        ISNULL(CAST(C.BlkBy AS varchar(10)), '  .') AS BlkBy,

1.        DB_NAME(B.Database_ID) AS DBName,

1.        B.command,

1.        ISNULL(B.cpu_time, A.cpu_time) AS CPUTime,

1.        ISNULL((B.reads + B.writes),(A.reads + A.writes)) AS DiskIO,

1.        A.last_request_start_time AS LastBatch,

1.        ISNULL(A.program_name, '') AS ProgramName,

1.        ISNULL(A.client_interface_name, '') AS ClientInterfaceName

1. FROM sys.dm_exec_sessions A

1.        LEFT OUTER JOIN sys.dm_exec_requests B ON A.session_id = B.session_id

1.        LEFT OUTER JOIN (

1.              SELECT A.request_session_id SPID, B.blocking_session_id BlkBy

1.              FROM sys.dm_tran_locks A

1.                     INNER JOIN sys.dm_os_waiting_tasks B ON A.lock_owner_address = B.resource_address

1.        ) C ON A.Session_ID = C.SPID

1.        OUTER APPLY sys.dm_exec_sql_text(B.sql_handle) D

1. go

이 글은 스프링노트에서 작성되었습니다.

다음과 같이 For루프 컨테이너를 추가합니다.

루프 편집기에서 다른 조건은 설정할 필요 없이 EvalExpression 속성 값에 true를 입력합니다.

이렇게 설정하는 것은 쿼리에서

WHILE (1=1)

BEGIN

 …

END

와 비슷한 형태입니다.

이제, For 루프 컨테이너 안에 필요한 작업들을 설정해서 넣고선, 5초 동안 대기하는 것을 구현해야 합니다.

이전에 올린 글에 FOR 루프 컨테이너를 이용해서 일정 시간 동안 대기하는 것을 구현한 것이 있습니다. 하지만 이 방법은 쓰지 마시기 바랍니다. (CPU가 과도하게 높이 올라갑니다.)

http://www.sqlleader.com/mboard.asp?exec=view&strBoardID=SS2005SSIS&intSeq=523

대신, SQL 명령을 이용하는 것이 가장 바람직할 것 같습니다.

작업들을 구성한 후, 가장 마지막 부분에 SQL 실행 작업을 추가한 후,

DB 연결을 설정하고, 다음과 같은 간단한 명령을 사용합니다.

예) 5초 동안 대기를 해야 할 경우

                  WAITFOR DELAY ’00:00:05’

매 번 루핑 때 마다 마지막 단계에서 5초간 대기하는 무한 루프가 만들어졌습니다.

이 글은 스프링노트에서 작성되었습니다.

 Inserting, Updating or Deleting Data in SQL Server from an Excel Spreadsheet

Problem
I have seen your previous tips (Export data from SQL Server to Excel and Different Options for Importing Data into SQL Server) related to working with Excel and SQL Server data.  The main command used in one of the tips is OPENROWSET.  This has been beneficial for us because in our environment because our business users provide data to us in the form of Excel spreadsheets.  Many of the situations end up translating to INSERT, UPDATE or DELETE code in one or more of our SQL Server databases.  We always upload the data to a table and then begin the process.  Although this process works are you familiar with any other options to directly perform the INSERT, UPDATE or DELETE operations?  Are their any benefits to transitioning our code to another approach with the OPENROWSET command?

Solution
Yes - You are correct the OPENROWSET command can directly support INSERT, UPDATE or DELETE operations as shown in these tips: Export data from SQL Server to Excel and Different Options for Importing Data into SQL Server.  In addition, the OPENROWSET command can also support SELECT statements where a table is joined to the Excel spreadsheet.  Let's work through some examples with the SQL Server 2005 AdventureWorks sample database for each operation with a sample Excel spreadsheet.

Prerequisites

For all of these examples, please do the following:

• Download this Excel (Office 2003) spreadsheet to perform the operations.
• In order to follow the examples, be sure to save the Excel spreadsheet into a directory called C:\MSSQLTips\.
• Review each of the worksheets in the Excel file to see the different data.
• Be sure to close the Excel spreadsheet before running any of the code.
• Review each of the commands below before executing them in your environment.

OPENROWSET Examples

Below are four examples to show some of the flexibility with the OPENROWSET command:

General Analysis

In terms of transitioning your code to the new approach, I have yet to experience any performance issues with 1000's of records, but that seems to be the first concern.  If you test the approach and the overall performance is not an issue, then consider the approach.  It also may be faster to perform a single UPDATE as is the case with the example above versus uploading (INSERT...SELECT) the data and then performing an UPDATE based on the new table.  In addition, by using the commands listed above versus a two step process the overall code may be a little bit cleaner.  A second consideration with SQL Server 2005 is that the Surface Area Configuration setting must be enabled to use this code in either case.  Finally, with either approach be sure to clean up any Excel spreadsheets or temporary tables once you are finished with them.

이 글은 스프링노트에서 작성되었습니다.