Bora.Choi

Output Queue

A service uses Service Broker to send a message to another service. Service

Broker does this by putting the message into an output queue and

then sending it, possibly at a later time, to the queue for the other service.

A number of messages may build up in the output queue, waiting to be

sent, but they will eventually be sent and sent in the order in which they

were put into the queue.

The advantage of this extra layer of queuing is that the service sending

the message never waits for anything. But the extra layer also introduces

extra overhead. Service Broker will skip the output queue when both services

are on the same instance of SQL Server. In this case, it will put the

message directly into the queue from which the receiving service gets its

messages. Figure 15-3 shows how Service Broker efficiently sends a message

from one service to another

Error Case

1. Broker이 되게 DB가 셋팅 되어 있지 않을 때

• is_broker_enabled, is_trustworthy_on 이 setting 되어 있지 않음.
• 메세지를 전달 하면, Broker 비활성화 되었다고 하고, 받는 쪽 큐에는 대화가 전달되지 않는다.
•  큐의 데이터 전달은 sys.transmission_queue 에 insert 된 후 타켓 큐에 전달 된다.

• 해결
• ALTER DATABASE DBA SET TRUSTWORTHY , ENABLE_BROKER ON
  

  /* 전송큐에대한메세지행포함*/

  select conversation_handle, to_service_name, from_service_name,enqueue_time,

      message_sequence_number,message_type_name,

      is_conversation_error, is_end_of_dialog, message_body ,transmission_status

   from sys.transmission_queue
  
  2.Broker 셋팅 되어질 DB가 master의 owner와 다를 경우
  

  An exception occurred while enqueueing a message in the target queue.

  Error: 33009, State: 2.

  The database owner SID recorded in the master database differs from the database owner SID recorded in database 'DB명'.

  You should correct this situation by resetting the owner of database 'DB명' using the ALTER AUTHORIZATION statement.

  
  해결
  sp_changedbowner 로 변경
  
  3. 상대 큐가 서로 비활 성화 되었을 경우
  로직 적으로 문제가 되서 대화의 시도가 기본값 5 이상 실패하면 큐는 잠기게 된다.
  

  One or more messages could not be delivered to the local service targeted by this dialog.

  
  해결
  ALTER QUEUE 큐명 with status = ON;
  

--1. tempdb SizeSELECT name , db.log_reuse_wait_desc , ls.cntr_value  AS size_kb , lu.cntr_value AS used_kb , CAST(lu.cntr_value AS FLOAT) / CAST(ls.cntr_value AS FLOAT)    AS used_percent , CASE WHEN CAST(lu.cntr_value AS FLOAT) / CAST(ls.cntr_value AS FLOAT) > .5 THEN    CASE     /* tempdb special monitoring */     WHEN db.name = 'tempdb'       AND log_reuse_wait_desc NOT IN ('CHECKPOINT', 'NOTHING') THEN 'WARNING'      /* all other databases, monitor foor the 50% fill case */     WHEN db.name <> 'tempdb' THEN 'WARNING'     ELSE 'OK'     END   ELSE 'OK' END   AS log_status FROM sys.databases db JOIN sys.dm_os_performance_counters lu  ON db.name = lu.instance_name JOIN sys.dm_os_performance_counters ls  ON db.name = ls.instance_name WHERE lu.counter_name LIKE  'Log File(s) Used Size (KB)%' AND ls.counter_name LIKE 'Log File(s) Size (KB)%' -- 2. temdb 사용 session,taskSELECT top 5 * FROM sys.dm_db_session_space_usage  ORDER BY (user_objects_alloc_page_count + internal_objects_alloc_page_count) DESCSELECT top 5 * FROM sys.dm_db_task_space_usageORDER BY (user_objects_alloc_page_count + internal_objects_alloc_page_count) DESCSELECT t1.session_id, t1.request_id, t1.task_alloc,  t1.task_dealloc, t2.sql_handle, t2.statement_start_offset,   t2.statement_end_offset, t2.plan_handleFROM (Select session_id, request_id,    SUM(internal_objects_alloc_page_count) AS task_alloc,    SUM (internal_objects_dealloc_page_count) AS task_dealloc   FROM sys.dm_db_task_space_usage   GROUP BY session_id, request_id) AS t1,   sys.dm_exec_requests AS t2WHERE t1.session_id = t2.session_id  AND (t1.request_id = t2.request_id)ORDER BY t1.task_alloc DESC

--3.  종합

CREATE TABLE tempdb_space_usage (  -- This represents the time when the particular row was   -- inserted  dt datetime DEFAULT CURRENT_TIMESTAMP,   -- session id of the sessions that were active at the time  session_id    int DEFAULT null,   -- this represents the source DMV of information. It can be   -- track instance, session or task based allocation information.  scope char(7),      -- instance level unallocated extent pages in tempdb  Instance_unallocated_extent_pages bigint,  -- tempdb pages allocated to verstion store  version_store_pages    bigint,  -- tempdb pages allocated to user objects in the instance  Instance_userobj_alloc_pages bigint,              -- tempdb pages allocated to internal objects in the instance  Instance_internalobj_alloc_pages bigint,  -- tempdb pages allocated in mixed extents in the instance  Instance_mixed_extent_alloc_pages bigint,  -- tempdb pages allocated to user obejcts within this sesssion or task.  Sess_task_userobj_alloc_pages bigint,              -- tempdb user object pages deallocated within this sesssion   -- or task.  Sess_task_userobj_deallocated_pages bigint,  -- tempdb pages allocated to internal objects within this sesssion   -- or task  Sess_task_internalobj_alloc_pages    bigint,  -- tempdb internal object pages deallocated within this sesssion or   -- task  Sess_task_internalobj_deallocated_pages bigint,              -- query text for the active query for the task      query_text    nvarchar(max)    )go-- Create a clustered index on time column when the data was collectedCREATE CLUSTERED INDEX cidx ON tempdb_space_usage (dt)goCREATE PROC sp_sampleTempDbSpaceUsage AS  Instance level tempdb File space usage for all files within   -- tempdb  INSERT tempdb_space_usage (    scope,    Instance_unallocated_extent_pages,    version_store_pages,    Instance_userobj_alloc_pages,    Instance_internalobj_alloc_pages,    Instance_mixed_extent_alloc_pages)  SELECT     'instance',    SUM(unallocated_extent_page_count),    SUM(version_store_reserved_page_count),    SUM(user_object_reserved_page_count),    SUM(internal_object_reserved_page_count),    SUM(mixed_extent_page_count)  FROM sys.dm_db_file_space_usage        -- 2. tempdb space usage per session     --  INSERT tempdb_space_usage (    scope,    session_id,    Sess_task_userobj_alloc_pages,    Sess_task_userobj_deallocated_pages,    Sess_task_internalobj_alloc_pages,    Sess_task_internalobj_deallocated_pages)  SELECT    'session',     session_id,    user_objects_alloc_page_count,    user_objects_dealloc_page_count,    internal_objects_alloc_page_count,    internal_objects_dealloc_page_count  FROM sys.dm_db_session_space_usage    WHERE session_id > 50    -- 3. tempdb space usage per active task    --  INSERT tempdb_space_usage (    scope,    session_id,    Sess_task_userobj_alloc_pages,    Sess_task_userobj_deallocated_pages,    Sess_task_internalobj_alloc_pages,    Sess_task_internalobj_deallocated_pages,    query_text)  SELECT     'task',    R1.session_id,    R1.user_objects_alloc_page_count,    R1.user_objects_dealloc_page_count,    R1.internal_objects_alloc_page_count,    R1.internal_objects_dealloc_page_count,    R3.text  FROM sys.dm_db_task_space_usage AS R1    LEFT OUTER JOIN    sys.dm_exec_requests AS R2    ON R1.session_id = R2.session_id     OUTER APPLY sys.dm_exec_sql_text(R2.sql_handle) AS R3  WHERE R1.session_id > 50go

--5. 결과 쿼리 -- This query reports the maximum allocated space in tempdb over all the data points collectedSELECT  CONVERT (float, (MAX(version_store_pages +      Instance_userobj_alloc_pages +      Instance_internalobj_alloc_pages +      Instance_mixed_extent_alloc_pages)))/ 128.0    AS max_tempdb_allocation_MBFROM     tempdb_space_usage WHERE scope = ‘instance’

--This query reports the average allocated space in tempdb over all the data points collectedSELECT       CONVERT (float, (AVG(version_store_pages +      Instance_userobj_alloc_pages +      Instance_internalobj_alloc_pages +      Instance_mixed_extent_alloc_pages)))/ 128.0    AS avg_tempdb_allocation_MBFROM tempdb_space_usage WHERE scope = ‘instance’

-- 버전 정보 저장 할당양SELECT       MAX(version_store_pages) AS max_version_store_pages_allocated,  MAX(version_store_pages/128.0) AS max_version_store_allocated_space_MBFROM tempdb_space_usage WHERE scope = ‘instance’ 

SELECT  AVG(version_store_pages) AS max_version_store_pages_allocated,  AVG(version_store_pages)/ 128.0 AS max_version_store_allocated_space_MBFROM tempdb_space_usage WHERE scope = ‘instance’

-- top 5 내부 개첵 사용하는 requestSELECT top 5 MAX ((Sess_task_internalobj_alloc_pages) - (Sess_task_internalobj_deallocated_pages))  AS Max_Sess_task_allocated_pages_delta,     query_textFROM tempdb_space_usage WHERE scope = 'task' and session_id > 50GROUP BY query_textORDER BY Max_Sess_task_allocated_pages_delta  DESCSELECT top 5 AVG ((Sess_task_internalobj_alloc_pages) - (Sess_task_internalobj_deallocated_pages))  AS Avg_Sess_task_allocated_pages_delta, query_textFROM tempdb_space_usage WHERE scope = 'task' and session_id > 50GROUP BY query_textORDER BY Avg_Sess_task_allocated_pages_delta  DESC

SQL Server Magazine  2011.01 Cover Store
- Andrew J.Kelly

1. TIP 1: Stop Waiting Around

먼저 Waits and Queues 대해서 알아야 한다. 많이 보이는 Waits Type에 대해 알고 있어야, 튜닝을 할 수 있다.

2. TIP 2:Locate I/O Bottlenecks
 

I/O bottlenecks 에는 Performance의 중요한 key다. page_I/O_latch waits 혹은 log_write 대기가 발생하는지 살펴봐야 한다.
sys.dm_io_virtual_file_stats() 를 통해서 확인이 가능하다.
성능 counter Avg.Disk sec/Read , Avg sec/Write 카운터를 살펴본다.
OLTP 환경에서는 log file 대기는 몇 ms 초여야 한다. 적어도 10ms 이하여야 한다. 허나, 이보다 높을 경우에도 서비스에는 문제되지 않을 수 있다. 항상 자기 서버의 base line을 알아야 한다.

3. Tip 3: Root out Problem Queries

놀랍게도 서비스에 문제되는 쿼리는 8~ 10개 정도이다. 이 쿼리들이 해당 리소스를 80 ~ 90% 사용한다.
그러므로 문제되는 쿼리를 찾아서 튜닝을 해야 한다.
sys.dm_execc_query_stats, sys.dm_exec_cached_plans, sys.dm_exec_sql_text()를 이용
-> 각자에 맞는 쿼리를 만들어야 한다. 저 같은 경우도 주기적으로 쿼리하여 적재하고 모니터링 한다.

SELECT
COALESCE(DB_NAME(t.[dbid]),'Unknown') AS [DB Name],
ecp.objtype AS [Object Type],
t.[text] AS [Adhoc Batch or Object Call],
            SUBSTRING(t.[text], (qs.[statement_start_offset]/2) + 1,
            ((CASE qs.[statement_end_offset]
                        WHEN -1 THEN DATALENGTH(t.[text]) ELSE qs.[statement_end_offset] END
                                   - qs.[statement_start_offset])/2) + 1) AS [Executed Statement]
            , qs.[execution_count] AS [Counts]
            , qs.[total_worker_time] AS [Total Worker Time], (qs.[total_worker_time] /
qs.[execution_count]) AS [Avg Worker Time]
            , qs.[total_physical_reads] AS [Total Physical Reads],
(qs.[total_physical_reads] / qs.[execution_count]) AS [Avg Physical Reads]
            , qs.[total_logical_writes] AS [Total Logical Writes],
(qs.[total_logical_writes] / qs.[execution_count]) AS [Avg Logical Writes]
            , qs.[total_logical_reads] AS [Total Logical Reads],
(qs.[total_logical_reads] / qs.[execution_count]) AS [Avg Logical Reads]
            , qs.[total_clr_time] AS [Total CLR Time], (qs.[total_clr_time] /
qs.[execution_count]) AS [Avg CLR Time]
            , qs.[total_elapsed_time] AS [Total Elapsed Time], (qs.[total_elapsed_time]
/ qs.[execution_count]) AS [Avg Elapsed Time]
            , qs.[last_execution_time] AS [Last Exec Time], qs.[creation_time] AS [Creation Time]
FROM sys.dm_exec_query_stats AS qs
    JOIN sys.dm_exec_cached_plans ecp ON qs.plan_handle = ecp.plan_handle
            CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS t
--    ORDER BY [Total Worker Time] DESC
--    ORDER BY [Total Physical Reads] DESC
--    ORDER BY [Total Logical Writes] DESC
--    ORDER BY [Total Logical Reads] DESC
--    ORDER BY [Total CLR Time] DESC
--    ORDER BY [Total Elapsed Time] DESC
         ORDER BY [Counts] DESC

 4.Tip 4: Plan to Reuse

Plan을 재 사용하는지 확인 한다.

5. Tip 5: Monitor Index Usage

sys.dm_db_index_operational_stats() DMF를 이용해서 인덱스가 잘 사용되고 있는지, 어떤 정보로 사용되는지 확인한다.

6. Tip 6: Separate Datea and Log File
7. Tip 7:Use Separate Staging Database

Temp성 데이터를 exporting or importing 하는데는 많은 비용이 들어간다. 서비스되고 있는 DB는 Full 모드이라면 모든 활동을 logging 한다.
몇몇 DB는 성능 이익을 위해서 simple 모드로 해야 한다. 작은 로깅은 성능을 향상 시키고 작은 load만 있으면 된다.
Staging  Database는 Simple 모드로 하는것이 좋고 백업이 필요 없을 수도 있다.

8. Tip 8: Pay Attention to Log File

항상 Log File의 여유 공간을 확인해야 하고 증가를 주의깊게 봐야 한다. 
VLFs가 증가하면 성능은 저하된다.
http://www.sqlskills.com/BLOGS/KIMBERLY/post/Transaction-Log-VLFs-too-many-or-too-few.aspx

9. Tip 9: Minimize tempdb contention

application이 tempdb를 무겁게 사용하면 내부적으로 tempdb를 할당하기 위해 contention이 발생한다.
tempdb contention은 최소화 해야 한다.
Workign with tempdb in SQL Server 2005 : http://technet.microsoft.com/en-us/library/cc966545.aspx

processor core 하나에 tempdb file 하나를 생성하기를 권장한다. 모든 같은 사이즈여야 한다.그렇지 않으면 이익이 없다.
임시성 테이블을 항상 drop 해줘야 한다.

10. Tip 10 : Change the Max Memory Limit

• 복제 관련 시스템 저장프로시저 내용을 확인해 보고 싶을 경우가 종종 있다.
• 성능을 향상 시키고자 할 때나, 문제를 해결 하고자 할 때
• 그런데 시스템 저장 프로시저 내용을 확인하기는 쉽지 않다.
• 그런데 방법은 있다.

1. DAC 기능을 활성화 한다.  혹은 sqlcmd 명영 유틸을 사용시에는 -A 옵션을 사용한다.

exec sp_configure 'remote admin connections', 1
go
reconfigure
go

 2. 저장 프로시저 내역 확인

select object_definition (object_id('sys.sp_MSrepl_helparticlecolumns') )

3. 서비스에서 작업을 진행 했다면, DAC 연결을 반드시 종료한다.

Superdome VS DL580 G7 간단 비교 자료입니다.

TPC-C 테스트 결과로 보는 성능비교 (참고사이트 http://www.tpc.org)

Tpmc(transactions per minute) 순위에서 볼 수 있듯이 DL580이 Superdome보다 한 등급 위에 있는 것은 사실인 듯 합니다.

So let’s consider memory first; x64 with 32gb ram, we can allocate 2 to 4 gb to the o/s and give the rest to SQL Server, or this is what we believe? Well not really.

• Let's leave 2GB for the OS and other applications: 2GB
• And let's allocate 2GB for the MPA / Thread Stacks / Linked Servers etc: 2GB
• And finally, let's reserve 3GB for all the other applications on the server (AV, backup etc): 3GB
• So now our max server memory setting is: 32-2-2-3 = 25GB
• Remember - this is a baseline calculation

The average Enterprise SQL Server will likely have fibre channel HBAs, be SAN attached, probably have teamed / load balanced HBAs and NICs, will have a suite of hardware monitoring programs, will have an Antivirus, will be running a monitoring program such as Tivoli or MOM and will probably have other agents and services running as part of your standard build. You may well have IIS, reporting services, SSIS, old dts, full text. Do you really believe these can all run within 2gb – including the operating system?

CREATE DATABASE TEST ON (NAME = 'TEST_DATA', FILENAME = 'F:\MSSQL\DATA\TEST _DATA.MDF')
--,(NAME = 'SEARCHV_LOG', FILENAME = 'F:\MSSQL\LOG\SEARCHV_LOG.LDF')
FOR ATTACH_FORCE_REBUILD_LOG
-- 이건 기존 동일한 경로에 로그 파일을 생성합니다. 
그러니까 기존 로그 파일을 이름 변경 해야 합니다. 

 
--1. DB를 offline 시킵니다. , 
롤백 중이라면 offline 할때도 오래 걸립니다. 롤백이 완료 될때 까지 기다기리 때문입니다.  
클러스터 장비로 되어 있다면 강제로 그룹 이동 시킵니다. 

-- 2. log 파일 이름을 변경합니다.

-- 3. DB online 합니다. 

-- 4.  주의 대상으로 DB가 올라오게 됩니다. 
ALTER DATABASE TEST SET EMERGENCY
go

-- 5. 디비가 물리적으로 문제가 있는지 확인 합니다. 해당 작업은 대용량 DB는 오래 걸립니다. 무시하시고 가셔도 됩니다. 
-- 문제가 없으면 결과에 문제 없음으로 나옵니다. 어느 정도 문제가 있는것도 6번 단계에서 복원 될 수도 있습니다
 DBCC CHECKDB (문제DB)

-- 6.  
ALTER DATABASE TEST SET SINGLE_USER  with ROLLBACK IMMEDIATE
go
DBCC CHECKDB (문제DB명, REPAIR_ALLOW_DATA_LOSS)

-- 7.
ALTER DATABASE TEST SET MULTI_USER with  ROLLBACK IMMEDIATE

-- 8 . LOG Rebuild 작업
ALTER DATABASE TEST REBUILD LOG ON 
(NAME=TEST_LOG,FILENAME='F:\MSSQL\LOG\TEST_LOG.LDF'
, SIZE = 30720MB, file_growth = 500MB)

-- 10.ONLINE 상태가 되는지 확인

참고) DBCC REBUILD_LOG 상용해서도 가능합니다. 허나, 이건 단일 로그일때만 가능합니다.
DBCC REBUILD_LOG ('db_name', 'log_file_name')

waitresource = 11:24:3169121  -> 첫 번째 값은 DB 아이디.

DBCC traceon (3604) WITH no_infomsgs         
DBCC PAGE (11,24,3169121) with  no_infomsgs, tableresults

object_id 값과 index 값을 알았으니 어느 테이블에 어느 객체인지 확인 하면 된다.

[waiteresource 정보]
RID. 잠금이 보유 또는 요청된 테이블 내의 단일 행을 식별합니다. RID는 RID: db_id:file_id:page_no:row_no로 표시됩니다. 예를 들면 RID: 6:1:20789:0과 같습니다.

OBJECT. 잠금이 보유 또는 요청된 테이블을 식별합니다. OBJECT는 OBJECT: db_id:object_id로 표시됩니다. 예를 들면 TAB: 6:2009058193과 같습니다.

KEY. 잠금이 보유 또는 요청된 인덱스 내의 키 범위를 식별합니다. KEY는 KEY: db_id:hobt_id (index key hash value)로 표시됩니다. 예를 들면 KEY: 6:72057594057457664 (350007a4d329)와 같습니다.

PAG. 잠금이 보유 또는 요청된 페이지 리소스를 식별합니다. PAG는 PAG: db_id:file_id:page_no로 표시됩니다. 예를 들면 PAG: 6:1:20789와 같습니다.

EXT. 익스텐트 구조를 식별합니다. EXT는 EXT: db_id:file_id:extent_no로 표시됩니다. 예를 들면 EXT: 6:1:9와 같습니다.

DB. 데이터베이스 잠금을 식별합니다. DB는 다음 방법 중 하나로 표시됩니다.

DB: db_id

DB: db_id[BULK-OP-DB]. 이 방법은 백업 데이터베이스에서 수행된 데이터베이스 잠금을 식별합니다.

DB: db_id[BULK-OP-LOG]. 이 방법은 특정 데이터베이스에 대해 백업 로그에서 수행된 잠금을 식별합니다.

APP. 응용 프로그램 리소스에서 수행된 잠금을 식별합니다. APP는 APP: lock_resource로 표시됩니다. 예를 들면 APP: Formf370f478과 같습니다.

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

SELECT @TotalSizeMB AS CurrentSizeMB, GETDATE() AS StartTime

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

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

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

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

• SQL 2005 & 2008

• SQL 2000용

SQL:BatchStarting and RPC:Starting

Required: StartTime, TextData

Recommended: DBID

SQL:BatchCompleted and RPC:Completed

Required: StartTime, EndTime, TextData

Recommended: Reads, Writes, CPU, Duration, DBID

Audit:Logout

Required: StartTime, EndTime

Recommended: TextData, Reads, Writes, CPU, Duration

Audit:Login and ExistingConnection

Required: StartTime, EndTime

Recommended: TextData

SP:StmtStarting

Required: StartTime, TextData, ObjectID, NestLevel

Recommended: DBID

SP:StmtCompleted

Required: StartTime, EndTime, TextData, ObjectID, NestLevel

Recommended: DBID, Reads, Writes, CPU, Duration, IntegerData