Internal Connection Fatal Error - SQL 2000 and ASP VS 2003

We have an ASP.NET 2003 app that accesses a MS SQL 2000 Std. database. Rand
omly it returns this error:
Internal connection fatal error.
Description: An unhandled exception occurred during the execution of the cur
rent web request. Please review the stack trace for more information about t
he error and where it originated in the code.
Exception Details: System.InvalidOperationException: Internal connection fat
al error.
Source Error:
An unhandled exception was generated during the execution of the current web
request. Information regarding the origin and location of the exception can
be identified using the exception stack trace below.
Stack Trace:
[InvalidOperationException: Internal connection fatal error.]
System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior,
RunBehavior runBehavior, Boolean returnStream) +723
System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +195
tradeTicket.MonetaGroup.Approval.approve(Int32 Trans_ID, String Security_Typ
e) in C:\Inetpub\wwwroot\tradeTicket\Data\empl
oyee.vb:1191
tradeTicket.ApproveBond.buttonApproveNew_Click(Object sender, EventArgs e) i
n C:\Inetpub\wwwroot\tradeTicket\ApproveBo
nd.aspx.vb:469
System.Web.UI.WebControls.Button.OnClick(EventArgs e) +108
System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePo
stBackEvent(String eventArgument) +57
System.Web.UI.Page. RaisePostBackEvent(IPostBackEventHandler
sourceControl, S
tring eventArgument) +18
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33
System.Web.UI.Page.ProcessRequestMain() +1277
----
--
Version Information: Microsoft NET Framework Version:1.1.4322.573; ASP.NET V
ersion:1.1.4322.573
We have SQL Server 2000 and Win2000 Server.
Microsoft SQL Server 2000 - 8.00.534 (Intel X86) Nov 19 2001 13:23:50 Cop
yright (c) 1988-2000 Microsoft Corporation Standard Edition on Windows NT 5
.0 (Build 2195: Service Pack 3)
Is there a setting I can bump up to fix this problem? We do close our conne
ctions. Here is a snippet of code:
----
--
Private Sub buttonApproveNew_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles buttonApproveNew.Click
Dim Approve As New MonetaGroup.Approval()
Dim curId = Request.QueryString("id")
Dim curSecurity = "B"
Dim nextID As String
Dim nextSecurity As String
Dim nextTransStatus As Integer
Approve.FindNextUnapprovedTransaction(curId, curSecurity, nextID, nextSecuri
ty, nextTransStatus)
Approve.approve(Request.QueryString("id"), "B") 'Line 469 error message
If nextID <> 0 Then
Dim returnValue = checkSecurity(nextID, nextTransStatus, nextSecurity)
Dim strBuilder As StringBuilder = New StringBuilder()
strBuilder.Append("<script language='javascript'>")
'strBuilder.Append("alert('The transaction was approved.');")
strBuilder.Append("window.open('" & returnValue & "','_self');")
strBuilder.Append("</script>")
RegisterStartupScript("focus", strBuilder.ToString)
Else
Dim strBuilder As StringBuilder = New StringBuilder()
strBuilder.Append("<script language='javascript'>")
'strBuilder.Append("alert('The transaction was approved.');")
strBuilder.Append("window.open('Approval.aspx','_self');")
strBuilder.Append("</script>")
RegisterStartupScript("focus", strBuilder.ToString)
End If
End Sub
Public Function checkSecurity(ByVal id As Integer, ByVal status As Integer,
ByVal security As String)
Dim returnValue As String
If status = 2 Then
If security = "MF" Then
returnValue = "../tradeTicket/ApproveMF.aspx?id=" & id & "&approve=0"
ElseIf security = "S" Then
returnValue = "../tradeTicket/ApproveStock.aspx?id=" & id & "&approve=0"
ElseIf security = "B" Then
returnValue = "../tradeTicket/ApproveBond.aspx?id=" & id & "&approve=0"
End If
Else
If security = "MF" Then
returnValue = "../tradeTicket/ApproveMF.aspx?id=" & id & "&approve=1"
ElseIf security = "S" Then
returnValue = "../tradeTicket/ApproveStock.aspx?id=" & id & "&approve=1"
ElseIf security = "B" Then
returnValue = "../tradeTicket/ApproveBond.aspx?id=" & id & "&approve=1"
End If
End If
Return returnValue
End Function
----
--
Thank you in advance.
RichWe're having the same issue. Our setup is Win 2003 Server IIS and Win 2000
SQL. I've found that the version of MDAC is different on the two machines.
Is your setup also 2 machines? If it is, let me know if your versions of M
DAC differ.
Thanks,
Bobsql

Internal Connection Fatal Error - SQL 2000 and ASP VS 2003

We have an ASP.NET 2003 app that accesses a MS SQL 2000 Std. database. Randomly it returns this error:
Internal connection fatal error.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.InvalidOperationException: Internal connection fatal error.
Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.
Stack Trace:
[InvalidOperationException: Internal connection fatal error.]
System.Data.SqlClient.SqlCommand.ExecuteReader(Com mandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream) +723
System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +195
tradeTicket.MonetaGroup.Approval.approve(Int32 Trans_ID, String Security_Type) in C:\Inetpub\wwwroot\tradeTicket\Data\employee.vb:11 91
tradeTicket.ApproveBond.buttonApproveNew_Click(Obj ect sender, EventArgs e) in C:\Inetpub\wwwroot\tradeTicket\ApproveBond.aspx.vb :469
System.Web.UI.WebControls.Button.OnClick(EventArgs e) +108
System.Web.UI.WebControls.Button.System.Web.UI.IPo stBackEventHandler.RaisePostBackEvent(String eventArgument) +57
System.Web.UI.Page.RaisePostBackEvent(IPostBackEve ntHandler sourceControl, String eventArgument) +18
System.Web.UI.Page.RaisePostBackEvent(NameValueCol lection postData) +33
System.Web.UI.Page.ProcessRequestMain() +1277
Version Information: Microsoft NET Framework Version:1.1.4322.573; ASP.NET Version:1.1.4322.573
We have SQL Server 2000 and Win2000 Server.
Microsoft SQL Server 2000 - 8.00.534 (Intel X86) Nov 19 2001 13:23:50 Copyright (c) 1988-2000 Microsoft Corporation Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 3)
Is there a setting I can bump up to fix this problem? We do close our connections. Here is a snippet of code:
Private Sub buttonApproveNew_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles buttonApproveNew.Click
Dim Approve As New MonetaGroup.Approval()
Dim curId = Request.QueryString("id")
Dim curSecurity = "B"
Dim nextID As String
Dim nextSecurity As String
Dim nextTransStatus As Integer
Approve.FindNextUnapprovedTransaction(curId, curSecurity, nextID, nextSecurity, nextTransStatus)
Approve.approve(Request.QueryString("id"), "B") 'Line 469 error message
If nextID <> 0 Then
Dim returnValue = checkSecurity(nextID, nextTransStatus, nextSecurity)
Dim strBuilder As StringBuilder = New StringBuilder()
strBuilder.Append("<script language='javascript'>")
'strBuilder.Append("alert('The transaction was approved.');")
strBuilder.Append("window.open('" & returnValue & "','_self');")
strBuilder.Append("</script>")
RegisterStartupScript("focus", strBuilder.ToString)
Else
Dim strBuilder As StringBuilder = New StringBuilder()
strBuilder.Append("<script language='javascript'>")
'strBuilder.Append("alert('The transaction was approved.');")
strBuilder.Append("window.open('Approval.aspx','_s elf');")
strBuilder.Append("</script>")
RegisterStartupScript("focus", strBuilder.ToString)
End If
End Sub
Public Function checkSecurity(ByVal id As Integer, ByVal status As Integer, ByVal security As String)
Dim returnValue As String
If status = 2 Then
If security = "MF" Then
returnValue = "../tradeTicket/ApproveMF.aspx?id=" & id & "&approve=0"
ElseIf security = "S" Then
returnValue = "../tradeTicket/ApproveStock.aspx?id=" & id & "&approve=0"
ElseIf security = "B" Then
returnValue = "../tradeTicket/ApproveBond.aspx?id=" & id & "&approve=0"
End If
Else
If security = "MF" Then
returnValue = "../tradeTicket/ApproveMF.aspx?id=" & id & "&approve=1"
ElseIf security = "S" Then
returnValue = "../tradeTicket/ApproveStock.aspx?id=" & id & "&approve=1"
ElseIf security = "B" Then
returnValue = "../tradeTicket/ApproveBond.aspx?id=" & id & "&approve=1"
End If
End If
Return returnValue
End Function
Thank you in advance.
Rich
We're having the same issue. Our setup is Win 2003 Server IIS and Win 2000 SQL. I've found that the version of MDAC is different on the two machines. Is your setup also 2 machines? If it is, let me know if your versions of MDAC differ.
Thanks,
Bob

Intermittent Timeout - ExecuteNonQuery On Stored Procedure

Hi All,
I'll do my best to try and describe the issue I'm having with my
application.
I'm writing a VB.Net front end for a SQL 2000 Database. I have a generic
data layer which communicates with the database, and provides classes to the
front end application.
Most of the classes are filled using the stored procedures which fill
datatable which fill properties. However, I have a method in my login class,
which calls the ADO.net method ExecuteNonQuery on a stored procedure to
update a table (I've copied in the procedure T-SQL and the end of the mail -
it's nothing complicated!!!).
Intermittently, the method will not run, and errors on the ExecuteNonQuery
line with the error "Timeout expired. The timeout period elapsed prior to
completion of the operation or the server is not responding."
Before this runs, there is a method which fills a datatable using the Fill
Method of a Data Adapter and this runs everytime. However, the
ExecuteNonQuery does not run, and errors out.
When this does occur, I can open query analyzer and if I try to run any
stored procedures in the database, I get a timeout. Even Altering a stored
procedure times out. I can use other databases and run stored procedure in
them with no problems, but this specific database causes timeouts.
After say 5 minutes the attempt to run the ExecuteNonQuery works and it will
be fine for a while (couple of hours), then it will start to timeout again
for 5-10 mins.
What could be causing this, as it's database specific. Is there any way, I
can have the Database rebuild itself and clear out any dodgy temporary
tables?
Any method which uses a data adapter runs fine all the time, but the
ExecuteNonQuery fails intermittently.
I'm a bit lost really.
Any help is appreciated.
Thanks
Alex
******* Stored Procedure *********
ALTER PROC proc_Utility_UpdateUserLoggedIn
@.UserID int,
@.LoggedIn bit = 0
AS
SET NOCOUNT ON
UPDATE tblUser
SET
LastLogin = GetDate(),
LoggedIn = @.LoggedIn
WHERE UserID = @.UserID
*********************************
Hi Alex,
Are you ending properly transactions?
Miha Markic [MVP C#] - RightHand .NET consulting & development
miha at rthand com
www.rthand.com
"Alex Stevens" <AlexStevens_NOSPAMPLEASE@.gcc.co.uk> wrote in message
news:eFahpgUoEHA.2612@.TK2MSFTNGP15.phx.gbl...
> Hi All,
> I'll do my best to try and describe the issue I'm having with my
> application.
> I'm writing a VB.Net front end for a SQL 2000 Database. I have a generic
> data layer which communicates with the database, and provides classes to
> the
> front end application.
> Most of the classes are filled using the stored procedures which fill
> datatable which fill properties. However, I have a method in my login
> class,
> which calls the ADO.net method ExecuteNonQuery on a stored procedure to
> update a table (I've copied in the procedure T-SQL and the end of the
> mail -
> it's nothing complicated!!!).
> Intermittently, the method will not run, and errors on the ExecuteNonQuery
> line with the error "Timeout expired. The timeout period elapsed prior to
> completion of the operation or the server is not responding."
> Before this runs, there is a method which fills a datatable using the Fill
> Method of a Data Adapter and this runs everytime. However, the
> ExecuteNonQuery does not run, and errors out.
> When this does occur, I can open query analyzer and if I try to run any
> stored procedures in the database, I get a timeout. Even Altering a stored
> procedure times out. I can use other databases and run stored procedure in
> them with no problems, but this specific database causes timeouts.
> After say 5 minutes the attempt to run the ExecuteNonQuery works and it
> will
> be fine for a while (couple of hours), then it will start to timeout again
> for 5-10 mins.
> What could be causing this, as it's database specific. Is there any way, I
> can have the Database rebuild itself and clear out any dodgy temporary
> tables?
> Any method which uses a data adapter runs fine all the time, but the
> ExecuteNonQuery fails intermittently.
> I'm a bit lost really.
> Any help is appreciated.
> Thanks
> Alex
>
> ******* Stored Procedure *********
> ALTER PROC proc_Utility_UpdateUserLoggedIn
> @.UserID int,
> @.LoggedIn bit = 0
> AS
> SET NOCOUNT ON
> UPDATE tblUser
> SET
> LastLogin = GetDate(),
> LoggedIn = @.LoggedIn
> WHERE UserID = @.UserID
> *********************************
>
|||Hi
Timeouts are caused by SQL not getting it's work finished in time. This
indicates a blocking or performance issue.
Make sure that you have appropriate indexes in place, run sp_who2 and look
for any processes that are blocked by other processes when you run your query
through your VB code or Query Analyser.
Regards
Mike
"Alex Stevens" wrote:

> Hi All,
> I'll do my best to try and describe the issue I'm having with my
> application.
> I'm writing a VB.Net front end for a SQL 2000 Database. I have a generic
> data layer which communicates with the database, and provides classes to the
> front end application.
> Most of the classes are filled using the stored procedures which fill
> datatable which fill properties. However, I have a method in my login class,
> which calls the ADO.net method ExecuteNonQuery on a stored procedure to
> update a table (I've copied in the procedure T-SQL and the end of the mail -
> it's nothing complicated!!!).
> Intermittently, the method will not run, and errors on the ExecuteNonQuery
> line with the error "Timeout expired. The timeout period elapsed prior to
> completion of the operation or the server is not responding."
> Before this runs, there is a method which fills a datatable using the Fill
> Method of a Data Adapter and this runs everytime. However, the
> ExecuteNonQuery does not run, and errors out.
> When this does occur, I can open query analyzer and if I try to run any
> stored procedures in the database, I get a timeout. Even Altering a stored
> procedure times out. I can use other databases and run stored procedure in
> them with no problems, but this specific database causes timeouts.
> After say 5 minutes the attempt to run the ExecuteNonQuery works and it will
> be fine for a while (couple of hours), then it will start to timeout again
> for 5-10 mins.
> What could be causing this, as it's database specific. Is there any way, I
> can have the Database rebuild itself and clear out any dodgy temporary
> tables?
> Any method which uses a data adapter runs fine all the time, but the
> ExecuteNonQuery fails intermittently.
> I'm a bit lost really.
> Any help is appreciated.
> Thanks
> Alex
>
> ******* Stored Procedure *********
> ALTER PROC proc_Utility_UpdateUserLoggedIn
> @.UserID int,
> @.LoggedIn bit = 0
> AS
> SET NOCOUNT ON
> UPDATE tblUser
> SET
> LastLogin = GetDate(),
> LoggedIn = @.LoggedIn
> WHERE UserID = @.UserID
> *********************************
>
>
|||I'm not using transactions in the stored procedure........?
"Miha Markic [MVP C#]" <miha at rthand com> wrote in message
news:%23Au3lrUoEHA.3592@.TK2MSFTNGP14.phx.gbl...[vbcol=seagreen]
> Hi Alex,
> Are you ending properly transactions?
> --
> Miha Markic [MVP C#] - RightHand .NET consulting & development
> miha at rthand com
> www.rthand.com
> "Alex Stevens" <AlexStevens_NOSPAMPLEASE@.gcc.co.uk> wrote in message
> news:eFahpgUoEHA.2612@.TK2MSFTNGP15.phx.gbl...
ExecuteNonQuery[vbcol=seagreen]
to[vbcol=seagreen]
Fill[vbcol=seagreen]
stored[vbcol=seagreen]
in[vbcol=seagreen]
again[vbcol=seagreen]
I
>
|||As you can see the stored procedure (at the bottom of the original email) is
an extremely simple update procedure.
The only thiing that has been run before that on the SQL database is a
SELECT statement (posted at the end) which returns a resultset also
implementing the NOLOCK to stop the table being locked on a simple read.
In query analyzer, Select statements work fine, but updates don't.

> Make sure that you have appropriate indexes in place, run sp_who2 and look
> for any processes that are blocked by other processes when you run your
query
> through your VB code or Query Analyser.
The table has an int Primary Key, when the application is started, I
sometimes get two processes one which has a batch end time, and one which
has a batch end time of 01/01/1900 (presumably a Null).
I can't track down where this erroneous process comes from (it is on the
database in question), because it doesn't appear when I set through the
code.
How can I check to see if a process is blocking the UPDATE process?
Thanks
Alex
******Stored Proc*******
ALTER PROC proc_Get_User
-- Date Created: 28 April 2004
-- Procedure Description: Standard Get procedure.
-- Created By: Alex Stevens
-- Template version 1.0 Dated: 28/04/2004 12:41:58
-- Generated by CodeSmith 2.5
-- Used in Classes:
@.UserID int = Null,
@.UserName varChar(20) = Null
AS
BEGIN
SELECT dbo.tblUser.*
FROM dbo.tblUser (NOLOCK)
WHERE (@.UserID IS NULL OR UserID = @.UserID) OR
(@.UserName IS NULL OR UserName = @.UserName)
ORDER BY UserName
END
*************
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:F92E78E3-70CF-44C2-824D-4C686642307C@.microsoft.com...
> Hi
> Timeouts are caused by SQL not getting it's work finished in time. This
> indicates a blocking or performance issue.
> Make sure that you have appropriate indexes in place, run sp_who2 and look
> for any processes that are blocked by other processes when you run your
query[vbcol=seagreen]
> through your VB code or Query Analyser.
> Regards
> Mike
> "Alex Stevens" wrote:
the[vbcol=seagreen]
class,[vbcol=seagreen]
mail -[vbcol=seagreen]
ExecuteNonQuery[vbcol=seagreen]
to[vbcol=seagreen]
Fill[vbcol=seagreen]
stored[vbcol=seagreen]
in[vbcol=seagreen]
will[vbcol=seagreen]
again[vbcol=seagreen]
I[vbcol=seagreen]

Intermittent Timeout - ExecuteNonQuery On Stored Procedure

Hi All,
I'll do my best to try and describe the issue I'm having with my
application.
I'm writing a VB.Net front end for a SQL 2000 Database. I have a generic
data layer which communicates with the database, and provides classes to the
front end application.
Most of the classes are filled using the stored procedures which fill
datatable which fill properties. However, I have a method in my login class,
which calls the ADO.net method ExecuteNonQuery on a stored procedure to
update a table (I've copied in the procedure T-SQL and the end of the mail -
it's nothing complicated!!!).
Intermittently, the method will not run, and errors on the ExecuteNonQuery
line with the error "Timeout expired. The timeout period elapsed prior to
completion of the operation or the server is not responding."
Before this runs, there is a method which fills a datatable using the Fill
Method of a Data Adapter and this runs everytime. However, the
ExecuteNonQuery does not run, and errors out.
When this does occur, I can open query analyzer and if I try to run any
stored procedures in the database, I get a timeout. Even Altering a stored
procedure times out. I can use other databases and run stored procedure in
them with no problems, but this specific database causes timeouts.
After say 5 minutes the attempt to run the ExecuteNonQuery works and it will
be fine for a while (couple of hours), then it will start to timeout again
for 5-10 mins.
What could be causing this, as it's database specific. Is there any way, I
can have the Database rebuild itself and clear out any dodgy temporary
tables'
Any method which uses a data adapter runs fine all the time, but the
ExecuteNonQuery fails intermittently.
I'm a bit lost really.
Any help is appreciated.
Thanks
Alex
******* Stored Procedure *********
ALTER PROC proc_Utility_UpdateUserLoggedIn
@.UserID int,
@.LoggedIn bit = 0
AS
SET NOCOUNT ON
UPDATE tblUser
SET
LastLogin = GetDate(),
LoggedIn = @.LoggedIn
WHERE UserID = @.UserID
*********************************Hi Alex,
Are you ending properly transactions?
--
Miha Markic [MVP C#] - RightHand .NET consulting & development
miha at rthand com
www.rthand.com
"Alex Stevens" <AlexStevens_NOSPAMPLEASE@.gcc.co.uk> wrote in message
news:eFahpgUoEHA.2612@.TK2MSFTNGP15.phx.gbl...
> Hi All,
> I'll do my best to try and describe the issue I'm having with my
> application.
> I'm writing a VB.Net front end for a SQL 2000 Database. I have a generic
> data layer which communicates with the database, and provides classes to
> the
> front end application.
> Most of the classes are filled using the stored procedures which fill
> datatable which fill properties. However, I have a method in my login
> class,
> which calls the ADO.net method ExecuteNonQuery on a stored procedure to
> update a table (I've copied in the procedure T-SQL and the end of the
> mail -
> it's nothing complicated!!!).
> Intermittently, the method will not run, and errors on the ExecuteNonQuery
> line with the error "Timeout expired. The timeout period elapsed prior to
> completion of the operation or the server is not responding."
> Before this runs, there is a method which fills a datatable using the Fill
> Method of a Data Adapter and this runs everytime. However, the
> ExecuteNonQuery does not run, and errors out.
> When this does occur, I can open query analyzer and if I try to run any
> stored procedures in the database, I get a timeout. Even Altering a stored
> procedure times out. I can use other databases and run stored procedure in
> them with no problems, but this specific database causes timeouts.
> After say 5 minutes the attempt to run the ExecuteNonQuery works and it
> will
> be fine for a while (couple of hours), then it will start to timeout again
> for 5-10 mins.
> What could be causing this, as it's database specific. Is there any way, I
> can have the Database rebuild itself and clear out any dodgy temporary
> tables'
> Any method which uses a data adapter runs fine all the time, but the
> ExecuteNonQuery fails intermittently.
> I'm a bit lost really.
> Any help is appreciated.
> Thanks
> Alex
>
> ******* Stored Procedure *********
> ALTER PROC proc_Utility_UpdateUserLoggedIn
> @.UserID int,
> @.LoggedIn bit = 0
> AS
> SET NOCOUNT ON
> UPDATE tblUser
> SET
> LastLogin = GetDate(),
> LoggedIn = @.LoggedIn
> WHERE UserID = @.UserID
> *********************************
>|||Hi
Timeouts are caused by SQL not getting it's work finished in time. This
indicates a blocking or performance issue.
Make sure that you have appropriate indexes in place, run sp_who2 and look
for any processes that are blocked by other processes when you run your query
through your VB code or Query Analyser.
Regards
Mike
"Alex Stevens" wrote:
> Hi All,
> I'll do my best to try and describe the issue I'm having with my
> application.
> I'm writing a VB.Net front end for a SQL 2000 Database. I have a generic
> data layer which communicates with the database, and provides classes to the
> front end application.
> Most of the classes are filled using the stored procedures which fill
> datatable which fill properties. However, I have a method in my login class,
> which calls the ADO.net method ExecuteNonQuery on a stored procedure to
> update a table (I've copied in the procedure T-SQL and the end of the mail -
> it's nothing complicated!!!).
> Intermittently, the method will not run, and errors on the ExecuteNonQuery
> line with the error "Timeout expired. The timeout period elapsed prior to
> completion of the operation or the server is not responding."
> Before this runs, there is a method which fills a datatable using the Fill
> Method of a Data Adapter and this runs everytime. However, the
> ExecuteNonQuery does not run, and errors out.
> When this does occur, I can open query analyzer and if I try to run any
> stored procedures in the database, I get a timeout. Even Altering a stored
> procedure times out. I can use other databases and run stored procedure in
> them with no problems, but this specific database causes timeouts.
> After say 5 minutes the attempt to run the ExecuteNonQuery works and it will
> be fine for a while (couple of hours), then it will start to timeout again
> for 5-10 mins.
> What could be causing this, as it's database specific. Is there any way, I
> can have the Database rebuild itself and clear out any dodgy temporary
> tables'
> Any method which uses a data adapter runs fine all the time, but the
> ExecuteNonQuery fails intermittently.
> I'm a bit lost really.
> Any help is appreciated.
> Thanks
> Alex
>
> ******* Stored Procedure *********
> ALTER PROC proc_Utility_UpdateUserLoggedIn
> @.UserID int,
> @.LoggedIn bit = 0
> AS
> SET NOCOUNT ON
> UPDATE tblUser
> SET
> LastLogin = GetDate(),
> LoggedIn = @.LoggedIn
> WHERE UserID = @.UserID
> *********************************
>
>|||I'm not using transactions in the stored procedure........?
"Miha Markic [MVP C#]" <miha at rthand com> wrote in message
news:%23Au3lrUoEHA.3592@.TK2MSFTNGP14.phx.gbl...
> Hi Alex,
> Are you ending properly transactions?
> --
> Miha Markic [MVP C#] - RightHand .NET consulting & development
> miha at rthand com
> www.rthand.com
> "Alex Stevens" <AlexStevens_NOSPAMPLEASE@.gcc.co.uk> wrote in message
> news:eFahpgUoEHA.2612@.TK2MSFTNGP15.phx.gbl...
> > Hi All,
> >
> > I'll do my best to try and describe the issue I'm having with my
> > application.
> >
> > I'm writing a VB.Net front end for a SQL 2000 Database. I have a generic
> > data layer which communicates with the database, and provides classes to
> > the
> > front end application.
> >
> > Most of the classes are filled using the stored procedures which fill
> > datatable which fill properties. However, I have a method in my login
> > class,
> > which calls the ADO.net method ExecuteNonQuery on a stored procedure to
> > update a table (I've copied in the procedure T-SQL and the end of the
> > mail -
> > it's nothing complicated!!!).
> >
> > Intermittently, the method will not run, and errors on the
ExecuteNonQuery
> > line with the error "Timeout expired. The timeout period elapsed prior
to
> > completion of the operation or the server is not responding."
> >
> > Before this runs, there is a method which fills a datatable using the
Fill
> > Method of a Data Adapter and this runs everytime. However, the
> > ExecuteNonQuery does not run, and errors out.
> >
> > When this does occur, I can open query analyzer and if I try to run any
> > stored procedures in the database, I get a timeout. Even Altering a
stored
> > procedure times out. I can use other databases and run stored procedure
in
> > them with no problems, but this specific database causes timeouts.
> >
> > After say 5 minutes the attempt to run the ExecuteNonQuery works and it
> > will
> > be fine for a while (couple of hours), then it will start to timeout
again
> > for 5-10 mins.
> >
> > What could be causing this, as it's database specific. Is there any way,
I
> > can have the Database rebuild itself and clear out any dodgy temporary
> > tables'
> > Any method which uses a data adapter runs fine all the time, but the
> > ExecuteNonQuery fails intermittently.
> >
> > I'm a bit lost really.
> >
> > Any help is appreciated.
> >
> > Thanks
> >
> > Alex
> >
> >
> > ******* Stored Procedure *********
> > ALTER PROC proc_Utility_UpdateUserLoggedIn
> >
> > @.UserID int,
> > @.LoggedIn bit = 0
> >
> > AS
> >
> > SET NOCOUNT ON
> >
> > UPDATE tblUser
> > SET
> > LastLogin = GetDate(),
> > LoggedIn = @.LoggedIn
> >
> > WHERE UserID = @.UserID
> > *********************************
> >
> >
>|||As you can see the stored procedure (at the bottom of the original email) is
an extremely simple update procedure.
The only thiing that has been run before that on the SQL database is a
SELECT statement (posted at the end) which returns a resultset also
implementing the NOLOCK to stop the table being locked on a simple read.
In query analyzer, Select statements work fine, but updates don't.
> Make sure that you have appropriate indexes in place, run sp_who2 and look
> for any processes that are blocked by other processes when you run your
query
> through your VB code or Query Analyser.
The table has an int Primary Key, when the application is started, I
sometimes get two processes one which has a batch end time, and one which
has a batch end time of 01/01/1900 (presumably a Null).
I can't track down where this erroneous process comes from (it is on the
database in question), because it doesn't appear when I set through the
code.
How can I check to see if a process is blocking the UPDATE process'
Thanks
Alex
******Stored Proc*******
ALTER PROC proc_Get_User
---
-- Date Created: 28 April 2004
-- Procedure Description: Standard Get procedure.
-- Created By: Alex Stevens
-- Template version 1.0 Dated: 28/04/2004 12:41:58
-- Generated by CodeSmith 2.5
--
-- Used in Classes:
--
---
@.UserID int = Null,
@.UserName varChar(20) = Null
AS
BEGIN
SELECT dbo.tblUser.*
FROM dbo.tblUser (NOLOCK)
WHERE (@.UserID IS NULL OR UserID = @.UserID) OR
(@.UserName IS NULL OR UserName = @.UserName)
ORDER BY UserName
END
*************
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:F92E78E3-70CF-44C2-824D-4C686642307C@.microsoft.com...
> Hi
> Timeouts are caused by SQL not getting it's work finished in time. This
> indicates a blocking or performance issue.
> Make sure that you have appropriate indexes in place, run sp_who2 and look
> for any processes that are blocked by other processes when you run your
query
> through your VB code or Query Analyser.
> Regards
> Mike
> "Alex Stevens" wrote:
> > Hi All,
> >
> > I'll do my best to try and describe the issue I'm having with my
> > application.
> >
> > I'm writing a VB.Net front end for a SQL 2000 Database. I have a generic
> > data layer which communicates with the database, and provides classes to
the
> > front end application.
> >
> > Most of the classes are filled using the stored procedures which fill
> > datatable which fill properties. However, I have a method in my login
class,
> > which calls the ADO.net method ExecuteNonQuery on a stored procedure to
> > update a table (I've copied in the procedure T-SQL and the end of the
mail -
> > it's nothing complicated!!!).
> >
> > Intermittently, the method will not run, and errors on the
ExecuteNonQuery
> > line with the error "Timeout expired. The timeout period elapsed prior
to
> > completion of the operation or the server is not responding."
> >
> > Before this runs, there is a method which fills a datatable using the
Fill
> > Method of a Data Adapter and this runs everytime. However, the
> > ExecuteNonQuery does not run, and errors out.
> >
> > When this does occur, I can open query analyzer and if I try to run any
> > stored procedures in the database, I get a timeout. Even Altering a
stored
> > procedure times out. I can use other databases and run stored procedure
in
> > them with no problems, but this specific database causes timeouts.
> >
> > After say 5 minutes the attempt to run the ExecuteNonQuery works and it
will
> > be fine for a while (couple of hours), then it will start to timeout
again
> > for 5-10 mins.
> >
> > What could be causing this, as it's database specific. Is there any way,
I
> > can have the Database rebuild itself and clear out any dodgy temporary
> > tables'
> > Any method which uses a data adapter runs fine all the time, but the
> > ExecuteNonQuery fails intermittently.
> >
> > I'm a bit lost really.
> >
> > Any help is appreciated.
> >
> > Thanks
> >
> > Alex
> >
> >
> > ******* Stored Procedure *********
> > ALTER PROC proc_Utility_UpdateUserLoggedIn
> >
> > @.UserID int,
> > @.LoggedIn bit = 0
> >
> > AS
> >
> > SET NOCOUNT ON
> >
> > UPDATE tblUser
> > SET
> > LastLogin = GetDate(),
> > LoggedIn = @.LoggedIn
> >
> > WHERE UserID = @.UserID
> > *********************************
> >
> >
> >sql

Intermittent SQL Server does not exist or access denied.

ASP.NET application with intermittent returns of 503 errors with the
following message.
System.Data.SqlClient.SqlException: SQL Server does not exist or access
denied. SQL Server does not exist or access denied.
An unhandled exception was generated during the execution of the
current web request. Information regarding the origin and location of
the exception can be identified using the exception stack trace below.
Servers running Windows 2003 SQL 2000.
This has been a problem for a long time (less than .3% of the time). No
code changes have been made. The only changes was Windows SP1 and post
SP patches on all servers. The patching may have made this issue more
pronounced.
Any suggestions from the experts?
Thanks,
RoxanneWhen they are intermittent they are difficult to track down.
Often those related to network connectivity issues. Have you
checked the event logs on the IIS box - particularly looking
for any network related issues?
-Sue
On 2 Nov 2005 10:32:52 -0800, roxy636@.yahoo.com wrote:

>ASP.NET application with intermittent returns of 503 errors with the
>following message.
>System.Data.SqlClient.SqlException: SQL Server does not exist or access
>denied. SQL Server does not exist or access denied.
>An unhandled exception was generated during the execution of the
>current web request. Information regarding the origin and location of
>the exception can be identified using the exception stack trace below.
>Servers running Windows 2003 SQL 2000.
>This has been a problem for a long time (less than .3% of the time). No
>code changes have been made. The only changes was Windows SP1 and post
>SP patches on all servers. The patching may have made this issue more
>pronounced.
>Any suggestions from the experts?
>Thanks,
>Roxanne|||Search no further...
http://support.microsoft.com/defaul...kb;en-us;328476
Note, this answer took forever to find so I'm posting everywhere...|||So was your solution to enable connection pooling? Or was it simply a
tcp/ip configuration issue. Unless you explicitly disable connection
pooling, then connections should be pooled, were you disabling pooling?
Thanks for any info, we are seeing the exact same symptoms at a client
of ours.|||Connection pooling was disabled for security reasons, so you need to
add registry keys specified in article here:
path:
HKEY_LOCAL_MACHINE\System\CurrentControl
Set\services\Tcpip\Parameters
type: DWORD
name: TcpTimedWaitDelay
value: 30 (decimal)
type: DWORD
name: MaxUserPort
value: 10000 (decimal)
Ultimately is connection ooliing is enabled this shouldn't happen, but
try it anyway. Check netsats from command prompt...if you see around
4000 connections in TIME_WAIT, then that is the problem.|||Thanks, since connection pooling is enabled and this is occuring even
soon after a reboot under low load. I do not suspect we are creating
too many connections to sql, but I do suspect some network
communication problems. I am going to further diagnose thier network
configuration relative to the front and back planes of the web server.
But I will not overlook this as a possibility, so I will also get a
netstat -n Thanks so much for the info..|||Turned out to be Bandwidth Throttling, disabling that option for the
Application Pool alleviated the HTTP 503 errors.

Intermittent SQL Server does not exist or access denied.

ASP.NET application with intermittent returns of 503 errors with the
following message.
System.Data.SqlClient.SqlException: SQL Server does not exist or access
denied. SQL Server does not exist or access denied.
An unhandled exception was generated during the execution of the
current web request. Information regarding the origin and location of
the exception can be identified using the exception stack trace below.
Servers running Windows 2003 SQL 2000.
This has been a problem for a long time (less than .3% of the time). No
code changes have been made. The only changes was Windows SP1 and post
SP patches on all servers. The patching may have made this issue more
pronounced.
Any suggestions from the experts?
Thanks,
Roxanne
When they are intermittent they are difficult to track down.
Often those related to network connectivity issues. Have you
checked the event logs on the IIS box - particularly looking
for any network related issues?
-Sue
On 2 Nov 2005 10:32:52 -0800, roxy636@.yahoo.com wrote:

>ASP.NET application with intermittent returns of 503 errors with the
>following message.
>System.Data.SqlClient.SqlException: SQL Server does not exist or access
>denied. SQL Server does not exist or access denied.
>An unhandled exception was generated during the execution of the
>current web request. Information regarding the origin and location of
>the exception can be identified using the exception stack trace below.
>Servers running Windows 2003 SQL 2000.
>This has been a problem for a long time (less than .3% of the time). No
>code changes have been made. The only changes was Windows SP1 and post
>SP patches on all servers. The patching may have made this issue more
>pronounced.
>Any suggestions from the experts?
>Thanks,
>Roxanne
|||Search no further...
http://support.microsoft.com/default...b;en-us;328476
Note, this answer took forever to find so I'm posting everywhere...
|||So was your solution to enable connection pooling? Or was it simply a
tcp/ip configuration issue. Unless you explicitly disable connection
pooling, then connections should be pooled, were you disabling pooling?
Thanks for any info, we are seeing the exact same symptoms at a client
of ours.
|||Connection pooling was disabled for security reasons, so you need to
add registry keys specified in article here:
path:
HKEY_LOCAL_MACHINE\System\CurrentControlSet\servic es\Tcpip\Parameters
type: DWORD
name: TcpTimedWaitDelay
value: 30 (decimal)
type: DWORD
name: MaxUserPort
value: 10000 (decimal)
Ultimately is connection ooliing is enabled this shouldn't happen, but
try it anyway. Check netsats from command prompt...if you see around
4000 connections in TIME_WAIT, then that is the problem.
|||Thanks, since connection pooling is enabled and this is occuring even
soon after a reboot under low load. I do not suspect we are creating
too many connections to sql, but I do suspect some network
communication problems. I am going to further diagnose thier network
configuration relative to the front and back planes of the web server.
But I will not overlook this as a possibility, so I will also get a
netstat -n Thanks so much for the info..
|||Turned out to be Bandwidth Throttling, disabling that option for the
Application Pool alleviated the HTTP 503 errors.

Intermittent Excessive Compilation and Recompilation

We have an SQL Server 2000 sp3 installation (version 8.00.760) that is used
by a ASP.Net appliction.
Normally the rate of SQL compilation and recompilation is as folllows:
Compilations: 900 per minute.
Recompilations: 90 per minute.
On an intermittent basis, these rates jump to much higher levels:
Compilations: 15,000 per minute.
Recompilations: 15,000 per minute.
This behvaiour severly impacts performance and does not seem to have any
specific trigger.
Any insights would be greatly appreciated.
Sounds like you have some pretty poor code. See if this can get you
started:
http://support.microsoft.com/default.aspx?kbid=243586 Troubleshooting
Recompiles
Andrew J. Kelly SQL MVP
"David Sullivan" <DavidSullivan@.discussions.microsoft.com> wrote in message
news:87DA86BE-A83E-4406-AD33-D392D099170B@.microsoft.com...
> We have an SQL Server 2000 sp3 installation (version 8.00.760) that is
> used
> by a ASP.Net appliction.
> Normally the rate of SQL compilation and recompilation is as folllows:
> Compilations: 900 per minute.
> Recompilations: 90 per minute.
> On an intermittent basis, these rates jump to much higher levels:
> Compilations: 15,000 per minute.
> Recompilations: 15,000 per minute.
> This behvaiour severly impacts performance and does not seem to have any
> specific trigger.
> Any insights would be greatly appreciated.
|||What are your relative Batch Requests per second at the indicated times?
Could be memory pressure causing SQL Server to page. What the last set of
metrics is telling you is that everything is being flushed from the Proc
Cache.
Take a look at Cache Manager, Cache Hit Ratio for all of the instances.
Also, take a look at DBCC MEMORYSTATS to see what the relative distribution
of the memory management of the Buffer Pool looks like.
What edition are your running? How much memory? CPUs? Etc. etc., etc.
Sincerely,
Anthony Thomas

"David Sullivan" <DavidSullivan@.discussions.microsoft.com> wrote in message
news:87DA86BE-A83E-4406-AD33-D392D099170B@.microsoft.com...
We have an SQL Server 2000 sp3 installation (version 8.00.760) that is used
by a ASP.Net appliction.
Normally the rate of SQL compilation and recompilation is as folllows:
Compilations: 900 per minute.
Recompilations: 90 per minute.
On an intermittent basis, these rates jump to much higher levels:
Compilations: 15,000 per minute.
Recompilations: 15,000 per minute.
This behvaiour severly impacts performance and does not seem to have any
specific trigger.
Any insights would be greatly appreciated.
|||Hi Anthony,
Thanks for your feedback.
"Anthony Thomas" wrote:

> What are your relative Batch Requests per second at the indicated times?
Between 6000 and 9000 batches per minute - so 60 to 150 per second.

> Could be memory pressure causing SQL Server to page. What the last set of
> metrics is telling you is that everything is being flushed from the Proc
> Cache.
That's the weird thing. The proc cache size does not change to any great
degree.
It sits from 1Gb to 1.5Gb throughout the periods of execessive compilation.
The cache hit ratio is 90% or above throughout this period also.
However, there small "ripples" in the level of the proc cache. - it's as
though every proc execution results in at least once compilation.

> Take a look at Cache Manager, Cache Hit Ratio for all of the instances.
> Also, take a look at DBCC MEMORYSTATS to see what the relative distribution
> of the memory management of the Buffer Pool looks like.
> What edition are your running? How much memory? CPUs? Etc. etc., etc.
Enterprise Edition
6656 Mbytes RAM allocated to SQL Server.
IBM X335 - Twin Xeon 2.8GHz with hyper-threading OFF.
Database is approx 17GBytes.

> Sincerely,
>
> Anthony Thomas
>
> --
> "David Sullivan" <DavidSullivan@.discussions.microsoft.com> wrote in message
> news:87DA86BE-A83E-4406-AD33-D392D099170B@.microsoft.com...
> We have an SQL Server 2000 sp3 installation (version 8.00.760) that is used
> by a ASP.Net appliction.
> Normally the rate of SQL compilation and recompilation is as folllows:
> Compilations: 900 per minute.
> Recompilations: 90 per minute.
> On an intermittent basis, these rates jump to much higher levels:
> Compilations: 15,000 per minute.
> Recompilations: 15,000 per minute.
> This behvaiour severly impacts performance and does not seem to have any
> specific trigger.
> Any insights would be greatly appreciated.
>
|||A cache size of 1.0 to 1.5GB on a system that small wreaks of adhoc queries
and hardly any stored procedures. You need to optimize you code so the plans
can be reused.
Andrew J. Kelly SQL MVP
"David Sullivan" <DavidSullivan@.discussions.microsoft.com> wrote in message
news:C8F829AD-8C2C-4310-9F06-1269A9855816@.microsoft.com...[vbcol=seagreen]
> Hi Anthony,
> Thanks for your feedback.
> "Anthony Thomas" wrote:
> Between 6000 and 9000 batches per minute - so 60 to 150 per second.
> That's the weird thing. The proc cache size does not change to any great
> degree.
> It sits from 1Gb to 1.5Gb throughout the periods of execessive
> compilation.
> The cache hit ratio is 90% or above throughout this period also.
> However, there small "ripples" in the level of the proc cache. - it's as
> though every proc execution results in at least once compilation.
> Enterprise Edition
> 6656 Mbytes RAM allocated to SQL Server.
> IBM X335 - Twin Xeon 2.8GHz with hyper-threading OFF.
> Database is approx 17GBytes.
|||This may be so, but this alone does not explain why the rate of compilation
jumps 15 times the normal rate on a random basis.
"Andrew J. Kelly" wrote:

> A cache size of 1.0 to 1.5GB on a system that small wreaks of adhoc queries
> and hardly any stored procedures. You need to optimize you code so the plans
> can be reused.
> --
> Andrew J. Kelly SQL MVP
>
> "David Sullivan" <DavidSullivan@.discussions.microsoft.com> wrote in message
> news:C8F829AD-8C2C-4310-9F06-1269A9855816@.microsoft.com...
>
>
|||I'd have to agree with Andrew on this. If you have 6.5 GB allocated to SQL
Server, you MUST be running in AWE mode with the OS set to /PAE, or, you
really aren't using as much memory as you think you are.
Also, there is the Buffer Cache, which tends to make up the majority of the
BPool. If you are running in AWE, you should see that at least 50% - 80% of
the lower 3GB all dedicated to Buffer Cache, then the rest of the lower 3GB
should be allocated across the 4 other Memory Managers plus the MEM TO LEAVE
region, the bulk of those 4 dedicated to Proc Cache, which should be no more
than a few hundred MB. The only thing in the upper memory region, ubove
3GB, should be mostly Data Pages.
What those metrics are telling you is that, yes, you are correct, the first
time a procedure, or ad-hoc, query is executed, it is compiled, and the
execution plan put in the Procedure Cache. Recompilations tell you that an
ad-hoc query that was cached is not appropriate for auto-parameterization or
was explicitly recompiled or the procedure wan't found in the cache. This
means you have an enormous number or size of procedures and/or ad-hoc
queries an many are being either flushed for the cache or paged out to the
swap file for insufficient amount of memory space to make room for new
requests. This doens't mean that the Cache Size will change, but that the
current size is insufficient for the load you are throwing at it.
Do me a favor and execute the DBCC MEMORYSTATS, once for each scenario you
are describing now. It may be to your benefit to put this in a SQL Agent
job to append to a file once every 5 minutes or so for a few days, then you
can pick out a few around the events you are discribing. Believe me, an
anylsis of this output can be very useful.
Code the job for this execution:
SELECT RunTime = GETDATE()
DBCC MEMORYSTATS
On the Advanced Tab, specify an output file to push the results to and mark
it to append.
Also, the Batch Request per second is a perfmon metric. SQL Server:Server
BatchRequest/sec. I believe the 6,000 to 9,000 where the jobs or code you
are executing. This metric measures the T-SQL statement batches that are
sent to the server. It tells you how busy you are. The highest recorded
benchmarks TCP-C type are in the range of 1 - 3 million TPS. However,
something more along the lines of 1 - 4 million Batch Requests per HOUR
(300 - 1,000 Batch Requests per second) are more inline with a reasonably
busy server. You have about as much memory as some of our systems that run
in this range but only a 2-way with HTT turned off (why did you do that?)
were are's are 4-way Dells with HTT turned on.
Your database is 17 GB, which is not tiny but not huge either. The machines
I am speaking of above run more than 100 concurrent databases with an
aggregate space of consumption of about 250 GB, all flavors, OLTP, DSS, and
OLAP, which is a trick to manage on a single fail-over cluster, I assure
you.
We will be waiting to see the output above.
Best of luck.
Sincerely,
Anthony Thomas

"David Sullivan" <DavidSullivan@.discussions.microsoft.com> wrote in message
news:52909EE3-A4A7-4CA4-BD7C-9929097D8587@.microsoft.com...
This may be so, but this alone does not explain why the rate of compilation
jumps 15 times the normal rate on a random basis.
"Andrew J. Kelly" wrote:

> A cache size of 1.0 to 1.5GB on a system that small wreaks of adhoc
queries
> and hardly any stored procedures. You need to optimize you code so the
plans
> can be reused.
> --
> Andrew J. Kelly SQL MVP
>
> "David Sullivan" <DavidSullivan@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
> news:C8F829AD-8C2C-4310-9F06-1269A9855816@.microsoft.com...
times?[vbcol=seagreen]
Proc[vbcol=seagreen]
etc.[vbcol=seagreen]
any
>
>
|||I'm sorry, that permon counter is SQL Server:SQL Statistics, Batch Requests
/ sec.
Sincerely,
Anthony Thomas

"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:Oehq3%23LIFHA.580@.TK2MSFTNGP15.phx.gbl...
I'd have to agree with Andrew on this. If you have 6.5 GB allocated to SQL
Server, you MUST be running in AWE mode with the OS set to /PAE, or, you
really aren't using as much memory as you think you are.
Also, there is the Buffer Cache, which tends to make up the majority of the
BPool. If you are running in AWE, you should see that at least 50% - 80% of
the lower 3GB all dedicated to Buffer Cache, then the rest of the lower 3GB
should be allocated across the 4 other Memory Managers plus the MEM TO LEAVE
region, the bulk of those 4 dedicated to Proc Cache, which should be no more
than a few hundred MB. The only thing in the upper memory region, ubove
3GB, should be mostly Data Pages.
What those metrics are telling you is that, yes, you are correct, the first
time a procedure, or ad-hoc, query is executed, it is compiled, and the
execution plan put in the Procedure Cache. Recompilations tell you that an
ad-hoc query that was cached is not appropriate for auto-parameterization or
was explicitly recompiled or the procedure wan't found in the cache. This
means you have an enormous number or size of procedures and/or ad-hoc
queries an many are being either flushed for the cache or paged out to the
swap file for insufficient amount of memory space to make room for new
requests. This doens't mean that the Cache Size will change, but that the
current size is insufficient for the load you are throwing at it.
Do me a favor and execute the DBCC MEMORYSTATS, once for each scenario you
are describing now. It may be to your benefit to put this in a SQL Agent
job to append to a file once every 5 minutes or so for a few days, then you
can pick out a few around the events you are discribing. Believe me, an
anylsis of this output can be very useful.
Code the job for this execution:
SELECT RunTime = GETDATE()
DBCC MEMORYSTATS
On the Advanced Tab, specify an output file to push the results to and mark
it to append.
Also, the Batch Request per second is a perfmon metric. SQL Server:Server
BatchRequest/sec. I believe the 6,000 to 9,000 where the jobs or code you
are executing. This metric measures the T-SQL statement batches that are
sent to the server. It tells you how busy you are. The highest recorded
benchmarks TCP-C type are in the range of 1 - 3 million TPS. However,
something more along the lines of 1 - 4 million Batch Requests per HOUR
(300 - 1,000 Batch Requests per second) are more inline with a reasonably
busy server. You have about as much memory as some of our systems that run
in this range but only a 2-way with HTT turned off (why did you do that?)
were are's are 4-way Dells with HTT turned on.
Your database is 17 GB, which is not tiny but not huge either. The machines
I am speaking of above run more than 100 concurrent databases with an
aggregate space of consumption of about 250 GB, all flavors, OLTP, DSS, and
OLAP, which is a trick to manage on a single fail-over cluster, I assure
you.
We will be waiting to see the output above.
Best of luck.
Sincerely,
Anthony Thomas

"David Sullivan" <DavidSullivan@.discussions.microsoft.com> wrote in message
news:52909EE3-A4A7-4CA4-BD7C-9929097D8587@.microsoft.com...
This may be so, but this alone does not explain why the rate of compilation
jumps 15 times the normal rate on a random basis.
"Andrew J. Kelly" wrote:

> A cache size of 1.0 to 1.5GB on a system that small wreaks of adhoc
queries
> and hardly any stored procedures. You need to optimize you code so the
plans
> can be reused.
> --
> Andrew J. Kelly SQL MVP
>
> "David Sullivan" <DavidSullivan@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
> news:C8F829AD-8C2C-4310-9F06-1269A9855816@.microsoft.com...
times?[vbcol=seagreen]
Proc[vbcol=seagreen]
etc.[vbcol=seagreen]
any
>
>
|||Actually it does. Keeping in mind all the things Anthony stated in his post
you need to consider what happens when SQL Server needs more memory for
something. Since you have such a large percentage of the memory taken up by
the proc cache there will be times when she engine needs to clear out some
cache to make room for something else, probably data. I suspect that even
plans that are being reused are getting cleared and need to be compiled
again. I bet that if you look at your pagelifeexpectancy counter in perfmon
you will see large dips to almost 0 when this happens. This is when the
cache is getting cleared and basically has to start over again. The
PageLifeExpentancy counter would ideally be 1000 or more on a properly tuned
system. I am willing to bet yours hovers around 0 more often than not. You
can look for some magic switch to fix your problems or you can attack the
root of it. You will never get peak performance with adhoc queries that do
not reuse plans and recompile on a regular basis. Once you attack the most
used or heaviest offenders you will see a dramatic impact in overall
performance. Fixing one query that gets called 100K times a day and gets
compiled each time it runs can make a huge impact on performance. Fix
several of those and you are a hero.
Andrew J. Kelly SQL MVP
"David Sullivan" <DavidSullivan@.discussions.microsoft.com> wrote in message
news:52909EE3-A4A7-4CA4-BD7C-9929097D8587@.microsoft.com...[vbcol=seagreen]
> This may be so, but this alone does not explain why the rate of
> compilation
> jumps 15 times the normal rate on a random basis.
>
> "Andrew J. Kelly" wrote:
|||I'm sorry but instead of DBCC MEMORYSTATS, it is DBCC MEMORYSTATUS.
Sincerely,
Anthony Thomas

"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:uaCVQAMIFHA.2784@.TK2MSFTNGP09.phx.gbl...
I'm sorry, that permon counter is SQL Server:SQL Statistics, Batch Requests
/ sec.
Sincerely,
Anthony Thomas

"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:Oehq3%23LIFHA.580@.TK2MSFTNGP15.phx.gbl...
I'd have to agree with Andrew on this. If you have 6.5 GB allocated to SQL
Server, you MUST be running in AWE mode with the OS set to /PAE, or, you
really aren't using as much memory as you think you are.
Also, there is the Buffer Cache, which tends to make up the majority of the
BPool. If you are running in AWE, you should see that at least 50% - 80% of
the lower 3GB all dedicated to Buffer Cache, then the rest of the lower 3GB
should be allocated across the 4 other Memory Managers plus the MEM TO LEAVE
region, the bulk of those 4 dedicated to Proc Cache, which should be no more
than a few hundred MB. The only thing in the upper memory region, ubove
3GB, should be mostly Data Pages.
What those metrics are telling you is that, yes, you are correct, the first
time a procedure, or ad-hoc, query is executed, it is compiled, and the
execution plan put in the Procedure Cache. Recompilations tell you that an
ad-hoc query that was cached is not appropriate for auto-parameterization or
was explicitly recompiled or the procedure wan't found in the cache. This
means you have an enormous number or size of procedures and/or ad-hoc
queries an many are being either flushed for the cache or paged out to the
swap file for insufficient amount of memory space to make room for new
requests. This doens't mean that the Cache Size will change, but that the
current size is insufficient for the load you are throwing at it.
Do me a favor and execute the DBCC MEMORYSTATS, once for each scenario you
are describing now. It may be to your benefit to put this in a SQL Agent
job to append to a file once every 5 minutes or so for a few days, then you
can pick out a few around the events you are discribing. Believe me, an
anylsis of this output can be very useful.
Code the job for this execution:
SELECT RunTime = GETDATE()
DBCC MEMORYSTATS
On the Advanced Tab, specify an output file to push the results to and mark
it to append.
Also, the Batch Request per second is a perfmon metric. SQL Server:Server
BatchRequest/sec. I believe the 6,000 to 9,000 where the jobs or code you
are executing. This metric measures the T-SQL statement batches that are
sent to the server. It tells you how busy you are. The highest recorded
benchmarks TCP-C type are in the range of 1 - 3 million TPS. However,
something more along the lines of 1 - 4 million Batch Requests per HOUR
(300 - 1,000 Batch Requests per second) are more inline with a reasonably
busy server. You have about as much memory as some of our systems that run
in this range but only a 2-way with HTT turned off (why did you do that?)
were are's are 4-way Dells with HTT turned on.
Your database is 17 GB, which is not tiny but not huge either. The machines
I am speaking of above run more than 100 concurrent databases with an
aggregate space of consumption of about 250 GB, all flavors, OLTP, DSS, and
OLAP, which is a trick to manage on a single fail-over cluster, I assure
you.
We will be waiting to see the output above.
Best of luck.
Sincerely,
Anthony Thomas

"David Sullivan" <DavidSullivan@.discussions.microsoft.com> wrote in message
news:52909EE3-A4A7-4CA4-BD7C-9929097D8587@.microsoft.com...
This may be so, but this alone does not explain why the rate of compilation
jumps 15 times the normal rate on a random basis.
"Andrew J. Kelly" wrote:

> A cache size of 1.0 to 1.5GB on a system that small wreaks of adhoc
queries
> and hardly any stored procedures. You need to optimize you code so the
plans
> can be reused.
> --
> Andrew J. Kelly SQL MVP
>
> "David Sullivan" <DavidSullivan@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
> news:C8F829AD-8C2C-4310-9F06-1269A9855816@.microsoft.com...
times?[vbcol=seagreen]
Proc[vbcol=seagreen]
etc.[vbcol=seagreen]
any
>
>

Intermittent Excessive Compilation and Recompilation

We have an SQL Server 2000 sp3 installation (version 8.00.760) that is used
by a ASP.Net appliction.
Normally the rate of SQL compilation and recompilation is as folllows:
Compilations: 900 per minute.
Recompilations: 90 per minute.
On an intermittent basis, these rates jump to much higher levels:
Compilations: 15,000 per minute.
Recompilations: 15,000 per minute.
This behvaiour severly impacts performance and does not seem to have any
specific trigger.
Any insights would be greatly appreciated.Sounds like you have some pretty poor code. See if this can get you
started:
http://support.microsoft.com/default.aspx?kbid=243586 Troubleshooting
Recompiles
Andrew J. Kelly SQL MVP
"David Sullivan" <DavidSullivan@.discussions.microsoft.com> wrote in message
news:87DA86BE-A83E-4406-AD33-D392D099170B@.microsoft.com...
> We have an SQL Server 2000 sp3 installation (version 8.00.760) that is
> used
> by a ASP.Net appliction.
> Normally the rate of SQL compilation and recompilation is as folllows:
> Compilations: 900 per minute.
> Recompilations: 90 per minute.
> On an intermittent basis, these rates jump to much higher levels:
> Compilations: 15,000 per minute.
> Recompilations: 15,000 per minute.
> This behvaiour severly impacts performance and does not seem to have any
> specific trigger.
> Any insights would be greatly appreciated.|||What are your relative Batch Requests per second at the indicated times?
Could be memory pressure causing SQL Server to page. What the last set of
metrics is telling you is that everything is being flushed from the Proc
Cache.
Take a look at Cache Manager, Cache Hit Ratio for all of the instances.
Also, take a look at DBCC MEMORYSTATS to see what the relative distribution
of the memory management of the Buffer Pool looks like.
What edition are your running? How much memory? CPUs? Etc. etc., etc.
Sincerely,
Anthony Thomas
"David Sullivan" <DavidSullivan@.discussions.microsoft.com> wrote in message
news:87DA86BE-A83E-4406-AD33-D392D099170B@.microsoft.com...
We have an SQL Server 2000 sp3 installation (version 8.00.760) that is used
by a ASP.Net appliction.
Normally the rate of SQL compilation and recompilation is as folllows:
Compilations: 900 per minute.
Recompilations: 90 per minute.
On an intermittent basis, these rates jump to much higher levels:
Compilations: 15,000 per minute.
Recompilations: 15,000 per minute.
This behvaiour severly impacts performance and does not seem to have any
specific trigger.
Any insights would be greatly appreciated.|||Hi Anthony,
Thanks for your feedback.
"Anthony Thomas" wrote:

> What are your relative Batch Requests per second at the indicated times?
Between 6000 and 9000 batches per minute - so 60 to 150 per second.

> Could be memory pressure causing SQL Server to page. What the last set of
> metrics is telling you is that everything is being flushed from the Proc
> Cache.
That's the weird thing. The proc cache size does not change to any great
degree.
It sits from 1Gb to 1.5Gb throughout the periods of execessive compilation.
The cache hit ratio is 90% or above throughout this period also.
However, there small "ripples" in the level of the proc cache. - it's as
though every proc execution results in at least once compilation.

> Take a look at Cache Manager, Cache Hit Ratio for all of the instances.
> Also, take a look at DBCC MEMORYSTATS to see what the relative distributio
n
> of the memory management of the Buffer Pool looks like.
> What edition are your running? How much memory? CPUs? Etc. etc., etc.
Enterprise Edition
6656 Mbytes RAM allocated to SQL Server.
IBM X335 - Twin Xeon 2.8GHz with hyper-threading OFF.
Database is approx 17GBytes.

> Sincerely,
>
> Anthony Thomas
>
> --
> "David Sullivan" <DavidSullivan@.discussions.microsoft.com> wrote in messag
e
> news:87DA86BE-A83E-4406-AD33-D392D099170B@.microsoft.com...
> We have an SQL Server 2000 sp3 installation (version 8.00.760) that is use
d
> by a ASP.Net appliction.
> Normally the rate of SQL compilation and recompilation is as folllows:
> Compilations: 900 per minute.
> Recompilations: 90 per minute.
> On an intermittent basis, these rates jump to much higher levels:
> Compilations: 15,000 per minute.
> Recompilations: 15,000 per minute.
> This behvaiour severly impacts performance and does not seem to have any
> specific trigger.
> Any insights would be greatly appreciated.
>|||A cache size of 1.0 to 1.5GB on a system that small wreaks of adhoc queries
and hardly any stored procedures. You need to optimize you code so the plans
can be reused.
Andrew J. Kelly SQL MVP
"David Sullivan" <DavidSullivan@.discussions.microsoft.com> wrote in message
news:C8F829AD-8C2C-4310-9F06-1269A9855816@.microsoft.com...[vbcol=seagreen]
> Hi Anthony,
> Thanks for your feedback.
> "Anthony Thomas" wrote:
>
> Between 6000 and 9000 batches per minute - so 60 to 150 per second.
>
> That's the weird thing. The proc cache size does not change to any great
> degree.
> It sits from 1Gb to 1.5Gb throughout the periods of execessive
> compilation.
> The cache hit ratio is 90% or above throughout this period also.
> However, there small "ripples" in the level of the proc cache. - it's as
> though every proc execution results in at least once compilation.
>
> Enterprise Edition
> 6656 Mbytes RAM allocated to SQL Server.
> IBM X335 - Twin Xeon 2.8GHz with hyper-threading OFF.
> Database is approx 17GBytes.
>|||This may be so, but this alone does not explain why the rate of compilation
jumps 15 times the normal rate on a random basis.
"Andrew J. Kelly" wrote:

> A cache size of 1.0 to 1.5GB on a system that small wreaks of adhoc querie
s
> and hardly any stored procedures. You need to optimize you code so the pla
ns
> can be reused.
> --
> Andrew J. Kelly SQL MVP
>
> "David Sullivan" <DavidSullivan@.discussions.microsoft.com> wrote in messag
e
> news:C8F829AD-8C2C-4310-9F06-1269A9855816@.microsoft.com...
>
>|||I'd have to agree with Andrew on this. If you have 6.5 GB allocated to SQL
Server, you MUST be running in AWE mode with the OS set to /PAE, or, you
really aren't using as much memory as you think you are.
Also, there is the Buffer Cache, which tends to make up the majority of the
BPool. If you are running in AWE, you should see that at least 50% - 80% of
the lower 3GB all dedicated to Buffer Cache, then the rest of the lower 3GB
should be allocated across the 4 other Memory Managers plus the MEM TO LEAVE
region, the bulk of those 4 dedicated to Proc Cache, which should be no more
than a few hundred MB. The only thing in the upper memory region, ubove
3GB, should be mostly Data Pages.
What those metrics are telling you is that, yes, you are correct, the first
time a procedure, or ad-hoc, query is executed, it is compiled, and the
execution plan put in the Procedure Cache. Recompilations tell you that an
ad-hoc query that was cached is not appropriate for auto-parameterization or
was explicitly recompiled or the procedure wan't found in the cache. This
means you have an enormous number or size of procedures and/or ad-hoc
queries an many are being either flushed for the cache or paged out to the
swap file for insufficient amount of memory space to make room for new
requests. This doens't mean that the Cache Size will change, but that the
current size is insufficient for the load you are throwing at it.
Do me a favor and execute the DBCC MEMORYSTATS, once for each scenario you
are describing now. It may be to your benefit to put this in a SQL Agent
job to append to a file once every 5 minutes or so for a few days, then you
can pick out a few around the events you are discribing. Believe me, an
anylsis of this output can be very useful.
Code the job for this execution:
SELECT RunTime = GETDATE()
DBCC MEMORYSTATS
On the Advanced Tab, specify an output file to push the results to and mark
it to append.
Also, the Batch Request per second is a perfmon metric. SQL Server:Server
BatchRequest/sec. I believe the 6,000 to 9,000 where the jobs or code you
are executing. This metric measures the T-SQL statement batches that are
sent to the server. It tells you how busy you are. The highest recorded
benchmarks TCP-C type are in the range of 1 - 3 million TPS. However,
something more along the lines of 1 - 4 million Batch Requests per HOUR
(300 - 1,000 Batch Requests per second) are more inline with a reasonably
busy server. You have about as much memory as some of our systems that run
in this range but only a 2-way with HTT turned off (why did you do that?)
were are's are 4-way Dells with HTT turned on.
Your database is 17 GB, which is not tiny but not huge either. The machines
I am speaking of above run more than 100 concurrent databases with an
aggregate space of consumption of about 250 GB, all flavors, OLTP, DSS, and
OLAP, which is a trick to manage on a single fail-over cluster, I assure
you.
We will be waiting to see the output above.
Best of luck.
Sincerely,
Anthony Thomas
"David Sullivan" <DavidSullivan@.discussions.microsoft.com> wrote in message
news:52909EE3-A4A7-4CA4-BD7C-9929097D8587@.microsoft.com...
This may be so, but this alone does not explain why the rate of compilation
jumps 15 times the normal rate on a random basis.
"Andrew J. Kelly" wrote:

> A cache size of 1.0 to 1.5GB on a system that small wreaks of adhoc
queries
> and hardly any stored procedures. You need to optimize you code so the
plans
> can be reused.
> --
> Andrew J. Kelly SQL MVP
>
> "David Sullivan" <DavidSullivan@.discussions.microsoft.com> wrote in
message
> news:C8F829AD-8C2C-4310-9F06-1269A9855816@.microsoft.com...
times?[vbcol=seagreen]
Proc[vbcol=seagreen]
etc.[vbcol=seagreen]
any[vbcol=seagreen]
>
>|||I'm sorry, that permon counter is SQL Server:SQL Statistics, Batch Requests
/ sec.
Sincerely,
Anthony Thomas
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:Oehq3%23LIFHA.580@.TK2MSFTNGP15.phx.gbl...
I'd have to agree with Andrew on this. If you have 6.5 GB allocated to SQL
Server, you MUST be running in AWE mode with the OS set to /PAE, or, you
really aren't using as much memory as you think you are.
Also, there is the Buffer Cache, which tends to make up the majority of the
BPool. If you are running in AWE, you should see that at least 50% - 80% of
the lower 3GB all dedicated to Buffer Cache, then the rest of the lower 3GB
should be allocated across the 4 other Memory Managers plus the MEM TO LEAVE
region, the bulk of those 4 dedicated to Proc Cache, which should be no more
than a few hundred MB. The only thing in the upper memory region, ubove
3GB, should be mostly Data Pages.
What those metrics are telling you is that, yes, you are correct, the first
time a procedure, or ad-hoc, query is executed, it is compiled, and the
execution plan put in the Procedure Cache. Recompilations tell you that an
ad-hoc query that was cached is not appropriate for auto-parameterization or
was explicitly recompiled or the procedure wan't found in the cache. This
means you have an enormous number or size of procedures and/or ad-hoc
queries an many are being either flushed for the cache or paged out to the
swap file for insufficient amount of memory space to make room for new
requests. This doens't mean that the Cache Size will change, but that the
current size is insufficient for the load you are throwing at it.
Do me a favor and execute the DBCC MEMORYSTATS, once for each scenario you
are describing now. It may be to your benefit to put this in a SQL Agent
job to append to a file once every 5 minutes or so for a few days, then you
can pick out a few around the events you are discribing. Believe me, an
anylsis of this output can be very useful.
Code the job for this execution:
SELECT RunTime = GETDATE()
DBCC MEMORYSTATS
On the Advanced Tab, specify an output file to push the results to and mark
it to append.
Also, the Batch Request per second is a perfmon metric. SQL Server:Server
BatchRequest/sec. I believe the 6,000 to 9,000 where the jobs or code you
are executing. This metric measures the T-SQL statement batches that are
sent to the server. It tells you how busy you are. The highest recorded
benchmarks TCP-C type are in the range of 1 - 3 million TPS. However,
something more along the lines of 1 - 4 million Batch Requests per HOUR
(300 - 1,000 Batch Requests per second) are more inline with a reasonably
busy server. You have about as much memory as some of our systems that run
in this range but only a 2-way with HTT turned off (why did you do that?)
were are's are 4-way Dells with HTT turned on.
Your database is 17 GB, which is not tiny but not huge either. The machines
I am speaking of above run more than 100 concurrent databases with an
aggregate space of consumption of about 250 GB, all flavors, OLTP, DSS, and
OLAP, which is a trick to manage on a single fail-over cluster, I assure
you.
We will be waiting to see the output above.
Best of luck.
Sincerely,
Anthony Thomas
"David Sullivan" <DavidSullivan@.discussions.microsoft.com> wrote in message
news:52909EE3-A4A7-4CA4-BD7C-9929097D8587@.microsoft.com...
This may be so, but this alone does not explain why the rate of compilation
jumps 15 times the normal rate on a random basis.
"Andrew J. Kelly" wrote:

> A cache size of 1.0 to 1.5GB on a system that small wreaks of adhoc
queries
> and hardly any stored procedures. You need to optimize you code so the
plans
> can be reused.
> --
> Andrew J. Kelly SQL MVP
>
> "David Sullivan" <DavidSullivan@.discussions.microsoft.com> wrote in
message
> news:C8F829AD-8C2C-4310-9F06-1269A9855816@.microsoft.com...
times?[vbcol=seagreen]
Proc[vbcol=seagreen]
etc.[vbcol=seagreen]
any[vbcol=seagreen]
>
>|||Actually it does. Keeping in mind all the things Anthony stated in his post
you need to consider what happens when SQL Server needs more memory for
something. Since you have such a large percentage of the memory taken up by
the proc cache there will be times when she engine needs to clear out some
cache to make room for something else, probably data. I suspect that even
plans that are being reused are getting cleared and need to be compiled
again. I bet that if you look at your pagelifeexpectancy counter in perfmon
you will see large dips to almost 0 when this happens. This is when the
cache is getting cleared and basically has to start over again. The
PageLifeExpentancy counter would ideally be 1000 or more on a properly tuned
system. I am willing to bet yours hovers around 0 more often than not. You
can look for some magic switch to fix your problems or you can attack the
root of it. You will never get peak performance with adhoc queries that do
not reuse plans and recompile on a regular basis. Once you attack the most
used or heaviest offenders you will see a dramatic impact in overall
performance. Fixing one query that gets called 100K times a day and gets
compiled each time it runs can make a huge impact on performance. Fix
several of those and you are a hero.
--
Andrew J. Kelly SQL MVP
"David Sullivan" <DavidSullivan@.discussions.microsoft.com> wrote in message
news:52909EE3-A4A7-4CA4-BD7C-9929097D8587@.microsoft.com...[vbcol=seagreen]
> This may be so, but this alone does not explain why the rate of
> compilation
> jumps 15 times the normal rate on a random basis.
>
> "Andrew J. Kelly" wrote:
>|||I'm sorry but instead of DBCC MEMORYSTATS, it is DBCC MEMORYSTATUS.
Sincerely,
Anthony Thomas
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:uaCVQAMIFHA.2784@.TK2MSFTNGP09.phx.gbl...
I'm sorry, that permon counter is SQL Server:SQL Statistics, Batch Requests
/ sec.
Sincerely,
Anthony Thomas
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:Oehq3%23LIFHA.580@.TK2MSFTNGP15.phx.gbl...
I'd have to agree with Andrew on this. If you have 6.5 GB allocated to SQL
Server, you MUST be running in AWE mode with the OS set to /PAE, or, you
really aren't using as much memory as you think you are.
Also, there is the Buffer Cache, which tends to make up the majority of the
BPool. If you are running in AWE, you should see that at least 50% - 80% of
the lower 3GB all dedicated to Buffer Cache, then the rest of the lower 3GB
should be allocated across the 4 other Memory Managers plus the MEM TO LEAVE
region, the bulk of those 4 dedicated to Proc Cache, which should be no more
than a few hundred MB. The only thing in the upper memory region, ubove
3GB, should be mostly Data Pages.
What those metrics are telling you is that, yes, you are correct, the first
time a procedure, or ad-hoc, query is executed, it is compiled, and the
execution plan put in the Procedure Cache. Recompilations tell you that an
ad-hoc query that was cached is not appropriate for auto-parameterization or
was explicitly recompiled or the procedure wan't found in the cache. This
means you have an enormous number or size of procedures and/or ad-hoc
queries an many are being either flushed for the cache or paged out to the
swap file for insufficient amount of memory space to make room for new
requests. This doens't mean that the Cache Size will change, but that the
current size is insufficient for the load you are throwing at it.
Do me a favor and execute the DBCC MEMORYSTATS, once for each scenario you
are describing now. It may be to your benefit to put this in a SQL Agent
job to append to a file once every 5 minutes or so for a few days, then you
can pick out a few around the events you are discribing. Believe me, an
anylsis of this output can be very useful.
Code the job for this execution:
SELECT RunTime = GETDATE()
DBCC MEMORYSTATS
On the Advanced Tab, specify an output file to push the results to and mark
it to append.
Also, the Batch Request per second is a perfmon metric. SQL Server:Server
BatchRequest/sec. I believe the 6,000 to 9,000 where the jobs or code you
are executing. This metric measures the T-SQL statement batches that are
sent to the server. It tells you how busy you are. The highest recorded
benchmarks TCP-C type are in the range of 1 - 3 million TPS. However,
something more along the lines of 1 - 4 million Batch Requests per HOUR
(300 - 1,000 Batch Requests per second) are more inline with a reasonably
busy server. You have about as much memory as some of our systems that run
in this range but only a 2-way with HTT turned off (why did you do that?)
were are's are 4-way Dells with HTT turned on.
Your database is 17 GB, which is not tiny but not huge either. The machines
I am speaking of above run more than 100 concurrent databases with an
aggregate space of consumption of about 250 GB, all flavors, OLTP, DSS, and
OLAP, which is a trick to manage on a single fail-over cluster, I assure
you.
We will be waiting to see the output above.
Best of luck.
Sincerely,
Anthony Thomas
"David Sullivan" <DavidSullivan@.discussions.microsoft.com> wrote in message
news:52909EE3-A4A7-4CA4-BD7C-9929097D8587@.microsoft.com...
This may be so, but this alone does not explain why the rate of compilation
jumps 15 times the normal rate on a random basis.
"Andrew J. Kelly" wrote:

> A cache size of 1.0 to 1.5GB on a system that small wreaks of adhoc
queries
> and hardly any stored procedures. You need to optimize you code so the
plans
> can be reused.
> --
> Andrew J. Kelly SQL MVP
>
> "David Sullivan" <DavidSullivan@.discussions.microsoft.com> wrote in
message
> news:C8F829AD-8C2C-4310-9F06-1269A9855816@.microsoft.com...
times?[vbcol=seagreen]
Proc[vbcol=seagreen]
etc.[vbcol=seagreen]
any[vbcol=seagreen]
>
>sql

Intermittent Excessive Compilation and Recompilation

We have an SQL Server 2000 sp3 installation (version 8.00.760) that is used
by a ASP.Net appliction.
Normally the rate of SQL compilation and recompilation is as folllows:
Compilations: 900 per minute.
Recompilations: 90 per minute.
On an intermittent basis, these rates jump to much higher levels:
Compilations: 15,000 per minute.
Recompilations: 15,000 per minute.
This behvaiour severly impacts performance and does not seem to have any
specific trigger.
Any insights would be greatly appreciated.Sounds like you have some pretty poor code. See if this can get you
started:
http://support.microsoft.com/default.aspx?kbid=243586 Troubleshooting
Recompiles
Andrew J. Kelly SQL MVP
"David Sullivan" <DavidSullivan@.discussions.microsoft.com> wrote in message
news:87DA86BE-A83E-4406-AD33-D392D099170B@.microsoft.com...
> We have an SQL Server 2000 sp3 installation (version 8.00.760) that is
> used
> by a ASP.Net appliction.
> Normally the rate of SQL compilation and recompilation is as folllows:
> Compilations: 900 per minute.
> Recompilations: 90 per minute.
> On an intermittent basis, these rates jump to much higher levels:
> Compilations: 15,000 per minute.
> Recompilations: 15,000 per minute.
> This behvaiour severly impacts performance and does not seem to have any
> specific trigger.
> Any insights would be greatly appreciated.|||What are your relative Batch Requests per second at the indicated times?
Could be memory pressure causing SQL Server to page. What the last set of
metrics is telling you is that everything is being flushed from the Proc
Cache.
Take a look at Cache Manager, Cache Hit Ratio for all of the instances.
Also, take a look at DBCC MEMORYSTATS to see what the relative distribution
of the memory management of the Buffer Pool looks like.
What edition are your running? How much memory? CPUs? Etc. etc., etc.
Sincerely,
Anthony Thomas
"David Sullivan" <DavidSullivan@.discussions.microsoft.com> wrote in message
news:87DA86BE-A83E-4406-AD33-D392D099170B@.microsoft.com...
We have an SQL Server 2000 sp3 installation (version 8.00.760) that is used
by a ASP.Net appliction.
Normally the rate of SQL compilation and recompilation is as folllows:
Compilations: 900 per minute.
Recompilations: 90 per minute.
On an intermittent basis, these rates jump to much higher levels:
Compilations: 15,000 per minute.
Recompilations: 15,000 per minute.
This behvaiour severly impacts performance and does not seem to have any
specific trigger.
Any insights would be greatly appreciated.|||Hi Anthony,
Thanks for your feedback.
"Anthony Thomas" wrote:
> What are your relative Batch Requests per second at the indicated times?
Between 6000 and 9000 batches per minute - so 60 to 150 per second.
> Could be memory pressure causing SQL Server to page. What the last set of
> metrics is telling you is that everything is being flushed from the Proc
> Cache.
That's the weird thing. The proc cache size does not change to any great
degree.
It sits from 1Gb to 1.5Gb throughout the periods of execessive compilation.
The cache hit ratio is 90% or above throughout this period also.
However, there small "ripples" in the level of the proc cache. - it's as
though every proc execution results in at least once compilation.
> Take a look at Cache Manager, Cache Hit Ratio for all of the instances.
> Also, take a look at DBCC MEMORYSTATS to see what the relative distribution
> of the memory management of the Buffer Pool looks like.
> What edition are your running? How much memory? CPUs? Etc. etc., etc.
Enterprise Edition
6656 Mbytes RAM allocated to SQL Server.
IBM X335 - Twin Xeon 2.8GHz with hyper-threading OFF.
Database is approx 17GBytes.
> Sincerely,
>
> Anthony Thomas
>
> --
> "David Sullivan" <DavidSullivan@.discussions.microsoft.com> wrote in message
> news:87DA86BE-A83E-4406-AD33-D392D099170B@.microsoft.com...
> We have an SQL Server 2000 sp3 installation (version 8.00.760) that is used
> by a ASP.Net appliction.
> Normally the rate of SQL compilation and recompilation is as folllows:
> Compilations: 900 per minute.
> Recompilations: 90 per minute.
> On an intermittent basis, these rates jump to much higher levels:
> Compilations: 15,000 per minute.
> Recompilations: 15,000 per minute.
> This behvaiour severly impacts performance and does not seem to have any
> specific trigger.
> Any insights would be greatly appreciated.
>|||A cache size of 1.0 to 1.5GB on a system that small wreaks of adhoc queries
and hardly any stored procedures. You need to optimize you code so the plans
can be reused.
--
Andrew J. Kelly SQL MVP
"David Sullivan" <DavidSullivan@.discussions.microsoft.com> wrote in message
news:C8F829AD-8C2C-4310-9F06-1269A9855816@.microsoft.com...
> Hi Anthony,
> Thanks for your feedback.
> "Anthony Thomas" wrote:
>> What are your relative Batch Requests per second at the indicated times?
> Between 6000 and 9000 batches per minute - so 60 to 150 per second.
>> Could be memory pressure causing SQL Server to page. What the last set
>> of
>> metrics is telling you is that everything is being flushed from the Proc
>> Cache.
> That's the weird thing. The proc cache size does not change to any great
> degree.
> It sits from 1Gb to 1.5Gb throughout the periods of execessive
> compilation.
> The cache hit ratio is 90% or above throughout this period also.
> However, there small "ripples" in the level of the proc cache. - it's as
> though every proc execution results in at least once compilation.
>> Take a look at Cache Manager, Cache Hit Ratio for all of the instances.
>> Also, take a look at DBCC MEMORYSTATS to see what the relative
>> distribution
>> of the memory management of the Buffer Pool looks like.
>> What edition are your running? How much memory? CPUs? Etc. etc., etc.
> Enterprise Edition
> 6656 Mbytes RAM allocated to SQL Server.
> IBM X335 - Twin Xeon 2.8GHz with hyper-threading OFF.
> Database is approx 17GBytes.
>> Sincerely,
>>
>> Anthony Thomas
>>
>> --
>> "David Sullivan" <DavidSullivan@.discussions.microsoft.com> wrote in
>> message
>> news:87DA86BE-A83E-4406-AD33-D392D099170B@.microsoft.com...
>> We have an SQL Server 2000 sp3 installation (version 8.00.760) that is
>> used
>> by a ASP.Net appliction.
>> Normally the rate of SQL compilation and recompilation is as folllows:
>> Compilations: 900 per minute.
>> Recompilations: 90 per minute.
>> On an intermittent basis, these rates jump to much higher levels:
>> Compilations: 15,000 per minute.
>> Recompilations: 15,000 per minute.
>> This behvaiour severly impacts performance and does not seem to have any
>> specific trigger.
>> Any insights would be greatly appreciated.|||This may be so, but this alone does not explain why the rate of compilation
jumps 15 times the normal rate on a random basis.
"Andrew J. Kelly" wrote:
> A cache size of 1.0 to 1.5GB on a system that small wreaks of adhoc queries
> and hardly any stored procedures. You need to optimize you code so the plans
> can be reused.
> --
> Andrew J. Kelly SQL MVP
>
> "David Sullivan" <DavidSullivan@.discussions.microsoft.com> wrote in message
> news:C8F829AD-8C2C-4310-9F06-1269A9855816@.microsoft.com...
> > Hi Anthony,
> > Thanks for your feedback.
> >
> > "Anthony Thomas" wrote:
> >
> >> What are your relative Batch Requests per second at the indicated times?
> > Between 6000 and 9000 batches per minute - so 60 to 150 per second.
> >
> >> Could be memory pressure causing SQL Server to page. What the last set
> >> of
> >> metrics is telling you is that everything is being flushed from the Proc
> >> Cache.
> > That's the weird thing. The proc cache size does not change to any great
> > degree.
> > It sits from 1Gb to 1.5Gb throughout the periods of execessive
> > compilation.
> > The cache hit ratio is 90% or above throughout this period also.
> > However, there small "ripples" in the level of the proc cache. - it's as
> > though every proc execution results in at least once compilation.
> >
> >>
> >> Take a look at Cache Manager, Cache Hit Ratio for all of the instances.
> >> Also, take a look at DBCC MEMORYSTATS to see what the relative
> >> distribution
> >> of the memory management of the Buffer Pool looks like.
> >>
> >> What edition are your running? How much memory? CPUs? Etc. etc., etc.
> > Enterprise Edition
> > 6656 Mbytes RAM allocated to SQL Server.
> > IBM X335 - Twin Xeon 2.8GHz with hyper-threading OFF.
> > Database is approx 17GBytes.
> >
> >>
> >> Sincerely,
> >>
> >>
> >> Anthony Thomas
> >>
> >>
> >> --
> >>
> >> "David Sullivan" <DavidSullivan@.discussions.microsoft.com> wrote in
> >> message
> >> news:87DA86BE-A83E-4406-AD33-D392D099170B@.microsoft.com...
> >> We have an SQL Server 2000 sp3 installation (version 8.00.760) that is
> >> used
> >> by a ASP.Net appliction.
> >> Normally the rate of SQL compilation and recompilation is as folllows:
> >> Compilations: 900 per minute.
> >> Recompilations: 90 per minute.
> >>
> >> On an intermittent basis, these rates jump to much higher levels:
> >> Compilations: 15,000 per minute.
> >> Recompilations: 15,000 per minute.
> >>
> >> This behvaiour severly impacts performance and does not seem to have any
> >> specific trigger.
> >> Any insights would be greatly appreciated.
> >>
>
>|||I'd have to agree with Andrew on this. If you have 6.5 GB allocated to SQL
Server, you MUST be running in AWE mode with the OS set to /PAE, or, you
really aren't using as much memory as you think you are.
Also, there is the Buffer Cache, which tends to make up the majority of the
BPool. If you are running in AWE, you should see that at least 50% - 80% of
the lower 3GB all dedicated to Buffer Cache, then the rest of the lower 3GB
should be allocated across the 4 other Memory Managers plus the MEM TO LEAVE
region, the bulk of those 4 dedicated to Proc Cache, which should be no more
than a few hundred MB. The only thing in the upper memory region, ubove
3GB, should be mostly Data Pages.
What those metrics are telling you is that, yes, you are correct, the first
time a procedure, or ad-hoc, query is executed, it is compiled, and the
execution plan put in the Procedure Cache. Recompilations tell you that an
ad-hoc query that was cached is not appropriate for auto-parameterization or
was explicitly recompiled or the procedure wan't found in the cache. This
means you have an enormous number or size of procedures and/or ad-hoc
queries an many are being either flushed for the cache or paged out to the
swap file for insufficient amount of memory space to make room for new
requests. This doens't mean that the Cache Size will change, but that the
current size is insufficient for the load you are throwing at it.
Do me a favor and execute the DBCC MEMORYSTATS, once for each scenario you
are describing now. It may be to your benefit to put this in a SQL Agent
job to append to a file once every 5 minutes or so for a few days, then you
can pick out a few around the events you are discribing. Believe me, an
anylsis of this output can be very useful.
Code the job for this execution:
SELECT RunTime = GETDATE()
DBCC MEMORYSTATS
On the Advanced Tab, specify an output file to push the results to and mark
it to append.
Also, the Batch Request per second is a perfmon metric. SQL Server:Server
BatchRequest/sec. I believe the 6,000 to 9,000 where the jobs or code you
are executing. This metric measures the T-SQL statement batches that are
sent to the server. It tells you how busy you are. The highest recorded
benchmarks TCP-C type are in the range of 1 - 3 million TPS. However,
something more along the lines of 1 - 4 million Batch Requests per HOUR
(300 - 1,000 Batch Requests per second) are more inline with a reasonably
busy server. You have about as much memory as some of our systems that run
in this range but only a 2-way with HTT turned off (why did you do that?)
were are's are 4-way Dells with HTT turned on.
Your database is 17 GB, which is not tiny but not huge either. The machines
I am speaking of above run more than 100 concurrent databases with an
aggregate space of consumption of about 250 GB, all flavors, OLTP, DSS, and
OLAP, which is a trick to manage on a single fail-over cluster, I assure
you.
We will be waiting to see the output above.
Best of luck.
Sincerely,
Anthony Thomas
"David Sullivan" <DavidSullivan@.discussions.microsoft.com> wrote in message
news:52909EE3-A4A7-4CA4-BD7C-9929097D8587@.microsoft.com...
This may be so, but this alone does not explain why the rate of compilation
jumps 15 times the normal rate on a random basis.
"Andrew J. Kelly" wrote:
> A cache size of 1.0 to 1.5GB on a system that small wreaks of adhoc
queries
> and hardly any stored procedures. You need to optimize you code so the
plans
> can be reused.
> --
> Andrew J. Kelly SQL MVP
>
> "David Sullivan" <DavidSullivan@.discussions.microsoft.com> wrote in
message
> news:C8F829AD-8C2C-4310-9F06-1269A9855816@.microsoft.com...
> > Hi Anthony,
> > Thanks for your feedback.
> >
> > "Anthony Thomas" wrote:
> >
> >> What are your relative Batch Requests per second at the indicated
times?
> > Between 6000 and 9000 batches per minute - so 60 to 150 per second.
> >
> >> Could be memory pressure causing SQL Server to page. What the last set
> >> of
> >> metrics is telling you is that everything is being flushed from the
Proc
> >> Cache.
> > That's the weird thing. The proc cache size does not change to any great
> > degree.
> > It sits from 1Gb to 1.5Gb throughout the periods of execessive
> > compilation.
> > The cache hit ratio is 90% or above throughout this period also.
> > However, there small "ripples" in the level of the proc cache. - it's as
> > though every proc execution results in at least once compilation.
> >
> >>
> >> Take a look at Cache Manager, Cache Hit Ratio for all of the instances.
> >> Also, take a look at DBCC MEMORYSTATS to see what the relative
> >> distribution
> >> of the memory management of the Buffer Pool looks like.
> >>
> >> What edition are your running? How much memory? CPUs? Etc. etc.,
etc.
> > Enterprise Edition
> > 6656 Mbytes RAM allocated to SQL Server.
> > IBM X335 - Twin Xeon 2.8GHz with hyper-threading OFF.
> > Database is approx 17GBytes.
> >
> >>
> >> Sincerely,
> >>
> >>
> >> Anthony Thomas
> >>
> >>
> >> --
> >>
> >> "David Sullivan" <DavidSullivan@.discussions.microsoft.com> wrote in
> >> message
> >> news:87DA86BE-A83E-4406-AD33-D392D099170B@.microsoft.com...
> >> We have an SQL Server 2000 sp3 installation (version 8.00.760) that is
> >> used
> >> by a ASP.Net appliction.
> >> Normally the rate of SQL compilation and recompilation is as folllows:
> >> Compilations: 900 per minute.
> >> Recompilations: 90 per minute.
> >>
> >> On an intermittent basis, these rates jump to much higher levels:
> >> Compilations: 15,000 per minute.
> >> Recompilations: 15,000 per minute.
> >>
> >> This behvaiour severly impacts performance and does not seem to have
any
> >> specific trigger.
> >> Any insights would be greatly appreciated.
> >>
>
>|||I'm sorry, that permon counter is SQL Server:SQL Statistics, Batch Requests
/ sec.
Sincerely,
Anthony Thomas
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:Oehq3%23LIFHA.580@.TK2MSFTNGP15.phx.gbl...
I'd have to agree with Andrew on this. If you have 6.5 GB allocated to SQL
Server, you MUST be running in AWE mode with the OS set to /PAE, or, you
really aren't using as much memory as you think you are.
Also, there is the Buffer Cache, which tends to make up the majority of the
BPool. If you are running in AWE, you should see that at least 50% - 80% of
the lower 3GB all dedicated to Buffer Cache, then the rest of the lower 3GB
should be allocated across the 4 other Memory Managers plus the MEM TO LEAVE
region, the bulk of those 4 dedicated to Proc Cache, which should be no more
than a few hundred MB. The only thing in the upper memory region, ubove
3GB, should be mostly Data Pages.
What those metrics are telling you is that, yes, you are correct, the first
time a procedure, or ad-hoc, query is executed, it is compiled, and the
execution plan put in the Procedure Cache. Recompilations tell you that an
ad-hoc query that was cached is not appropriate for auto-parameterization or
was explicitly recompiled or the procedure wan't found in the cache. This
means you have an enormous number or size of procedures and/or ad-hoc
queries an many are being either flushed for the cache or paged out to the
swap file for insufficient amount of memory space to make room for new
requests. This doens't mean that the Cache Size will change, but that the
current size is insufficient for the load you are throwing at it.
Do me a favor and execute the DBCC MEMORYSTATS, once for each scenario you
are describing now. It may be to your benefit to put this in a SQL Agent
job to append to a file once every 5 minutes or so for a few days, then you
can pick out a few around the events you are discribing. Believe me, an
anylsis of this output can be very useful.
Code the job for this execution:
SELECT RunTime = GETDATE()
DBCC MEMORYSTATS
On the Advanced Tab, specify an output file to push the results to and mark
it to append.
Also, the Batch Request per second is a perfmon metric. SQL Server:Server
BatchRequest/sec. I believe the 6,000 to 9,000 where the jobs or code you
are executing. This metric measures the T-SQL statement batches that are
sent to the server. It tells you how busy you are. The highest recorded
benchmarks TCP-C type are in the range of 1 - 3 million TPS. However,
something more along the lines of 1 - 4 million Batch Requests per HOUR
(300 - 1,000 Batch Requests per second) are more inline with a reasonably
busy server. You have about as much memory as some of our systems that run
in this range but only a 2-way with HTT turned off (why did you do that?)
were are's are 4-way Dells with HTT turned on.
Your database is 17 GB, which is not tiny but not huge either. The machines
I am speaking of above run more than 100 concurrent databases with an
aggregate space of consumption of about 250 GB, all flavors, OLTP, DSS, and
OLAP, which is a trick to manage on a single fail-over cluster, I assure
you.
We will be waiting to see the output above.
Best of luck.
Sincerely,
Anthony Thomas
"David Sullivan" <DavidSullivan@.discussions.microsoft.com> wrote in message
news:52909EE3-A4A7-4CA4-BD7C-9929097D8587@.microsoft.com...
This may be so, but this alone does not explain why the rate of compilation
jumps 15 times the normal rate on a random basis.
"Andrew J. Kelly" wrote:
> A cache size of 1.0 to 1.5GB on a system that small wreaks of adhoc
queries
> and hardly any stored procedures. You need to optimize you code so the
plans
> can be reused.
> --
> Andrew J. Kelly SQL MVP
>
> "David Sullivan" <DavidSullivan@.discussions.microsoft.com> wrote in
message
> news:C8F829AD-8C2C-4310-9F06-1269A9855816@.microsoft.com...
> > Hi Anthony,
> > Thanks for your feedback.
> >
> > "Anthony Thomas" wrote:
> >
> >> What are your relative Batch Requests per second at the indicated
times?
> > Between 6000 and 9000 batches per minute - so 60 to 150 per second.
> >
> >> Could be memory pressure causing SQL Server to page. What the last set
> >> of
> >> metrics is telling you is that everything is being flushed from the
Proc
> >> Cache.
> > That's the weird thing. The proc cache size does not change to any great
> > degree.
> > It sits from 1Gb to 1.5Gb throughout the periods of execessive
> > compilation.
> > The cache hit ratio is 90% or above throughout this period also.
> > However, there small "ripples" in the level of the proc cache. - it's as
> > though every proc execution results in at least once compilation.
> >
> >>
> >> Take a look at Cache Manager, Cache Hit Ratio for all of the instances.
> >> Also, take a look at DBCC MEMORYSTATS to see what the relative
> >> distribution
> >> of the memory management of the Buffer Pool looks like.
> >>
> >> What edition are your running? How much memory? CPUs? Etc. etc.,
etc.
> > Enterprise Edition
> > 6656 Mbytes RAM allocated to SQL Server.
> > IBM X335 - Twin Xeon 2.8GHz with hyper-threading OFF.
> > Database is approx 17GBytes.
> >
> >>
> >> Sincerely,
> >>
> >>
> >> Anthony Thomas
> >>
> >>
> >> --
> >>
> >> "David Sullivan" <DavidSullivan@.discussions.microsoft.com> wrote in
> >> message
> >> news:87DA86BE-A83E-4406-AD33-D392D099170B@.microsoft.com...
> >> We have an SQL Server 2000 sp3 installation (version 8.00.760) that is
> >> used
> >> by a ASP.Net appliction.
> >> Normally the rate of SQL compilation and recompilation is as folllows:
> >> Compilations: 900 per minute.
> >> Recompilations: 90 per minute.
> >>
> >> On an intermittent basis, these rates jump to much higher levels:
> >> Compilations: 15,000 per minute.
> >> Recompilations: 15,000 per minute.
> >>
> >> This behvaiour severly impacts performance and does not seem to have
any
> >> specific trigger.
> >> Any insights would be greatly appreciated.
> >>
>
>|||Actually it does. Keeping in mind all the things Anthony stated in his post
you need to consider what happens when SQL Server needs more memory for
something. Since you have such a large percentage of the memory taken up by
the proc cache there will be times when she engine needs to clear out some
cache to make room for something else, probably data. I suspect that even
plans that are being reused are getting cleared and need to be compiled
again. I bet that if you look at your pagelifeexpectancy counter in perfmon
you will see large dips to almost 0 when this happens. This is when the
cache is getting cleared and basically has to start over again. The
PageLifeExpentancy counter would ideally be 1000 or more on a properly tuned
system. I am willing to bet yours hovers around 0 more often than not. You
can look for some magic switch to fix your problems or you can attack the
root of it. You will never get peak performance with adhoc queries that do
not reuse plans and recompile on a regular basis. Once you attack the most
used or heaviest offenders you will see a dramatic impact in overall
performance. Fixing one query that gets called 100K times a day and gets
compiled each time it runs can make a huge impact on performance. Fix
several of those and you are a hero.
--
Andrew J. Kelly SQL MVP
"David Sullivan" <DavidSullivan@.discussions.microsoft.com> wrote in message
news:52909EE3-A4A7-4CA4-BD7C-9929097D8587@.microsoft.com...
> This may be so, but this alone does not explain why the rate of
> compilation
> jumps 15 times the normal rate on a random basis.
>
> "Andrew J. Kelly" wrote:
>> A cache size of 1.0 to 1.5GB on a system that small wreaks of adhoc
>> queries
>> and hardly any stored procedures. You need to optimize you code so the
>> plans
>> can be reused.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "David Sullivan" <DavidSullivan@.discussions.microsoft.com> wrote in
>> message
>> news:C8F829AD-8C2C-4310-9F06-1269A9855816@.microsoft.com...
>> > Hi Anthony,
>> > Thanks for your feedback.
>> >
>> > "Anthony Thomas" wrote:
>> >
>> >> What are your relative Batch Requests per second at the indicated
>> >> times?
>> > Between 6000 and 9000 batches per minute - so 60 to 150 per second.
>> >
>> >> Could be memory pressure causing SQL Server to page. What the last
>> >> set
>> >> of
>> >> metrics is telling you is that everything is being flushed from the
>> >> Proc
>> >> Cache.
>> > That's the weird thing. The proc cache size does not change to any
>> > great
>> > degree.
>> > It sits from 1Gb to 1.5Gb throughout the periods of execessive
>> > compilation.
>> > The cache hit ratio is 90% or above throughout this period also.
>> > However, there small "ripples" in the level of the proc cache. - it's
>> > as
>> > though every proc execution results in at least once compilation.
>> >
>> >>
>> >> Take a look at Cache Manager, Cache Hit Ratio for all of the
>> >> instances.
>> >> Also, take a look at DBCC MEMORYSTATS to see what the relative
>> >> distribution
>> >> of the memory management of the Buffer Pool looks like.
>> >>
>> >> What edition are your running? How much memory? CPUs? Etc. etc.,
>> >> etc.
>> > Enterprise Edition
>> > 6656 Mbytes RAM allocated to SQL Server.
>> > IBM X335 - Twin Xeon 2.8GHz with hyper-threading OFF.
>> > Database is approx 17GBytes.
>> >
>> >>
>> >> Sincerely,
>> >>
>> >>
>> >> Anthony Thomas
>> >>
>> >>
>> >> --
>> >>
>> >> "David Sullivan" <DavidSullivan@.discussions.microsoft.com> wrote in
>> >> message
>> >> news:87DA86BE-A83E-4406-AD33-D392D099170B@.microsoft.com...
>> >> We have an SQL Server 2000 sp3 installation (version 8.00.760) that is
>> >> used
>> >> by a ASP.Net appliction.
>> >> Normally the rate of SQL compilation and recompilation is as folllows:
>> >> Compilations: 900 per minute.
>> >> Recompilations: 90 per minute.
>> >>
>> >> On an intermittent basis, these rates jump to much higher levels:
>> >> Compilations: 15,000 per minute.
>> >> Recompilations: 15,000 per minute.
>> >>
>> >> This behvaiour severly impacts performance and does not seem to have
>> >> any
>> >> specific trigger.
>> >> Any insights would be greatly appreciated.
>> >>
>>|||I'm sorry but instead of DBCC MEMORYSTATS, it is DBCC MEMORYSTATUS.
Sincerely,
Anthony Thomas
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:uaCVQAMIFHA.2784@.TK2MSFTNGP09.phx.gbl...
I'm sorry, that permon counter is SQL Server:SQL Statistics, Batch Requests
/ sec.
Sincerely,
Anthony Thomas
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:Oehq3%23LIFHA.580@.TK2MSFTNGP15.phx.gbl...
I'd have to agree with Andrew on this. If you have 6.5 GB allocated to SQL
Server, you MUST be running in AWE mode with the OS set to /PAE, or, you
really aren't using as much memory as you think you are.
Also, there is the Buffer Cache, which tends to make up the majority of the
BPool. If you are running in AWE, you should see that at least 50% - 80% of
the lower 3GB all dedicated to Buffer Cache, then the rest of the lower 3GB
should be allocated across the 4 other Memory Managers plus the MEM TO LEAVE
region, the bulk of those 4 dedicated to Proc Cache, which should be no more
than a few hundred MB. The only thing in the upper memory region, ubove
3GB, should be mostly Data Pages.
What those metrics are telling you is that, yes, you are correct, the first
time a procedure, or ad-hoc, query is executed, it is compiled, and the
execution plan put in the Procedure Cache. Recompilations tell you that an
ad-hoc query that was cached is not appropriate for auto-parameterization or
was explicitly recompiled or the procedure wan't found in the cache. This
means you have an enormous number or size of procedures and/or ad-hoc
queries an many are being either flushed for the cache or paged out to the
swap file for insufficient amount of memory space to make room for new
requests. This doens't mean that the Cache Size will change, but that the
current size is insufficient for the load you are throwing at it.
Do me a favor and execute the DBCC MEMORYSTATS, once for each scenario you
are describing now. It may be to your benefit to put this in a SQL Agent
job to append to a file once every 5 minutes or so for a few days, then you
can pick out a few around the events you are discribing. Believe me, an
anylsis of this output can be very useful.
Code the job for this execution:
SELECT RunTime = GETDATE()
DBCC MEMORYSTATS
On the Advanced Tab, specify an output file to push the results to and mark
it to append.
Also, the Batch Request per second is a perfmon metric. SQL Server:Server
BatchRequest/sec. I believe the 6,000 to 9,000 where the jobs or code you
are executing. This metric measures the T-SQL statement batches that are
sent to the server. It tells you how busy you are. The highest recorded
benchmarks TCP-C type are in the range of 1 - 3 million TPS. However,
something more along the lines of 1 - 4 million Batch Requests per HOUR
(300 - 1,000 Batch Requests per second) are more inline with a reasonably
busy server. You have about as much memory as some of our systems that run
in this range but only a 2-way with HTT turned off (why did you do that?)
were are's are 4-way Dells with HTT turned on.
Your database is 17 GB, which is not tiny but not huge either. The machines
I am speaking of above run more than 100 concurrent databases with an
aggregate space of consumption of about 250 GB, all flavors, OLTP, DSS, and
OLAP, which is a trick to manage on a single fail-over cluster, I assure
you.
We will be waiting to see the output above.
Best of luck.
Sincerely,
Anthony Thomas
"David Sullivan" <DavidSullivan@.discussions.microsoft.com> wrote in message
news:52909EE3-A4A7-4CA4-BD7C-9929097D8587@.microsoft.com...
This may be so, but this alone does not explain why the rate of compilation
jumps 15 times the normal rate on a random basis.
"Andrew J. Kelly" wrote:
> A cache size of 1.0 to 1.5GB on a system that small wreaks of adhoc
queries
> and hardly any stored procedures. You need to optimize you code so the
plans
> can be reused.
> --
> Andrew J. Kelly SQL MVP
>
> "David Sullivan" <DavidSullivan@.discussions.microsoft.com> wrote in
message
> news:C8F829AD-8C2C-4310-9F06-1269A9855816@.microsoft.com...
> > Hi Anthony,
> > Thanks for your feedback.
> >
> > "Anthony Thomas" wrote:
> >
> >> What are your relative Batch Requests per second at the indicated
times?
> > Between 6000 and 9000 batches per minute - so 60 to 150 per second.
> >
> >> Could be memory pressure causing SQL Server to page. What the last set
> >> of
> >> metrics is telling you is that everything is being flushed from the
Proc
> >> Cache.
> > That's the weird thing. The proc cache size does not change to any great
> > degree.
> > It sits from 1Gb to 1.5Gb throughout the periods of execessive
> > compilation.
> > The cache hit ratio is 90% or above throughout this period also.
> > However, there small "ripples" in the level of the proc cache. - it's as
> > though every proc execution results in at least once compilation.
> >
> >>
> >> Take a look at Cache Manager, Cache Hit Ratio for all of the instances.
> >> Also, take a look at DBCC MEMORYSTATS to see what the relative
> >> distribution
> >> of the memory management of the Buffer Pool looks like.
> >>
> >> What edition are your running? How much memory? CPUs? Etc. etc.,
etc.
> > Enterprise Edition
> > 6656 Mbytes RAM allocated to SQL Server.
> > IBM X335 - Twin Xeon 2.8GHz with hyper-threading OFF.
> > Database is approx 17GBytes.
> >
> >>
> >> Sincerely,
> >>
> >>
> >> Anthony Thomas
> >>
> >>
> >> --
> >>
> >> "David Sullivan" <DavidSullivan@.discussions.microsoft.com> wrote in
> >> message
> >> news:87DA86BE-A83E-4406-AD33-D392D099170B@.microsoft.com...
> >> We have an SQL Server 2000 sp3 installation (version 8.00.760) that is
> >> used
> >> by a ASP.Net appliction.
> >> Normally the rate of SQL compilation and recompilation is as folllows:
> >> Compilations: 900 per minute.
> >> Recompilations: 90 per minute.
> >>
> >> On an intermittent basis, these rates jump to much higher levels:
> >> Compilations: 15,000 per minute.
> >> Recompilations: 15,000 per minute.
> >>
> >> This behvaiour severly impacts performance and does not seem to have
any
> >> specific trigger.
> >> Any insights would be greatly appreciated.
> >>
>
>