View timed out every now and then

[fniles]

We are using SQL Server 2005 and VB.NET 2008.
We have a view that is called by the application every minute.
The view is doing a SELECT of 2 more views and a table.
When I run the view on the SQL Server Management Studio, it runs for 2
seconds.
Most of the time when the view is run from the VB.NET program it runs
as fast.
But, about once or twice a day in the program the view will timed out
(error "Timeout expired. The timeout period elapsed prior to
completion of the operation or the server is not responding.), even
after I set the command time out to 45 seconds (which is a lot longer
than the 2 seconds it takes to run it on the SSMS).

Why would the same SELECT view runs fast most of the time, but very
slow once or twice a day ?

SQL Server machine has 8 processors of 2993 Mhz each and 32 MB of
RAM.
Right now the available physical memory there is 18.48 GB, and SQL
Server is using 9-50% of CPU and 11,358,080K of RAM. It has 32
database, but only 5 are very active databases.

Dim m_daSQL As SqlClient.SqlDataAdapter
Dim m_dsSQL As DataSet
Dim m_cmdSQL As SqlClient.SqlCommand

m_cmdSQL = New SqlClient.SqlCommand
With m_cmdSQL
.Connection = adoCon
.CommandText = sSQL
End With
m_daSQL = New SqlClient.SqlDataAdapter
m_dsSQL = New DataSet
m_daSQL.SelectCommand = m_cmdSQL
m_cmdSQL.CommandTimeout = iTimeOut
m_daSQL.Fill(m_dsSQL) ----->>>> Timed out here

This is the stack of where the view timed out in the program:
System.Data.SqlClient.SqlConnection.OnError(SqlException exception,
Boolean breakConnection)
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException
exception, Boolean breakConnection)

System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject
stateObj)
System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior,
SqlCommand cmdHandler, SqlDataReader dataStream,
BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject
stateObj)
System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
System.Data.SqlClient.SqlDataReader.get_MetaData()
System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader
ds, RunBehavior runBehavior, String resetOptionsString)
System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior
cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean
async)
System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior
cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String
method, DbAsyncResult result)
System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior
cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String
method)
System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior
behavior, String method)
System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior
behavior)

System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior
behavior)
System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset,
DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String
srcTable, IDbCommand command, CommandBehavior behavior)
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32
startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
CommandBehavior behavior)
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)

 

[Erland Sommarskog]

We are using SQL Server 2005 and VB.NET 2008.
We have a view that is called by the application every minute.
The view is doing a SELECT of 2 more views and a table.
When I run the view on the SQL Server Management Studio, it runs for 2
seconds.
Most of the time when the view is run from the VB.NET program it runs
as fast.
But, about once or twice a day in the program the view will timed out
(error "Timeout expired. The timeout period elapsed prior to
completion of the operation or the server is not responding.), even
after I set the command time out to 45 seconds (which is a lot longer
than the 2 seconds it takes to run it on the SSMS).

Why would the same SELECT view runs fast most of the time, but very
slow once or twice a day ?

First of all, views cannot time out. A view is just a query. That's
all there is. There is no execution plan or anything saved for the view.
And the time out happens in the client application, that grows tired of
waiting.

As for the cause, the most likely reason at hand, I would say is blocking.


--
Erland Sommarskog, SQL Server MVP, (e-mail address removed)

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

 

[fniles]

Yes, you are correct, Erland.
That's what I meant, when I ran the query on the VB.NET program I got
the timeout error, even though the same query will run fine most of
the times.
That's what I thought also.
So, even though the query is a SELECT statement (joining a few
tables), something else can block the SELECT statement to run ?
Will it be another SELECT statement or INSERT that's blocking it ?
How can I find out about it ?
Is it by running this query ?
SELECT
database_id,sql_handle,session_id ,status ,blocking_session_id,wait_type ,wait_time ,wait_resource,transaction_id
FROM sys.dm_exec_requests WHERE status = N'suspended';
If that's the way, the problem is when I run it most of the time, it
returns nothing.

Thanks, Erland.

 

[Erland Sommarskog]

That's what I thought also.
So, even though the query is a SELECT statement (joining a few
tables), something else can block the SELECT statement to run ?
Will it be another SELECT statement or INSERT that's blocking it ?

It would have to be a process that perfoms an update of some sort.

How can I find out about it ?
Is it by running this query ?
SELECT
database_id,sql_handle,session_id ,status ,blocking_session_id,wait_type
,wait_time ,wait_resource,transaction_id
FROM sys.dm_exec_requests WHERE status = N'suspended';
If that's the way, the problem is when I run it most of the time, it
returns nothing.

You need to run it when it's happening - and you may not be there to
do it. One way to overcome that particular problem is to make use of
the "blocked process threshold" report. I'm not going into details here,
but search for "blocked process threshold" and you will find some
interesting stuff.



--
Erland Sommarskog, SQL Server MVP, (e-mail address removed)

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

 

[Jason Keats]

Dim m_daSQL As SqlClient.SqlDataAdapter
Dim m_dsSQL As DataSet
Dim m_cmdSQL As SqlClient.SqlCommand

m_cmdSQL = New SqlClient.SqlCommand
With m_cmdSQL
.Connection = adoCon
.CommandText = sSQL
End With
m_daSQL = New SqlClient.SqlDataAdapter
m_dsSQL = New DataSet
m_daSQL.SelectCommand = m_cmdSQL
m_cmdSQL.CommandTimeout = iTimeOut
m_daSQL.Fill(m_dsSQL) ----->>>> Timed out here

Why aren't you using a transaction and some error handling?

If you don't succeed, rollback, wait a little while, then try again.

 

[fniles]

Thank you
I could do that, but I just want to find out what causes it to begin
with, because most of the time it works fine.