豢豌: http://purumae.tistory.com/173

SSB 豕 覃伎 覦朱骸 給.
讌蠍覿 危エ覲願給.

1. ろ襴讌 蟲 - tempdb 覿襴

SQL Server ろ襴讌 蟲煙 蠍磯蓋 豺 一危壱手骸 碁 襦蠏 殊 覓朱Μ朱 覿襴 ろ 覲朱エ 豺る 蟆.

蠏碁磯, SSB QUEUE tempdb 譟伎.
磯殊 譯手 覦 覃讌螳 襷朱 襷 襦 tempdb 一危 手骸 碁 襦蠏 殊 I/O螳 讀螳蟆 .

讀, SSB襯 覃讌襯 ″ ろ企朱.. tempdb 殊 覲 ろ 覲朱エ 麹 譯殊伎 .


2. 螳 螳 覃讌襯 ″.

BOL 旧 讌 ろ語 覃讌 1螳襯 覲企朱襷 1覯 BEGIN DIALOG CONVERSATION覓語 給. 覦蠖襷覃 襷 覃讌襯 覯襷 ″螻 襯 襷豺 覦伎給.

讌襷, 覃讌襯 覲企朱襷 襷る 襯 BEGIN / END 覦覲給覩襦 焔レ 譬襴 給.

覯 襯 覃 狩 Dialog Handle 襦 Dialog Pool 蟲 蟆 譬給.


3. Dialog Handle 螳襯 燕 .

Dialog Handle 伎狩讌襷.. 蠏碁り 1螳 碁れ 牛 覈 覃讌襯 ″る 蠏瑚 ろ 覓語襯 手鍵 給.

企 sys.sysdesend (Dialog Endpoint Send) ろ 企 蟆渚 覦蠍 覓語碁一. 企朱 ろ 企 伎 れ ろ語 る襦 蟆給.

伎.. Thread 1螳 碁れ 燕 蟆 麹.
(sp_who 煙朱 SQL Server 郁屋 Application Server 語 襯 谿瑚 給.)


4. TARGET 覯 Receive Queue 覯 螳 覃讌襯 蟶朱碁.

2 蟠螻襦 覯 螳 覃讌襯 ″る, TARGET RECEIVE 覓語 TOP (1) TOP (n) 給.

QUEUE 1螳 蟶朱 豌襴 蟆螻 n螳 蟶朱 豌襴 蟆 焔 谿企 蟲褐 る 螳 蟆 螳給.

螳語朱 n 覯 旧 1,000 ~ 10,000 螳 麹 蟆 螳給.


5. 蟲

INITIATOR

- SQL Server Service螳 覃 朱 n螳 Dialog Handle 燕螻, 燕 碁 螳 DialogPool企 ロ.
- 150TrickEnableFlag 螳 螻 覓伎螻 code reading蠍 覦.
- 150 Trick れ ろ語 る0 .
USE InitDB;
GO
 
-- Dialog Pool れ 企 
CREATE TABLE dbo.DialogPoolConfig (
        _150TrickEnableFlag bit NOT NULL,
        DialogCount int NOT NULL
);
GO
 

-- Dialog Pool 企 
CREATE TABLE dbo.DialogPool (
        DialogPoolID int IDENTITY(0, 1) NOT NULL,
        DialogHandle uniqueidentifier NOT NULL,
 
        CONSTRAINT PK_DialogPool PRIMARY KEY (DialogPoolID)
);
GO

/**
version : 1
author : 蟾
e-mail : purumae@eyedentitygames.com
created date : 2011-06-21
description :  碁  れ螳 谿語^ 碁れ 覩碁Μ 燕.
**/
 
CREATE PROCEDURE dbo.P_StartUp_CreateDialogs
AS
SET NOCOUNT ON;
SET XACT_ABORT ON;
 
DECLARE @i int = 1
        , @intDialogCount int = 0
        , @bit150TrickEnableFlag bit
        , @uidDialogHandle uniqueidentifier;
 
SELECT @intDialogCount = DialogCount * (CASE _150TrickEnableFlag WHEN 0 THEN 1 ELSE 150 END)
        , @bit150TrickEnableFlag = _150TrickEnableFlag
FROM dbo.DialogPoolConfig;

TRUNCATE TABLE dbo.DialogPool;
 
WHILE @i <= @intDialogCount
BEGIN
        BEGIN DIALOG CONVERSATION @uidDialogHandle
               FROM SERVICE InitDBService
               TO SERVICE N'TargetDBService'
               ON CONTRACT LoggingContract
               WITH ENCRYPTION = OFF;
 
        IF @bit150TrickEnableFlag = 0 OR (@bit150TrickEnableFlag = 1 AND (@i % 150) = 0)
               INSERT dbo.DialogPool (DialogHandle)
               VALUES (@uidDialogHandle);
 
        SET @i += 1;
END
GO


USE master;
GO
 
CREATE PROCEDURE dbo.P_StartUp_CreateDialogs
AS
SET NOCOUNT ON;
SET XACT_ABORT ON;
 
EXEC InitDB.dbo.P_StartUp_CreateDialogs;
 
RETURN 0;
GO

EXEC sp_configure 'show advanced option', '1';
RECONFIGURE;
GO

EXEC sp_configure 'scan for startup procs', '1';
RECONFIGURE;
GO

EXEC sp_procoption N'P_StartUp_CreateDialogs', 'STARTUP', 'on'
GO




- Dialog Pool ル Dialog Handle 牛 覃讌襯 ″ SP襯 燕.
- 1螳 Thread螳 1螳 碁れ 襦 "@@SPID % 燕 碁 " 螳 .
- 蟆 覯 螳 蠍磯 SQL Server 郁屋 襷埼 蟇一 覯渚蟆 襷ろ.
, IIS 螳 豌伎朱 connection pool 蟲る 蟆 覦碁一焔讌 給.
(蠏碁 reasonable 覿覦磯 螳 蟆朱 螳.)
USE InitDB;
GO
 
/**
version : 1
author : 蟾
e-mail : purumae@eyedentitygames.com
created date : 2011-06-21
description :  碁れ 詞給.
**/
 
CREATE PROCEDURE dbo.P_GetDialog
        @uidDialogHandle uniqueidentifier OUTPUT
AS
SET NOCOUNT ON;
SET XACT_ABORT ON;
 
SELECT @uidDialogHandle = DialogHandle
FROM dbo.DialogPool WITH (INDEX(PK_DialogPool), FORCESEEK)
WHERE DialogPoolID = @@SPID % (SELECT TOP (1) DialogCount FROM dbo.DialogPoolConfig WITH (NOLOCK))
OPTION (KEEPFIXED PLAN);
 
RETURN 0;
GO

/**
version : 1
author : 蟾
e-mail : purumae@eyedentitygames.com
created date : 2011-06-21
description : Dialog Pool 轟 Dialog螳  譬襭 譟伎讌  蟆曙,  Dialog襦 蟲豌危.
**/
 
CREATE PROCEDURE dbo.P_RenewDialog
AS
SET NOCOUNT ON;
SET XACT_ABORT ON;
 
DECLARE @i int = 1
        , @j int
        , @uidDialogHandle uniqueidentifier;
 
SET @j = 
       CASE (SELECT _150TrickEnableFlag FROM dbo.DialogPoolConfig)
               WHEN 0 THEN 1
               ELSE 150
        END;
 
WHILE @i <= @j
BEGIN
        BEGIN DIALOG CONVERSATION @uidDialogHandle
               FROM SERVICE InitDBService
               TO SERVICE N'TargetDBService'
               ON CONTRACT LoggingContract
               WITH ENCRYPTION = OFF;
 
        SET @i += 1;
END
 
UPDATE DP
SET DialogHandle = @uidDialogHandle
FROM dbo.DialogPool DP WITH (INDEX(PK_DialogPool), FORCESEEK)
WHERE DialogPoolID = @@SPID % (SELECT TOP (1) DialogCount FROM dbo.DialogPoolConfig WITH (NOLOCK))
OPTION (KEEPFIXED PLAN);
 
RETURN 0;
GO

/**
version : 1
author : 蟾
e-mail : purumae@eyedentitygames.com
created date : 2011-06-28
description : GameLog 覃語襯 ″.
**/
 
CREATE PROCEDURE dbo.P_SendGameLogMessage
        @xmlMessage xml --// ″ 覃語
AS
SET NOCOUNT ON;
SET XACT_ABORT ON;
 
DECLARE @intReturnValue int
        , @uidDialogHandle uniqueidentifier;
 
BEGIN TRY
        BEGIN TRANSACTION;
 
        /**_# Dialog Pool襦覿  碁れ 詞給.*/
        EXEC dbo.P_GetDialog @uidDialogHandle = @uidDialogHandle OUTPUT;
 
        /**_# 覃讌襯 ″.*/
        SEND ON CONVERSATION @uidDialogHandle
               MESSAGE TYPE MsgToTargetDB (@xmlMessage);
 
        COMMIT TRANSACTION;
END TRY
BEGIN CATCH
        /**_#  碁れ 譟伎讌 朱, Dialog Pool 碁れ 螳煙螻 蠏語.*/
        IF ERROR_NUMBER() = 8426
        BEGIN
               IF @@TRANCOUNT > 0
                       ROLLBACK TRANSACTION;
 
               EXEC dbo.P_RenewDialog;
               EXEC dbo.P_SendGameLogMessage @xmlMessage = @xmlMessage;
 
               RETURN 0;
        END
 
        /**_# 8426語  Rethrow.*/
        GOTO ErrorHandler;
END CATCH;
 
RETURN 0;
 
ErrorHandler:
IF XACT_STATE() <> 0
        ROLLBACK TRANSACTION;
 
DECLARE @nvcErrorMessage nvarchar(4000) = N'Error %d, Level %d, State %d, Procedure %s, Line %d, Message: ' + ERROR_MESSAGE()
        , @intErrorNumber int = ERROR_NUMBER()
        , @intErrorSeverity int = ERROR_SEVERITY()
        , @intErrorState int = ERROR_STATE()
        , @intErrorLine int = ERROR_LINE()
        , @nvcErrorProcedure nvarchar(128) = ERROR_PROCEDURE();
 
RAISERROR (@nvcErrorMessage, @intErrorSeverity, 1, @intErrorNumber, @intErrorSeverity, @intErrorState, @nvcErrorProcedure, @intErrorLine);
 
RETURN @intReturnValue;
GO
}}


TARGET

- TARGET 覯 QUEUE 燕 襦碁ゼ  螳 .
- RECEIVE TOP (1000)  , 覯 1,000螳 覃讌襯 蟶朱 豌襴襦 .
{{{
ALTER PROCEDURE dbo.P_TargetDBQueueActivate
AS
 
DECLARE @tblReceive table (
    seq int IDENTITY(1, 1) NOT NULL PRIMARY KEY,
    message_type_name sysname NOT NULL,
    message_body xml NULL,
    [conversation_handle] uniqueidentifier NOT NULL
);
 
DECLARE @i int = 1
    , @xmlMessage xml
    , @nvcMessageType sysname
    , @uidDialogHandle uniqueidentifier
    , @intDoc int;
 
WHILE (1 = 1)
BEGIN
    BEGIN TRANSACTION;
 
    WAITFOR (
        RECEIVE TOP (1000) message_type_name, message_body, [conversation_handle]
        FROM dbo.TargetDBQueue
        INTO @tblReceive
    ), TIMEOUT 3000;
 
    IF @@ROWCOUNT = 0
    BEGIN
        ROLLBACK TRANSACTION;
        BREAK;
    END
 
    WHILE 1 = 1
    BEGIN
        SELECT @xmlMessage = message_body
            , @nvcMessageType = message_type_name
            , @uidDialogHandle = [conversation_handle]
            , @i += 1
        FROM @tblReceive
        WHERE seq = @i;
 
        IF @@ROWCOUNT = 0
            BREAK;
 
        IF (@nvcMessageType = N'http://schemas.microsoft.com/SQL/ServiceBroker/Error') OR (@nvcMessageType = N'MsgEndOfStream')
            END CONVERSATION @uidDialogHandle;
 
        ELSE IF (@nvcMessageType = N'MsgToTargetDB')
        BEGIN
            EXEC sp_xml_preparedocument @intDoc OUTPUT, @xmlMessage;
 
            INSERT dbo.GameLogs (LogDate, LogCode, LogString)
            SELECT LogDate, LogCode, LogString
            FROM OPENXML (@intDoc, N'/root/GameLog')
            WITH (
                LogDate datetime N'@LogDate',
                LogCode tinyint N'@LogCode',
                LogString nvarchar(100) N'@LogString'
            );
 
            EXEC sp_xml_removedocument @intDoc;
        END
    END
 
    DELETE @tblReceive;
 
    COMMIT TRANSACTION;
END
GO 




- INITIATOR 覯 Query襯 ろ SQL Server Service襯 .
USE InitDB;
GO
 
DELETE dbo.DialogPoolConfig;
 
INSERT dbo.DialogPoolConfig (_150TrickEnableFlag, DialogCount)
VALUES (0, 10);
GO


- 伎 Dialog Pool 10螳 碁れ ル 蟆 Query襦 誤 給.
USE InitDB;
GO
 
SELECT * FROM dbo.DialogPool;
GO


- 襦 るジ 螳 語 Query襯 ろ 覃讌襯 ″. (語 3,000螳)
USE InitDB;
GO
 
DECLARE @xmlMessage xml;
DECLARE @i int = 1;
 
WHILE @i <= 3000
BEGIN
        SET @xmlMessage = N'<root><GameLog LogDate="' + CONVERT(nvarchar(19), GETDATE(), 121) + N'" LogCode="1" LogString="abc"/></root>';
 
        EXEC dbo.P_SendGameLogMessage @xmlMessage;
 
        SET @i += 1;
END
GO



- TARGET 覯 覃讌螳 螻 讌 誤.
USE TargetDB; 
GO

SELECT * FROM dbo.GameLogs;
GO


CLEAN
-  Query襯  SSB 螳豌企ゼ 蟇壱.

INITIATOR
{{{
USE master;
GO
 
DROP ENDPOINT InitDBEndpoint;
DROP CERTIFICATE InitDBAuthCertPriv;
DROP MASTER KEY;
DROP LOGIN TargetDBProxy;
DROP CERTIFICATE TargetDBAuthCertPub;
DROP USER TargetDBProxy;
EXEC sp_procoption N'P_StartUp_CreateDialogs', 'STARTUP', 'off'
DROP PROCEDURE dbo.P_StartUp_CreateDialogs;
GO



TARGET
USE master;
GO
 
DROP DATABASE TargetDB;
DROP ENDPOINT TargetDBEndpoint;
DROP CERTIFICATE TargetDBAuthCertPriv;
DROP MASTER KEY;
DROP LOGIN InitDBProxy;
DROP CERTIFICATE InitDBAuthCertPub;
DROP USER InitDBProxy;
GO


れ ろ語 蟾 瑚 ろ 企 sys.sysdesend 150 Trick 覲願給.