sqlclr - How dangerous (performance/resources) is it to have long running (~10 minute) stored procedures on SQL Server? -

i'm investigating idea of deploying not-resource-intensive known-to-be-long-running stored procedures on sql server. long running, thinking in 10 minute range.

the reason long running not resource intensive due fact accessing external systems via c# sqlclr code, , performance of external systems causes performance problem, sql server spending of time waiting results external system.

i 100% realize doing type of thing "not recommended" on sql server , powershell more appropriate, i'm hoping limit question whether doing actually physically harmful overall sql server performance/resources, , if so, how harmful.

in scenario, overall server load not high, there might @ 20 other queries running, of normal queries against sql tables - @ there might perhaps 3 users running 1 of these slow queries.

so question is: there real risks having such queries available on sql server, there concerns related blocking, connections, etc should aware of?

edit:

to facilitate discussion, let's running on 4 cpu, 8gb ram box.

the motivation doing (from business perspective) facilitates using sql common abstraction layer accessing multiple heterogeneous external systems, thereby removing reliance on numerous end users having various local proprietary client software installed, or having knowledge of obscure call syntax various systems involved.

i hope people refrain voting close question due philosophical opinions on whether person "should" or not. being able accomplish using sql server economically valuable. if in fact technically dangerous, should not possible describe of specifics of danger?

edit 2 @ request of moderators provide additional specifics in order narrow scope of asking.

scenario:

i have 20 different systems in corporate environment. each of these systems has specialized api requires installation of software access system, specialized knowledge of proper syntax in order query data within system. each system has api can access via c#.

as within company familiar sql server, economically advantageous provide industry-standard api of these systems removes both requirement install special software on each client desktop, end-user requirement learn complicated syntax query each different system. common api in case sql server stored procedures (implemented c# sql clr stored procedures, transparent caller). interface (parameters) these procedures simple , documented, , insulate user complications of underlying system call implementation , syntax.

the execution time of actual calls given underlying system range less 1 second long 10 minutes, depending on specific procedure , system being called. no additional expensive processing being done within sql procedure, long run time procedure waiting remote system complete query , return results.

typical resultset size 1 50,000 rows, average being somewhere around 1000 rows. very large resultset 5 mb in size.

at given time, there might maximum of 25 concurrently executing queries active on server, minimum of 20 of queries executing "regular" tsql stored procedures reading normal sql server tables resident in local database, whereas maximum of 5 of them might these "special" c# sql clr queries accessing external systems.

all queries reads, no writes, , there no transactional, multi-command queries being executed.

25 maximum concurrent queries x 5 mb maximum resultset size = @ 125 mb of "pure data" in memory @ given time, plus accompanying sql server "overhead".

a typical server run on 4 cpu, 8gb ram box running sql server 2012. have extreme leeway massively increase power of box if choose - there no budgetary constraints in scenario.

so, considering this scenario, know of specific technical reasons why implementation not work, or legitimate speculation on technical limitations might arise?

as aside....i don't know if ever listened stackoverflow podcast jeff , joel did while first being developed, question reminiscent of anecdote joel told asking doing unusual in sql server (for specific valid reason), , of answers "you shouldn't doing that!" :)

references may interested:

https://blog.stackexchange.com/2009/01/podcast-38/

https://stackoverflow.fogbugz.com/default.asp?pg=pgwiki&command=view&ixwikipage=29025

sql server "after insert" trigger doesn't see just-inserted row

similarly, realize question highly unusual, don't think should controversial if considered purely technical perspective.

mods: please let me know if additional detail sufficient minimize misunderstanding. hope question can remain open well-intentioned, legitimate, , involves think interesting edge-case of sql server platform capability.

given in cases:

• access external systems
• access read-only

then, conceptually, there should no specific inherent dangers stability of sql server. however, there several things consider and/or watch out for:

• a c# api means dll add reference project, right? 3rd party dll need loaded sql server along dll. things tricky. safe assume dll communicate on network other servers, hence need marked with permission_set = external_access @ least.

  • ideally sign 3rd party dll same .pfx file (hopefully) using assembly. if 3rd party dll signed, think can re-sign it.
  • does 3rd party dll clean external resources? opening external resources , not disposing of them can lead problems. gc might clean orphaned external file , network handles, believe happens when class has no more references, , not sure how affected fact main class containing sqlclr methods remain active until app domain unloaded.
  • if api dll making http connections, limited how many connections can made particular uri. while might not issue windows app or console apps (since have own app domain), issue within sqlclr due there being single app domain (for particular assembly) shared across sessions (i.e. spids). default limit 2 connections given uri. if have 3 sessions hitting same api call @ same time, 3rd either blocked or exception. fortunately, configurable via servicepointmanager.defaultconnectionlimit property.
  • is api dll using methods marked host protection attribute (hpa)? find out either when load dll via create assembly or if code calls api method using marked hpa. if so, need mark api dll , assembly unsafe. risk here depends on particular hpas. example, doing timezone conversions via timezoneinfo has potential memory leak, hence marked mayleakonabort.
  • is api dll storing values in static class variables? sql server let know if dll when issue create assembly without setting assembly unsafe. while setting assembly unsafe allow work, have potential multiple sessions running code accesses static variable experience "odd" behavior , might incorrect results. issue being, again, there single app domain shared sessions, hence why unsafe required in order make use of static variables. thing can done mitigate issue put lock around api call makes use of static variable. but, isn't absolute fix since static variable might used across different api calls. stored value might make sense 1 particular session keep using, incorrect between sessions.
  • does api dll reference libraries not in supported .net framework libraries list? if so, need load sql server well. but, keep in mind not guaranteed able load unsupported .net framework dll sql server. sql server allows pure assemblies (i.e. managed code only). dlls mixed (managed , unmanaged c++ code) cannot loaded. , if unsupported framework dll pure , can loaded now, might change in future version of .net. , has, in fact, happened before. scope of risk framework update using clr version 4.0 (which sql server 2012, 2014, , 2016 bound to).
  • is api dll pure (managed code only) or mixed (managed , unmanaged c++)? if 3rd party dll mixed, won't able load anyway.

• if, however, dealing web service api, concerns reduced.

  • you need use external_access
  • you need make sure dispose() of objects can diposed.
  • you need set servicepointmanager.defaultconnectionlimit property. again, particular method there single app domain shared across sessions. default value defaultconnectionlimit 2, not enough since same uri (for given api call) accessed across multiple sessions.

• one concern sql server health potential sqlclr code lock scheduler cannot else until 1 process finishes. due sql server using preemptive multitasking requires threads give put on hold. if sql server code executes query not issue, if waiting on response external resource, potential exists. have not seen scheduler locked sqlclr process, still possible idea try "play nice" sql server. if possible make async call api code, can use timer call thread.sleep(0); every 10 or 100 milliseconds (or that), until external process returns. calling thread.sleep(0); how sqlclr code lets sqlos know (i.e. sqlclr process) can put on hold.

  doing type of asynchronous work in sqlclr requires assembly marked unsafe. chances that, various other reasons mentioned, assemblies marked unsafe. if not, if reason mark them unsafe, still worth it, being in-house project.

• one option can mitigate stability issues related unsafe sqlclr code (at least main sql server process) quarantine in separate sql server instance. example, run separate instance of sql server express nothing other handle these api calls. long specify max server memory sql server express instance allowed use, memory leak won't affect main sql server instance, express instance. , if scheduler locked long-running, external process, again affects express instance.

  then set linked server main instance express instance users can work on main instance , join tables there, etc

• finally, usability standpoint, consider making these sqlclr objects table-valued functions instead of stored procedures. far easier integrate results. not able join, if there cases when doesn't want entire result set, easy add where clause dump temp table (via insert...exec) delete unwanted rows.