0

Bear with me here, ok!!

We use SMO a lot to do all kinds of things, including to check for the presence of particular stored procedures in the database. So we have a method in our data access class called HasProc, which returns a boolean. It's in a part of the application that hasn't been changed for over a year, possibly two years.

Lately, it's been taking ages (10s) to return a value, and I've been trying to pin down why.

It turns out that even defining the variable that will hold the SMO Server (not instantiating it, just defining it) causes a 10s delay in the code arriving into the function.

Here's the relevant bit of the code, which just returns True now, for clarity:

Public Function HasProc(ByVal storedProcName As String) As Boolean
    Dim s As Microsoft.SqlServer.Management.Smo.Server
    Return True
End Function

In Visual Studio 12, stepping through the code using F11, the 10 second delay happens before the code highlight arrives at Public Function etc...

If I comment out the Dim statement, it all runs instantly.

Even more weirdly, if I disable my ethernet adaptor, the delay does not occur.

This is reproducible across three computers. I'm using VS2012, and SMO v11, to which we recently upgraded in order to support SQL Server 2012.

The other thing is that the delay happens even if the Return True statement is before, rather than after the Dim statement.

Any ideas?

ChrisA
  • 4,163
  • 5
  • 28
  • 44

1 Answers1

0

This would happen if the static initializer for that class performs network IO (which is generally a bad idea).

If you pause the debugger during the delay, you can find out exactly what it's doing.

SLaks
  • 868,454
  • 176
  • 1,908
  • 1,964
  • Unfortunately, the only thing the debugger stops on is the line that calls the method containing the SMO call. I created the object that calls the method in a separate line, so it should have been fully instantiated by the time it called HasProc. I enabled the .NET Framework Source stepping option in Tools > Options > Debugging. – ChrisA Mar 17 '13 at 16:25
  • What happens if you pause again while it's frozen? What do you see in the Call Stack window? – SLaks Mar 17 '13 at 16:36
  • Pausing while frozen gets nothing in the call stack between the line that calls the method, and the first line of the method once the delay has elapsed. – ChrisA Mar 17 '13 at 18:59