Independent string routine inside SQL Server CLR DLL appears to be single-threaded by default

a string routine inside clr dll has no dll globals except compiled readonly regex variables, appears be single-threaded.  declaration of routine follows:

[system.security.permissions.permissionsetattribute(system.security.permissions.securityaction.inheritancedemand, name = "fulltrust")]

[system.security.permissions.permissionsetattribute(system.security.permissions.securityaction.linkdemand, name = "fulltrust")]

[microsoft.sqlserver.server.sqlfunction(isdeterministic = true, systemdataaccess = systemdataaccesskind.read, dataaccess = dataaccesskind.read)]

[return: sqlfacet(maxsize = -1)]

public static string fnparsetracequerytextnew([sqlfacet(maxsize = -1)] string strquerytext, bool busequeryparameters, int imaxoutputlength, int ieventclass,

                     string strobjectname, string strservername, int idatabaseid, int iobjectid)

i pass in string information , return manipulated string.  the dataaccesskind.read entry required handle special situation not occur often.  the result more mgmt studio windows use access routine, slower each thread sum being approximately equal of single-threaded performance.  i have specified external permission level , generate serialization assembly option set auto because not seem apply situation.

the function declaration sql follows:

create function [dbo].[parsetracequerytext_s](@querytext nvarchar(max), @usequeryparameters bit, @maxoutputlength int, @eventclass int, @objectname nvarchar(128),

                                                                                  @strservername nvarchar(128), @intdatabaseid int, @intobjectid int)

returns nvarchar(max)

as external name [parsetracequerytext].[parsetracequerytextns.parsetracequerytext].[fnparsetracequerytextnew]

and assembly declaration follows:

create assembly [parsetracequerytext] from @dllname with permission_set = external_access

i must have missed something, have no idea is.  i appreciate guidance regarding problem.  i have dual-core machine , can keep 1 of cores busy.  i keep both cores busy possible because there tremendous amount of work done , processor-intensive work, cut elapsed time down substantially if second thread moving through quickly.

 

unless write multi-threaded, unsafe , result in non-yielding scheduler problems if incorrectly, won't use multiple threads.

---

jonathan kehayias
http://sqlblog.com/blogs/jonathan_kehayias/
http://www.twitter.com/sqlsarg
http://www.sqlclr.net/
please click mark answer button if post solves problem!

SQL Server  >  .NET Framework inside SQL Server