Microsoft KB Archive/239904

= Delivery of Mail to Local Users Not Prompt =

Article ID: 239904

Article Last Modified on 10/10/2003

-

APPLIES TO


 * Microsoft Commercial Internet System 2.0
 * Microsoft SQL Server 6.5 Standard Edition

-



This article was previously published under Q239904



SYMPTOMS
When incoming mail is destined for local users the e-mail messages build up in the Queue folder on the mail server and are delivered slowly. There may be a large number of local e-mail messages (.eml files) without corresponding local transaction (extension .ltr) files.



CAUSE
If the database containing the Membership directory is not processing the requests from the LDAP server fast enough, then the messages waiting to be processed will queue up.



RESOLUTION
This problem may be caused by inefficient query optimization if the Membership database is on a SQL Server 6.5 server. SQL Server 6.5 requires that you manually update database statistics as the database table and index sizes change over time. This makes sure that the SQL Query Optimizer selects the optimal indexes for the processing of database requests. Failure to periodically update the database statistics can result in dramatic performance degradation as the database characteristics change.

You can execute the following commands against each Membership Directory database in the system (including all partition databases) regularly to ensure the statistics are up to date: DBCC UPDATEUSAGE (0, Object_Lookup) WITH NO_INFOMSGS, COUNT_ROWS go  DBCC UPDATEUSAGE (0, Objects_Attributes) WITH NO_INFOMSGS, COUNT_ROWS go  Update statistics Object_Lookup go  Update statistics Object_Attributes go  Sp_recompile Object_Lookup go  Sp_recompile Object_Attributes go You can run this command manually from ISQLW or run it as a scheduled task from within SQL Enterprise Manager.



WORKAROUND
Use SQL Server 7.0 for the Membership database. SQL Server 7.0 automatically updates database statistics.



MORE INFORMATION
When MCIS Mail receives a message destined for a local user it queries the LDAP server to obtain further information on the user. LDAP in turn queries SQL Server for the user information. This request takes the form of a stored procedure (sp_ims if the IMS-optimized stored procedures are not enabled, sp_ims1 or sp_ims25 if they are). The longer the stored procedures take to return, the longer local mail will take to be delivered.

To test if these stored procedures are running promptly run the procedures against the membership database manually with a couple of valid local e-mail addresses, for example: EXEC sp_ims 'user1@localdomain.com', 'user2@localdomain.com' The results should be returned promptly.

