Objects related to DB: occasional

Showing posts with label occasional. Show all posts

Friday, March 9, 2012

Occasional Very Slow Performance.. with SQL Server 2000

Hi I am using JDB with SQL Server to call Stored Procedures..
The problem is that it is working fine for some time.. But after a
certain period the time taken to execute rises to around 15 to 50 secs
from 1 sec .After restart it again starts working fine...
can anyone tell me the reason for this behaviortry to update the statistics of your tables or defrag your indexes
"Yogi" <sendpost@.gmail.com> wrote in message
news:1148306840.530834.91440@.j33g2000cwa.googlegroups.com...
> Hi I am using JDB with SQL Server to call Stored Procedures..
> The problem is that it is working fine for some time.. But after a
> certain period the time taken to execute rises to around 15 to 50 secs
> from 1 sec .After restart it again starts working fine...
> can anyone tell me the reason for this behavior
>|||Two things to check for:
See if you get different execution plans
See if you have any blocking
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Yogi" <sendpost@.gmail.com> wrote in message
news:1148306840.530834.91440@.j33g2000cwa.googlegroups.com...
> Hi I am using JDB with SQL Server to call Stored Procedures..
> The problem is that it is working fine for some time.. But after a
> certain period the time taken to execute rises to around 15 to 50 secs
> from 1 sec .After restart it again starts working fine...
> can anyone tell me the reason for this behavior
>

Occasional Very Slow Performance.. with SQL Server 2000

Hi I am using JDB with SQL Server to call Stored Procedures..
The problem is that it is working fine for some time.. But after a
certain period the time taken to execute rises to around 15 to 50 secs
from 1 sec .After restart it again starts working fine...
can anyone tell me the reason for this behaviortry to update the statistics of your tables or defrag your indexes
"Yogi" <sendpost@.gmail.com> wrote in message
news:1148306840.530834.91440@.j33g2000cwa.googlegroups.com...
> Hi I am using JDB with SQL Server to call Stored Procedures..
> The problem is that it is working fine for some time.. But after a
> certain period the time taken to execute rises to around 15 to 50 secs
> from 1 sec .After restart it again starts working fine...
> can anyone tell me the reason for this behavior
>|||Two things to check for:
See if you get different execution plans
See if you have any blocking
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Yogi" <sendpost@.gmail.com> wrote in message
news:1148306840.530834.91440@.j33g2000cwa.googlegroups.com...
> Hi I am using JDB with SQL Server to call Stored Procedures..
> The problem is that it is working fine for some time.. But after a
> certain period the time taken to execute rises to around 15 to 50 secs
> from 1 sec .After restart it again starts working fine...
> can anyone tell me the reason for this behavior
>

Occasional record fails to merge replicate, but SQL Server reports no errors

Rob,
I have seen this happen in 2 circumstances:
(1) Firstly when the filter was set to 1=2 and inserts
were made while the merge agent was running
(2) Secondly, if you bulk insert the rows and choose the
defaults, then FIRE_TRIGGERS is false and consequently
the rows are not added to MSmerge_contents.
In either case, you need to run sp_addtabletocontents to
include the rows then resynchronise. Alternatively you
can use sp_mergedummyupdate for a single row.
For your case I'd first check to see if there are
corresponding records in MSmerge_contents - ie did the
triggers fire? This should help narrow things down.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Paul, thanks for the quick reply. I have no filters on the merge and do not
do any bulk inserts, but I will take your suggestion and review
msmerge_contents next time this occurs.
Rob Kraft
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:2dd801c4c0fd$22539b60$a601280a@.phx.gbl...
> Rob,
> I have seen this happen in 2 circumstances:
> (1) Firstly when the filter was set to 1=2 and inserts
> were made while the merge agent was running
> (2) Secondly, if you bulk insert the rows and choose the
> defaults, then FIRE_TRIGGERS is false and consequently
> the rows are not added to MSmerge_contents.
> In either case, you need to run sp_addtabletocontents to
> include the rows then resynchronise. Alternatively you
> can use sp_mergedummyupdate for a single row.
> For your case I'd first check to see if there are
> corresponding records in MSmerge_contents - ie did the
> triggers fire? This should help narrow things down.
> HTH,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>

Occasional Error When Executing CLR Stored Procedure

This CLR stored procedure executes without fail 99% of the time. However, occasionally it will fail with the following error:

A .NET Framework error occurred during execution of user-defined routine or aggregate "Run_SRS_Report": System.Exception: Attempt to perform native server operation (AllocateNativeRequest) outside of its valid scope.

Below is the section of code that fails:

<code>

Private Shared Sub GetReportParameters()
Dim drReportParameters As SqlDataReader

Try
' set the parameters for the command request.
_SQLCommandRequest = _SQLConnection.CreateCommand()

_SQLCommandRequest.CommandText = _spReportParms

_SQLCommandRequest.CommandType = CommandType.StoredProcedure

' get the information for the data reader request.
drReportParameters = _SQLCommandRequest.ExecuteReader

' make sure that we have something first.
If Not drReportParameters Is Nothing Then

' find out if we have any rows returned.
If drReportParameters.HasRows Then

' read each of the rows looking for the respective value.
While drReportParameters.Read

' make sure that we can get the appropriate code id.
If (Not IsDBNull(drReportParameters.Item("Code_ID"))) AndAlso (Not IsDBNull(drReportParameters.Item("Display_Text"))) Then

' set the parameters for each of the following internal variables.
Select Case drReportParameters.Item("Code_ID").ToString
Case Is = "PDFP Path"
_FileLocationPDFP = drReportParameters.Item("Display_Text").ToString
Case Is = "PDF Temp Path"
_FileLocationPDFTemp = drReportParameters.Item("Display_Text").ToString
Case Is = "LogFile Path"
'''_SqlPipe.Send("Path from sys codes: " & drReportParameters.Item("Display_Text").ToString)
_FileLocationLogFile = Path.Combine(drReportParameters.Item("Display_Text").ToString, "SRSReportLog_" & Now.ToString("HHmmss") & ".txt")
Case Is = "LogFile Flag"
_swLogFlag = IIf(drReportParameters.Item("Display_Text").ToString = "False", False, True)
Case Is = "Delete PDF"
_DeletePDFFlag = IIf(drReportParameters.Item("Display_Text").ToString = "False", False, True)
Case Is = "PrintTool"
_PrintTool = drReportParameters.Item("Display_Text").ToString
Case Is = "BPP Path"
_BppPath = drReportParameters.Item("CharVar1").ToString
Case Is = "SendToPrinter"
_SendToPrinterFlag = IIf(drReportParameters.Item("Display_Text").ToString = "False", False, True)
Case Else
' ignore it.
End Select

End If

End While

End If

Catch ex As Exception
' throw a new exception to trip up the PrintReport() function.
Throw New Exception(ex.Message)

Finally

' make sure that we close out the datareader, regardless.
If Not drReportParameters Is Nothing Then
If Not drReportParameters.IsClosed Then drReportParameters.Close()
End If

End Try

End Sub

</code>

Any ideas why this would fail only occasionally (maybe 1% of the time it's executed)?

_SQLCommandRequest is a shared (that is, static) variable, right? You are seeing this error because your proceduce is being executed on multiple threads at the same time, and multiple threads are not allowed to use the same SqlConnection at the same time. (The error message is fixed in the next version of SQL Server to be more clear what the problem is).

It looks like you frequently use static variables within your SP, so even if you fix this problem by using a local, non-shared SqlCommand object, you will run into other problems due to multiple threads accessing the same shared state.

Steven
|||

Understood. Thanks for the explanation.

So all I have to do is make everything local, non-shared then right?

Except for the main sub procedure declaration below which has to remain shared right?:

<Microsoft.SqlServer.Server.SqlProcedure()> _
Public Shared Sub Run_SRS_Report(ByVal ip_URL As String _

Am I on the right track?

|||

Yes, everything should be a local or class instance field, unless it is a constant, readonly value. Ideally, you should try to deploy the assembly under SAFE or EXTERNAL_ACCESS permission set, which disallows the use of static variables to prevent this type of problem.

|||

Thanks Steven. I've changed everything up so there are no static variables and re-deployed. Hopefully that will fix the problem. If not I'll post again.

Occasional Error When Executing CLR Stored Procedure

This CLR stored procedure executes without fail 99% of the time. However, occasionally it will fail with the following error:

A .NET Framework error occurred during execution of user-defined routine or aggregate "Run_SRS_Report": System.Exception: Attempt to perform native server operation (AllocateNativeRequest) outside of its valid scope.

Below is the section of code that fails:

<code>

Private Shared Sub GetReportParameters()
Dim drReportParameters As SqlDataReader

Try
' set the parameters for the command request.
_SQLCommandRequest = _SQLConnection.CreateCommand()

_SQLCommandRequest.CommandText = _spReportParms

_SQLCommandRequest.CommandType = CommandType.StoredProcedure

' get the information for the data reader request.
drReportParameters = _SQLCommandRequest.ExecuteReader

' make sure that we have something first.
If Not drReportParameters Is Nothing Then

' find out if we have any rows returned.
If drReportParameters.HasRows Then

' read each of the rows looking for the respective value.
While drReportParameters.Read

' make sure that we can get the appropriate code id.
If (Not IsDBNull(drReportParameters.Item("Code_ID"))) AndAlso (Not IsDBNull(drReportParameters.Item("Display_Text"))) Then

End If

End While

End If

Catch ex As Exception
' throw a new exception to trip up the PrintReport() function.
Throw New Exception(ex.Message)

Finally

' make sure that we close out the datareader, regardless.
If Not drReportParameters Is Nothing Then
If Not drReportParameters.IsClosed Then drReportParameters.Close()
End If

End Try

End Sub

</code>

Any ideas why this would fail only occasionally (maybe 1% of the time it's executed)?

Steven
|||

Understood. Thanks for the explanation.

So all I have to do is make everything local, non-shared then right?

Except for the main sub procedure declaration below which has to remain shared right?:

<Microsoft.SqlServer.Server.SqlProcedure()> _
Public Shared Sub Run_SRS_Report(ByVal ip_URL As String _

Am I on the right track?

|||

Thanks Steven. I've changed everything up so there are no static variables and re-deployed. Hopefully that will fix the problem. If not I'll post again.

Occasional delay in processing incoming packets

I have a problem where occasionally (less that 1%) a packet is not
processed for as long as 25 seconds.
1) I get the exact same behavior with ADO 2.8 and ADO.NET.
2) A detailed view of the chain of events looks like this:
? T+0 seconds ? connection from the pool is allocated
? T+0 seconds ? vb call to Command.Execute (or c# call to
DataAdapter.Fill)
? T+0 seconds ? single packet (118 bytes) is sent to database machine
(from a web server)
? T+0.1 seconds ? ack received from database machine indicating packet
was successfully received in full
? T+25 seconds ? SQL Profiler trace "Start Time" for database call
? T+25 seconds ? SQL Profiler trace "End Time" for database call
? T+25 seconds ? data returns to application
? T+25 seconds ? connection is returned to the pool
So the "lost time" occurs on the database machine between the time the
packet is received, and the time Profiler claims SQL Server started
working on the query.
I've tried to run perfmon on the obvious counters on the database but
I've failed to find anything out of the ordinary. There are so many
counters! Any ideas on what specifically I should be looking at now?
~ James
Occasional delay in processing incoming packetsJames wrote:
> I have a problem where occasionally (less that 1%) a packet is not
> processed for as long as 25 seconds.
> 1) I get the exact same behavior with ADO 2.8 and ADO.NET.
> 2) A detailed view of the chain of events looks like this:
> . T+0 seconds - connection from the pool is allocated
> . T+0 seconds - vb call to Command.Execute (or c# call to
> DataAdapter.Fill)
> . T+0 seconds - single packet (118 bytes) is sent to database machine
> (from a web server)
> . T+0.1 seconds - ack received from database machine indicating packet
> was successfully received in full
> . T+25 seconds - SQL Profiler trace "Start Time" for database call
> . T+25 seconds - SQL Profiler trace "End Time" for database call
> . T+25 seconds - data returns to application
> . T+25 seconds - connection is returned to the pool
>
> So the "lost time" occurs on the database machine between the time the
> packet is received, and the time Profiler claims SQL Server started
> working on the query.
> I've tried to run perfmon on the obvious counters on the database but
> I've failed to find anything out of the ordinary. There are so many
> counters! Any ideas on what specifically I should be looking at now?
> ~ James
> Occasional delay in processing incoming packets
If you can, try setting up an alias to teh SQL Server machine using a
different network protocol (e.g. named pipes instead of TCP/IP). That
way you can see if the problem is related to the network library. If the
start time in Profiler is T+25, it sounds like SQL Server is not getting
the packet until T+25.
You can set up aliases from the SQL Server Client Tools - Client Network
Utility.
David G.|||I created a Named Pipes alias and used that instead of TCP/IP. I've
never done that before, so I checked to make sure the connections were
using Named Pipes before continuing.
During this test I still have the same problem.
"David G." <david_nospam@.nospam.com> wrote in message news:<eOaWzPOhEHA.1276@.TK2MSFTNGP09.phx.gbl>...
> James wrote:
> > I have a problem where occasionally (less that 1%) a packet is not
> > processed for as long as 25 seconds.
> >
> > 1) I get the exact same behavior with ADO 2.8 and ADO.NET.
> >
> > 2) A detailed view of the chain of events looks like this:
> >
> > . T+0 seconds - connection from the pool is allocated
> > . T+0 seconds - vb call to Command.Execute (or c# call to
> > DataAdapter.Fill)
> > . T+0 seconds - single packet (118 bytes) is sent to database machine
> > (from a web server)
> > . T+0.1 seconds - ack received from database machine indicating packet
> > was successfully received in full
> > . T+25 seconds - SQL Profiler trace "Start Time" for database call
> > . T+25 seconds - SQL Profiler trace "End Time" for database call
> > . T+25 seconds - data returns to application
> > . T+25 seconds - connection is returned to the pool
> >
> >
> > So the "lost time" occurs on the database machine between the time the
> > packet is received, and the time Profiler claims SQL Server started
> > working on the query.
> >
> > I've tried to run perfmon on the obvious counters on the database but
> > I've failed to find anything out of the ordinary. There are so many
> > counters! Any ideas on what specifically I should be looking at now?
> >
> > ~ James
> >
> > Occasional delay in processing incoming packets
> If you can, try setting up an alias to teh SQL Server machine using a
> different network protocol (e.g. named pipes instead of TCP/IP). That
> way you can see if the problem is related to the network library. If the
> start time in Profiler is T+25, it sounds like SQL Server is not getting
> the packet until T+25.
> You can set up aliases from the SQL Server Client Tools - Client Network
> Utility.|||James wrote:
> I created a Named Pipes alias and used that instead of TCP/IP. I've
> never done that before, so I checked to make sure the connections were
> using Named Pipes before continuing.
> During this test I still have the same problem.
> "David G." <david_nospam@.nospam.com> wrote in message
> news:<eOaWzPOhEHA.1276@.TK2MSFTNGP09.phx.gbl>...
>> James wrote:
>> I have a problem where occasionally (less that 1%) a packet is not
>> processed for as long as 25 seconds.
>> 1) I get the exact same behavior with ADO 2.8 and ADO.NET.
>> 2) A detailed view of the chain of events looks like this:
>> . T+0 seconds - connection from the pool is allocated
>> . T+0 seconds - vb call to Command.Execute (or c# call to
>> DataAdapter.Fill)
>> . T+0 seconds - single packet (118 bytes) is sent to database
>> machine (from a web server)
>> . T+0.1 seconds - ack received from database machine indicating
>> packet was successfully received in full
>> . T+25 seconds - SQL Profiler trace "Start Time" for database call
>> . T+25 seconds - SQL Profiler trace "End Time" for database call
>> . T+25 seconds - data returns to application
>> . T+25 seconds - connection is returned to the pool
>>
>> So the "lost time" occurs on the database machine between the time
>> the packet is received, and the time Profiler claims SQL Server
>> started working on the query.
>> I've tried to run perfmon on the obvious counters on the database
>> but I've failed to find anything out of the ordinary. There are so
>> many counters! Any ideas on what specifically I should be looking
>> at now?
>> ~ James
>> Occasional delay in processing incoming packets
>> If you can, try setting up an alias to teh SQL Server machine using a
>> different network protocol (e.g. named pipes instead of TCP/IP). That
>> way you can see if the problem is related to the network library. If
>> the start time in Profiler is T+25, it sounds like SQL Server is not
>> getting the packet until T+25.
>> You can set up aliases from the SQL Server Client Tools - Client
>> Network Utility.
Is this occurring from only one client or from all clients? What SP are
you running on SQL Server?
--
David G.|||There are 11 web servers and they all exhibit the same behavior.
At first I thought the times of the occurrences on the different web
servers did not correlate ? but now I think they do. Previously I was
only tracking delays of 5 seconds or greater. I think if I track all
delays I may get more matches.
I also tried tracing everything that was executing on the server ? I
did that for 6 minutes and I had one delay of 6 seconds on one web
server to compare. Then I looked for things that started at the same
time that my delayed sp was supposed to start? and I found only two
items ? which is very strange because this is a busy database. So I
grouped the results of the trace by StartTime per second yielding 360
numbers . The normal parts of the graph bounce around between 200 and
600 (not including full text calls) ? and for the second that I
submitted the delayed sp, the number plummets to 2. Full text calls
plummet to 0, and EndTimes for this second are 0 also. This was
followed by a peak of 860 (non-ft StartTimes) 7 seconds later. I do
not know if this happens every time ? I have more testing to do.
I wish I had somewhere I could post the data for DL but I do not right
now.
Anyone know what the capacity of SQL server is for incoming sql
requests? It averaged 895/second (including fulltext index calls to
sp_fulltext_getdata) with a peak of 1549 during this 6 minutes.
>> What SP are you running on SQL Server?
Service Pack?
SQL Server Machine:
Windows Server 2003 Enterprise (NT 5.2 (3790))
Quad Xeon 3.06GHz, 4GB RAM
SQL Server Enterprise 8.00.818 (SP3)
Stored Procedure?
The stored procedure I targeted just does a single row select on a
small table using the PK. I haven't gone to this level of detail on
any other sp, but I assume the problem can affect any call to the db.
~ James
"David G." <david_nospam@.nospam.com> wrote in message news:<eDkzArfhEHA.3912@.TK2MSFTNGP11.phx.gbl>...
> James wrote:
> > I created a Named Pipes alias and used that instead of TCP/IP. I've
> > never done that before, so I checked to make sure the connections were
> > using Named Pipes before continuing.
> >
> > During this test I still have the same problem.
> >
> > "David G." <david_nospam@.nospam.com> wrote in message
> > news:<eOaWzPOhEHA.1276@.TK2MSFTNGP09.phx.gbl>...
> >> James wrote:
> >> I have a problem where occasionally (less that 1%) a packet is not
> >> processed for as long as 25 seconds.
> >>
> >> 1) I get the exact same behavior with ADO 2.8 and ADO.NET.
> >>
> >> 2) A detailed view of the chain of events looks like this:
> >>
> >> . T+0 seconds - connection from the pool is allocated
> >> . T+0 seconds - vb call to Command.Execute (or c# call to
> >> DataAdapter.Fill)
> >> . T+0 seconds - single packet (118 bytes) is sent to database
> >> machine (from a web server)
> >> . T+0.1 seconds - ack received from database machine indicating
> >> packet was successfully received in full
> >> . T+25 seconds - SQL Profiler trace "Start Time" for database call
> >> . T+25 seconds - SQL Profiler trace "End Time" for database call
> >> . T+25 seconds - data returns to application
> >> . T+25 seconds - connection is returned to the pool
> >>
> >>
> >> So the "lost time" occurs on the database machine between the time
> >> the packet is received, and the time Profiler claims SQL Server
> >> started working on the query.
> >>
> >> I've tried to run perfmon on the obvious counters on the database
> >> but I've failed to find anything out of the ordinary. There are so
> >> many counters! Any ideas on what specifically I should be looking
> >> at now?
> >>
> >> ~ James
> >>
> >> Occasional delay in processing incoming packets
> >>
> >> If you can, try setting up an alias to teh SQL Server machine using a
> >> different network protocol (e.g. named pipes instead of TCP/IP). That
> >> way you can see if the problem is related to the network library. If
> >> the start time in Profiler is T+25, it sounds like SQL Server is not
> >> getting the packet until T+25.
> >>
> >> You can set up aliases from the SQL Server Client Tools - Client
> >> Network Utility.
> Is this occurring from only one client or from all clients? What SP are
> you running on SQL Server?

Occasional delay in processing incoming packets

Occasional Authentication Failure

This is an interesting problem. I've never seen this in 5+ years of SQL Server work.
I just started a new job and the folks tell me this happens once a month or so.
We use Trusted Connections for the bulk of work. For some reason one of the multiple SQL Servers we run will just stop recognizing trusted connections. Out of the blue you will just start getting the "Cannot associate user (null) with a trusted connecti
on". The server will log an error indicating it can't find the Domain Controller. The strange thing is only 1 of our SQL Server boxes are affected. There are at least 3 other machines that don't have this issue.
Reboot the server and it's fixed. But, obviously we don't like bouncing production servers. These are running on Server 2003.
I was curious if anyone had seen this before.
Well usually when it says Null as the user , it is a problem with Windows
Authentication itself, not a SQL issue
Check this article :
How to troubleshoot connectivity issues in SQL Server 2000
http://support.microsoft.com/default...b;en-us;827422
HTH
Dylan
"Shawn Brock" <Shawn Brock@.discussions.microsoft.com> wrote in message
news:B62E0490-D630-4378-A3B9-FE36378221CE@.microsoft.com...
> This is an interesting problem. I've never seen this in 5+ years of SQL
Server work.
> I just started a new job and the folks tell me this happens once a month
or so.
> We use Trusted Connections for the bulk of work. For some reason one of
the multiple SQL Servers we run will just stop recognizing trusted
connections. Out of the blue you will just start getting the "Cannot
associate user (null) with a trusted connection". The server will log an
error indicating it can't find the Domain Controller. The strange thing is
only 1 of our SQL Server boxes are affected. There are at least 3 other
machines that don't have this issue.
> Reboot the server and it's fixed. But, obviously we don't like bouncing
production servers. These are running on Server 2003.
> I was curious if anyone had seen this before.

Occasional Authentication Failure

This is an interesting problem. I've never seen this in 5+ years of SQL Ser
ver work.
I just started a new job and the folks tell me this happens once a month or
so.
We use Trusted Connections for the bulk of work. For some reason one of the
multiple SQL Servers we run will just stop recognizing trusted connections.
Out of the blue you will just start getting the "Cannot associate user (nu
ll) with a trusted connecti
on". The server will log an error indicating it can't find the Domain Contr
oller. The strange thing is only 1 of our SQL Server boxes are affected. T
here are at least 3 other machines that don't have this issue.
Reboot the server and it's fixed. But, obviously we don't like bouncing pro
duction servers. These are running on Server 2003.
I was curious if anyone had seen this before.Well usually when it says Null as the user , it is a problem with Windows
Authentication itself, not a SQL issue
Check this article :
How to troubleshoot connectivity issues in SQL Server 2000
http://support.microsoft.com/defaul...kb;en-us;827422
HTH
Dylan
"Shawn Brock" <Shawn Brock@.discussions.microsoft.com> wrote in message
news:B62E0490-D630-4378-A3B9-FE36378221CE@.microsoft.com...
> This is an interesting problem. I've never seen this in 5+ years of SQL
Server work.
> I just started a new job and the folks tell me this happens once a month
or so.
> We use Trusted Connections for the bulk of work. For some reason one of
the multiple SQL Servers we run will just stop recognizing trusted
connections. Out of the blue you will just start getting the "Cannot
associate user (null) with a trusted connection". The server will log an
error indicating it can't find the Domain Controller. The strange thing is
only 1 of our SQL Server boxes are affected. There are at least 3 other
machines that don't have this issue.
> Reboot the server and it's fixed. But, obviously we don't like bouncing
production servers. These are running on Server 2003.
> I was curious if anyone had seen this before.

Occasional "Timeout expired" message - on SP that should take 1 second

Hi there,
I've got something kind of weird going on. I have a stored procedure that
normally takes less than one second to execute, but it has started taking
over 30 seconds from time to time.
The SP is called by an ASP page on a web server, running on a separate
machine. The database server is running SQL Server 2000. The ASP page
makes an ODBC connection, using TCP/IP and creates & sends the stored
procedure call.
From time to time, something goes haywire, and the ASP page returns
[Microsoft][ODBC SQL Server Driver]Timeout expired
Then, the ASP page will not work no matter what we do, even if we leave it a
day or two. (No locks on the database, sometimes not even any connections!)
The only way to fix it is to run the stored procedure through Query
Analyzer. It will take a little over 30 seconds, but finish successfully.
Then, if I run the exact same SP again, it will complete in 0 seconds, and
the web page will work fine again.
I've seen this before, but I cannot for the life of me remember what we did
to fix it. We tried changing the ODBC connection to Named Pipes instead of
TCP/IP but that didn't help.
Any suggestions?
BeverleyI am having the same issue and the solution provided by SQL crafter in the S
ql Server Timeout expired post is not working. Could someone help us with t
his issue?
Thanks,
Rick|||I have been struggling with a similar problem for a couple of weeks and came
to this group in search of a solution. Perhaps a pooling of information
will help.
In my situation, I have a VB interactive front-end on multiple machines
accessing a SQL2K database through ODBC over TCP/IP. It has been in service
for over 2 years and the master table now has over 750K records in it.
Recently we started receiving occasional "Timeout Expired" errors in pretty
much the same manner as you describe -- a query that would normally take
1-2 seconds would suddenly take over 90 and crap out. I ran database
integrity checks, reconstructed the indexes, created additional indexes, ran
numerous original and reconfigured queries through the Index Analyzer. And
of course spent hours trying various searches in the MS KB. I have reached
the following conclusions:
The problem (in my case, at least) originates with the Query Optimizer. A
very specific query run through the Query Analyzer will *always* select an
appropriate index or mix of indexes. The *identical* query submitted
through ODBC, however, will sometimes select a completely inappropriate
index mix, or no index at all, wind up executing full-table scans or other
time-wasting substitutes, and time out. This effect, I suspect, is a
permanent feature of MSSQL, but does not manifest on smaller tables because
the malfunction does not cause a timeout on smaller tables, just an
inexcusable waste of time.
I proved this theory by adding index hints to my ODBC-originated queries
(which Books Online says should never be necessary), and observing the
performance stabilize. Problem is, I consider this a completely hokey
solution since if I ever decide to reconfigure my indexes, I will have
dozens of coordinating code changes to perform. Also, there are several
queries in which I cannot use index hints because the query requires
column-level 'OR'-ing with which hints are incompatible.
So I am still looking for a "proper" solution from one of you out there, or
at least an acknowledgment from MS that this is a behavioral issue with
SQL2K that needs to be addressed.
- Fred
"Beverley" <ali_webitems@.hotmail.com> wrote in message
news:OpxbH$K9DHA.712@.tk2msftngp13.phx.gbl...
> Hi there,
> I've got something kind of weird going on. I have a stored procedure that
> normally takes less than one second to execute, but it has started taking
> over 30 seconds from time to time.
> The SP is called by an ASP page on a web server, running on a separate
> machine. The database server is running SQL Server 2000. The ASP page
> makes an ODBC connection, using TCP/IP and creates & sends the stored
> procedure call.
> From time to time, something goes haywire, and the ASP page returns
> [Microsoft][ODBC SQL Server Driver]Timeout expired
> Then, the ASP page will not work no matter what we do, even if we leave it
a
> day or two. (No locks on the database, sometimes not even any
connections!)
> The only way to fix it is to run the stored procedure through Query
> Analyzer. It will take a little over 30 seconds, but finish successfully.
> Then, if I run the exact same SP again, it will complete in 0 seconds, and
> the web page will work fine again.
> I've seen this before, but I cannot for the life of me remember what we
did
> to fix it. We tried changing the ODBC connection to Named Pipes instead
of
> TCP/IP but that didn't help.
> Any suggestions?
> Beverley
>|||I have been struggling with a similar problem for a couple of weeks and
came
to this group in search of a solution. Perhaps a pooling of information
will help.
In my situation, I have a VB interactive front-end on multiple machines
accessing a SQL2K database through ODBC over TCP/IP. It has been in
service
for over 2 years and the master table now has over 750K records in it.
Recently we started receiving occasional "Timeout Expired" errors in pretty
much the same manner as you describe -- a query that would normally take
1-2 seconds would suddenly take over 90 and crap out. I ran database
integrity checks, reconstructed the indexes, created additional indexes,
ran
numerous original and reconfigured queries through the Index Analyzer. And
of course spent hours trying various searches in the MS KB. I have reached
the following conclusions:
The problem (in my case, at least) originates with the Query Optimizer. A
very specific query run through the Query Analyzer will *always* select an
appropriate index or mix of indexes. The *identical* query submitted
through ODBC, however, will sometimes select a completely inappropriate
index mix, or no index at all, wind up executing full-table scans or other
time-wasting substitutes, and time out. This effect, I suspect, is a
permanent feature of MSSQL, but does not manifest on smaller tables because
the malfunction does not cause a timeout on smaller tables, just an
inexcusable waste of time.
I proved this theory by adding index hints to my ODBC-originated queries
(which Books Online says should never be necessary), and observing the
performance stabilize. Problem is, I consider this a completely hokey
solution since if I ever decide to reconfigure my indexes, I will have
dozens of coordinating code changes to perform. Also, there are several
queries in which I cannot use index hints because the query requires
column-level 'OR'-ing with which hints are incompatible.
So I am still looking for a "proper" solution from one of you out there, or
at least an acknowledgment from MS that this is a behavioral issue with
SQL2K that needs to be addressed.
- ITFred|||ITFRED, have you found a solution to your problem at all'
I have a similar problem, I also get the *occasional timeout*.
Mine is an ASP web application connected to MSDE, and the application
gets a timeout error occasionally when the ASP code tries to update
many records in a table, but smaller tables work fine.
The error message is:
> Error Number -214xxxxxx, ODBC SQL Server Driver timeout expired.
In a testing environment on a dev server, the same ASP application
connects to a SQL2K database and it has NOT encountered any problems.
My conclusion is (thanks to all your posts I have read) that it is in
ODBC connections where the MSDE engine times out.
Can anyone please come up with a solution!'
Is using a different database an option, perhaps mysql or postgreSQL?
Thanks.
huge
ITFred wrote:
> *I have been struggling with a similar problem for a couple of weeks
> and
> came
> to this group in search of a solution. Perhaps a pooling of
> information
> will help.
> In my situation, I have a VB interactive front-end on multiple
> machines
> accessing a SQL2K database through ODBC over TCP/IP. It has been in
> service
> for over 2 years and the master table now has over 750K records in
> it.
> Recently we started receiving occasional "Timeout Expired" errors in
> pretty
> much the same manner as you describe -- a query that would normally
> take
> 1-2 seconds would suddenly take over 90 and crap out. I ran
> database
> integrity checks, reconstructed the indexes, created additional
> indexes,
> ran
> numerous original and reconfigured queries through the Index
> Analyzer. And
> of course spent hours trying various searches in the MS KB. I have
> reached
> the following conclusions:
> The problem (in my case, at least) originates with the Query
> Optimizer. A
> very specific query run through the Query Analyzer will *always*
> select an
> appropriate index or mix of indexes. The *identical* query
> submitted
> through ODBC, however, will sometimes select a completely
> inappropriate
> index mix, or no index at all, wind up executing full-table scans or
> other
> time-wasting substitutes, and time out. This effect, I suspect, is
> a
> permanent feature of MSSQL, but does not manifest on smaller tables
> because
> the malfunction does not cause a timeout on smaller tables, just an
> inexcusable waste of time.
> I proved this theory by adding index hints to my ODBC-originated
> queries
> (which Books Online says should never be necessary), and observing
> the
> performance stabilize. Problem is, I consider this a completely
> hokey
> solution since if I ever decide to reconfigure my indexes, I will
> have
> dozens of coordinating code changes to perform. Also, there are
> several
> queries in which I cannot use index hints because the query requires
> column-level 'OR'-ing with which hints are incompatible.
> So I am still looking for a "proper" solution from one of you out
> there, or
> at least an acknowledgment from MS that this is a behavioral issue
> with
> SQL2K that needs to be addressed.
> - ITFred *
huge
---
Posted via http://www.mcse.ms
---
View this thread: http://www.mcse.ms/message397556.html

Friday, March 9, 2012

Occasional Very Slow Performance.. with SQL Server 2000

Occasional Very Slow Performance.. with SQL Server 2000

Occasional record fails to merge replicate, but SQL Server reports no errors

Occasional Error When Executing CLR Stored Procedure

Occasional Error When Executing CLR Stored Procedure

Occasional delay in processing incoming packets

Occasional delay in processing incoming packets

Occasional delay in processing incoming packets

Occasional Authentication Failure

Occasional Authentication Failure

Occasional "Timeout expired" message - on SP that should take 1 second

Objects related to DB

Blog Archive

About Me