Search code examples
sqlsql-serverstored-procedureslinked-serversql-view

Need to periodically drop and recreate stored procedure?

Here is my issue. I'm using SQL Server 2014. I have an ASP.Net web-application that accepts a string from a user. That string is passed to a stored procedure (SP), which queries a view, which queries a linked server. My data-access layer returns a datatable to my business object. If there IS data - the inputted string is considered valid (it has a match on the linked server). If there is NO data, the inputted string is considered invalid (there is no match on the linked server).

So simply, here is the path of info:

WebApp - Stored Procedure - View - Linked Server - Table with data

Obviously this path is reversed once the query executes (and the data is passed from the table and ultimately to the web-app).

Here is where it gets weird. Twice in the last 13 months since the SP was created (today being the second time), no data was returned to the web-app when VALID strings were entered. To be clear, this SP has ALWAYS worked (other than these two times). But, once it fails, it NEVER works until it's dropped and recreated.

So, here is what WORKS:

  • Querying the linked server, directly, and adding the string to the WHERE clause.
  • Querying the view, directly, and adding the string to the WHERE clause.

Here is what DOESN'T work:

  • Entering the string into the web-app.
  • Querying the SP, directly, and passing the string as a parameter.

And the really, really weird part:

BOTH TIMES that this has happened, dropping and recreating the stored procedure solved the problem. How is it that a SP will work for months, and then just stop working? And then how come the fix is to drop and recreate the SP?

Firstly, why is this happening? There are lots of other web-apps that call SPs, that call views, that call the SAME linked server that have never failed. But twice in a year ONE SP fails without apparent reason - and deleting and recreating it solved the problem?

Please help - this is really confusing...And please let me know if you need more information.

EDIT

In response to alroc's comment:

  • The script that creates the stored procedure doesn't have anything to do with security. Any user that can access the DB can access the SP.
  • I'm the only DBA here, so no one else "should" be changing any security or permissions on the DB. However, if they were doing that, I would expect these problems elsewhere, since the web-app using the same SQL login (same connection string) that works for every single other process - and there are hundreds of them.

SECOND EDIT

In response to beercohol's comment:

  • The query is not timing out - it returns no results almost instantly. The timeout is set to 30 seconds and this isn't coming close to approaching that threshold.
  • I ran DBCC CheckDB and no errors were found.
  • I will try recompiling the SP next time this happens, but why would the execution plan periodically go bad for ONE SP out of hundreds or thousands?
Solution

  • This problem is solved. Adding WITH RECOMPILE has permanently fixed the issue. I talked to another software developer and he told me that there is a bug within SQL causing cached performance plans to start performing more and more poorly over time. It still doesn't explain why this only happens to SOME stored procedures and not all - but, regardless, forcing them to recompile upon execution works!

    This, however, would not be a viable option for a highly demanded SP, since you don't want to recompile it a gazillion times - but for my purposes it works just fine.