SQL Stored Procedure Throws Error every 25 minutes

Question

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 procedure itself that is causing the issue or if it could be clashing with something else happening on the server or if the code in the application is possibly erroring out. It's worked well until this week. We made a change to the application this week and added new codes to the ADT_Diagnosis table. Since the addition of the codes, it's steadily been throwing an error. All tips and suggestions are appreciated. Thanks in advance!

This is the error that it sends me:

Error Occured on the Nursing Bed Service. ”System.Data.SqlClient.SqlException (0x80131904): Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning() at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlDataReader.ConsumeMetaData() at System.Data.SqlClient.SqlDataReader.get_MetaData() at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader() at Service.MainWindow.UpdateDataFromADT_Codes(SqlConnection SqlConn) at Service.MainWindow.TaskThread_DoWork(Object sender, DoWorkEventArgs e)

Here is the SQL stored procedure:

ALTER PROCEDURE GetActivePatientCoreMeasures 
   -- Add the parameters for the stored procedure here
AS
BEGIN
   -- SET NOCOUNT ON added to prevent extra result sets from
   -- interfering with SELECT statements.
   SET NOCOUNT ON;

SELECT DISTINCT ADT.[PV1 Room],adt.[Patient Account Number], 
    [Diagnosis Codes].Description
FROM [CPSISQL].dbo.ADT
LEFT JOIN [CPSISQL].dbo.ADT_Diagnosis 
ON [CPSISQL].dbo.ADT.[Patient Account Number] =
    [CPSISQL].dbo.ADT_Diagnosis.[Patient Account #]
RIGHT JOIN [Diagnosis Codes]
ON ADT_Diagnosis.[Diagnosis Code] = [Diagnosis Codes].Code
WHERE [CPSISQL].dbo.ADT.[PV1 Discharge Date/Time] = ''
AND
(
[CPSISQL].dbo.ADT.[PV1 Department] = '028' OR
[CPSISQL].dbo.ADT.[PV1 Department] = '030' OR
[CPSISQL].dbo.ADT.[PV1 Department] = '032' OR
[CPSISQL].dbo.ADT.[PV1 Department] = '038' OR
[CPSISQL].dbo.ADT.[PV1 Department] = '042' 
)
AND NOT(ADT_Diagnosis.[Diagnosis Type] = 'A')
ORDER BY adt.[PV1 Room]
END

Answer 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.

Answer 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.

Answer 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.

SELECT DISTINCT adt.[PV1 Room],adt.[Patient Account Number], codes.Description
FROM [CPSISQL].dbo.ADT adt
INNER JOIN [CPSISQL].dbo.ADT_Diagnosis diag
  ON adt.[Patient Account Number] = diag.[Patient Account #]
INNER JOIN [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'
ORDER BY adt.[PV1 Room]

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