This project is read-only.
1
Vote

Timeout error when running the Blocking and Wait Statistics report

description

I successfully imported all the collected data. I selected a very broad time range from the parameters window and clicked "Ok". After 30 seconds I received this error in the log file:
 
SQLNexus Critical: 0 : Exception: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.. Source: .Net SqlClient Data Provider. Stack: 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(TdsParserStateObject stateObj)
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.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)
at sqlnexus.fmNexus.RefreshReport(LocalReport report, ReportViewer rv) in C:\temp\nexus\sqlnexus_codeplex\sqlnexus\fmNexus.cs:line 1012. InnerException: .
DateTime=2011-02-07T20:23:39.3509960Z

comments

amitban wrote Feb 8, 2011 at 12:03 PM

This is currently by design. The reports have a timeout setting of 30 seconds. If the SQL queries defined in the DataSets of the report cannot fetch the data within 30 seconds, the report will timeout. Are you seeing this behavior of version 3.0 of SQL Nexus as well?

gsprague wrote Feb 8, 2011 at 10:22 PM

Yes. I'm currently using 3.0 of SQL Nexus. Is this timeout parameter something I can change on my own, or do I need a patch?

amitban wrote Feb 10, 2011 at 6:09 PM

I stand corrected. Just got this verified. The datasets which fetch the data have a timeout of 2 minutes. Are you seeing this behavior for a specific report? If yes, which report. And can you also check if the SQL instance on which the Nexus database resides on doesn't have any non-default query timeout values set. This can be verified by looking at the sp_configure output.

amitban wrote Feb 10, 2011 at 7:18 PM

There are some reports where we don't expect it the dataset in the report to take a long time, those non-core reports have a 30-second timeout. The core SQL Nexus reports have a default timeout of 2 minutes. So you shouldn't be seeing a 30-second timeout there.

gsprague wrote Feb 10, 2011 at 8:07 PM

After the data imported successfully, the very next thing I did was to click on the Blocking and Wait Statistics report. The report opened find but the time range was set to a very small increment like less than 1 hour). Then I clicked on the Parameters button at the top of the page and used the slider to select my 14 hour window. Upon clicking Ok from the parameters window, the report runs for 30 seconds (i timed it myself) and then I get an error in the UI and the log file has the exception that I already pasted into the original description.

The "query wait" setting on the host where the SQL Nexus database is located is set to the max value - 2147483647.

Let me know if there is anything else you need.

Looking forward to a resolution!

ovc wrote Oct 25, 2011 at 5:36 AM

Hello. I have the same issue while trying to view details of a query. This happens also in 3.0.
Are there now available some options to set the timeout parameters?

amitban wrote Oct 25, 2011 at 5:50 AM

You can reduce the time range for which the data is being fetched using the Parameters tab in SQL Nexus.

wrote Feb 22, 2013 at 12:49 AM