Sql Stored Procedure Throws Error Every 25 Minutes

I have a stored procedure that throws an error every 25 minutes. I've got code in the application that sends me an email when it fails. I'm trying to nail down if it is the proce

Solution 1:

You need to rule out connectivity issues. See SQL Connection Error: System.Data.SqlClient.SqlException (0x80131904).

If that doesn't solve the issue, run the stored procedure manually from SSMS (SQL Server Management Studio), preferably as soon as the error happens, and see how long it takes to come back. The default command timeout in VB.NET is 30 seconds, so if it takes longer from SSMS then it means you have to either increase the timeout (see Set custom default CommandTimeout for all new Command Objects), or make your stored procedure faster.

To make you stored procedure faster, run it again from SSMS, but this time make sure "Include Actual Execution Plan" is enabled. This will tell you what operations in the query plan are taking longer and can even suggest what indexes to add to your table to improve performance.

Solution 2:

First, check to ensure that you have a connection to the SQL server from the machine running the application (don't forget to check the config file being used on the application).

It could also be some type of network block, such as a firewall or a different connection type that needs to be configured (named-pipes vs. tcp/ip). You can check the SQL Server's configuration through SQL Serve Configuration Manager.

Solution 3:

Without knowing more about your database, and the tables involved it is hard to diagnose your problem. However, the query in the stored procedure is oddly written, and may be the cause of performance problems. You don't know how many times I've rewritten somebody's ugly query and all of the sudden things start working much better. So I'd fix that.

There are a few things that just don't make sense:

• What is the point of the DISTINCT. I don't know the structure of your database, but it could be that if you take that off you'll see that you're returning millions of rows because there's something wrong with your joins (and there definitely is something wrong with your joins).
• There is a LEFT JOIN to ADT_Diagnosis, yet ADT_Diagnosis is also used in a required predicate in the WHERE clause. So if no rows are matched to ADT from ADT_Diagnosis then that predicate is automatically false, and no row will be returned at all. You might be thinking != 'A" would allow NULL values, but NULL is neither = 'A' or != 'A' so it is automatically false.
• There is a RIGHT JOIN to *Diagnosis Codes. It does not seem likely that somebody would want a Description returned without a matching row in ADT_Diagnosis. That could lead to a big mistake. This is all academic since the predicate mentioned in the previous point will prevent any rows from returning. Still, it may be confusing to the query planner.
• NOT(ADT_Diagnosis.[Diagnosis Type] = 'A') is less confusingly written as ADT_Diagnosis.[Diagnosis Type] != 'A'.
• Instead of repeating OR several times, use IN.
• Bonus: Feel free to use shorter aliases instead of table names. It will make the query easier to read.

Here's the query rewritten taking into account the above points. Because the predicate involving ADT_Diagnosis requires that a row is returned from that table, I changed the joins from LEFT and RIGHT to INNER, since that table is on the right and left respectively. This addresses the second and third point. The multiple 'OR' occurrences were removed and replaced with an IN.

I did not remove the DISTINCT because I don't know enough about these tables. I have seen many instances where DISTINCT was used to fix the result from a query with bad joins, so it may no longer be necessary.

SELECTDISTINCT adt.[PV1 Room],adt.[Patient Account Number], codes.Description
FROM [CPSISQL].dbo.ADT adt
INNERJOIN [CPSISQL].dbo.ADT_Diagnosis diag
  ON adt.[Patient Account Number] = diag.[Patient Account #]
INNERJOIN [Diagnosis Codes] codes ON diag.[Diagnosis Code] = codes.Code
WHERE adt.[PV1 Discharge Date/Time] =''AND adt.[PV1 Department] IN ('028','030','032', '038', '042')
  AND diag.[Diagnosis Type] !='A'ORDERBY adt.[PV1 Room]

This should return the exact same result, but hopefully (and likely) with a much better plan.