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企 ロ.
- 150
TrickEnableFlag 螳 螻 覓伎螻 code reading蠍 覦.
-
150 Trick れ ろ語 る０ .
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 覲願給.