microsoft.public.sqlserver.notificationsvcs
[Top] [All Lists]

High SQL server CPU usage while distributing

Subject: High SQL server CPU usage while distributing
From: "InvalidLastName"
Date: Fri, 8 Jul 2005 18:56:02 -0400
Newsgroups: microsoft.public.sqlserver.notificationsvcs
ï
Recently we started stress testing our SQLNS (for SQL2K) application. Here are some basic background information:
- running SQLNS enterprise sp1 v2.0.3008.0
- 1 generator, 2 distributors (W2k3 boxes)
- custom content formatter and custom delivery component (the delivery component uses notificationStatusCallback to report NotificationStatus to distributor, as shown as http://msdn.microsoft.com/library/default.asp?url="">)
- quad CPU SQL server cluster w/ 4G memory
- basic distributor logging (see ADF below)
 

<DistributorLogging>

<LogBeforeDeliveryAttempts>false</LogBeforeDeliveryAttempts>

<LogStatusInfo>false</LogStatusInfo>

<LogNotificationText>false</LogNotificationText>

</DistributorLogging>

 
The most serious performance issue we encoutered is average 75% CPU usage on SQL server while distributors were distributing. At the time the SQLNS application server CPU usage was at only about 10%. This is a totally unacceptable result.
 
After we dug more on this problem, we found out the high CPU was caused by NSInsertESSNotificationsDistributionLogEntries stored procedure. This stored procedure has "300" parameters, and "100" IF/ELSE blocks. I believe it is used by StatusCallback to log the results back to notification table and NSDistributionLog table. It was designed to log up to 100 notifications at a time (3 parameters per record). However, in our case, we only need  3 out of 300 parameters and the first IF/ELSE for logging just ONE notification.
 
Here are the alternatives we experimented and their results:
 
A) "Hacked" NSInsertESSNotificationsDistributionLogEntries stored procedure by returning to calling program (added "return" statement right after the first IF/ELSE. The result was the SQL server CPU usage dropped from average 75% to 7%. Yeah, 10 times difference by just bypass 99 IF/ELSE blocks in the stored procedure. The SQLNS application was able to deliver 50 notifications/second/distributor
 
B) Skip calling notificationStatusCallback for sucessfully delivered notification. This will totally remove the logging, however, I believe Distributor also uses notificationStatusCallback to determine the the result of current batch (work item). Because no successful status reported by delivery component, the distributor marked the current work item as "failure" , and this work item will be picked up by distributor again at next retry. (We actually wrote a custom stored procedure which mimics the 300-parameter NSInsertESSNotificationsDistributionLogEntries to just update the notification status. But distributor marked the work item "failure" because no sucessful status reported back by custom delivery component. However, those "successful" delivered notification (deliverystatuscode =6) were not picked up again by distributor during retry)
 
C) Changed custom delivery component to "cache" the NotificationStatus for each notification,and submitted the NotificationStatus in batch (when cache size reaches 100, or in Flush() to log all the cached NotificationStatus). SQL server CPU dropped from 75% to 10%.
 
The alternative A resulted the best performance and very healthy CPU usage pattern, and option C is a close second. We don't consider to ues option B because I don't think it is right to skip the notificationStatusCallback in delivery component
 
The option A requires to "hack" the SQLNS stored procedure, which I try to avoid. However, the option C will cause some status cached notification being re-sent if distributor encouters server failure while distributing the notification. Since the status is not logged immediately, the SQLNS will treat those un-reported notifications as never delivered, and re-send those at retry.
 
The last thing we want to do is to modify the SQLNS stored procedures. However, it is also important for us not resending duplicate notifications.
 
Here are some questions:
- Has Anyone seen the similar high CPU usage from basic logging as we encoutered (I know from SQLNS BOL, the logging is not recommended, because distributor already has basic logging. But this "basic" logging is killing us. It doesn't make any sense to me)
 
- Did we terriblely do something wrong in our SQLNS application? We seperate the data files, log files and index files. The event and subscription are on different file groups. I don't think the hard drive is not an issue here. We are using production cluster to perform stress testing.
 
 
- How come just 100 IF/ELSE blocks in a stored procedure can cause such high CPU usages on SQL server? I never expect that updating 100 records cross network via connection pooling is even more efficient then wrapping update in 100 IF/ELSE blocks
 
Any suggestion would be appreciated
 
Thanks
 
ILN
 
 
 
 

Her it comes, one of the two monster logging stored procedure which has 300 parameters and 100 IF/ELSE blocks (the other one is NSInsertESSNotificationsDistributionLogEntries )
 
PROCEDURE dbo."NSSetESSNotificationsNotificationDeliveryStatus" (
  @distributorId           int,
  @callerCurrentUtcTime datetime,
  @notificationId0     bigint = NULL,
  @workItemId0         bigint = NULL,
  @deliveryStatus0      tinyint = NULL,
  @callerSentTime0      datetime = NULL,
  @deliveryStatusInfo0  nvarchar(2048) = NULL,
  @notificationText0  nvarchar(2048) = NULL,
  @notificationId1     bigint = NULL,
  @workItemId1         bigint = NULL,
  @deliveryStatus1      tinyint = NULL,
  @callerSentTime1      datetime = NULL,
  @deliveryStatusInfo1  nvarchar(2048) = NULL,
  @notificationText1  nvarchar(2048) = NULL,
  @notificationId2     bigint = NULL,
  @workItemId2         bigint = NULL,
  @deliveryStatus2      tinyint = NULL,
  @callerSentTime2      datetime = NULL,
  @deliveryStatusInfo2  nvarchar(2048) = NULL,
  @notificationText2  nvarchar(2048) = NULL,
  @notificationId3     bigint = NULL,
  @workItemId3         bigint = NULL,
  @deliveryStatus3      tinyint = NULL,
  @callerSentTime3      datetime = NULL,
  @deliveryStatusInfo3  nvarchar(2048) = NULL,
  @notificationText3  nvarchar(2048) = NULL,
  @notificationId4     bigint = NULL,
  @workItemId4         bigint = NULL,
  @deliveryStatus4      tinyint = NULL,
  @callerSentTime4      datetime = NULL,
  @deliveryStatusInfo4  nvarchar(2048) = NULL,
  @notificationText4  nvarchar(2048) = NULL,
  @notificationId5     bigint = NULL,
  @workItemId5         bigint = NULL,
  @deliveryStatus5      tinyint = NULL,
  @callerSentTime5      datetime = NULL,
  @deliveryStatusInfo5  nvarchar(2048) = NULL,
  @notificationText5  nvarchar(2048) = NULL,
  @notificationId6     bigint = NULL,
  @workItemId6         bigint = NULL,
  @deliveryStatus6      tinyint = NULL,
  @callerSentTime6      datetime = NULL,
  @deliveryStatusInfo6  nvarchar(2048) = NULL,
  @notificationText6  nvarchar(2048) = NULL,
  @notificationId7     bigint = NULL,
  @workItemId7         bigint = NULL,
  @deliveryStatus7      tinyint = NULL,
  @callerSentTime7      datetime = NULL,
  @deliveryStatusInfo7  nvarchar(2048) = NULL,
  @notificationText7  nvarchar(2048) = NULL,
  @notificationId8     bigint = NULL,
  @workItemId8         bigint = NULL,
  @deliveryStatus8      tinyint = NULL,
  @callerSentTime8      datetime = NULL,
  @deliveryStatusInfo8  nvarchar(2048) = NULL,
  @notificationText8  nvarchar(2048) = NULL,
  @notificationId9     bigint = NULL,
  @workItemId9         bigint = NULL,
  @deliveryStatus9      tinyint = NULL,
  @callerSentTime9      datetime = NULL,
  @deliveryStatusInfo9  nvarchar(2048) = NULL,
  @notificationText9  nvarchar(2048) = NULL,
  @notificationId10     bigint = NULL,
  @workItemId10         bigint = NULL,
  @deliveryStatus10      tinyint = NULL,
  @callerSentTime10      datetime = NULL,
  @deliveryStatusInfo10  nvarchar(2048) = NULL,
  @notificationText10  nvarchar(2048) = NULL,
  @notificationId11     bigint = NULL,
  @workItemId11         bigint = NULL,
  @deliveryStatus11      tinyint = NULL,
  @callerSentTime11      datetime = NULL,
  @deliveryStatusInfo11  nvarchar(2048) = NULL,
  @notificationText11  nvarchar(2048) = NULL,
  @notificationId12     bigint = NULL,
  @workItemId12         bigint = NULL,
  @deliveryStatus12      tinyint = NULL,
  @callerSentTime12      datetime = NULL,
  @deliveryStatusInfo12  nvarchar(2048) = NULL,
  @notificationText12  nvarchar(2048) = NULL,
  @notificationId13     bigint = NULL,
  @workItemId13         bigint = NULL,
  @deliveryStatus13      tinyint = NULL,
  @callerSentTime13      datetime = NULL,
  @deliveryStatusInfo13  nvarchar(2048) = NULL,
  @notificationText13  nvarchar(2048) = NULL,
  @notificationId14     bigint = NULL,
  @workItemId14         bigint = NULL,
  @deliveryStatus14      tinyint = NULL,
  @callerSentTime14      datetime = NULL,
  @deliveryStatusInfo14  nvarchar(2048) = NULL,
  @notificationText14  nvarchar(2048) = NULL,
  @notificationId15     bigint = NULL,
  @workItemId15         bigint = NULL,
  @deliveryStatus15      tinyint = NULL,
  @callerSentTime15      datetime = NULL,
  @deliveryStatusInfo15  nvarchar(2048) = NULL,
  @notificationText15  nvarchar(2048) = NULL,
  @notificationId16     bigint = NULL,
  @workItemId16         bigint = NULL,
  @deliveryStatus16      tinyint = NULL,
  @callerSentTime16      datetime = NULL,
  @deliveryStatusInfo16  nvarchar(2048) = NULL,
  @notificationText16  nvarchar(2048) = NULL,
  @notificationId17     bigint = NULL,
  @workItemId17         bigint = NULL,
  @deliveryStatus17      tinyint = NULL,
  @callerSentTime17      datetime = NULL,
  @deliveryStatusInfo17  nvarchar(2048) = NULL,
  @notificationText17  nvarchar(2048) = NULL,
  @notificationId18     bigint = NULL,
  @workItemId18         bigint = NULL,
  @deliveryStatus18      tinyint = NULL,
  @callerSentTime18      datetime = NULL,
  @deliveryStatusInfo18  nvarchar(2048) = NULL,
  @notificationText18  nvarchar(2048) = NULL,
  @notificationId19     bigint = NULL,
  @workItemId19         bigint = NULL,
  @deliveryStatus19      tinyint = NULL,
  @callerSentTime19      datetime = NULL,
  @deliveryStatusInfo19  nvarchar(2048) = NULL,
  @notificationText19  nvarchar(2048) = NULL,
  @notificationId20     bigint = NULL,
  @workItemId20         bigint = NULL,
  @deliveryStatus20      tinyint = NULL,
  @callerSentTime20      datetime = NULL,
  @deliveryStatusInfo20  nvarchar(2048) = NULL,
  @notificationText20  nvarchar(2048) = NULL,
  @notificationId21     bigint = NULL,
  @workItemId21         bigint = NULL,
  @deliveryStatus21      tinyint = NULL,
  @callerSentTime21      datetime = NULL,
  @deliveryStatusInfo21  nvarchar(2048) = NULL,
  @notificationText21  nvarchar(2048) = NULL,
  @notificationId22     bigint = NULL,
  @workItemId22         bigint = NULL,
  @deliveryStatus22      tinyint = NULL,
  @callerSentTime22      datetime = NULL,
  @deliveryStatusInfo22  nvarchar(2048) = NULL,
  @notificationText22  nvarchar(2048) = NULL,
  @notificationId23     bigint = NULL,
  @workItemId23         bigint = NULL,
  @deliveryStatus23      tinyint = NULL,
  @callerSentTime23      datetime = NULL,
  @deliveryStatusInfo23  nvarchar(2048) = NULL,
  @notificationText23  nvarchar(2048) = NULL,
  @notificationId24     bigint = NULL,
  @workItemId24         bigint = NULL,
  @deliveryStatus24      tinyint = NULL,
  @callerSentTime24      datetime = NULL,
  @deliveryStatusInfo24  nvarchar(2048) = NULL,
  @notificationText24  nvarchar(2048) = NULL,
  @notificationId25     bigint = NULL,
  @workItemId25         bigint = NULL,
  @deliveryStatus25      tinyint = NULL,
  @callerSentTime25      datetime = NULL,
  @deliveryStatusInfo25  nvarchar(2048) = NULL,
  @notificationText25  nvarchar(2048) = NULL,
  @notificationId26     bigint = NULL,
  @workItemId26         bigint = NULL,
  @deliveryStatus26      tinyint = NULL,
  @callerSentTime26      datetime = NULL,
  @deliveryStatusInfo26  nvarchar(2048) = NULL,
  @notificationText26  nvarchar(2048) = NULL,
  @notificationId27     bigint = NULL,
  @workItemId27         bigint = NULL,
  @deliveryStatus27      tinyint = NULL,
  @callerSentTime27      datetime = NULL,
  @deliveryStatusInfo27  nvarchar(2048) = NULL,
  @notificationText27  nvarchar(2048) = NULL,
  @notificationId28     bigint = NULL,
  @workItemId28         bigint = NULL,
  @deliveryStatus28      tinyint = NULL,
  @callerSentTime28      datetime = NULL,
  @deliveryStatusInfo28  nvarchar(2048) = NULL,
  @notificationText28  nvarchar(2048) = NULL,
  @notificationId29     bigint = NULL,
  @workItemId29         bigint = NULL,
  @deliveryStatus29      tinyint = NULL,
  @callerSentTime29      datetime = NULL,
  @deliveryStatusInfo29  nvarchar(2048) = NULL,
  @notificationText29  nvarchar(2048) = NULL,
  @notificationId30     bigint = NULL,
  @workItemId30         bigint = NULL,
  @deliveryStatus30      tinyint = NULL,
  @callerSentTime30      datetime = NULL,
  @deliveryStatusInfo30  nvarchar(2048) = NULL,
  @notificationText30  nvarchar(2048) = NULL,
  @notificationId31     bigint = NULL,
  @workItemId31         bigint = NULL,
  @deliveryStatus31      tinyint = NULL,
  @callerSentTime31      datetime = NULL,
  @deliveryStatusInfo31  nvarchar(2048) = NULL,
  @notificationText31  nvarchar(2048) = NULL,
  @notificationId32     bigint = NULL,
  @workItemId32         bigint = NULL,
  @deliveryStatus32      tinyint = NULL,
  @callerSentTime32      datetime = NULL,
  @deliveryStatusInfo32  nvarchar(2048) = NULL,
  @notificationText32  nvarchar(2048) = NULL,
  @notificationId33     bigint = NULL,
  @workItemId33         bigint = NULL,
  @deliveryStatus33      tinyint = NULL,
  @callerSentTime33      datetime = NULL,
  @deliveryStatusInfo33  nvarchar(2048) = NULL,
  @notificationText33  nvarchar(2048) = NULL,
  @notificationId34     bigint = NULL,
  @workItemId34         bigint = NULL,
  @deliveryStatus34      tinyint = NULL,
  @callerSentTime34      datetime = NULL,
  @deliveryStatusInfo34  nvarchar(2048) = NULL,
  @notificationText34  nvarchar(2048) = NULL,
  @notificationId35     bigint = NULL,
  @workItemId35         bigint = NULL,
  @deliveryStatus35      tinyint = NULL,
  @callerSentTime35      datetime = NULL,
  @deliveryStatusInfo35  nvarchar(2048) = NULL,
  @notificationText35  nvarchar(2048) = NULL,
  @notificationId36     bigint = NULL,
  @workItemId36         bigint = NULL,
  @deliveryStatus36      tinyint = NULL,
  @callerSentTime36      datetime = NULL,
  @deliveryStatusInfo36  nvarchar(2048) = NULL,
  @notificationText36  nvarchar(2048) = NULL,
  @notificationId37     bigint = NULL,
  @workItemId37         bigint = NULL,
  @deliveryStatus37      tinyint = NULL,
  @callerSentTime37      datetime = NULL,
  @deliveryStatusInfo37  nvarchar(2048) = NULL,
  @notificationText37  nvarchar(2048) = NULL,
  @notificationId38     bigint = NULL,
  @workItemId38         bigint = NULL,
  @deliveryStatus38      tinyint = NULL,
  @callerSentTime38      datetime = NULL,
  @deliveryStatusInfo38  nvarchar(2048) = NULL,
  @notificationText38  nvarchar(2048) = NULL,
  @notificationId39     bigint = NULL,
  @workItemId39         bigint = NULL,
  @deliveryStatus39      tinyint = NULL,
  @callerSentTime39      datetime = NULL,
  @deliveryStatusInfo39  nvarchar(2048) = NULL,
  @notificationText39  nvarchar(2048) = NULL,
  @notificationId40     bigint = NULL,
  @workItemId40         bigint = NULL,
  @deliveryStatus40      tinyint = NULL,
  @callerSentTime40      datetime = NULL,
  @deliveryStatusInfo40  nvarchar(2048) = NULL,
  @notificationText40  nvarchar(2048) = NULL,
  @notificationId41     bigint = NULL,
  @workItemId41         bigint = NULL,
  @deliveryStatus41      tinyint = NULL,
  @callerSentTime41      datetime = NULL,
  @deliveryStatusInfo41  nvarchar(2048) = NULL,
  @notificationText41  nvarchar(2048) = NULL,
  @notificationId42     bigint = NULL,
  @workItemId42         bigint = NULL,
  @deliveryStatus42      tinyint = NULL,
  @callerSentTime42      datetime = NULL,
  @deliveryStatusInfo42  nvarchar(2048) = NULL,
  @notificationText42  nvarchar(2048) = NULL,
  @notificationId43     bigint = NULL,
  @workItemId43         bigint = NULL,
  @deliveryStatus43      tinyint = NULL,
  @callerSentTime43      datetime = NULL,
  @deliveryStatusInfo43  nvarchar(2048) = NULL,
  @notificationText43  nvarchar(2048) = NULL,
  @notificationId44     bigint = NULL,
  @workItemId44         bigint = NULL,
  @deliveryStatus44      tinyint = NULL,
  @callerSentTime44      datetime = NULL,
  @deliveryStatusInfo44  nvarchar(2048) = NULL,
  @notificationText44  nvarchar(2048) = NULL,
  @notificationId45     bigint = NULL,
  @workItemId45         bigint = NULL,
  @deliveryStatus45      tinyint = NULL,
  @callerSentTime45      datetime = NULL,
  @deliveryStatusInfo45  nvarchar(2048) = NULL,
  @notificationText45  nvarchar(2048) = NULL,
  @notificationId46     bigint = NULL,
  @workItemId46         bigint = NULL,
  @deliveryStatus46      tinyint = NULL,
  @callerSentTime46      datetime = NULL,
  @deliveryStatusInfo46  nvarchar(2048) = NULL,
  @notificationText46  nvarchar(2048) = NULL,
  @notificationId47     bigint = NULL,
  @workItemId47         bigint = NULL,
  @deliveryStatus47      tinyint = NULL,
  @callerSentTime47      datetime = NULL,
  @deliveryStatusInfo47  nvarchar(2048) = NULL,
  @notificationText47  nvarchar(2048) = NULL,
  @notificationId48     bigint = NULL,
  @workItemId48         bigint = NULL,
  @deliveryStatus48      tinyint = NULL,
  @callerSentTime48      datetime = NULL,
  @deliveryStatusInfo48  nvarchar(2048) = NULL,
  @notificationText48  nvarchar(2048) = NULL,
  @notificationId49     bigint = NULL,
  @workItemId49         bigint = NULL,
  @deliveryStatus49      tinyint = NULL,
  @callerSentTime49      datetime = NULL,
  @deliveryStatusInfo49  nvarchar(2048) = NULL,
  @notificationText49  nvarchar(2048) = NULL,
  @notificationId50     bigint = NULL,
  @workItemId50         bigint = NULL,
  @deliveryStatus50      tinyint = NULL,
  @callerSentTime50      datetime = NULL,
  @deliveryStatusInfo50  nvarchar(2048) = NULL,
  @notificationText50  nvarchar(2048) = NULL,
  @notificationId51     bigint = NULL,
  @workItemId51         bigint = NULL,
  @deliveryStatus51      tinyint = NULL,
  @callerSentTime51      datetime = NULL,
  @deliveryStatusInfo51  nvarchar(2048) = NULL,
  @notificationText51  nvarchar(2048) = NULL,
  @notificationId52     bigint = NULL,
  @workItemId52         bigint = NULL,
  @deliveryStatus52      tinyint = NULL,
  @callerSentTime52      datetime = NULL,
  @deliveryStatusInfo52  nvarchar(2048) = NULL,
  @notificationText52  nvarchar(2048) = NULL,
  @notificationId53     bigint = NULL,
  @workItemId53         bigint = NULL,
  @deliveryStatus53      tinyint = NULL,
  @callerSentTime53      datetime = NULL,
  @deliveryStatusInfo53  nvarchar(2048) = NULL,
  @notificationText53  nvarchar(2048) = NULL,
  @notificationId54     bigint = NULL,
  @workItemId54         bigint = NULL,
  @deliveryStatus54      tinyint = NULL,
  @callerSentTime54      datetime = NULL,
  @deliveryStatusInfo54  nvarchar(2048) = NULL,
  @notificationText54  nvarchar(2048) = NULL,
  @notificationId55     bigint = NULL,
  @workItemId55         bigint = NULL,
  @deliveryStatus55      tinyint = NULL,
  @callerSentTime55      datetime = NULL,
  @deliveryStatusInfo55  nvarchar(2048) = NULL,
  @notificationText55  nvarchar(2048) = NULL,
  @notificationId56     bigint = NULL,
  @workItemId56         bigint = NULL,
  @deliveryStatus56      tinyint = NULL,
  @callerSentTime56      datetime = NULL,
  @deliveryStatusInfo56  nvarchar(2048) = NULL,
  @notificationText56  nvarchar(2048) = NULL,
  @notificationId57     bigint = NULL,
  @workItemId57         bigint = NULL,
  @deliveryStatus57      tinyint = NULL,
  @callerSentTime57      datetime = NULL,
  @deliveryStatusInfo57  nvarchar(2048) = NULL,
  @notificationText57  nvarchar(2048) = NULL,
  @notificationId58     bigint = NULL,
  @workItemId58         bigint = NULL,
  @deliveryStatus58      tinyint = NULL,
  @callerSentTime58      datetime = NULL,
  @deliveryStatusInfo58  nvarchar(2048) = NULL,
  @notificationText58  nvarchar(2048) = NULL,
  @notificationId59     bigint = NULL,
  @workItemId59         bigint = NULL,
  @deliveryStatus59      tinyint = NULL,
  @callerSentTime59      datetime = NULL,
  @deliveryStatusInfo59  nvarchar(2048) = NULL,
  @notificationText59  nvarchar(2048) = NULL,
  @notificationId60     bigint = NULL,
  @workItemId60         bigint = NULL,
  @deliveryStatus60      tinyint = NULL,
  @callerSentTime60      datetime = NULL,
  @deliveryStatusInfo60  nvarchar(2048) = NULL,
  @notificationText60  nvarchar(2048) = NULL,
  @notificationId61     bigint = NULL,
  @workItemId61         bigint = NULL,
  @deliveryStatus61      tinyint = NULL,
  @callerSentTime61      datetime = NULL,
  @deliveryStatusInfo61  nvarchar(2048) = NULL,
  @notificationText61  nvarchar(2048) = NULL,
  @notificationId62     bigint = NULL,
  @workItemId62         bigint = NULL,
  @deliveryStatus62      tinyint = NULL,
  @callerSentTime62      datetime = NULL,
  @deliveryStatusInfo62  nvarchar(2048) = NULL,
  @notificationText62  nvarchar(2048) = NULL,
  @notificationId63     bigint = NULL,
  @workItemId63         bigint = NULL,
  @deliveryStatus63      tinyint = NULL,
  @callerSentTime63      datetime = NULL,
  @deliveryStatusInfo63  nvarchar(2048) = NULL,
  @notificationText63  nvarchar(2048) = NULL,
  @notificationId64     bigint = NULL,
  @workItemId64         bigint = NULL,
  @deliveryStatus64      tinyint = NULL,
  @callerSentTime64      datetime = NULL,
  @deliveryStatusInfo64  nvarchar(2048) = NULL,
  @notificationText64  nvarchar(2048) = NULL,
  @notificationId65     bigint = NULL,
  @workItemId65         bigint = NULL,
  @deliveryStatus65      tinyint = NULL,
  @callerSentTime65      datetime = NULL,
  @deliveryStatusInfo65  nvarchar(2048) = NULL,
  @notificationText65  nvarchar(2048) = NULL,
  @notificationId66     bigint = NULL,
  @workItemId66         bigint = NULL,
  @deliveryStatus66      tinyint = NULL,
  @callerSentTime66      datetime = NULL,
  @deliveryStatusInfo66  nvarchar(2048) = NULL,
  @notificationText66  nvarchar(2048) = NULL,
  @notificationId67     bigint = NULL,
  @workItemId67         bigint = NULL,
  @deliveryStatus67      tinyint = NULL,
  @callerSentTime67      datetime = NULL,
  @deliveryStatusInfo67  nvarchar(2048) = NULL,
  @notificationText67  nvarchar(2048) = NULL,
  @notificationId68     bigint = NULL,
  @workItemId68         bigint = NULL,
  @deliveryStatus68      tinyint = NULL,
  @callerSentTime68      datetime = NULL,
  @deliveryStatusInfo68  nvarchar(2048) = NULL,
  @notificationText68  nvarchar(2048) = NULL,
  @notificationId69     bigint = NULL,
  @workItemId69         bigint = NULL,
  @deliveryStatus69      tinyint = NULL,
  @callerSentTime69      datetime = NULL,
  @deliveryStatusInfo69  nvarchar(2048) = NULL,
  @notificationText69  nvarchar(2048) = NULL,
  @notificationId70     bigint = NULL,
  @workItemId70         bigint = NULL,
  @deliveryStatus70      tinyint = NULL,
  @callerSentTime70      datetime = NULL,
  @deliveryStatusInfo70  nvarchar(2048) = NULL,
  @notificationText70  nvarchar(2048) = NULL,
  @notificationId71     bigint = NULL,
  @workItemId71         bigint = NULL,
  @deliveryStatus71      tinyint = NULL,
  @callerSentTime71      datetime = NULL,
  @deliveryStatusInfo71  nvarchar(2048) = NULL,
  @notificationText71  nvarchar(2048) = NULL,
  @notificationId72     bigint = NULL,
  @workItemId72         bigint = NULL,
  @deliveryStatus72      tinyint = NULL,
  @callerSentTime72      datetime = NULL,
  @deliveryStatusInfo72  nvarchar(2048) = NULL,
  @notificationText72  nvarchar(2048) = NULL,
  @notificationId73     bigint = NULL,
  @workItemId73         bigint = NULL,
  @deliveryStatus73      tinyint = NULL,
  @callerSentTime73      datetime = NULL,
  @deliveryStatusInfo73  nvarchar(2048) = NULL,
  @notificationText73  nvarchar(2048) = NULL,
  @notificationId74     bigint = NULL,
  @workItemId74         bigint = NULL,
  @deliveryStatus74      tinyint = NULL,
  @callerSentTime74      datetime = NULL,
  @deliveryStatusInfo74  nvarchar(2048) = NULL,
  @notificationText74  nvarchar(2048) = NULL,
  @notificationId75     bigint = NULL,
  @workItemId75         bigint = NULL,
  @deliveryStatus75      tinyint = NULL,
  @callerSentTime75      datetime = NULL,
  @deliveryStatusInfo75  nvarchar(2048) = NULL,
  @notificationText75  nvarchar(2048) = NULL,
  @notificationId76     bigint = NULL,
  @workItemId76         bigint = NULL,
  @deliveryStatus76      tinyint = NULL,
  @callerSentTime76      datetime = NULL,
  @deliveryStatusInfo76  nvarchar(2048) = NULL,
  @notificationText76  nvarchar(2048) = NULL,
  @notificationId77     bigint = NULL,
  @workItemId77         bigint = NULL,
  @deliveryStatus77      tinyint = NULL,
  @callerSentTime77      datetime = NULL,
  @deliveryStatusInfo77  nvarchar(2048) = NULL,
  @notificationText77  nvarchar(2048) = NULL,
  @notificationId78     bigint = NULL,
  @workItemId78         bigint = NULL,
  @deliveryStatus78      tinyint = NULL,
  @callerSentTime78      datetime = NULL,
  @deliveryStatusInfo78  nvarchar(2048) = NULL,
  @notificationText78  nvarchar(2048) = NULL,
  @notificationId79     bigint = NULL,
  @workItemId79         bigint = NULL,
  @deliveryStatus79      tinyint = NULL,
  @callerSentTime79      datetime = NULL,
  @deliveryStatusInfo79  nvarchar(2048) = NULL,
  @notificationText79  nvarchar(2048) = NULL,
  @notificationId80     bigint = NULL,
  @workItemId80         bigint = NULL,
  @deliveryStatus80      tinyint = NULL,
  @callerSentTime80      datetime = NULL,
  @deliveryStatusInfo80  nvarchar(2048) = NULL,
  @notificationText80  nvarchar(2048) = NULL,
  @notificationId81     bigint = NULL,
  @workItemId81         bigint = NULL,
  @deliveryStatus81      tinyint = NULL,
  @callerSentTime81      datetime = NULL,
  @deliveryStatusInfo81  nvarchar(2048) = NULL,
  @notificationText81  nvarchar(2048) = NULL,
  @notificationId82     bigint = NULL,
  @workItemId82         bigint = NULL,
  @deliveryStatus82      tinyint = NULL,
  @callerSentTime82      datetime = NULL,
  @deliveryStatusInfo82  nvarchar(2048) = NULL,
  @notificationText82  nvarchar(2048) = NULL,
  @notificationId83     bigint = NULL,
  @workItemId83         bigint = NULL,
  @deliveryStatus83      tinyint = NULL,
  @callerSentTime83      datetime = NULL,
  @deliveryStatusInfo83  nvarchar(2048) = NULL,
  @notificationText83  nvarchar(2048) = NULL,
  @notificationId84     bigint = NULL,
  @workItemId84         bigint = NULL,
  @deliveryStatus84      tinyint = NULL,
  @callerSentTime84      datetime = NULL,
  @deliveryStatusInfo84  nvarchar(2048) = NULL,
  @notificationText84  nvarchar(2048) = NULL,
  @notificationId85     bigint = NULL,
  @workItemId85         bigint = NULL,
  @deliveryStatus85      tinyint = NULL,
  @callerSentTime85      datetime = NULL,
  @deliveryStatusInfo85  nvarchar(2048) = NULL,
  @notificationText85  nvarchar(2048) = NULL,
  @notificationId86     bigint = NULL,
  @workItemId86         bigint = NULL,
  @deliveryStatus86      tinyint = NULL,
  @callerSentTime86      datetime = NULL,
  @deliveryStatusInfo86  nvarchar(2048) = NULL,
  @notificationText86  nvarchar(2048) = NULL,
  @notificationId87     bigint = NULL,
  @workItemId87         bigint = NULL,
  @deliveryStatus87      tinyint = NULL,
  @callerSentTime87      datetime = NULL,
  @deliveryStatusInfo87  nvarchar(2048) = NULL,
  @notificationText87  nvarchar(2048) = NULL,
  @notificationId88     bigint = NULL,
  @workItemId88         bigint = NULL,
  @deliveryStatus88      tinyint = NULL,
  @callerSentTime88      datetime = NULL,
  @deliveryStatusInfo88  nvarchar(2048) = NULL,
  @notificationText88  nvarchar(2048) = NULL,
  @notificationId89     bigint = NULL,
  @workItemId89         bigint = NULL,
  @deliveryStatus89      tinyint = NULL,
  @callerSentTime89      datetime = NULL,
  @deliveryStatusInfo89  nvarchar(2048) = NULL,
  @notificationText89  nvarchar(2048) = NULL,
  @notificationId90     bigint = NULL,
  @workItemId90         bigint = NULL,
  @deliveryStatus90      tinyint = NULL,
  @callerSentTime90      datetime = NULL,
  @deliveryStatusInfo90  nvarchar(2048) = NULL,
  @notificationText90  nvarchar(2048) = NULL,
  @notificationId91     bigint = NULL,
  @workItemId91         bigint = NULL,
  @deliveryStatus91      tinyint = NULL,
  @callerSentTime91      datetime = NULL,
  @deliveryStatusInfo91  nvarchar(2048) = NULL,
  @notificationText91  nvarchar(2048) = NULL,
  @notificationId92     bigint = NULL,
  @workItemId92         bigint = NULL,
  @deliveryStatus92      tinyint = NULL,
  @callerSentTime92      datetime = NULL,
  @deliveryStatusInfo92  nvarchar(2048) = NULL,
  @notificationText92  nvarchar(2048) = NULL,
  @notificationId93     bigint = NULL,
  @workItemId93         bigint = NULL,
  @deliveryStatus93      tinyint = NULL,
  @callerSentTime93      datetime = NULL,
  @deliveryStatusInfo93  nvarchar(2048) = NULL,
  @notificationText93  nvarchar(2048) = NULL,
  @notificationId94     bigint = NULL,
  @workItemId94         bigint = NULL,
  @deliveryStatus94      tinyint = NULL,
  @callerSentTime94      datetime = NULL,
  @deliveryStatusInfo94  nvarchar(2048) = NULL,
  @notificationText94  nvarchar(2048) = NULL,
  @notificationId95     bigint = NULL,
  @workItemId95         bigint = NULL,
  @deliveryStatus95      tinyint = NULL,
  @callerSentTime95      datetime = NULL,
  @deliveryStatusInfo95  nvarchar(2048) = NULL,
  @notificationText95  nvarchar(2048) = NULL,
  @notificationId96     bigint = NULL,
  @workItemId96         bigint = NULL,
  @deliveryStatus96      tinyint = NULL,
  @callerSentTime96      datetime = NULL,
  @deliveryStatusInfo96  nvarchar(2048) = NULL,
  @notificationText96  nvarchar(2048) = NULL,
  @notificationId97     bigint = NULL,
  @workItemId97         bigint = NULL,
  @deliveryStatus97      tinyint = NULL,
  @callerSentTime97      datetime = NULL,
  @deliveryStatusInfo97  nvarchar(2048) = NULL,
  @notificationText97  nvarchar(2048) = NULL,
  @notificationId98     bigint = NULL,
  @workItemId98         bigint = NULL,
  @deliveryStatus98      tinyint = NULL,
  @callerSentTime98      datetime = NULL,
  @deliveryStatusInfo98  nvarchar(2048) = NULL,
  @notificationText98  nvarchar(2048) = NULL,
  @notificationId99     bigint = NULL,
  @workItemId99         bigint = NULL,
  @deliveryStatus99      tinyint = NULL,
  @callerSentTime99      datetime = NULL,
  @deliveryStatusInfo99  nvarchar(2048) = NULL,
  @notificationText99  nvarchar(2048) = NULL) AS
BEGIN
 BEGIN TRANSACTION
  DECLARE @serverCurrentUtcTime datetime
  DECLARE @clockDeltaMS  int
  DECLARE @serverSentTime  datetime
  DECLARE @distributionLogId  bigint
 
  -- Determine the delta between the caller's clock and the server's.
  SELECT @serverCurrentUtcTime = GETUTCDATE()
  SELECT @clockDeltaMS = DATEDIFF(MILLISECOND, @callerCurrentUtcTime, @serverCurrentUtcTime)
 
  IF @notificationId0 IS NOT NULL
  BEGIN
    -- Calculate what the sent time is, relative to the server's clock.
    IF @callerSentTime0 IS NOT NULL   
      SET @serverSentTime = DATEADD(MILLISECOND, @clockDeltaMS, @callerSentTime0)
    ELSE
      SET @serverSentTime = NULL
 
    -- Update the row in the notifications table, recording the sent time as relative to
    -- the server's clock.
    UPDATE "NSESSNotificationsNotifications"
    SET DeliveryStatusCode = @deliveryStatus0,
        SentTime = @serverSentTime
    WHERE NotificationId = @notificationId0
    -- Insert a row in the delivery log, recording the sent time as relative to
    -- the server's clock.
    INSERT INTO "NSDistributionLog" (NotificationClassId, NotificationId, DistributorWorkItemId, DistributorId, DeliveryRequestTime, SentTime, DeliveryStatusCode, DeliveryStatusInfo, NotificationText, LinkDistributionLogId)
    VALUES (1, @notificationId0, @workItemId0, @distributorId, NULL, @serverSentTime, @deliveryStatus0, @deliveryStatusInfo0, @notificationText0, NULL)
    SELECT @distributionLogId = @@IDENTITY -- dont know why it needs @@IDENTITY here, @distributionLogId is not used in anywhere. the sproc is already close 3000 lines
  END
 
return -- we are done here ...
  IF @notificationId1 IS NOT NULL
  BEGIN
    --
...
..
 
 
<Prev in Thread] Current Thread [Next in Thread>
  • High SQL server CPU usage while distributing, InvalidLastName <=