DarenHan's Blog

# re: Another fire-fighting case due to "cut costs"

i found the code to limit concurrent execution of stored procedure to prevent bring down a server.

Create table MyTable

(

RowId int identity(1,1),

HitStartedAt datetime,

HitTimestamp datetime,

UserName varchar(100)

)

Go

Create proc LegacyProc (@user varchar(100), @CalledTime datetime)

as

Begin

Insert Into MyTable

Values(@CalledTime, getdate(), @user);

--To wait for 10 sec : not required for your procedures, producing the latency to check the concurrent users action

WAITFOR DELAY '000:00:10'

End

Go

Create Proc MyProc

(

@user varchar(100)

)

as

Begin

Declare @PorcName as NVarchar(1000), @CalledTime datetime

Begin Tran

--To get the Current SP Name, it should be unique for each SP / each batch

SET @PorcName =object_name(@@ProcID)

SET @CalledTime = Getdate()

--Lock the Current Proc

Exec sp_getapplock @Resource = @PorcName, @LockMode = 'Exclusive'

--Execute Your Legacy Procedures

Exec LegacyProc @user, @CalledTime

--Release the lock

Exec sp_releaseapplock @Resource = @PorcName

Commit Tran

End

# re: Another fire-fighting case due to "cut costs"

Adapted from Sizing and deployment guide of Crystal Enterprise 10:

"For production environments, it is recommended that the CMS database not be placed on:

1) The database server against which the reports are running

2) Any one of the Job Server or Page Server machines

3) One of the CMS machines.

The reason for this is that if the Database Server, Job Server, or Page Server is busy processing the

data for reports, the retrieval time for the CMS to query the same server will be negatively

impacted. Please refer to the Crystal Enterprise Installation guide for information on supported

CMS databases."

Currently, in our Prod server, the ce10 which is the CMS database is situated

on the same database server.

An irony which contradicts with the deployment and sizing guide of crystal enterprise 10 guide from BusinessObjects. lolz