Showing posts with label responses. Show all posts
Showing posts with label responses. Show all posts

Sunday, March 11, 2012

Connection from PHP slower when server is remote

I'm trying to get some clues on why I get (much) slower responses from
my PHP applications when dealing with a remote sql server as opposed to
a local sql server.
Here's my situation:
Server #1
MSSQL 2000 sp4
Windows 2000 Server
PHP4 on IIS5 (CGI)
Server #2
MSSQL 2000 sp4
Windows 2003 Server
PHP4 on IIS6 (CGI)
Server #1 and #2 are on the same local network and the same switch. We
use TCP/IP as the connection protocol and our connections use IPs
instead of domain names.
Server#1PHP --> Server#2 MSSQL = slow
Server#2PHP --> Server#1 MSSQL = slow
Server#1PHP --> Server#1 MSSQL = fast
Server#2PHP --> Server#2 MSSQL = fast
This slower behavior is most noticable when many stored procedures
(10-15) are being executed on one php page. When the database is local
on a page such as this, the response time is around 2 seconds. When the
sql server is remote, the response time is between 8 and 15 seconds.
Does this make sense at all? Does anyone have any ideas of what the
bottleneck could be?
Hi
You may want to look at how your network is performing using an network
monitoring tool and perfmon. A local SQL Server could be using shared memory
which may be more significant on a poorly performing network.
You can also use SQL profiler to compare the times taken to execute the
stored procedures on each of the servers, this will allow you to check if
they are similar or acceptable if they are on different hardware.
Check that indexes are not fragmented and statistics are up to date on each
machine. Make sure that you have either a maintenance plan or some job that
will run periodically to maintain your indexes and statistics.
You may also what to check fragmentation of the physical disc. If you HDD is
very fragmented you may want to stop SQL Server and defrag it.
John
"gharmel" wrote:

> I'm trying to get some clues on why I get (much) slower responses from
> my PHP applications when dealing with a remote sql server as opposed to
> a local sql server.
> Here's my situation:
> Server #1
> MSSQL 2000 sp4
> Windows 2000 Server
> PHP4 on IIS5 (CGI)
> Server #2
> MSSQL 2000 sp4
> Windows 2003 Server
> PHP4 on IIS6 (CGI)
> Server #1 and #2 are on the same local network and the same switch. We
> use TCP/IP as the connection protocol and our connections use IPs
> instead of domain names.
> Server#1PHP --> Server#2 MSSQL = slow
> Server#2PHP --> Server#1 MSSQL = slow
> Server#1PHP --> Server#1 MSSQL = fast
> Server#2PHP --> Server#2 MSSQL = fast
> This slower behavior is most noticable when many stored procedures
> (10-15) are being executed on one php page. When the database is local
> on a page such as this, the response time is around 2 seconds. When the
> sql server is remote, the response time is between 8 and 15 seconds.
> Does this make sense at all? Does anyone have any ideas of what the
> bottleneck could be?
>
|||After more investigation it seems that every query had a connect and
disconnect. The act of connecting/disconnecting seems to be much more
expensive when the SQL server is not local.
|||Hi
Usually connection pooling is used to overcome this.
John
"gharmel" wrote:

> After more investigation it seems that every query had a connect and
> disconnect. The act of connecting/disconnecting seems to be much more
> expensive when the SQL server is not local.
>

Connection from PHP slower when server is remote

I'm trying to get some clues on why I get (much) slower responses from
my php applications when dealing with a remote sql server as opposed to
a local sql server.
Here's my situation:
Server #1
MSSQL 2000 sp4
Windows 2000 Server
PHP4 on IIS5 (CGI)
Server #2
MSSQL 2000 sp4
Windows 2003 Server
PHP4 on IIS6 (CGI)
Server #1 and #2 are on the same local network and the same switch. We
use TCP/IP as the connection protocol and our connections use IPs
instead of domain names.
Server#1PHP --> Server#2 MSSQL = slow
Server#2PHP --> Server#1 MSSQL = slow
Server#1PHP --> Server#1 MSSQL = fast
Server#2PHP --> Server#2 MSSQL = fast
This slower behavior is most noticable when many stored procedures
(10-15) are being executed on one php page. When the database is local
on a page such as this, the response time is around 2 seconds. When the
sql server is remote, the response time is between 8 and 15 seconds.
Does this make sense at all? Does anyone have any ideas of what the
bottleneck could be?Hi
You may want to look at how your network is performing using an network
monitoring tool and perfmon. A local SQL Server could be using shared memory
which may be more significant on a poorly performing network.
You can also use SQL profiler to compare the times taken to execute the
stored procedures on each of the servers, this will allow you to check if
they are similar or acceptable if they are on different hardware.
Check that indexes are not fragmented and statistics are up to date on each
machine. Make sure that you have either a maintenance plan or some job that
will run periodically to maintain your indexes and statistics.
You may also what to check fragmentation of the physical disc. If you HDD is
very fragmented you may want to stop SQL Server and defrag it.
John
"gharmel" wrote:

> I'm trying to get some clues on why I get (much) slower responses from
> my php applications when dealing with a remote sql server as opposed to
> a local sql server.
> Here's my situation:
> Server #1
> MSSQL 2000 sp4
> Windows 2000 Server
> PHP4 on IIS5 (CGI)
> Server #2
> MSSQL 2000 sp4
> Windows 2003 Server
> PHP4 on IIS6 (CGI)
> Server #1 and #2 are on the same local network and the same switch. We
> use TCP/IP as the connection protocol and our connections use IPs
> instead of domain names.
> Server#1PHP --> Server#2 MSSQL = slow
> Server#2PHP --> Server#1 MSSQL = slow
> Server#1PHP --> Server#1 MSSQL = fast
> Server#2PHP --> Server#2 MSSQL = fast
> This slower behavior is most noticable when many stored procedures
> (10-15) are being executed on one php page. When the database is local
> on a page such as this, the response time is around 2 seconds. When the
> sql server is remote, the response time is between 8 and 15 seconds.
> Does this make sense at all? Does anyone have any ideas of what the
> bottleneck could be?
>|||After more investigation it seems that every query had a connect and
disconnect. The act of connecting/disconnecting seems to be much more
expensive when the SQL server is not local.|||Hi
Usually connection pooling is used to overcome this.
John
"gharmel" wrote:

> After more investigation it seems that every query had a connect and
> disconnect. The act of connecting/disconnecting seems to be much more
> expensive when the SQL server is not local.
>

Connection from PHP slower when server is remote

I'm trying to get some clues on why I get (much) slower responses from
my PHP applications when dealing with a remote sql server as opposed to
a local sql server.
Here's my situation:
Server #1
MSSQL 2000 sp4
Windows 2000 Server
PHP4 on IIS5 (CGI)
Server #2
MSSQL 2000 sp4
Windows 2003 Server
PHP4 on IIS6 (CGI)
Server #1 and #2 are on the same local network and the same switch. We
use TCP/IP as the connection protocol and our connections use IPs
instead of domain names.
Server#1PHP --> Server#2 MSSQL = slow
Server#2PHP --> Server#1 MSSQL = slow
Server#1PHP --> Server#1 MSSQL = fast
Server#2PHP --> Server#2 MSSQL = fast
This slower behavior is most noticable when many stored procedures
(10-15) are being executed on one php page. When the database is local
on a page such as this, the response time is around 2 seconds. When the
sql server is remote, the response time is between 8 and 15 seconds.
Does this make sense at all? Does anyone have any ideas of what the
bottleneck could be?Hi
You may want to look at how your network is performing using an network
monitoring tool and perfmon. A local SQL Server could be using shared memory
which may be more significant on a poorly performing network.
You can also use SQL profiler to compare the times taken to execute the
stored procedures on each of the servers, this will allow you to check if
they are similar or acceptable if they are on different hardware.
Check that indexes are not fragmented and statistics are up to date on each
machine. Make sure that you have either a maintenance plan or some job that
will run periodically to maintain your indexes and statistics.
You may also what to check fragmentation of the physical disc. If you HDD is
very fragmented you may want to stop SQL Server and defrag it.
John
"gharmel" wrote:
> I'm trying to get some clues on why I get (much) slower responses from
> my PHP applications when dealing with a remote sql server as opposed to
> a local sql server.
> Here's my situation:
> Server #1
> MSSQL 2000 sp4
> Windows 2000 Server
> PHP4 on IIS5 (CGI)
> Server #2
> MSSQL 2000 sp4
> Windows 2003 Server
> PHP4 on IIS6 (CGI)
> Server #1 and #2 are on the same local network and the same switch. We
> use TCP/IP as the connection protocol and our connections use IPs
> instead of domain names.
> Server#1PHP --> Server#2 MSSQL = slow
> Server#2PHP --> Server#1 MSSQL = slow
> Server#1PHP --> Server#1 MSSQL = fast
> Server#2PHP --> Server#2 MSSQL = fast
> This slower behavior is most noticable when many stored procedures
> (10-15) are being executed on one php page. When the database is local
> on a page such as this, the response time is around 2 seconds. When the
> sql server is remote, the response time is between 8 and 15 seconds.
> Does this make sense at all? Does anyone have any ideas of what the
> bottleneck could be?
>|||After more investigation it seems that every query had a connect and
disconnect. The act of connecting/disconnecting seems to be much more
expensive when the SQL server is not local.|||Hi
Usually connection pooling is used to overcome this.
John
"gharmel" wrote:
> After more investigation it seems that every query had a connect and
> disconnect. The act of connecting/disconnecting seems to be much more
> expensive when the SQL server is not local.
>

Wednesday, March 7, 2012

Connection errors

Hi - I originally posted this to windows.server.general, but no responses
there.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
I have database application that stops working and the event log shows two
errors - I suspect that they are related but I don't know for certain and
I'm not sure whether they refer to connections to SQL Server or to the
Windows server.
The first error is number 17883:
Error: 17883, Severity: 1, State: 0
The Scheduler 0 appears to be hung.
And the second is number 17809
Could not connect. The maximum number of '32767' configured ...
Error: 17809, Severity: 20, State: 1
Unfortunately, I don't have the FULL text.
Now, I've investigated this as best I can and Microsoft say that the 17883
error occurs when the underlying disk system is too quick (e.g. when using a
SAN), but that this was fixed in SQL Server 2000 sp4. Yes, my server is
fibre-attached to a SAN **but I'm already running sp4** ... Here are some
URLs that I found:
http://www.developersdex.com/sql/message.asp?p=1866&ID=%3C48E20E01-71FA-483B-ADFC-7806C2FCC9D8%40microsoft.com%3E
http://support.microsoft.com/default.aspx?kbid=810885
http://support.microsoft.com/kb/815056
http://support.microsoft.com/kb/816840
Regarding error 17809, I've put in some monitoring software and the number
of SQL Server connections never goes above 200, so I'm thinking that the
32767 connections is not a SQL Server connection but in fact a "lower" level
connection to the server [I say "lower" because I don't really understand
this side of networking (I'm just a VB developer)]. It sounds to me as if
the Windows 2003 sp1 server hosting the SQL Server has all its connections
open and can't cope with anything new. If this is the case it's either that
Windows 2003 can't cope with being on a SAN (which I simply can't believe)
or - if I'm being ultra paranoid - the server is suffering some sort of
denial of service (DOS) attack.
Also...when the database application connection dies, it returns one of the
following error messages (trapped from the ADO object error stack):
Number = -2147467259, Description = [DBNETLIB][ConnectionRead
(recv()).]General network error. Check your network documentation., Source = Microsoft OLE DB Provider for SQL Server, SQLState = 08S01, Native Error = 11.
Number = -2147467259, Description = [DBNETLIB][ConnectionWrite
(send()).]General network error. Check your network documentation., Source = Microsoft OLE DB Provider for SQL Server, SQLState = 08S01, Native Error = 11.
Any help or suggestions would be most appreciated,
Thanks!
GriffHi
Have monitored the system with perfmon and ruled out KB815056?
Have you ruled out autogrow (or at least if autogrow is on the size of
increment is not too excessive), create/alter database issues and ruled out
KB810885?
Checked that you have at least version 2040 (SELECT @.@.VERSION)
Checked with the SAN vendor to see if there are any configuration issues?
If you have done all these then I would suggest that you apply trace flag
1262 and log a call with PSS to analyse the mini-dump.
John
"Griff" wrote:
> Hi - I originally posted this to windows.server.general, but no responses
> there.
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> I have database application that stops working and the event log shows two
> errors - I suspect that they are related but I don't know for certain and
> I'm not sure whether they refer to connections to SQL Server or to the
> Windows server.
> The first error is number 17883:
> Error: 17883, Severity: 1, State: 0
> The Scheduler 0 appears to be hung.
> And the second is number 17809
> Could not connect. The maximum number of '32767' configured ...
> Error: 17809, Severity: 20, State: 1
> Unfortunately, I don't have the FULL text.
> Now, I've investigated this as best I can and Microsoft say that the 17883
> error occurs when the underlying disk system is too quick (e.g. when using a
> SAN), but that this was fixed in SQL Server 2000 sp4. Yes, my server is
> fibre-attached to a SAN **but I'm already running sp4** ... Here are some
> URLs that I found:
> http://www.developersdex.com/sql/message.asp?p=1866&ID=%3C48E20E01-71FA-483B-ADFC-7806C2FCC9D8%40microsoft.com%3E
> http://support.microsoft.com/default.aspx?kbid=810885
> http://support.microsoft.com/kb/815056
> http://support.microsoft.com/kb/816840
> Regarding error 17809, I've put in some monitoring software and the number
> of SQL Server connections never goes above 200, so I'm thinking that the
> 32767 connections is not a SQL Server connection but in fact a "lower" level
> connection to the server [I say "lower" because I don't really understand
> this side of networking (I'm just a VB developer)]. It sounds to me as if
> the Windows 2003 sp1 server hosting the SQL Server has all its connections
> open and can't cope with anything new. If this is the case it's either that
> Windows 2003 can't cope with being on a SAN (which I simply can't believe)
> or - if I'm being ultra paranoid - the server is suffering some sort of
> denial of service (DOS) attack.
> Also...when the database application connection dies, it returns one of the
> following error messages (trapped from the ADO object error stack):
> Number = -2147467259, Description = [DBNETLIB][ConnectionRead
> (recv()).]General network error. Check your network documentation., Source => Microsoft OLE DB Provider for SQL Server, SQLState = 08S01, Native Error => 11.
> Number = -2147467259, Description = [DBNETLIB][ConnectionWrite
> (send()).]General network error. Check your network documentation., Source => Microsoft OLE DB Provider for SQL Server, SQLState = 08S01, Native Error => 11.
> Any help or suggestions would be most appreciated,
> Thanks!
> Griff
>
>|||I'd check with the SAN vendor. I've experienced issues with some vendors
until they corrected their fiber channel drivers.
--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another Certification Exam
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:4AD9C407-4AE1-41D8-B969-52CD87BE78F7@.microsoft.com...
> Hi
> Have monitored the system with perfmon and ruled out KB815056?
> Have you ruled out autogrow (or at least if autogrow is on the size of
> increment is not too excessive), create/alter database issues and ruled
> out
> KB810885?
> Checked that you have at least version 2040 (SELECT @.@.VERSION)
> Checked with the SAN vendor to see if there are any configuration issues?
> If you have done all these then I would suggest that you apply trace flag
> 1262 and log a call with PSS to analyse the mini-dump.
> John
> "Griff" wrote:
>> Hi - I originally posted this to windows.server.general, but no responses
>> there.
>> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
>> I have database application that stops working and the event log shows
>> two
>> errors - I suspect that they are related but I don't know for certain and
>> I'm not sure whether they refer to connections to SQL Server or to the
>> Windows server.
>> The first error is number 17883:
>> Error: 17883, Severity: 1, State: 0
>> The Scheduler 0 appears to be hung.
>> And the second is number 17809
>> Could not connect. The maximum number of '32767' configured
>> ...
>> Error: 17809, Severity: 20, State: 1
>> Unfortunately, I don't have the FULL text.
>> Now, I've investigated this as best I can and Microsoft say that the
>> 17883
>> error occurs when the underlying disk system is too quick (e.g. when
>> using a
>> SAN), but that this was fixed in SQL Server 2000 sp4. Yes, my server is
>> fibre-attached to a SAN **but I'm already running sp4** ... Here are
>> some
>> URLs that I found:
>>
>> http://www.developersdex.com/sql/message.asp?p=1866&ID=%3C48E20E01-71FA-483B-ADFC-7806C2FCC9D8%40microsoft.com%3E
>> http://support.microsoft.com/default.aspx?kbid=810885
>> http://support.microsoft.com/kb/815056
>> http://support.microsoft.com/kb/816840
>> Regarding error 17809, I've put in some monitoring software and the
>> number
>> of SQL Server connections never goes above 200, so I'm thinking that the
>> 32767 connections is not a SQL Server connection but in fact a "lower"
>> level
>> connection to the server [I say "lower" because I don't really understand
>> this side of networking (I'm just a VB developer)]. It sounds to me as
>> if
>> the Windows 2003 sp1 server hosting the SQL Server has all its
>> connections
>> open and can't cope with anything new. If this is the case it's either
>> that
>> Windows 2003 can't cope with being on a SAN (which I simply can't
>> believe)
>> or - if I'm being ultra paranoid - the server is suffering some sort of
>> denial of service (DOS) attack.
>> Also...when the database application connection dies, it returns one of
>> the
>> following error messages (trapped from the ADO object error stack):
>> Number = -2147467259, Description = [DBNETLIB][ConnectionRead
>> (recv()).]General network error. Check your network documentation.,
>> Source =>> Microsoft OLE DB Provider for SQL Server, SQLState = 08S01, Native Error
>> =>> 11.
>> Number = -2147467259, Description = [DBNETLIB][ConnectionWrite
>> (send()).]General network error. Check your network documentation.,
>> Source =>> Microsoft OLE DB Provider for SQL Server, SQLState = 08S01, Native Error
>> =>> 11.
>> Any help or suggestions would be most appreciated,
>> Thanks!
>> Griff
>>|||Hi John
Firstly, KB815056 and KB810885 were allegedly fixed in sp4 which we already
have installed.
Interestingly you say that we should have "at least version 2040" - I've
checked and ours is registered as version 2039, yet we have the latest full
service pack. Presumably we should have the latest cumulative hot fix
(build 2162) mentioned on http://www.support.microsoft.com/ph/2852 ?
I'll also investigate the SAN side of things...thanks also Arnie.
Griff|||Hi Griff
Version 2040 was a fix for AWE http://support.microsoft.com/kb/899761, which
I would assume are using. I have found 2187 ok, but you would have to do your
own testing. Have you checked your current version number?
John
"Griff" wrote:
> Hi John
> Firstly, KB815056 and KB810885 were allegedly fixed in sp4 which we already
> have installed.
> Interestingly you say that we should have "at least version 2040" - I've
> checked and ours is registered as version 2039, yet we have the latest full
> service pack. Presumably we should have the latest cumulative hot fix
> (build 2162) mentioned on http://www.support.microsoft.com/ph/2852 ?
> I'll also investigate the SAN side of things...thanks also Arnie.
> Griff
>
>|||Hi John
We're using SqlServer 2000 STANDARD edition that doesn't support AWE [only
Enterprise (and Dev) does].
Current version we're running is 2039 (just vanilla sp4, no subsequent hot
fixes).
Griff|||Hi
I suggest you log check out the SAN vendor on this, if that does not work
call PSS.
John
"Griff" wrote:
> Hi John
> We're using SqlServer 2000 STANDARD edition that doesn't support AWE [only
> Enterprise (and Dev) does].
> Current version we're running is 2039 (just vanilla sp4, no subsequent hot
> fixes).
> Griff
>
>