Tip #171: If email router is having a slow day or how to add custom index

Sometimes things are slow. Very. Slow. So slow that your email router starts spitting dreadful messages:

#9628 – An error occurred while delivering the e-mail message with subject “None of your business” in mailbox foo@bar.baz for delivery to https://crm.bar.baz. System.ServiceModel.FaultException`1 [Microsoft.Xrm.Sdk.OrganizationServiceFault]: SQL timeout expired. (Fault Detail is equal to Microsoft.Xrm.Sdk.OrganizationServiceFault).

If you are on CRM Online, contact support, not much else you can do. On premises, however, it’s a different story. The most important bit of the message is highlighted.

tl;dr

If your email router keeps generating SQL Timeout errors, and you have a large number of email records (YMMV but think millions not thousands), then try adding the following index to EmailHashBase table:

CREATE NONCLUSTERED INDEX [indx_router] 
   ON [dbo].[EmailHashBase] 
(
	[ActivityId] ASC,
	[HashType] ASC
) ON [PRIMARY]

Longer version or how to fish

SQL timeout means we can skip CRM tracing and go straight to SQL Profiler.

  1. Start SQL Profiler, select T-SQL template. Make sure all columns are available, set database filter to your foobar_MSCRM database only to reduce noise. If possible, also filter the user by setting a condition on the account used to run email router.
  2. Ready? Start the trace, restart email router service (to force the operation without waiting for the polling interval). Keep your eye on the log, the moment you get the error message, stop SQL trace
  3. Now you have a massive trace to work with (your definition of “massive” may vary). What I usually do is go through the trace, select and copy bunches of select statements, paste into a SQL query window and run
  4. Sooner of later you’ll find a statement that just hangs. In my case it was something like
    WITH Candidates
    	 (
    		  ActivityId
    	 ) AS
    	 (SELECT  ActivityId
    	 FROM     EmailHashBase WITH (NOLOCK)
    	 WHERE    ActivityId IN
    		(SELECT  ActivityId
    		  FROM   EmailHashBase WITH (NOLOCK)
    		  WHERE    HashType = 0
    		           AND Hash IN (-101500970,-295814632)
    		  GROUP BY ActivityId
    		  HAVING   COUNT(ActivityId) >= 3
    		)
    		  AND HashType = 1
    		  AND Hash IN (-248594596,-742304120)
    	 GROUP BY ActivityId
    	 HAVING   COUNT(ActivityId) >= 2
    	 )
    SELECT   TOP 1 a.ActivityId
    FROM     ActivityPointerBase AS a WITH (NOLOCK)
    WHERE    a.ActivityId IN
    		 (
    		 SELECT ActivityId
    		 FROM    Candidates AS c
    		 WHERE(
    			(SELECT COUNT(*)
    			   FROM    EmailHashBase AS h
    			   WHERE   h.ActivityId = c.ActivityId
    			       AND h.HashType   = 0
    			) <= 3
    		    ) 
    		 )
    ORDER BY a.ModifiedOn DESC
    go
    
  5. That’s where you need to possess some SQL skills or get someone who does. Find which part is exactly slow, check the execution plan, look at where clauses and indexes, think. What was suspicious in my case is that EmailHashBase table had over 2 million records but the only helpful index was on ActivityId and HashType condition for some reason pushed SQL to an index scan.
  6. After adding a new combined index on ActivityId and HashType, the entire statement execution dropped from few minutes to under 1 second. And the router was happy again.

So here you have it. Measuring your SQL Server performance, identifying bottlenecks (specific to your schema and deployment) and tackling them by either throwing more hardware at the problem or by doing some troubleshooting, is a very important part of all on premises installations. And the best part of adding custom indexes is that it’s 100% supported.

Tweet about this on TwitterShare on Facebook0Share on Google+0

3 thoughts on “Tip #171: If email router is having a slow day or how to add custom index

  1. Awesome stuff George!

Leave a Reply

Your email address will not be published. Required fields are marked *