HttpWebRequest runs slowly first time within SQLCLR

Question

When making an HttpWebRequest within a CLR stored procedure (as per the code below), the first invocation after the Sql Server is (re-)started or after a given (but indeterminate) period of time waits for quite a length of time on the GetResponse() method call.

Is there any way to resolve this that doesn't involve a "hack" such as having a Sql Server Agent job running every few minutes to try and ensure that the first "slow" call is made by the Agent and not "real" production code?

function SqlString MakeWebRequest(string address, string parameters, int connectTO)
{
  SqlString returnData;
  HttpWebRequest request = (HttpWebRequest)WebRequest.Create(String.Concat(address.ToString(), "?", parameters.ToString())); 
  request.Timeout = (int)connectTO;
  request.Method = "GET";
  using (WebResponse response = request.GetResponse())
  {
    using (Stream responseStream = response.GetResponseStream())
    {
      using (StreamReader reader = new StreamReader(responseStream))
      {
        SqlString responseFromServer = reader.ReadToEnd();
        returnData = responseFromServer;
      }
    }
  }
  response.Close();

  return returnData;
}

(Error handling and other non-critical code has ben removed for brevity)

---

See also this Sql Server forums thread.

Answer 1

There is always a delay the first time SQLCLR loads the necessary assemblies. That should be the case not only for your function MakeWebRequest, but also for any .NET function in the SQLCLR.

Answer 2

HttpWebRequest is part of the System.Net assembly, which is not part of the supported libraries. I'd recommend using the library System.Web.Services instead to make web service calls from inside the SQLCLR.

Answer 3

Not sure but if the delay long enough that initial DNS lookups could be the culprit? ( how long is the delay verse a normal call? )

and/or

Is this URI internal to the Network / or a different internal network?

I have seen some weird networking delays from using load balance profiles inside a network that isn't setup right, the firewalls, load-balancers, and other network profiles might be "fighting" the initial connections...

I am not a great networking guy, but you might want to see what an SA has to say about this on serverfault.com as well...

good luck

Answer 4

I have tested and my first cold run (after SQL service restart) was in 3 seconds (not 30 as yours), all others are in 0 sec.

The code sample I've used to build a DLL:

using System;
using System.Data;
using System.Net;
using System.IO;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

namespace MySQLCLR
{
    public static class WebRequests
    {
        public static void MakeWebRequest(string address, string parameters, int connectTO)
        {
            string returnData;
            HttpWebRequest request = (HttpWebRequest)WebRequest.Create(String.Concat(address.ToString(), "?", parameters.ToString()));
            request.Timeout = (int)connectTO;
            request.Method = "GET";
            using (WebResponse response = request.GetResponse())
            {
                using (Stream responseStream = response.GetResponseStream())
                {
                    using (StreamReader reader = new StreamReader(responseStream))
                    {
                        returnData = reader.ReadToEnd();
                        reader.Close();
                    }
                    responseStream.Close();
                }
                response.Close();
            }
            SqlDataRecord rec = new SqlDataRecord(new SqlMetaData[] { new SqlMetaData("response", SqlDbType.NVarChar, 10000000) });
            rec.SetValue(0, returnData);
            SqlContext.Pipe.Send(rec);
        }
    }
}

Answer 5

This was a problem for me using HttpWebRequest at first. It's due to the the class looking for a proxy to use. If you set the object's Proxy value to null/Nothing, it'll zip right along.

Answer 6

Looks to me like code signing verification. The MS shipped system dlls are all signed and SQL verifies the signatures at load time. Apparently the certificate revocation list is expired and the certificate verification engine times out retrieving a new list. I have blogged about this problem before Fix slow application startup due to code sign validation and the problem is also described in this Technet article: Certificate Revocation and Status Checking.

The solution is pretty arcane and involves registry editing of the key: HKLM\SOFTWARE\Microsoft\Cryptography\OID\EncodingType 0\CertDllCreateCertificateChainEngine\Config:

• ChainUrlRetrievalTimeoutMilliseconds This is each individual CRL check call timeout. If is 0 or not present the default value of 15 seconds is used. Change this timeout to a reasonable value like 200 milliseconds.
• ChainRevAccumulativeUrlRetrievalTimeoutMilliseconds This is the aggregate CRL retrieval timeout. If set to 0 or not present the default value of 20 seconds is used. Change this timeout to a value like 500 milliseconds.

There is also a more specific solution for Microsoft signed assemblies (this is from the Biztalk documentation, but applies to any assembly load):

Manually load Microsoft Certificate Revocation lists

When starting a .NET application, the .NET Framework will attempt to download the Certificate Revocation list (CRL) for any signed assembly. If your system does not have direct access to the Internet, or is restricted from accessing the Microsoft.com domain, this may delay startup of BizTalk Server. To avoid this delay at application startup, you can use the following steps to manually download and install the code signing Certificate Revocation Lists on your system.

1. Download the latest CRL updates from http://crl.microsoft.com/pki/crl/products/CodeSignPCA.crl and http://crl.microsoft.com/pki/crl/products/CodeSignPCA2.crl.
2. Move the CodeSignPCA.crl and CodeSignPCA2.crl files to the isolated system.
3. From a command prompt, enter the following command to use the certutil utility to update the local certificate store with the CRL downloaded in step 1: certutil –addstore CA c:\CodeSignPCA.crl

The CRL files are updated regularly, so you should consider setting a reoccurring task of downloading and installing the CRL updates. To view the next update time, double-click the .crl file and view the value of the Next Update field.