I'm investigating the idea of deploying some not-resource-intensive but known-to-be-long-running stored procedures on SQL Server. By long running, I am thinking in the 10 minute range. The reason they are long running but not resource intensive is due to the fact that they are accessing external systems via C# SQLCLR code, and it is the performance of those external systems that causes the performance problem, SQL Server is spending most of its time just waiting for results from the external system. I 100% realize that doing this type of thing is probably "not recommended" on SQL Server and that something like PowerShell would be more appropriate, but I'm hoping to limit the question to whether doing this is actually physically harmful to overall SQL Server performance/resources, and if so, how harmful. In my scenario, overall server load would not be high, there might be at most 20 other queries running, most of which would be normal queries against SQL tables - at most there might be perhaps 3 users running one of these slow queries. So my question is: are there any real risks with having such queries available on SQL Server, are there any concerns related to blocking, connections, etc that I should be aware of? EDIT : To facilitate discussion, let's say this is running on a 4 CPU, 8GB RAM box. The motivation for doing this (from a business perspective) is that it facilitates using SQL as a common abstraction layer for accessing multiple heterogeneous external systems, thereby removing reliance on numerous end users from having various local proprietary client software installed, or having knowledge of obscure call syntax for the various systems involved. I really hope that people refrain from voting to close this question due to philosophical opinions on whether a person "should" do this or not. Being able to accomplish this using SQL Server is economically valuable. But if it is in fact technically dangerous, then should it not be possible to describe some of the specifics of the danger? EDIT 2 At the request of the moderators I will provide some additional specifics in order to narrow the scope of what it is I am asking. Scenario: I have 20 different systems in my corporate environment. Each of these systems has a specialized API that requires the installation of software to access the system, as well as specialized knowledge of the proper syntax in order to query data within the system. Each system also has an API that can be access via C#. As everyone within the company is very familiar with SQL server, it is economically advantageous to provide a industry-standard API to all of these systems that removes both the requirement to install special software on each client desktop, as well as the end-user requirement to learn the complicated syntax to query each different system. The common API in this case is SQL Server stored procedures (implemented as C# SQL CLR stored procedures, but that is completely transparent to the caller). The interface (parameters) to these procedures are simple and well documented, and completely insulate the user from the complications of the underlying system call implementation and syntax. The execution time of the actual calls to any given underlying system range from less than 1 second to as long as 10 minutes, depending on the specific procedure and system being called. No additional expensive processing is being done within this SQL procedure, the long run time is simply the procedure waiting for the remote system to complete the query and return the results. Typical resultset size would be from 1 to 50,000 rows, with the average probably being somewhere around 1000 rows. A very large resultset would be 5 MB in size. At any given time, there might be a maximum of 25 concurrently executing queries active on this server, a minimum of 20 of those queries would be executing "regular" TSQL stored procedures reading normal SQL Server tables resident in the local database, whereas a maximum of 5 of them might be these "special" C# SQL CLR queries accessing external systems. All queries are reads, no writes, and there are no transactional, multi-command queries being executed. 25 maximum concurrent queries x 5 MB maximum resultset size = at most 125 MB of "pure data" in memory at any given time, plus any accompanying SQL Server "overhead". A typical server this would run on would be a 4 CPU, 8GB RAM box running SQL Server 2012. I have extreme leeway to massively increase the power of this box if I so choose - there are no budgetary constraints in this scenario. So, considering this scenario, does anyone know of any specific technical reasons why this implementation would not work, or legitimate speculation on where some technical limitations might arise? As an aside....I don't know if anyone ever listened to the StackoverFlow podcast that Jeff and Joel did back while SO was first being developed, but this question is somewhat reminiscent of the anecdote Joel told where he was asking about doing something unusual in SQL Server (for a very specific but valid reason), and all of the answers were essentially "you shouldn't be doing that!" :) References for those who may be 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 the just-inserted row Similarly, I certainly realize this question is highly unusual, but I don't think it should be that controversial if considered from a purely technical perspective. Mods : Please let me know if this additional detail is sufficient to minimize misunderstanding. I really hope this question can remain open as it is well-intentioned, legitimate, and involves what I think is a very interesting edge-case of SQL Server platform capability.

Given that in all cases: Access is only to external systems Access is read-only then, conceptually, there should be no specific inherent dangers to the stability of SQL Server. HOWEVER, there are several things to consider and/or watch out for: A C# API means a DLL that you add as a Reference to your project, right? This 3rd party DLL will need to be loaded into SQL Server along with your DLL. This is where things get tricky. It is safe to assume that the DLL will communicate over the network to other servers, hence it will need to be marked WITH PERMISSION_SET = EXTERNAL_ACCESS at the very least. Ideally you would sign the 3rd party DLL with the same .pfx file that you are (hopefully) using for your assembly. If the 3rd party DLL is already signed, I think you can re-sign it. Does this 3rd party DLL properly clean up its external resources? Opening external resources and not properly disposing of them can lead to problems. GC might clean up orphaned external file and network handles, but I believe that happens when the class has no more references, and I am not sure how that is affected by the fact that the main class containing the SQLCLR methods will remain active until the App Domain is unloaded. If the API DLL is making HTTP connections, it will be limited to how many connections can be made to a particular URI. While this might not be an issue for Windows app or Console apps (since they all have their own App Domain), it is an issue within SQLCLR due to there being only a single App Domain (for a particular Assembly) that is shared across all sessions (i.e. SPIDs). The default limit is 2 connections to a given URI. If you have 3 sessions hitting the same API call at the same time, the 3rd will either be blocked or get an exception. Fortunately, this is configurable via the ServicePointManager.DefaultConnectionLimit property. Is the API DLL using any methods that are marked with a Host Protection Attribute (HPA)? You will find out either when you load the DLL via CREATE ASSEMBLY or if your code calls an API method that is using something marked with an HPA. If so, you will need to mark the API DLL and your assembly as UNSAFE . The risk here depends on the particular HPAs. For example, doing TimeZone conversions via TimeZoneInfo has a potential memory leak, hence it is marked with MayLeakOnAbort . Is the API DLL storing values in static class variables? SQL Server will let you know if the DLL does this when you issue CREATE ASSEMBLY without setting the assembly to UNSAFE . While setting the assembly to UNSAFE will allow it to work, you now have the potential that multiple sessions running code that accesses a static variable will experience "odd" behavior and might get incorrect results. The issue being, again, that there is a single App Domain that is shared for all sessions, hence why UNSAFE is required in order to make use of static variables. The only thing that can be done to mitigate this issue is to put a lock around any API call that makes use of a static variable. BUT, this isn't an absolute fix since the static variable might be used across different API calls. A stored value might make sense for one particular session to keep using, but incorrect between sessions. Does the API DLL reference any libraries that are not in the Supported .NET Framework Libraries list? If so, you will need to load those into SQL Server as well. BUT, keep in mind that you are not guaranteed to be able to load any unsupported .NET Framework DLL into SQL Server. SQL Server only allows pure assemblies (i.e. managed code only). DLLs that are mixed (managed and unmanaged C++ code) cannot be loaded. And even if an unsupported Framework DLL is pure and can be loaded now, that might change in a future version of .NET. And this has, in fact, happened before. The scope of risk is any Framework update that is using CLR version 4.0 (which is what SQL Server 2012, 2014, and 2016 are bound to). Is the API DLL pure (managed code only) or mixed (managed and unmanaged C++)? If the 3rd party DLL is mixed, then you won't be able to load it anyway. IF, however, you are dealing with a Web Service API, then the concerns are greatly reduced. You need to use EXTERNAL_ACCESS You need to make sure to properly Dispose() of all objects that can be diposed. You need to set the ServicePointManager.DefaultConnectionLimit property. Again, for any particular method there is a single App Domain that is shared across all sessions. The default value for DefaultConnectionLimit is 2, which is likely not enough since the same URI (for a given API call) will be accessed across multiple sessions. One concern about SQL Server health is the potential for SQLCLR code to lock up a scheduler so that it cannot do anything else until this one process finishes. This is due to SQL Server using preemptive multitasking which requires threads to give themselves up to be put on hold. If your SQL Server code executes a query then this is not an issue, but if it is just waiting on a response from an external resource, then this potential exists. I have not personally seen a scheduler get locked up by a SQLCLR process, but it is still possible so it would be a good idea to try to "play nice" with SQL Server. If it is possible to make an async call to the API code, then you can use a Timer to call Thread.Sleep(0); every 10 or 100 milliseconds (or something like that), until the external process returns. Calling Thread.Sleep(0); is how the SQLCLR code lets SQLOS know that it (i.e. the SQLCLR process) can be put on hold. Doing any type of asynchronous work in SQLCLR requires that the assembly be marked as UNSAFE . Chances are likely that, for the various other reasons already mentioned, your assemblies will already be marked as UNSAFE . But even if not, then if this were the only reason to mark them as UNSAFE , then it is still worth it, especially with this being an in-house project. One option that can help mitigate stability issues related to UNSAFE SQLCLR code (at least for the main SQL Server process) is to quarantine it in a separate SQL Server instance. For example, you could run a separate instance of SQL Server express that does nothing other than handle these API calls. As long as you specify a Max Server Memory that the SQL Server Express instance is allowed to use, then a memory leak won't affect the main SQL Server instance, just the Express instance. And if a Scheduler is locked up by a long-running, external process, then it again only affects the Express instance. Then you just set up a Linked Server from the main instance to the Express instance so that users can work on the main instance and JOIN to any tables there, etc Finally, from a usability standpoint, consider making these SQLCLR objects Table-Valued Functions instead of Stored Procedures. It will be far easier to integrate their results. Not only would you be able to JOIN, but if there are cases when someone doesn't want the entire result set, it is easy to add a WHERE clause than to dump everything to a temp table (via INSERT...EXEC ) only to delete unwanted rows.

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

Solomon Rutzky

Reputation: 48826

Given that in all cases:

Access is only to external systems
Access is read-only

then, conceptually, there should be no specific inherent dangers to the stability of SQL Server. HOWEVER, there are several things to consider and/or watch out for:

A C# API means a DLL that you add as a Reference to your project, right? This 3rd party DLL will need to be loaded into SQL Server along with your DLL. This is where things get tricky. It is safe to assume that the DLL will communicate over the network to other servers, hence it will need to be marked WITH PERMISSION_SET = EXTERNAL_ACCESS at the very least.
- Ideally you would sign the 3rd party DLL with the same .pfx file that you are (hopefully) using for your assembly. If the 3rd party DLL is already signed, I think you can re-sign it.
- Does this 3rd party DLL properly clean up its external resources? Opening external resources and not properly disposing of them can lead to problems. GC might clean up orphaned external file and network handles, but I believe that happens when the class has no more references, and I am not sure how that is affected by the fact that the main class containing the SQLCLR methods will remain active until the App Domain is unloaded.
- If the API DLL is making HTTP connections, it will be limited to how many connections can be made to a particular URI. While this might not be an issue for Windows app or Console apps (since they all have their own App Domain), it is an issue within SQLCLR due to there being only a single App Domain (for a particular Assembly) that is shared across all sessions (i.e. SPIDs). The default limit is 2 connections to a given URI. If you have 3 sessions hitting the same API call at the same time, the 3rd will either be blocked or get an exception. Fortunately, this is configurable via the ServicePointManager.DefaultConnectionLimit property.
- Is the API DLL using any methods that are marked with a Host Protection Attribute (HPA)? You will find out either when you load the DLL via CREATE ASSEMBLY or if your code calls an API method that is using something marked with an HPA. If so, you will need to mark the API DLL and your assembly as UNSAFE. The risk here depends on the particular HPAs. For example, doing TimeZone conversions via TimeZoneInfo has a potential memory leak, hence it is marked with MayLeakOnAbort.
- Is the API DLL storing values in static class variables? SQL Server will let you know if the DLL does this when you issue CREATE ASSEMBLY without setting the assembly to UNSAFE. While setting the assembly to UNSAFE will allow it to work, you now have the potential that multiple sessions running code that accesses a static variable will experience "odd" behavior and might get incorrect results. The issue being, again, that there is a single App Domain that is shared for all sessions, hence why UNSAFE is required in order to make use of static variables. The only thing that can be done to mitigate this issue is to put a lock around any API call that makes use of a static variable. BUT, this isn't an absolute fix since the static variable might be used across different API calls. A stored value might make sense for one particular session to keep using, but incorrect between sessions.
- Does the API DLL reference any libraries that are not in the Supported .NET Framework Libraries list? If so, you will need to load those into SQL Server as well. BUT, keep in mind that you are not guaranteed to be able to load any unsupported .NET Framework DLL into SQL Server. SQL Server only allows pure assemblies (i.e. managed code only). DLLs that are mixed (managed and unmanaged C++ code) cannot be loaded. And even if an unsupported Framework DLL is pure and can be loaded now, that might change in a future version of .NET. And this has, in fact, happened before. The scope of risk is any Framework update that is using CLR version 4.0 (which is what SQL Server 2012, 2014, and 2016 are bound to).
- Is the API DLL pure (managed code only) or mixed (managed and unmanaged C++)? If the 3rd party DLL is mixed, then you won't be able to load it anyway.
IF, however, you are dealing with a Web Service API, then the concerns are greatly reduced.
- You need to use EXTERNAL_ACCESS
- You need to make sure to properly Dispose() of all objects that can be diposed.
- You need to set the ServicePointManager.DefaultConnectionLimit property. Again, for any particular method there is a single App Domain that is shared across all sessions. The default value for DefaultConnectionLimit is 2, which is likely not enough since the same URI (for a given API call) will be accessed across multiple sessions.
One concern about SQL Server health is the potential for SQLCLR code to lock up a scheduler so that it cannot do anything else until this one process finishes. This is due to SQL Server using preemptive multitasking which requires threads to give themselves up to be put on hold. If your SQL Server code executes a query then this is not an issue, but if it is just waiting on a response from an external resource, then this potential exists. I have not personally seen a scheduler get locked up by a SQLCLR process, but it is still possible so it would be a good idea to try to "play nice" with SQL Server. If it is possible to make an async call to the API code, then you can use a Timer to call Thread.Sleep(0); every 10 or 100 milliseconds (or something like that), until the external process returns. Calling Thread.Sleep(0); is how the SQLCLR code lets SQLOS know that it (i.e. the SQLCLR process) can be put on hold.

Doing any type of asynchronous work in SQLCLR requires that the assembly be marked as UNSAFE. Chances are likely that, for the various other reasons already mentioned, your assemblies will already be marked as UNSAFE. But even if not, then if this were the only reason to mark them as UNSAFE, then it is still worth it, especially with this being an in-house project.
One option that can help mitigate stability issues related to UNSAFE SQLCLR code (at least for the main SQL Server process) is to quarantine it in a separate SQL Server instance. For example, you could run a separate instance of SQL Server express that does nothing other than handle these API calls. As long as you specify a Max Server Memory that the SQL Server Express instance is allowed to use, then a memory leak won't affect the main SQL Server instance, just the Express instance. And if a Scheduler is locked up by a long-running, external process, then it again only affects the Express instance.

Then you just set up a Linked Server from the main instance to the Express instance so that users can work on the main instance and JOIN to any tables there, etc
Finally, from a usability standpoint, consider making these SQLCLR objects Table-Valued Functions instead of Stored Procedures. It will be far easier to integrate their results. Not only would you be able to JOIN, but if there are cases when someone doesn't want the entire result set, it is easy to add a WHERE clause than to dump everything to a temp table (via INSERT...EXEC) only to delete unwanted rows.

Upvotes: 3

usr

Reputation: 171178

This is not a problem by itself. Execution will be reliable based on what you described. "Reliable" in the sense that although this is not best practice it will work and not cause problems randomly.

Some problems come to mind:

If the SQL Server instance goes down (failover, crash, reboot, ...) the procedure aborts mid-way. You must be OK with that. 10min is a long time window so the chance is high.
There are probably limits to how many such executions you can run at the same time. I'm not aware of the particular limits. They do not apply if N = 3.
Long-running transactions cause log growth and can lead to nasty blocking.

None of these concerns destabilize the instance just by their mere existence. These are totally normal things that are just magnified by the 10min duration.

I don't know why some of the comments are so agitated. 10min queries are common in data warehousing and those queries are putting the server under 100% load which is worse than 10min of waiting. No problem here.

If you disagree please leave a comment stating the reason. Please state what exactly causes problems when you issue web service calls from SQL Server.

Upvotes: 1

Bwire

Reputation: 1201

Use the right tool for the right job. In your test environment everything will work well but as soon as you leave that environment and move to production you will be fried.

The database as your primary store of data should be highly available and those not resource intensive tasks will always multiply quickly depending on the number of concurrent connections being made. As you design the system keep in mind the end users.

Upvotes: -3

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

Answers (3)

Related Questions