I have a question about the "Connection Timeout" from the
client to the Sql server. Everything works fine when I
change the "connection timeout" property in the
connection string for the scenario: "the machine is alive
but the sql server is down" but in the scenario that the
network connection to the server is lost I get a very
long timeout.
Any Ideas?
Hi Peter,
How long did you set it to? The default is usually pretty good. The only
times we find we need to change it is over an Internet connection.
Regards,
Greg Low [MVP]
MSDE Manager SQL Tools
www.whitebearconsulting.com
"Peter Ericsson" <peter.l.ericsson@.ericsson.com> wrote in message
news:0c4901c509d3$ef53e790$a401280a@.phx.gbl...
>I have a question about the "Connection Timeout" from the
> client to the Sql server. Everything works fine when I
> change the "connection timeout" property in the
> connection string for the scenario: "the machine is alive
> but the sql server is down" but in the scenario that the
> network connection to the server is lost I get a very
> long timeout.
> Any Ideas?
>
|||I have tried values from 2 to 15 seconds but I don't
think that that is the problem. Because everything works
fine if the SQL Server is down, I get my timeout
depending on the setting. The problem occurs when the
machine that is running the Sql server loses the network
connection, then I get a timout after about 50 seconds.
/Peter
>--Original Message--
>Hi Peter,
>How long did you set it to? The default is usually
pretty good. The only
>times we find we need to change it is over an Internet
connection.
>Regards,
>--
>Greg Low [MVP]
>MSDE Manager SQL Tools
>www.whitebearconsulting.com
>"Peter Ericsson" <peter.l.ericsson@.ericsson.com> wrote
in message[vbcol=seagreen]
>news:0c4901c509d3$ef53e790$a401280a@.phx.gbl...
the[vbcol=seagreen]
alive[vbcol=seagreen]
the
>
>.
>
|||Maybe the following apply to your situation:
TCP/IP Settings for SQL Server Drivers When Pooling Is
Disabled
http://support.microsoft.com/?id=328476
PRB: "WSAEADDRESSINUSE" Error Message When You Try to
Connect Through an Anonymous Port After You Increase the
IMAP Connection Limit
http://support.microsoft.com/?id=319502
-Sue
On Thu, 3 Feb 2005 04:27:01 -0800, "Peter Ericsson"
<peter.l.ericsson@.ericsson.com> wrote:
[vbcol=seagreen]
>I have tried values from 2 to 15 seconds but I don't
>think that that is the problem. Because everything works
>fine if the SQL Server is down, I get my timeout
>depending on the setting. The problem occurs when the
>machine that is running the Sql server loses the network
>connection, then I get a timout after about 50 seconds.
>/Peter
>pretty good. The only
>connection.
>in message
>the
>alive
>the
|||Hi what kind of query your run?
how many records affected by your query?
"Peter Ericsson" <peter.l.ericsson@.ericsson.com> wrote in message
news:0c4901c509d3$ef53e790$a401280a@.phx.gbl...
>I have a question about the "Connection Timeout" from the
> client to the Sql server. Everything works fine when I
> change the "connection timeout" property in the
> connection string for the scenario: "the machine is alive
> but the sql server is down" but in the scenario that the
> network connection to the server is lost I get a very
> long timeout.
> Any Ideas?
>
Saturday, February 25, 2012
Connection Timeout
See if this answers your question:
http://classicasp.aspfaq.com/general/how-do-i-increase-timeout-values.html
Andrew J. Kelly SQL MVP
"tc" <tull@.idcodeware.co.uk> wrote in message
news:uDuIIqhzHHA.4712@.TK2MSFTNGP04.phx.gbl...
> Hi all.
> I have a PPC app which connects to an SQL server via RF. App is fine, but
> when
> out of range the connection timeout is too long. I don't want to backlog
> data, in this instance the user must be within range for the connection
> to
> work. Using CF SQLClient namespace.
> I've had a look at the general sql client connection properties, but I
> can't
> find a way to reduce the connection timeout to say, 5 seconds.
> Any ideas?
>
>
I don't see the distinction for this. It is just a property (CommandTimeOut)
that you set for the connection. It should not matter what the client or the
server is or what they do.
Andrew J. Kelly SQL MVP
"tc" <tull@.idcodeware.co.uk> wrote in message
news:ujKRwhpzHHA.1204@.TK2MSFTNGP03.phx.gbl...
> It's the right idea, but I need to impose this change within my managed VB
> code on the portable, I'm not loading data from a web server, just a
> standard SQL server on a local network.
> Thanks anyway.
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:O5nkQPizHHA.3600@.TK2MSFTNGP04.phx.gbl...
>
|||Andrew J. Kelly wrote:
> I don't see the distinction for this. It is just a property (CommandTimeOut)
> that you set for the connection. It should not matter what the client or the
> server is or what they do.
>
The article he pointed you to does indeed describe how to use the
ConnectionTimeout property of the ADODB.Connection object, which is
similar to what you want.
If you are using the SqlConnection object on .NET 2.0 you'll notice that
the ConnectionTimeout property is readonly. What you need to do is
include the connection timeout in the connection string.
"Data Source=SERVERNAME;Initial Catalog=DBNAME;Integrated
Security=SSPI;Connect Timeout=5"
which will give you a 5 second timeout on the connection.
Arild
|||I guess anything is possible but this is a property of the connection so I
highly doubt it. Are you sure it is using the connection you think? Make
sure there isn't code somewhere that is either creating another or new
connection or overriding the settings.
Andrew J. Kelly SQL MVP
"tc" <tull@.idcodeware.co.uk> wrote in message
news:%23Hc3uFH0HHA.4928@.TK2MSFTNGP05.phx.gbl...
> It looked so promising!
> Is there any chance that the timeout parameter is ignored in the Compact
> Framework? I've tried all settings between 5 and 20 seconds, but the
> portable still sits there for 90.
> Any ideas?
> "Arild Bakken" <arild.bakken@.telecomputing.no> wrote in message
> news:%23Cq5Jm2zHHA.4476@.TK2MSFTNGP06.phx.gbl...
>
http://classicasp.aspfaq.com/general/how-do-i-increase-timeout-values.html
Andrew J. Kelly SQL MVP
"tc" <tull@.idcodeware.co.uk> wrote in message
news:uDuIIqhzHHA.4712@.TK2MSFTNGP04.phx.gbl...
> Hi all.
> I have a PPC app which connects to an SQL server via RF. App is fine, but
> when
> out of range the connection timeout is too long. I don't want to backlog
> data, in this instance the user must be within range for the connection
> to
> work. Using CF SQLClient namespace.
> I've had a look at the general sql client connection properties, but I
> can't
> find a way to reduce the connection timeout to say, 5 seconds.
> Any ideas?
>
>
I don't see the distinction for this. It is just a property (CommandTimeOut)
that you set for the connection. It should not matter what the client or the
server is or what they do.
Andrew J. Kelly SQL MVP
"tc" <tull@.idcodeware.co.uk> wrote in message
news:ujKRwhpzHHA.1204@.TK2MSFTNGP03.phx.gbl...
> It's the right idea, but I need to impose this change within my managed VB
> code on the portable, I'm not loading data from a web server, just a
> standard SQL server on a local network.
> Thanks anyway.
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:O5nkQPizHHA.3600@.TK2MSFTNGP04.phx.gbl...
>
|||Andrew J. Kelly wrote:
> I don't see the distinction for this. It is just a property (CommandTimeOut)
> that you set for the connection. It should not matter what the client or the
> server is or what they do.
>
The article he pointed you to does indeed describe how to use the
ConnectionTimeout property of the ADODB.Connection object, which is
similar to what you want.
If you are using the SqlConnection object on .NET 2.0 you'll notice that
the ConnectionTimeout property is readonly. What you need to do is
include the connection timeout in the connection string.
"Data Source=SERVERNAME;Initial Catalog=DBNAME;Integrated
Security=SSPI;Connect Timeout=5"
which will give you a 5 second timeout on the connection.
Arild
|||I guess anything is possible but this is a property of the connection so I
highly doubt it. Are you sure it is using the connection you think? Make
sure there isn't code somewhere that is either creating another or new
connection or overriding the settings.
Andrew J. Kelly SQL MVP
"tc" <tull@.idcodeware.co.uk> wrote in message
news:%23Hc3uFH0HHA.4928@.TK2MSFTNGP05.phx.gbl...
> It looked so promising!
> Is there any chance that the timeout parameter is ignored in the Compact
> Framework? I've tried all settings between 5 and 20 seconds, but the
> portable still sits there for 90.
> Any ideas?
> "Arild Bakken" <arild.bakken@.telecomputing.no> wrote in message
> news:%23Cq5Jm2zHHA.4476@.TK2MSFTNGP06.phx.gbl...
>
connection timeout
I forgot to mention, that I tried to use SET LOGINTIME:
EXEC SQL SET OPTION LOGINTIME 60;
EXEC SQL CONNECT TO :db_str AS :db_user
But it still interrupts after 10 sec (default value).
Nikolai.
Note this warning from BOL:
While the Embedded SQL for C API is still supported in Microsoft SQL Server
2000, no future versions of SQL Server will include the files needed to do
programming work on applications that use this API. Connections from
existing applications written using Embedded SQL for C will still be
supported in the next version of SQL Server, but this support will also be
dropped in a future release. When writing new applications, avoid using
Embedded SQL for C. When modifying existing applications, you are strongly
encouraged to remove dependencies on Embedded SQL for C. Instead of
Embedded SQL for C, you can use Microsoft ActiveX Data Objects (ADO), OLE
DB, or ODBC to access data in SQL Server.
Cindy Gross, MCDBA, MCSE
http://cindygross.tripod.com
This posting is provided "AS IS" with no warranties, and confers no rights.
EXEC SQL SET OPTION LOGINTIME 60;
EXEC SQL CONNECT TO :db_str AS :db_user
But it still interrupts after 10 sec (default value).
Nikolai.
quote:Try logintimeout instead of logintime.
>--Original Message--
>Our application uses Embedded SQL for C to connect to
>MSSQL:
>EXEC SQL CONNECT TO :db_str AS :db_user
>The application runs on the same system as MSSQL server.
>The customer complains that if their server is busy, then
>the application can not connect to the server.
>Questions:
>How to increase connection timeout using Embedded SQL for
>C?
>Is it possible to change connection timeout for local
>connections on a MSSQL (e.g. using SQL Server Enterprise
>Manager)?
>.
>
Note this warning from BOL:
While the Embedded SQL for C API is still supported in Microsoft SQL Server
2000, no future versions of SQL Server will include the files needed to do
programming work on applications that use this API. Connections from
existing applications written using Embedded SQL for C will still be
supported in the next version of SQL Server, but this support will also be
dropped in a future release. When writing new applications, avoid using
Embedded SQL for C. When modifying existing applications, you are strongly
encouraged to remove dependencies on Embedded SQL for C. Instead of
Embedded SQL for C, you can use Microsoft ActiveX Data Objects (ADO), OLE
DB, or ODBC to access data in SQL Server.
Cindy Gross, MCDBA, MCSE
http://cindygross.tripod.com
This posting is provided "AS IS" with no warranties, and confers no rights.
Connection Timeout
I am trying to upload data to my sql database via a web service installed on
the same machine as sql server. When my upload takes more than about 2
minutes I get a timeout error message from my web service and the data
tranfer was not completed. Transfers taking less time complete
satisfactorily. My web.config file for the web service has httpRuntime
executionTimeout set to 360 and maxRequestLength set to 30720. I continue t
o
get timeout error messages from my web service. I need my connection to sta
y
open for upto 5 minutes for those situations when I have large files to move
or my client computer has a slow internet upload capability. Am I forgettin
g
some setting that still needs to be made?
Thanks,
Fred Herringthere is a proxytimeout setting somewhere increase it like this
proxy.Timeout=2000;
see here
http://www.dotnetbips.com/displayarticle.aspx?id=112
we had the same problem and this fixed it
"Fred Herring" <FredHerring@.discussions.microsoft.com> wrote in message
news:B5CBD211-81B5-4693-A2A3-32FBA66780AF@.microsoft.com...
>I am trying to upload data to my sql database via a web service installed
>on
> the same machine as sql server. When my upload takes more than about 2
> minutes I get a timeout error message from my web service and the data
> tranfer was not completed. Transfers taking less time complete
> satisfactorily. My web.config file for the web service has httpRuntime
> executionTimeout set to 360 and maxRequestLength set to 30720. I continue
> to
> get timeout error messages from my web service. I need my connection to
> stay
> open for upto 5 minutes for those situations when I have large files to
> move
> or my client computer has a slow internet upload capability. Am I
> forgetting
> some setting that still needs to be made?
> Thanks,
> Fred Herring
>
>
the same machine as sql server. When my upload takes more than about 2
minutes I get a timeout error message from my web service and the data
tranfer was not completed. Transfers taking less time complete
satisfactorily. My web.config file for the web service has httpRuntime
executionTimeout set to 360 and maxRequestLength set to 30720. I continue t
o
get timeout error messages from my web service. I need my connection to sta
y
open for upto 5 minutes for those situations when I have large files to move
or my client computer has a slow internet upload capability. Am I forgettin
g
some setting that still needs to be made?
Thanks,
Fred Herringthere is a proxytimeout setting somewhere increase it like this
proxy.Timeout=2000;
see here
http://www.dotnetbips.com/displayarticle.aspx?id=112
we had the same problem and this fixed it
"Fred Herring" <FredHerring@.discussions.microsoft.com> wrote in message
news:B5CBD211-81B5-4693-A2A3-32FBA66780AF@.microsoft.com...
>I am trying to upload data to my sql database via a web service installed
>on
> the same machine as sql server. When my upload takes more than about 2
> minutes I get a timeout error message from my web service and the data
> tranfer was not completed. Transfers taking less time complete
> satisfactorily. My web.config file for the web service has httpRuntime
> executionTimeout set to 360 and maxRequestLength set to 30720. I continue
> to
> get timeout error messages from my web service. I need my connection to
> stay
> open for upto 5 minutes for those situations when I have large files to
> move
> or my client computer has a slow internet upload capability. Am I
> forgetting
> some setting that still needs to be made?
> Thanks,
> Fred Herring
>
>
Connection Timeout
I’ve 2 database servers. These servers communicate with the other’s data
bases
via TCP/IP with SQL authentication using VB, COM+ & ASP applications. Both
servers are running W2k & SQL2k, each with the latest SPs & hot fixes.
Neither server is a domain server. Server A also supports a VB application
the users access through Terminal Services sessions.
Recently we began experiencing a problem on server A. When logged on to the
server with an administrator account the user can connect to the other
server’s database through the VB applications & through SQL Query Analyzer
.
However, when logged on as a user that isn’t an administrator, they cannot
connect to the other server; they get a Timeout Expired error message. This
occurs every time.
From server A when logged on as a standard user we can telnet to server B on
port 1433. We cannot odbcping nor use SQL Query Analyzer to connect to serve
r
B. The IP address & the SQL user /password is included in all connections.
I ran Netmon on server B to see what was coming from server A. I only saw
ack messages. I’m not a Netmon expert, so interpreting these messages is a
little beyond me.
I also suspect the problem is on server A. Why else would one user be able
to connect & another not. Beyond that I’m lost.
The only error I see in any of the Event logs from server A is an error
described by KB 326912.
Thanks in advance for any ideas.You need to make the connections from the client side and review them. See
if the tcp 3 way handshake is completing.
Q169292 The Basics of Reading TCP/IP Traces
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||I'm able to see a 3 Way Hand Shake & Graceful Close. There is also some Push
activity in the trace.
Examining this activity is iffy at best, because these are production
servers. My problem only occurs when the user on Server A is not an
administrator. Some of the activity in the capture could be the result of
other user activity. I think the trace I captured was during an isolated
period, but I cannot be assured of this. The Hand Shake & Grageful Close wer
e
the 1st & last events in the capture.
"Kevin McDonnell [MSFT]" wrote:
> You need to make the connections from the client side and review them. Se
e
> if the tcp 3 way handshake is completing.
> Q169292 The Basics of Reading TCP/IP Traces
> Thanks,
> Kevin McDonnell
> Microsoft Corporation
> This posting is provided AS IS with no warranties, and confers no rights.
>
>|||Is there a policy defined on the system that might explain why there is a
permission problem?
gpresult.exe will show you if any policies are in place.
Are there any security templates in use?
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||I'm not aware of a policy that is set to do that.
I ran gpresult without learning much, which didn't surprise me since this is
a stand-alone server.
There are Local Policies, most are default values. I did read through all of
these & nothing stuck out as a possible culprit. Do you have any policies in
mind that may need further scrutiny?
Sam
"Kevin McDonnell [MSFT]" wrote:
> Is there a policy defined on the system that might explain why there is a
> permission problem?
> gpresult.exe will show you if any policies are in place.
> Are there any security templates in use?
> Thanks,
> Kevin McDonnell
> Microsoft Corporation
> This posting is provided AS IS with no warranties, and confers no rights.
>
>|||Clients need "access this computer from the network" permission in order to
establish a connection.
Can the same client map a drive to the server?
Does the problem happen with Named Pipes as well as TCP/IP?
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||I checked the 'Access this computer from the Network' local policy. It
includes Users.
The user connects to Server A through a Terminal Services Connection.
Mapping a drive isn't an option I've pursued.
Since these are standalone servers I don't know how to connect from one to
the other using named pipes. (One is in Atlanta & the other in Washington DC
.
They communicate via Internet. Force Encryption is on.) If I enter the
www.servername.com of Server B, I think that uses TCP/IP. I tried it in Quer
y
Analyzer & got the Timeout message.
I installed FileMon from www.SysInternal.com on Server A. I saw some files
that were not found, but the system reverted to Winnt\system32 for the same
files & was succesful. I didn't see any unsuccessful attempts logged for the
User. This leads me to suppose it isn't a file permission problem.
I restarted Server A last night, but this didn't accompolish anything either
.
Sam
"Kevin McDonnell [MSFT]" wrote:
> Clients need "access this computer from the network" permission in order t
o
> establish a connection.
> Can the same client map a drive to the server?
> Does the problem happen with Named Pipes as well as TCP/IP?
> Thanks,
> Kevin McDonnell
> Microsoft Corporation
> This posting is provided AS IS with no warranties, and confers no rights.
>
>|||I would run Microsoft Network Monitor from the Terminal Server machine to
trace the traffic to SQL.
Compare the admin trace to the user trace.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||I have the traces. I was able to run them when there wasn't any other
activity between the systems.
Both traces are escentially the the same except in the admin trace I see
more TDS protocol packets. Both start with the 3 Way Hand Shake & end with
the Graceful Close. The 1st 12 packets of each trace are the same. Then the
admin trace has 12 TDS protocol packets with a description beginning with
'UNKNOWN EPM ACK Len ='. The trace that fails does not have any of these
packets.
The user trace does have some TDS protocol packets, but for some reason that
the trace doesn't show, it doesn't continue with the TDS packets the admin
trace has.
Sam
"Kevin McDonnell [MSFT]" wrote:
> I would run Microsoft Network Monitor from the Terminal Server machine to
> trace the traffic to SQL.
> Compare the admin trace to the user trace.
> Thanks,
> Kevin McDonnell
> Microsoft Corporation
> This posting is provided AS IS with no warranties, and confers no rights.
>
>|||Hi Sam,
Sounds like you made a good capture. Unfortunately, the best way to
resolve this would be to open a case and have a SQL Engineer review the
traces. It would be too difficult to diagnose the traces in this forum.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.
bases
via TCP/IP with SQL authentication using VB, COM+ & ASP applications. Both
servers are running W2k & SQL2k, each with the latest SPs & hot fixes.
Neither server is a domain server. Server A also supports a VB application
the users access through Terminal Services sessions.
Recently we began experiencing a problem on server A. When logged on to the
server with an administrator account the user can connect to the other
server’s database through the VB applications & through SQL Query Analyzer
.
However, when logged on as a user that isn’t an administrator, they cannot
connect to the other server; they get a Timeout Expired error message. This
occurs every time.
From server A when logged on as a standard user we can telnet to server B on
port 1433. We cannot odbcping nor use SQL Query Analyzer to connect to serve
r
B. The IP address & the SQL user /password is included in all connections.
I ran Netmon on server B to see what was coming from server A. I only saw
ack messages. I’m not a Netmon expert, so interpreting these messages is a
little beyond me.
I also suspect the problem is on server A. Why else would one user be able
to connect & another not. Beyond that I’m lost.
The only error I see in any of the Event logs from server A is an error
described by KB 326912.
Thanks in advance for any ideas.You need to make the connections from the client side and review them. See
if the tcp 3 way handshake is completing.
Q169292 The Basics of Reading TCP/IP Traces
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||I'm able to see a 3 Way Hand Shake & Graceful Close. There is also some Push
activity in the trace.
Examining this activity is iffy at best, because these are production
servers. My problem only occurs when the user on Server A is not an
administrator. Some of the activity in the capture could be the result of
other user activity. I think the trace I captured was during an isolated
period, but I cannot be assured of this. The Hand Shake & Grageful Close wer
e
the 1st & last events in the capture.
"Kevin McDonnell [MSFT]" wrote:
> You need to make the connections from the client side and review them. Se
e
> if the tcp 3 way handshake is completing.
> Q169292 The Basics of Reading TCP/IP Traces
> Thanks,
> Kevin McDonnell
> Microsoft Corporation
> This posting is provided AS IS with no warranties, and confers no rights.
>
>|||Is there a policy defined on the system that might explain why there is a
permission problem?
gpresult.exe will show you if any policies are in place.
Are there any security templates in use?
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||I'm not aware of a policy that is set to do that.
I ran gpresult without learning much, which didn't surprise me since this is
a stand-alone server.
There are Local Policies, most are default values. I did read through all of
these & nothing stuck out as a possible culprit. Do you have any policies in
mind that may need further scrutiny?
Sam
"Kevin McDonnell [MSFT]" wrote:
> Is there a policy defined on the system that might explain why there is a
> permission problem?
> gpresult.exe will show you if any policies are in place.
> Are there any security templates in use?
> Thanks,
> Kevin McDonnell
> Microsoft Corporation
> This posting is provided AS IS with no warranties, and confers no rights.
>
>|||Clients need "access this computer from the network" permission in order to
establish a connection.
Can the same client map a drive to the server?
Does the problem happen with Named Pipes as well as TCP/IP?
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||I checked the 'Access this computer from the Network' local policy. It
includes Users.
The user connects to Server A through a Terminal Services Connection.
Mapping a drive isn't an option I've pursued.
Since these are standalone servers I don't know how to connect from one to
the other using named pipes. (One is in Atlanta & the other in Washington DC
.
They communicate via Internet. Force Encryption is on.) If I enter the
www.servername.com of Server B, I think that uses TCP/IP. I tried it in Quer
y
Analyzer & got the Timeout message.
I installed FileMon from www.SysInternal.com on Server A. I saw some files
that were not found, but the system reverted to Winnt\system32 for the same
files & was succesful. I didn't see any unsuccessful attempts logged for the
User. This leads me to suppose it isn't a file permission problem.
I restarted Server A last night, but this didn't accompolish anything either
.
Sam
"Kevin McDonnell [MSFT]" wrote:
> Clients need "access this computer from the network" permission in order t
o
> establish a connection.
> Can the same client map a drive to the server?
> Does the problem happen with Named Pipes as well as TCP/IP?
> Thanks,
> Kevin McDonnell
> Microsoft Corporation
> This posting is provided AS IS with no warranties, and confers no rights.
>
>|||I would run Microsoft Network Monitor from the Terminal Server machine to
trace the traffic to SQL.
Compare the admin trace to the user trace.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||I have the traces. I was able to run them when there wasn't any other
activity between the systems.
Both traces are escentially the the same except in the admin trace I see
more TDS protocol packets. Both start with the 3 Way Hand Shake & end with
the Graceful Close. The 1st 12 packets of each trace are the same. Then the
admin trace has 12 TDS protocol packets with a description beginning with
'UNKNOWN EPM ACK Len ='. The trace that fails does not have any of these
packets.
The user trace does have some TDS protocol packets, but for some reason that
the trace doesn't show, it doesn't continue with the TDS packets the admin
trace has.
Sam
"Kevin McDonnell [MSFT]" wrote:
> I would run Microsoft Network Monitor from the Terminal Server machine to
> trace the traffic to SQL.
> Compare the admin trace to the user trace.
> Thanks,
> Kevin McDonnell
> Microsoft Corporation
> This posting is provided AS IS with no warranties, and confers no rights.
>
>|||Hi Sam,
Sounds like you made a good capture. Unfortunately, the best way to
resolve this would be to open a case and have a SQL Engineer review the
traces. It would be too difficult to diagnose the traces in this forum.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.
connection timeout
Hi there
I'm using SQL Server 2000. I often use the UPDATE ... SET ... statements to
change data in my database, affecting up to 3 million rows of data. If I
issue the statements from Enterprise Manager I get a "timeout expired" error
on large queries, whereas if I execute the queries from Query Analyser, they
perform fine if very slowly. Mostly at the moment I'm happy that they
execute.
Now, if I issue the statements over an ADO connection from Microsoft Excel
(using a VBA procedure, with the statements issued either on-the-fly or via
a stored query), I get the same "timeout expired" error.
Any ideas?
Best regards
LoaneHi
Make sure that your database and log file have been grown big enough before
you run the update.
Make sure that the Where clause column is indexed.
If possible, break up your update into smaller pieces.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Loane Sharp" <look_sharp_not@.hotmail.com> wrote in message
news:u$mMC4GZFHA.228@.TK2MSFTNGP12.phx.gbl...
> Hi there
> I'm using SQL Server 2000. I often use the UPDATE ... SET ... statements
> to change data in my database, affecting up to 3 million rows of data. If
> I issue the statements from Enterprise Manager I get a "timeout expired"
> error on large queries, whereas if I execute the queries from Query
> Analyser, they perform fine if very slowly. Mostly at the moment I'm happy
> that they execute.
> Now, if I issue the statements over an ADO connection from Microsoft Excel
> (using a VBA procedure, with the statements issued either on-the-fly or
> via a stored query), I get the same "timeout expired" error.
> Any ideas?
> Best regards
> Loane
>|||That is because EM has a default timeout for connections at 30 seconds as do
most drivers. QA on the other hand defaults to 0 which means it never times
out. When ever possible try to avoid doing operations on so many rows all
in one transaction. Most operations such as that can sit in a loop and do
the updates in smaller batches of say 10 or 20 thousand rows at a time.
That way you should never timeout and won't block other users as much or for
as long a period of time. There are plenty of ways to approach a situation
such as that. If you need help then post the DDL for the table and the
update statement you normally would use.
Andrew J. Kelly SQL MVP
"Loane Sharp" <look_sharp_not@.hotmail.com> wrote in message
news:u$mMC4GZFHA.228@.TK2MSFTNGP12.phx.gbl...
> Hi there
> I'm using SQL Server 2000. I often use the UPDATE ... SET ... statements
> to change data in my database, affecting up to 3 million rows of data. If
> I issue the statements from Enterprise Manager I get a "timeout expired"
> error on large queries, whereas if I execute the queries from Query
> Analyser, they perform fine if very slowly. Mostly at the moment I'm happy
> that they execute.
> Now, if I issue the statements over an ADO connection from Microsoft Excel
> (using a VBA procedure, with the statements issued either on-the-fly or
> via a stored query), I get the same "timeout expired" error.
> Any ideas?
> Best regards
> Loane
>
I'm using SQL Server 2000. I often use the UPDATE ... SET ... statements to
change data in my database, affecting up to 3 million rows of data. If I
issue the statements from Enterprise Manager I get a "timeout expired" error
on large queries, whereas if I execute the queries from Query Analyser, they
perform fine if very slowly. Mostly at the moment I'm happy that they
execute.
Now, if I issue the statements over an ADO connection from Microsoft Excel
(using a VBA procedure, with the statements issued either on-the-fly or via
a stored query), I get the same "timeout expired" error.
Any ideas?
Best regards
LoaneHi
Make sure that your database and log file have been grown big enough before
you run the update.
Make sure that the Where clause column is indexed.
If possible, break up your update into smaller pieces.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Loane Sharp" <look_sharp_not@.hotmail.com> wrote in message
news:u$mMC4GZFHA.228@.TK2MSFTNGP12.phx.gbl...
> Hi there
> I'm using SQL Server 2000. I often use the UPDATE ... SET ... statements
> to change data in my database, affecting up to 3 million rows of data. If
> I issue the statements from Enterprise Manager I get a "timeout expired"
> error on large queries, whereas if I execute the queries from Query
> Analyser, they perform fine if very slowly. Mostly at the moment I'm happy
> that they execute.
> Now, if I issue the statements over an ADO connection from Microsoft Excel
> (using a VBA procedure, with the statements issued either on-the-fly or
> via a stored query), I get the same "timeout expired" error.
> Any ideas?
> Best regards
> Loane
>|||That is because EM has a default timeout for connections at 30 seconds as do
most drivers. QA on the other hand defaults to 0 which means it never times
out. When ever possible try to avoid doing operations on so many rows all
in one transaction. Most operations such as that can sit in a loop and do
the updates in smaller batches of say 10 or 20 thousand rows at a time.
That way you should never timeout and won't block other users as much or for
as long a period of time. There are plenty of ways to approach a situation
such as that. If you need help then post the DDL for the table and the
update statement you normally would use.
Andrew J. Kelly SQL MVP
"Loane Sharp" <look_sharp_not@.hotmail.com> wrote in message
news:u$mMC4GZFHA.228@.TK2MSFTNGP12.phx.gbl...
> Hi there
> I'm using SQL Server 2000. I often use the UPDATE ... SET ... statements
> to change data in my database, affecting up to 3 million rows of data. If
> I issue the statements from Enterprise Manager I get a "timeout expired"
> error on large queries, whereas if I execute the queries from Query
> Analyser, they perform fine if very slowly. Mostly at the moment I'm happy
> that they execute.
> Now, if I issue the statements over an ADO connection from Microsoft Excel
> (using a VBA procedure, with the statements issued either on-the-fly or
> via a stored query), I get the same "timeout expired" error.
> Any ideas?
> Best regards
> Loane
>
Connection Timeout
Is there any configuration option that controls how long
a user stays connected to mssql server? I have an
application that logs into mssql server and then uses
that one login for all work never logging out, after
about 7 days(fairly consistently) the connection to the
mssql gets killed and the application has to be
restarted. I was just curious if there is some kind of
parameter within mssql server that kills logins after x
number of days?
Thanks,
CarlHi,
There is no parameter in SQL server which will kill the user after x number
of days.
Rather you can write ur own code based on the login_time in
master..sysprocesses table and compare with getdate() for each process id
available in the list.
If any of the SPID is older then x days you can use
KILL SPID to kill the user. SPID also you can take it from
master..sysprocesses tables
Thanks
Hari
MCDBA
"z-man" <anonymous@.discussions.microsoft.com> wrote in message
news:817d01c4076d$e655aa20$a601280a@.phx.gbl...
> Is there any configuration option that controls how long
> a user stays connected to mssql server? I have an
> application that logs into mssql server and then uses
> that one login for all work never logging out, after
> about 7 days(fairly consistently) the connection to the
> mssql gets killed and the application has to be
> restarted. I was just curious if there is some kind of
> parameter within mssql server that kills logins after x
> number of days?
> Thanks,
> Carl
>|||There isn't anything like that built into SQL Server. You
could write your own procedure to do something like that but
nothing built into SQL Server.
If the application is on PC, server other than SQL Server,
you may want to look into network connectivity issues. You
may want to check the event logs where the application is
and see if you can find any clues in the logs.
-Sue
On Thu, 11 Mar 2004 05:36:47 -0800, "z-man"
<anonymous@.discussions.microsoft.com> wrote:
>Is there any configuration option that controls how long
>a user stays connected to mssql server? I have an
>application that logs into mssql server and then uses
>that one login for all work never logging out, after
>about 7 days(fairly consistently) the connection to the
>mssql gets killed and the application has to be
>restarted. I was just curious if there is some kind of
>parameter within mssql server that kills logins after x
>number of days?
>Thanks,
>Carl|||There is a 2 hour tcp session timeout and 1 hour for name pipe sessions.
But, this is controlled by the OS, not SQL.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||That's a good point...and looking at the keepalive settings
could be an option. After thinking about it more and that if
this happens about every 7 days, it may more likely be
related to a weekly process or something similar that is
hosing the connection. I think I'd go through the logs and
look for activity around the time the connection gets hosed.
-Sue
On Thu, 11 Mar 2004 21:02:41 GMT, kevmc@.online.microsoft.com
(Kevin McDonnell [MSFT]) wrote:
>There is a 2 hour tcp session timeout and 1 hour for name pipe sessions.
>But, this is controlled by the OS, not SQL.
>Thanks,
>Kevin McDonnell
>Microsoft Corporation
>This posting is provided AS IS with no warranties, and confers no rights.
>
a user stays connected to mssql server? I have an
application that logs into mssql server and then uses
that one login for all work never logging out, after
about 7 days(fairly consistently) the connection to the
mssql gets killed and the application has to be
restarted. I was just curious if there is some kind of
parameter within mssql server that kills logins after x
number of days?
Thanks,
CarlHi,
There is no parameter in SQL server which will kill the user after x number
of days.
Rather you can write ur own code based on the login_time in
master..sysprocesses table and compare with getdate() for each process id
available in the list.
If any of the SPID is older then x days you can use
KILL SPID to kill the user. SPID also you can take it from
master..sysprocesses tables
Thanks
Hari
MCDBA
"z-man" <anonymous@.discussions.microsoft.com> wrote in message
news:817d01c4076d$e655aa20$a601280a@.phx.gbl...
> Is there any configuration option that controls how long
> a user stays connected to mssql server? I have an
> application that logs into mssql server and then uses
> that one login for all work never logging out, after
> about 7 days(fairly consistently) the connection to the
> mssql gets killed and the application has to be
> restarted. I was just curious if there is some kind of
> parameter within mssql server that kills logins after x
> number of days?
> Thanks,
> Carl
>|||There isn't anything like that built into SQL Server. You
could write your own procedure to do something like that but
nothing built into SQL Server.
If the application is on PC, server other than SQL Server,
you may want to look into network connectivity issues. You
may want to check the event logs where the application is
and see if you can find any clues in the logs.
-Sue
On Thu, 11 Mar 2004 05:36:47 -0800, "z-man"
<anonymous@.discussions.microsoft.com> wrote:
>Is there any configuration option that controls how long
>a user stays connected to mssql server? I have an
>application that logs into mssql server and then uses
>that one login for all work never logging out, after
>about 7 days(fairly consistently) the connection to the
>mssql gets killed and the application has to be
>restarted. I was just curious if there is some kind of
>parameter within mssql server that kills logins after x
>number of days?
>Thanks,
>Carl|||There is a 2 hour tcp session timeout and 1 hour for name pipe sessions.
But, this is controlled by the OS, not SQL.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||That's a good point...and looking at the keepalive settings
could be an option. After thinking about it more and that if
this happens about every 7 days, it may more likely be
related to a weekly process or something similar that is
hosing the connection. I think I'd go through the logs and
look for activity around the time the connection gets hosed.
-Sue
On Thu, 11 Mar 2004 21:02:41 GMT, kevmc@.online.microsoft.com
(Kevin McDonnell [MSFT]) wrote:
>There is a 2 hour tcp session timeout and 1 hour for name pipe sessions.
>But, this is controlled by the OS, not SQL.
>Thanks,
>Kevin McDonnell
>Microsoft Corporation
>This posting is provided AS IS with no warranties, and confers no rights.
>
Connection Timed Out Error
hello all,
can any one explain sql State HYT00
im getting this as a part of the connection timed out message, while connecting from my node to sql server 2000 instance running on windows 2000 server.
thanks in advancepresume the connection is there and you can ping the other server?
How are you attempting to connect?
Has it just stopped? Have you ever had connection? have you checked that the correct protocols are enabled on the target server?
Jim|||YES JIM
I CAN PING THE MACHINE
IVE CONFIGURED THE CLIENT NETWORK UTILITY TO USE TCP
PROTOCOL TO LISTEN TO THE DEFAULT PORT 1433. THE SERVER
NETWORK UTILITY IS ALSO CONFIGURED
I COULD CONNECT TO THE SERVER BEFORE, BUT IT DROPS ALL OF A
SUDDEN,AND DISPLAYS THE 'CONNECTION TIMED OUT' MESSAGE
IM GETTING THIS MESSAGE WHILE ATTEMTPTING AN ODBC CREATION.|||http://vyaskn.tripod.com/sql_odbc_timeout_expired.htm to resolve the issue.
HTH
can any one explain sql State HYT00
im getting this as a part of the connection timed out message, while connecting from my node to sql server 2000 instance running on windows 2000 server.
thanks in advancepresume the connection is there and you can ping the other server?
How are you attempting to connect?
Has it just stopped? Have you ever had connection? have you checked that the correct protocols are enabled on the target server?
Jim|||YES JIM
I CAN PING THE MACHINE
IVE CONFIGURED THE CLIENT NETWORK UTILITY TO USE TCP
PROTOCOL TO LISTEN TO THE DEFAULT PORT 1433. THE SERVER
NETWORK UTILITY IS ALSO CONFIGURED
I COULD CONNECT TO THE SERVER BEFORE, BUT IT DROPS ALL OF A
SUDDEN,AND DISPLAYS THE 'CONNECTION TIMED OUT' MESSAGE
IM GETTING THIS MESSAGE WHILE ATTEMTPTING AN ODBC CREATION.|||http://vyaskn.tripod.com/sql_odbc_timeout_expired.htm to resolve the issue.
HTH
Connection timed out
We are running SQL Server 2000 Version 8.00.760. We are running our
application using Java. We are frequently getting Connection Timed Out in
our appserver. Is anyone aware of such an issue this version of SQL Server
? We have similar configurations on several other servers, but only the
appserver's connected to this particular database is giving connection
timeouts. Is this a known bug ? or are we hit with a virus ?
I searched Google, but did not find any info.
-Nags
Nags,
The "application timeout" usually means that the driver or the client code
has some sort of timeout within which, if sql server doesnt execute and
return the query, then it times out. For example, I think the default at
the ODBC driver setting is 30 seconds.
That leads us to the next question - why is it taking 30 seconds or more
(I'm making the assumption here that 30 sec is your default timeout as
well). I'm also making another assumption - that the other configurations
you have are EXACTLY like this "problem server" configuation which includes
the same amt of tables, and the same amt of data in these tables. And that,
there was a time maybe when things were fine on this "problem" server.
A couple of things you'd want ot check -
1. Does this happen for any query/hit against the sql server? example, even
logging on (user name/password) results in a timeout?
2. Does it happen for a set of queries?
3. If you know the queries that are commonly executed by this application,
OR some of the queries that you've seen timeout. Take those queries in
Query analyser and see how long they run.
4. Try updating statistics on the tables.
5. Compare index structures on the tables on the 'good' db's with their
counterparts on this problem db.
Hope this helps.
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.
|||I'm facing the pro as well.
I look into error log, it said 'Server terminating due to 'Stop' request
from Service Control Manager'. The db server also using the Autogrowth
option, and everytime it growing the log file, seems like the server stop
responding.
Pls advise.
Thanks in advance.
"Vikram Jayaram [MS]" wrote:
> Nags,
> The "application timeout" usually means that the driver or the client code
> has some sort of timeout within which, if sql server doesnt execute and
> return the query, then it times out. For example, I think the default at
> the ODBC driver setting is 30 seconds.
> That leads us to the next question - why is it taking 30 seconds or more
> (I'm making the assumption here that 30 sec is your default timeout as
> well). I'm also making another assumption - that the other configurations
> you have are EXACTLY like this "problem server" configuation which includes
> the same amt of tables, and the same amt of data in these tables. And that,
> there was a time maybe when things were fine on this "problem" server.
> A couple of things you'd want ot check -
> 1. Does this happen for any query/hit against the sql server? example, even
> logging on (user name/password) results in a timeout?
> 2. Does it happen for a set of queries?
> 3. If you know the queries that are commonly executed by this application,
> OR some of the queries that you've seen timeout. Take those queries in
> Query analyser and see how long they run.
> 4. Try updating statistics on the tables.
> 5. Compare index structures on the tables on the 'good' db's with their
> counterparts on this problem db.
> Hope this helps.
> Vikram Jayaram
> Microsoft, SQL Server
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.
>
>
application using Java. We are frequently getting Connection Timed Out in
our appserver. Is anyone aware of such an issue this version of SQL Server
? We have similar configurations on several other servers, but only the
appserver's connected to this particular database is giving connection
timeouts. Is this a known bug ? or are we hit with a virus ?
I searched Google, but did not find any info.
-Nags
Nags,
The "application timeout" usually means that the driver or the client code
has some sort of timeout within which, if sql server doesnt execute and
return the query, then it times out. For example, I think the default at
the ODBC driver setting is 30 seconds.
That leads us to the next question - why is it taking 30 seconds or more
(I'm making the assumption here that 30 sec is your default timeout as
well). I'm also making another assumption - that the other configurations
you have are EXACTLY like this "problem server" configuation which includes
the same amt of tables, and the same amt of data in these tables. And that,
there was a time maybe when things were fine on this "problem" server.
A couple of things you'd want ot check -
1. Does this happen for any query/hit against the sql server? example, even
logging on (user name/password) results in a timeout?
2. Does it happen for a set of queries?
3. If you know the queries that are commonly executed by this application,
OR some of the queries that you've seen timeout. Take those queries in
Query analyser and see how long they run.
4. Try updating statistics on the tables.
5. Compare index structures on the tables on the 'good' db's with their
counterparts on this problem db.
Hope this helps.
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.
|||I'm facing the pro as well.
I look into error log, it said 'Server terminating due to 'Stop' request
from Service Control Manager'. The db server also using the Autogrowth
option, and everytime it growing the log file, seems like the server stop
responding.
Pls advise.
Thanks in advance.
"Vikram Jayaram [MS]" wrote:
> Nags,
> The "application timeout" usually means that the driver or the client code
> has some sort of timeout within which, if sql server doesnt execute and
> return the query, then it times out. For example, I think the default at
> the ODBC driver setting is 30 seconds.
> That leads us to the next question - why is it taking 30 seconds or more
> (I'm making the assumption here that 30 sec is your default timeout as
> well). I'm also making another assumption - that the other configurations
> you have are EXACTLY like this "problem server" configuation which includes
> the same amt of tables, and the same amt of data in these tables. And that,
> there was a time maybe when things were fine on this "problem" server.
> A couple of things you'd want ot check -
> 1. Does this happen for any query/hit against the sql server? example, even
> logging on (user name/password) results in a timeout?
> 2. Does it happen for a set of queries?
> 3. If you know the queries that are commonly executed by this application,
> OR some of the queries that you've seen timeout. Take those queries in
> Query analyser and see how long they run.
> 4. Try updating statistics on the tables.
> 5. Compare index structures on the tables on the 'good' db's with their
> counterparts on this problem db.
> Hope this helps.
> Vikram Jayaram
> Microsoft, SQL Server
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.
>
>
Connection timed out
We are running SQL Server 2000 Version 8.00.760. We are running our
application using Java. We are frequently getting Connection Timed Out in
our appserver. Is anyone aware of such an issue this version of SQL Server
? We have similar configurations on several other servers, but only the
appserver's connected to this particular database is giving connection
timeouts. Is this a known bug ? or are we hit with a virus ?
I searched Google, but did not find any info.
-NagsNags,
The "application timeout" usually means that the driver or the client code
has some sort of timeout within which, if sql server doesnt execute and
return the query, then it times out. For example, I think the default at
the ODBC driver setting is 30 seconds.
That leads us to the next question - why is it taking 30 seconds or more
(I'm making the assumption here that 30 sec is your default timeout as
well). I'm also making another assumption - that the other configurations
you have are EXACTLY like this "problem server" configuation which includes
the same amt of tables, and the same amt of data in these tables. And that,
there was a time maybe when things were fine on this "problem" server.
A couple of things you'd want ot check -
1. Does this happen for any query/hit against the sql server? example, even
logging on (user name/password) results in a timeout?
2. Does it happen for a set of queries?
3. If you know the queries that are commonly executed by this application,
OR some of the queries that you've seen timeout. Take those queries in
Query analyser and see how long they run.
4. Try updating statistics on the tables.
5. Compare index structures on the tables on the 'good' db's with their
counterparts on this problem db.
Hope this helps.
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.|||I'm facing the pro as well.
I look into error log, it said 'Server terminating due to 'Stop' request
from Service Control Manager'. The db server also using the Autogrowth
option, and everytime it growing the log file, seems like the server stop
responding.
Pls advise.
Thanks in advance.
"Vikram Jayaram [MS]" wrote:
> Nags,
> The "application timeout" usually means that the driver or the client code
> has some sort of timeout within which, if sql server doesnt execute and
> return the query, then it times out. For example, I think the default at
> the ODBC driver setting is 30 seconds.
> That leads us to the next question - why is it taking 30 seconds or more
> (I'm making the assumption here that 30 sec is your default timeout as
> well). I'm also making another assumption - that the other configurations
> you have are EXACTLY like this "problem server" configuation which include
s
> the same amt of tables, and the same amt of data in these tables. And that
,
> there was a time maybe when things were fine on this "problem" server.
> A couple of things you'd want ot check -
> 1. Does this happen for any query/hit against the sql server? example, eve
n
> logging on (user name/password) results in a timeout?
> 2. Does it happen for a set of queries?
> 3. If you know the queries that are commonly executed by this application,
> OR some of the queries that you've seen timeout. Take those queries in
> Query analyser and see how long they run.
> 4. Try updating statistics on the tables.
> 5. Compare index structures on the tables on the 'good' db's with their
> counterparts on this problem db.
> Hope this helps.
> Vikram Jayaram
> Microsoft, SQL Server
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.
>
>
application using Java. We are frequently getting Connection Timed Out in
our appserver. Is anyone aware of such an issue this version of SQL Server
? We have similar configurations on several other servers, but only the
appserver's connected to this particular database is giving connection
timeouts. Is this a known bug ? or are we hit with a virus ?
I searched Google, but did not find any info.
-NagsNags,
The "application timeout" usually means that the driver or the client code
has some sort of timeout within which, if sql server doesnt execute and
return the query, then it times out. For example, I think the default at
the ODBC driver setting is 30 seconds.
That leads us to the next question - why is it taking 30 seconds or more
(I'm making the assumption here that 30 sec is your default timeout as
well). I'm also making another assumption - that the other configurations
you have are EXACTLY like this "problem server" configuation which includes
the same amt of tables, and the same amt of data in these tables. And that,
there was a time maybe when things were fine on this "problem" server.
A couple of things you'd want ot check -
1. Does this happen for any query/hit against the sql server? example, even
logging on (user name/password) results in a timeout?
2. Does it happen for a set of queries?
3. If you know the queries that are commonly executed by this application,
OR some of the queries that you've seen timeout. Take those queries in
Query analyser and see how long they run.
4. Try updating statistics on the tables.
5. Compare index structures on the tables on the 'good' db's with their
counterparts on this problem db.
Hope this helps.
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.|||I'm facing the pro as well.
I look into error log, it said 'Server terminating due to 'Stop' request
from Service Control Manager'. The db server also using the Autogrowth
option, and everytime it growing the log file, seems like the server stop
responding.
Pls advise.
Thanks in advance.
"Vikram Jayaram [MS]" wrote:
> Nags,
> The "application timeout" usually means that the driver or the client code
> has some sort of timeout within which, if sql server doesnt execute and
> return the query, then it times out. For example, I think the default at
> the ODBC driver setting is 30 seconds.
> That leads us to the next question - why is it taking 30 seconds or more
> (I'm making the assumption here that 30 sec is your default timeout as
> well). I'm also making another assumption - that the other configurations
> you have are EXACTLY like this "problem server" configuation which include
s
> the same amt of tables, and the same amt of data in these tables. And that
,
> there was a time maybe when things were fine on this "problem" server.
> A couple of things you'd want ot check -
> 1. Does this happen for any query/hit against the sql server? example, eve
n
> logging on (user name/password) results in a timeout?
> 2. Does it happen for a set of queries?
> 3. If you know the queries that are commonly executed by this application,
> OR some of the queries that you've seen timeout. Take those queries in
> Query analyser and see how long they run.
> 4. Try updating statistics on the tables.
> 5. Compare index structures on the tables on the 'good' db's with their
> counterparts on this problem db.
> Hope this helps.
> Vikram Jayaram
> Microsoft, SQL Server
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.
>
>
Connection timed out
We are running SQL Server 2000 Version 8.00.760. We are running our
application using Java. We are frequently getting Connection Timed Out in
our appserver. Is anyone aware of such an issue this version of SQL Server
? We have similar configurations on several other servers, but only the
appserver's connected to this particular database is giving connection
timeouts. Is this a known bug ? or are we hit with a virus ?
I searched Google, but did not find any info.
-NagsNags,
The "application timeout" usually means that the driver or the client code
has some sort of timeout within which, if sql server doesnt execute and
return the query, then it times out. For example, I think the default at
the ODBC driver setting is 30 seconds.
That leads us to the next question - why is it taking 30 seconds or more
(I'm making the assumption here that 30 sec is your default timeout as
well). I'm also making another assumption - that the other configurations
you have are EXACTLY like this "problem server" configuation which includes
the same amt of tables, and the same amt of data in these tables. And that,
there was a time maybe when things were fine on this "problem" server.
A couple of things you'd want ot check -
1. Does this happen for any query/hit against the sql server? example, even
logging on (user name/password) results in a timeout?
2. Does it happen for a set of queries?
3. If you know the queries that are commonly executed by this application,
OR some of the queries that you've seen timeout. Take those queries in
Query analyser and see how long they run.
4. Try updating statistics on the tables.
5. Compare index structures on the tables on the 'good' db's with their
counterparts on this problem db.
Hope this helps.
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.|||I'm facing the pro as well.
I look into error log, it said 'Server terminating due to 'Stop' request
from Service Control Manager'. The db server also using the Autogrowth
option, and everytime it growing the log file, seems like the server stop
responding.
Pls advise.
Thanks in advance.
"Vikram Jayaram [MS]" wrote:
> Nags,
> The "application timeout" usually means that the driver or the client code
> has some sort of timeout within which, if sql server doesnt execute and
> return the query, then it times out. For example, I think the default at
> the ODBC driver setting is 30 seconds.
> That leads us to the next question - why is it taking 30 seconds or more
> (I'm making the assumption here that 30 sec is your default timeout as
> well). I'm also making another assumption - that the other configurations
> you have are EXACTLY like this "problem server" configuation which includes
> the same amt of tables, and the same amt of data in these tables. And that,
> there was a time maybe when things were fine on this "problem" server.
> A couple of things you'd want ot check -
> 1. Does this happen for any query/hit against the sql server? example, even
> logging on (user name/password) results in a timeout?
> 2. Does it happen for a set of queries?
> 3. If you know the queries that are commonly executed by this application,
> OR some of the queries that you've seen timeout. Take those queries in
> Query analyser and see how long they run.
> 4. Try updating statistics on the tables.
> 5. Compare index structures on the tables on the 'good' db's with their
> counterparts on this problem db.
> Hope this helps.
> Vikram Jayaram
> Microsoft, SQL Server
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.
>
>
application using Java. We are frequently getting Connection Timed Out in
our appserver. Is anyone aware of such an issue this version of SQL Server
? We have similar configurations on several other servers, but only the
appserver's connected to this particular database is giving connection
timeouts. Is this a known bug ? or are we hit with a virus ?
I searched Google, but did not find any info.
-NagsNags,
The "application timeout" usually means that the driver or the client code
has some sort of timeout within which, if sql server doesnt execute and
return the query, then it times out. For example, I think the default at
the ODBC driver setting is 30 seconds.
That leads us to the next question - why is it taking 30 seconds or more
(I'm making the assumption here that 30 sec is your default timeout as
well). I'm also making another assumption - that the other configurations
you have are EXACTLY like this "problem server" configuation which includes
the same amt of tables, and the same amt of data in these tables. And that,
there was a time maybe when things were fine on this "problem" server.
A couple of things you'd want ot check -
1. Does this happen for any query/hit against the sql server? example, even
logging on (user name/password) results in a timeout?
2. Does it happen for a set of queries?
3. If you know the queries that are commonly executed by this application,
OR some of the queries that you've seen timeout. Take those queries in
Query analyser and see how long they run.
4. Try updating statistics on the tables.
5. Compare index structures on the tables on the 'good' db's with their
counterparts on this problem db.
Hope this helps.
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.|||I'm facing the pro as well.
I look into error log, it said 'Server terminating due to 'Stop' request
from Service Control Manager'. The db server also using the Autogrowth
option, and everytime it growing the log file, seems like the server stop
responding.
Pls advise.
Thanks in advance.
"Vikram Jayaram [MS]" wrote:
> Nags,
> The "application timeout" usually means that the driver or the client code
> has some sort of timeout within which, if sql server doesnt execute and
> return the query, then it times out. For example, I think the default at
> the ODBC driver setting is 30 seconds.
> That leads us to the next question - why is it taking 30 seconds or more
> (I'm making the assumption here that 30 sec is your default timeout as
> well). I'm also making another assumption - that the other configurations
> you have are EXACTLY like this "problem server" configuation which includes
> the same amt of tables, and the same amt of data in these tables. And that,
> there was a time maybe when things were fine on this "problem" server.
> A couple of things you'd want ot check -
> 1. Does this happen for any query/hit against the sql server? example, even
> logging on (user name/password) results in a timeout?
> 2. Does it happen for a set of queries?
> 3. If you know the queries that are commonly executed by this application,
> OR some of the queries that you've seen timeout. Take those queries in
> Query analyser and see how long they run.
> 4. Try updating statistics on the tables.
> 5. Compare index structures on the tables on the 'good' db's with their
> counterparts on this problem db.
> Hope this helps.
> Vikram Jayaram
> Microsoft, SQL Server
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.
>
>
Connection Time is too long
I'm using ado to connect to a sql server 2000, and suddendly the line that
does the connection (conx.open) is taking a long time. All the apps that
connecto to the server take a long time also. Additionally, when you use the
sql manager, when you retrieve the rows from a table it takes a long time. It
is not a network problem, because we have pretty good speed in our network.
It's something to do with the sql server, but I have not figure out what is
it. I cheked the current activity in the server, and doesn't have a lot of
processes running and half of them are sleeping.
any ideas how to solve this issue?
Thanks
If you connect on the actual SQL Server is there an issue? If not, then you
are looking at an application server or network issue.
"Adal" <Adal@.discussions.microsoft.com> wrote in message
news:5287E88B-270C-4A92-8FF6-2F1E0A252A64@.microsoft.com...
> I'm using ado to connect to a sql server 2000, and suddendly the line that
> does the connection (conx.open) is taking a long time. All the apps that
> connecto to the server take a long time also. Additionally, when you use
the
> sql manager, when you retrieve the rows from a table it takes a long time.
It
> is not a network problem, because we have pretty good speed in our
network.
> It's something to do with the sql server, but I have not figure out what
is
> it. I cheked the current activity in the server, and doesn't have a lot of
> processes running and half of them are sleeping.
> any ideas how to solve this issue?
> Thanks
|||Hi, I went to the SQL server, and worked directly on it, with the enterprise
manager, and it seem to me that the time to retrieve the information from
tables was still a little high. I still have to wait long time to get a
connection to sql from ado. But, as I said before, this is not all the time,
is every minute or so. I don't think it has anything to do with ado or
connection poolin, because when I work with enterprise manager and retrieve
information from tables, it sometimes takes longer to retrieve the
information, no matter how many rows does it have.
thanks
does the connection (conx.open) is taking a long time. All the apps that
connecto to the server take a long time also. Additionally, when you use the
sql manager, when you retrieve the rows from a table it takes a long time. It
is not a network problem, because we have pretty good speed in our network.
It's something to do with the sql server, but I have not figure out what is
it. I cheked the current activity in the server, and doesn't have a lot of
processes running and half of them are sleeping.
any ideas how to solve this issue?
Thanks
If you connect on the actual SQL Server is there an issue? If not, then you
are looking at an application server or network issue.
"Adal" <Adal@.discussions.microsoft.com> wrote in message
news:5287E88B-270C-4A92-8FF6-2F1E0A252A64@.microsoft.com...
> I'm using ado to connect to a sql server 2000, and suddendly the line that
> does the connection (conx.open) is taking a long time. All the apps that
> connecto to the server take a long time also. Additionally, when you use
the
> sql manager, when you retrieve the rows from a table it takes a long time.
It
> is not a network problem, because we have pretty good speed in our
network.
> It's something to do with the sql server, but I have not figure out what
is
> it. I cheked the current activity in the server, and doesn't have a lot of
> processes running and half of them are sleeping.
> any ideas how to solve this issue?
> Thanks
|||Hi, I went to the SQL server, and worked directly on it, with the enterprise
manager, and it seem to me that the time to retrieve the information from
tables was still a little high. I still have to wait long time to get a
connection to sql from ado. But, as I said before, this is not all the time,
is every minute or so. I don't think it has anything to do with ado or
connection poolin, because when I work with enterprise manager and retrieve
information from tables, it sometimes takes longer to retrieve the
information, no matter how many rows does it have.
thanks
Connection Time is too long
I'm using ado to connect to a sql server 2000, and suddendly the line that
does the connection (conx.open) is taking a long time. All the apps that
connecto to the server take a long time also. Additionally, when you use the
sql manager, when you retrieve the rows from a table it takes a long time. I
t
is not a network problem, because we have pretty good speed in our network.
It's something to do with the sql server, but I have not figure out what is
it. I cheked the current activity in the server, and doesn't have a lot of
processes running and half of them are sleeping.
any ideas how to solve this issue?
ThanksIf you connect on the actual SQL Server is there an issue? If not, then you
are looking at an application server or network issue.
"Adal" <Adal@.discussions.microsoft.com> wrote in message
news:5287E88B-270C-4A92-8FF6-2F1E0A252A64@.microsoft.com...
> I'm using ado to connect to a sql server 2000, and suddendly the line that
> does the connection (conx.open) is taking a long time. All the apps that
> connecto to the server take a long time also. Additionally, when you use
the
> sql manager, when you retrieve the rows from a table it takes a long time.
It
> is not a network problem, because we have pretty good speed in our
network.
> It's something to do with the sql server, but I have not figure out what
is
> it. I cheked the current activity in the server, and doesn't have a lot of
> processes running and half of them are sleeping.
> any ideas how to solve this issue?
> Thanks|||Hi, I went to the SQL server, and worked directly on it, with the enterprise
manager, and it seem to me that the time to retrieve the information from
tables was still a little high. I still have to wait long time to get a
connection to sql from ado. But, as I said before, this is not all the time,
is every minute or so. I don't think it has anything to do with ado or
connection poolin, because when I work with enterprise manager and retrieve
information from tables, it sometimes takes longer to retrieve the
information, no matter how many rows does it have.
thanks
does the connection (conx.open) is taking a long time. All the apps that
connecto to the server take a long time also. Additionally, when you use the
sql manager, when you retrieve the rows from a table it takes a long time. I
t
is not a network problem, because we have pretty good speed in our network.
It's something to do with the sql server, but I have not figure out what is
it. I cheked the current activity in the server, and doesn't have a lot of
processes running and half of them are sleeping.
any ideas how to solve this issue?
ThanksIf you connect on the actual SQL Server is there an issue? If not, then you
are looking at an application server or network issue.
"Adal" <Adal@.discussions.microsoft.com> wrote in message
news:5287E88B-270C-4A92-8FF6-2F1E0A252A64@.microsoft.com...
> I'm using ado to connect to a sql server 2000, and suddendly the line that
> does the connection (conx.open) is taking a long time. All the apps that
> connecto to the server take a long time also. Additionally, when you use
the
> sql manager, when you retrieve the rows from a table it takes a long time.
It
> is not a network problem, because we have pretty good speed in our
network.
> It's something to do with the sql server, but I have not figure out what
is
> it. I cheked the current activity in the server, and doesn't have a lot of
> processes running and half of them are sleeping.
> any ideas how to solve this issue?
> Thanks|||Hi, I went to the SQL server, and worked directly on it, with the enterprise
manager, and it seem to me that the time to retrieve the information from
tables was still a little high. I still have to wait long time to get a
connection to sql from ado. But, as I said before, this is not all the time,
is every minute or so. I don't think it has anything to do with ado or
connection poolin, because when I work with enterprise manager and retrieve
information from tables, it sometimes takes longer to retrieve the
information, no matter how many rows does it have.
thanks
Connection Thru Internet
Hello again pals, i meet you again to ask an old question i had, but no found answer.
Can i connect to a remote SQL Server thru Internet, lets say from a client like Query Analyzer?
I read to make it using a VPN, but i try to configure it and cant get connected.
I'm using a Win2000 Server or Win2003 Server,
Thanksfull!!!!
There is many people asking for that, i start to think it cant be done! :'(If you are using virtual private networking (VPN) to get onto the network where the SQL Server resides, yes you can access a SQL Server throught the internet. You need a router that allows for VPN, there is some configuration to do here and depending on your SQL authentication method, you are going to either have to setup accounts for the users NT logins(windows authentication) or setup user names and passwords for these users. Do'nt forget to assign them permissions within SQL as well.|||If you are using virtual private networking (VPN)
There is no other way to do it, without using VPN?, it has been a headache to try configure it!
Thanks for your help Thrasymachus, is well received|||This is the only way I can think of if you want use the QA to access the data. You can always create an online interface to do what your users need but it sounds like they need a lot of access.|||If you know how to Telnet and use OSQL you can query your database from the command line|||If you make the ports that your server is listening on open to the internet you could connect directly. If you are using QA though I'd think you were doing some administrative tasks. You really shouldn't open up access to your server to the internet unless it's absolutely neccessary. If you can use a VPN, do it.|||Another way would be to set up port forwarding. But again it's as unsecure as directly exposing the server.
Can i connect to a remote SQL Server thru Internet, lets say from a client like Query Analyzer?
I read to make it using a VPN, but i try to configure it and cant get connected.
I'm using a Win2000 Server or Win2003 Server,
Thanksfull!!!!
There is many people asking for that, i start to think it cant be done! :'(If you are using virtual private networking (VPN) to get onto the network where the SQL Server resides, yes you can access a SQL Server throught the internet. You need a router that allows for VPN, there is some configuration to do here and depending on your SQL authentication method, you are going to either have to setup accounts for the users NT logins(windows authentication) or setup user names and passwords for these users. Do'nt forget to assign them permissions within SQL as well.|||If you are using virtual private networking (VPN)
There is no other way to do it, without using VPN?, it has been a headache to try configure it!
Thanks for your help Thrasymachus, is well received|||This is the only way I can think of if you want use the QA to access the data. You can always create an online interface to do what your users need but it sounds like they need a lot of access.|||If you know how to Telnet and use OSQL you can query your database from the command line|||If you make the ports that your server is listening on open to the internet you could connect directly. If you are using QA though I'd think you were doing some administrative tasks. You really shouldn't open up access to your server to the internet unless it's absolutely neccessary. If you can use a VPN, do it.|||Another way would be to set up port forwarding. But again it's as unsecure as directly exposing the server.
Connection Throttle
I have an application that where some of the queries hit the server hard (I
cannot rewrite the app). Some of the queries take 5 or more minutes to run
and during this time the CPU is pegged and SQL no longer services other
queries until the the hog query is complete. I there a way I can throttle a
connection - like not allow a connection to use more than 25% of the CPU?
Thanks,
Troy
Try setting the MAXDOP to something less than the total number of
processors. But it sounds like you probably need to just tune your query.
Andrew J. Kelly SQL MVP
"Troy Murphy" <thm92630@.hotmail.com> wrote in message
news:O%23em8ifsEHA.224@.TK2MSFTNGP15.phx.gbl...
>I have an application that where some of the queries hit the server hard (I
>cannot rewrite the app). Some of the queries take 5 or more minutes to run
>and during this time the CPU is pegged and SQL no longer services other
>queries until the the hog query is complete. I there a way I can throttle
>a connection - like not allow a connection to use more than 25% of the CPU?
> Thanks,
> Troy
>
|||Troy Murphy wrote:
> I have an application that where some of the queries hit the server
> hard (I cannot rewrite the app). Some of the queries take 5 or more
> minutes to run and during this time the CPU is pegged and SQL no
> longer services other queries until the the hog query is complete. I
> there a way I can throttle a connection - like not allow a connection
> to use more than 25% of the CPU?
> Thanks,
> Troy
Maybe you could add appropriate indexes to the database to help the
query run more efficiently without worrying about touching the
application.
David Gugick
Imceda Software
www.imceda.com
|||Thanks, I will look into that. The queries are fairly optimized, but we
have 10's of millions of records. Even a simple select sum() takes several
minutes. However the main culprit is a morning update against a few hundred
thousand records that keeps the software unusable during that time.
Troy
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uezVitfsEHA.3572@.tk2msftngp13.phx.gbl...
> Try setting the MAXDOP to something less than the total number of
> processors. But it sounds like you probably need to just tune your
> query.
> --
> Andrew J. Kelly SQL MVP
>
> "Troy Murphy" <thm92630@.hotmail.com> wrote in message
> news:O%23em8ifsEHA.224@.TK2MSFTNGP15.phx.gbl...
>
|||Several minutes for a simple SUM() is pretty slow. I suspect you lack
enough memory and have the wrong disk configurations. Any chance you can
create a covering index for that SUM()? As for the updates are you trying
to do all of them in one transaction? If so you may want to break them up
into smaller batches. It's pretty easy to lop and update say 10,000 rows at
a time and will usually be faster and certainly less intrusive.
Andrew J. Kelly SQL MVP
"Troy Murphy" <thm92630@.hotmail.com> wrote in message
news:Onn12wisEHA.2780@.TK2MSFTNGP09.phx.gbl...
> Thanks, I will look into that. The queries are fairly optimized, but we
> have 10's of millions of records. Even a simple select sum() takes
> several minutes. However the main culprit is a morning update against a
> few hundred thousand records that keeps the software unusable during that
> time.
> Troy
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uezVitfsEHA.3572@.tk2msftngp13.phx.gbl...
>
|||On Thu, 14 Oct 2004 07:12:17 -0700, "Troy Murphy"
<thm92630@.hotmail.com> wrote:
>I have an application that where some of the queries hit the server hard (I
>cannot rewrite the app). Some of the queries take 5 or more minutes to run
>and during this time the CPU is pegged and SQL no longer services other
>queries until the the hog query is complete. I there a way I can throttle a
>connection - like not allow a connection to use more than 25% of the CPU?
No.
Better would be a way to assign a lower priority to the offending
connection/query.
But the thinking is that this kind of thing is very dangerous, as the
throttled thread may be holding resources that block other users.
Unfortunately, you can get conditions where the range of the heavy
query is logically limited, it just soaks up all the database
capability.
If your server is otherwise large enough memory-wise and your disk
usage is appropriate (log files on separate disk from data files) and
you have two or more processors, then short of recoding, which you say
you can't do, that's it.
J.
|||Thanks to all for the feedback. The SUM() is just an example and the
application is a 3rd party solution so changing the source would be
difficult to say the least.
Still, the behavior seems to be that only a single query will run at a time
and queries from other connections are on hold while the query is running.
I played with the MAXDOP as well as the CURSOR THRESHOLD setting with out
any changes in behavior (reboot of server after each change).
I can reproduce this behavior using query analyzer from 2 workstations where
WS1 will run a long query and while that is running WS2 will run a very
quick query. While WS1 is running WS2 has the world turning, but as soon as
WS1 completes, the result set from WS2 is served.
The server configuration is a dual processor with hyper-threading and 2 gb
RAM. Unfortunately the drive is un-striped IDE. Windows 2003 and SQL
Server 2000 Enterprise Edition. Running Performance Monitor on Processor
Time and SqlServer Cache Pages does not indicate an overload.
Troy
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:O9GpoIksEHA.2320@.TK2MSFTNGP12.phx.gbl...
> Several minutes for a simple SUM() is pretty slow. I suspect you lack
> enough memory and have the wrong disk configurations. Any chance you can
> create a covering index for that SUM()? As for the updates are you trying
> to do all of them in one transaction? If so you may want to break them up
> into smaller batches. It's pretty easy to lop and update say 10,000 rows
> at a time and will usually be faster and certainly less intrusive.
>
> --
> Andrew J. Kelly SQL MVP
>
> "Troy Murphy" <thm92630@.hotmail.com> wrote in message
> news:Onn12wisEHA.2780@.TK2MSFTNGP09.phx.gbl...
>
|||Troy,
What you are describing sounds more like a blocking issue and not so much
CPU. If you set MAXDOP to 1 and the query were to use all of the first
processor the others would still be available. Have you looked at sp_who2
to see if any blocking is going on while this is happening? It also still
can be that the tables are poorly indexed. Even though the app is 3rd party
you can most likely add indexes.
Andrew J. Kelly SQL MVP
"Troy Murphy" <thm92630@.hotmail.com> wrote in message
news:ufFY8ttsEHA.3556@.TK2MSFTNGP10.phx.gbl...
> Thanks to all for the feedback. The SUM() is just an example and the
> application is a 3rd party solution so changing the source would be
> difficult to say the least.
> Still, the behavior seems to be that only a single query will run at a
> time and queries from other connections are on hold while the query is
> running. I played with the MAXDOP as well as the CURSOR THRESHOLD setting
> with out any changes in behavior (reboot of server after each change).
> I can reproduce this behavior using query analyzer from 2 workstations
> where WS1 will run a long query and while that is running WS2 will run a
> very quick query. While WS1 is running WS2 has the world turning, but as
> soon as WS1 completes, the result set from WS2 is served.
> The server configuration is a dual processor with hyper-threading and 2 gb
> RAM. Unfortunately the drive is un-striped IDE. Windows 2003 and SQL
> Server 2000 Enterprise Edition. Running Performance Monitor on Processor
> Time and SqlServer Cache Pages does not indicate an overload.
> Troy
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:O9GpoIksEHA.2320@.TK2MSFTNGP12.phx.gbl...
>
|||Ok, I reproduced the scenerio described below and ran SP_WHO (I was actually
expecting this to wait for the query to complete, but it ran as expected).
Anyway, the user who was running the long query showed a status of 'Ready to
Run' while executing and the process that was waiting in limbo showed as
'Sleeping'. All values in the BLK field were 0 and each had a unique SPID.
I had reset all the Configuration settings back to default. We exported the
database to another server (single processor, 1 gb Ram, SCSI) and that
server did not seem to have this problem in that the small queries ran as
expected while the large query was still running. HELP!!!!
Troy
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uPahbQusEHA.2072@.tk2msftngp13.phx.gbl...
> Troy,
> What you are describing sounds more like a blocking issue and not so much
> CPU. If you set MAXDOP to 1 and the query were to use all of the first
> processor the others would still be available. Have you looked at sp_who2
> to see if any blocking is going on while this is happening? It also still
> can be that the tables are poorly indexed. Even though the app is 3rd
> party you can most likely add indexes.
> --
> Andrew J. Kelly SQL MVP
>
> "Troy Murphy" <thm92630@.hotmail.com> wrote in message
> news:ufFY8ttsEHA.3556@.TK2MSFTNGP10.phx.gbl...
>
|||What about disk queues? Is the first process creating a massive disk queue?
sp_who would not be affected as much by that. What about wait types? Try
running DBCC SQLPERF(Waitstats) or select * from sysprocesses and see what
the wait types and times are for the 2 spids.
Andrew J. Kelly SQL MVP
"Troy Murphy" <thm92630@.hotmail.com> wrote in message
news:efIBI7usEHA.3788@.TK2MSFTNGP15.phx.gbl...
> Ok, I reproduced the scenerio described below and ran SP_WHO (I was
> actually expecting this to wait for the query to complete, but it ran as
> expected). Anyway, the user who was running the long query showed a status
> of 'Ready to Run' while executing and the process that was waiting in
> limbo showed as 'Sleeping'. All values in the BLK field were 0 and each
> had a unique SPID. I had reset all the Configuration settings back to
> default. We exported the database to another server (single processor, 1
> gb Ram, SCSI) and that server did not seem to have this problem in that
> the small queries ran as expected while the large query was still running.
> HELP!!!!
> Troy
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uPahbQusEHA.2072@.tk2msftngp13.phx.gbl...
>
cannot rewrite the app). Some of the queries take 5 or more minutes to run
and during this time the CPU is pegged and SQL no longer services other
queries until the the hog query is complete. I there a way I can throttle a
connection - like not allow a connection to use more than 25% of the CPU?
Thanks,
Troy
Try setting the MAXDOP to something less than the total number of
processors. But it sounds like you probably need to just tune your query.
Andrew J. Kelly SQL MVP
"Troy Murphy" <thm92630@.hotmail.com> wrote in message
news:O%23em8ifsEHA.224@.TK2MSFTNGP15.phx.gbl...
>I have an application that where some of the queries hit the server hard (I
>cannot rewrite the app). Some of the queries take 5 or more minutes to run
>and during this time the CPU is pegged and SQL no longer services other
>queries until the the hog query is complete. I there a way I can throttle
>a connection - like not allow a connection to use more than 25% of the CPU?
> Thanks,
> Troy
>
|||Troy Murphy wrote:
> I have an application that where some of the queries hit the server
> hard (I cannot rewrite the app). Some of the queries take 5 or more
> minutes to run and during this time the CPU is pegged and SQL no
> longer services other queries until the the hog query is complete. I
> there a way I can throttle a connection - like not allow a connection
> to use more than 25% of the CPU?
> Thanks,
> Troy
Maybe you could add appropriate indexes to the database to help the
query run more efficiently without worrying about touching the
application.
David Gugick
Imceda Software
www.imceda.com
|||Thanks, I will look into that. The queries are fairly optimized, but we
have 10's of millions of records. Even a simple select sum() takes several
minutes. However the main culprit is a morning update against a few hundred
thousand records that keeps the software unusable during that time.
Troy
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uezVitfsEHA.3572@.tk2msftngp13.phx.gbl...
> Try setting the MAXDOP to something less than the total number of
> processors. But it sounds like you probably need to just tune your
> query.
> --
> Andrew J. Kelly SQL MVP
>
> "Troy Murphy" <thm92630@.hotmail.com> wrote in message
> news:O%23em8ifsEHA.224@.TK2MSFTNGP15.phx.gbl...
>
|||Several minutes for a simple SUM() is pretty slow. I suspect you lack
enough memory and have the wrong disk configurations. Any chance you can
create a covering index for that SUM()? As for the updates are you trying
to do all of them in one transaction? If so you may want to break them up
into smaller batches. It's pretty easy to lop and update say 10,000 rows at
a time and will usually be faster and certainly less intrusive.
Andrew J. Kelly SQL MVP
"Troy Murphy" <thm92630@.hotmail.com> wrote in message
news:Onn12wisEHA.2780@.TK2MSFTNGP09.phx.gbl...
> Thanks, I will look into that. The queries are fairly optimized, but we
> have 10's of millions of records. Even a simple select sum() takes
> several minutes. However the main culprit is a morning update against a
> few hundred thousand records that keeps the software unusable during that
> time.
> Troy
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uezVitfsEHA.3572@.tk2msftngp13.phx.gbl...
>
|||On Thu, 14 Oct 2004 07:12:17 -0700, "Troy Murphy"
<thm92630@.hotmail.com> wrote:
>I have an application that where some of the queries hit the server hard (I
>cannot rewrite the app). Some of the queries take 5 or more minutes to run
>and during this time the CPU is pegged and SQL no longer services other
>queries until the the hog query is complete. I there a way I can throttle a
>connection - like not allow a connection to use more than 25% of the CPU?
No.
Better would be a way to assign a lower priority to the offending
connection/query.
But the thinking is that this kind of thing is very dangerous, as the
throttled thread may be holding resources that block other users.
Unfortunately, you can get conditions where the range of the heavy
query is logically limited, it just soaks up all the database
capability.
If your server is otherwise large enough memory-wise and your disk
usage is appropriate (log files on separate disk from data files) and
you have two or more processors, then short of recoding, which you say
you can't do, that's it.
J.
|||Thanks to all for the feedback. The SUM() is just an example and the
application is a 3rd party solution so changing the source would be
difficult to say the least.
Still, the behavior seems to be that only a single query will run at a time
and queries from other connections are on hold while the query is running.
I played with the MAXDOP as well as the CURSOR THRESHOLD setting with out
any changes in behavior (reboot of server after each change).
I can reproduce this behavior using query analyzer from 2 workstations where
WS1 will run a long query and while that is running WS2 will run a very
quick query. While WS1 is running WS2 has the world turning, but as soon as
WS1 completes, the result set from WS2 is served.
The server configuration is a dual processor with hyper-threading and 2 gb
RAM. Unfortunately the drive is un-striped IDE. Windows 2003 and SQL
Server 2000 Enterprise Edition. Running Performance Monitor on Processor
Time and SqlServer Cache Pages does not indicate an overload.
Troy
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:O9GpoIksEHA.2320@.TK2MSFTNGP12.phx.gbl...
> Several minutes for a simple SUM() is pretty slow. I suspect you lack
> enough memory and have the wrong disk configurations. Any chance you can
> create a covering index for that SUM()? As for the updates are you trying
> to do all of them in one transaction? If so you may want to break them up
> into smaller batches. It's pretty easy to lop and update say 10,000 rows
> at a time and will usually be faster and certainly less intrusive.
>
> --
> Andrew J. Kelly SQL MVP
>
> "Troy Murphy" <thm92630@.hotmail.com> wrote in message
> news:Onn12wisEHA.2780@.TK2MSFTNGP09.phx.gbl...
>
|||Troy,
What you are describing sounds more like a blocking issue and not so much
CPU. If you set MAXDOP to 1 and the query were to use all of the first
processor the others would still be available. Have you looked at sp_who2
to see if any blocking is going on while this is happening? It also still
can be that the tables are poorly indexed. Even though the app is 3rd party
you can most likely add indexes.
Andrew J. Kelly SQL MVP
"Troy Murphy" <thm92630@.hotmail.com> wrote in message
news:ufFY8ttsEHA.3556@.TK2MSFTNGP10.phx.gbl...
> Thanks to all for the feedback. The SUM() is just an example and the
> application is a 3rd party solution so changing the source would be
> difficult to say the least.
> Still, the behavior seems to be that only a single query will run at a
> time and queries from other connections are on hold while the query is
> running. I played with the MAXDOP as well as the CURSOR THRESHOLD setting
> with out any changes in behavior (reboot of server after each change).
> I can reproduce this behavior using query analyzer from 2 workstations
> where WS1 will run a long query and while that is running WS2 will run a
> very quick query. While WS1 is running WS2 has the world turning, but as
> soon as WS1 completes, the result set from WS2 is served.
> The server configuration is a dual processor with hyper-threading and 2 gb
> RAM. Unfortunately the drive is un-striped IDE. Windows 2003 and SQL
> Server 2000 Enterprise Edition. Running Performance Monitor on Processor
> Time and SqlServer Cache Pages does not indicate an overload.
> Troy
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:O9GpoIksEHA.2320@.TK2MSFTNGP12.phx.gbl...
>
|||Ok, I reproduced the scenerio described below and ran SP_WHO (I was actually
expecting this to wait for the query to complete, but it ran as expected).
Anyway, the user who was running the long query showed a status of 'Ready to
Run' while executing and the process that was waiting in limbo showed as
'Sleeping'. All values in the BLK field were 0 and each had a unique SPID.
I had reset all the Configuration settings back to default. We exported the
database to another server (single processor, 1 gb Ram, SCSI) and that
server did not seem to have this problem in that the small queries ran as
expected while the large query was still running. HELP!!!!
Troy
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uPahbQusEHA.2072@.tk2msftngp13.phx.gbl...
> Troy,
> What you are describing sounds more like a blocking issue and not so much
> CPU. If you set MAXDOP to 1 and the query were to use all of the first
> processor the others would still be available. Have you looked at sp_who2
> to see if any blocking is going on while this is happening? It also still
> can be that the tables are poorly indexed. Even though the app is 3rd
> party you can most likely add indexes.
> --
> Andrew J. Kelly SQL MVP
>
> "Troy Murphy" <thm92630@.hotmail.com> wrote in message
> news:ufFY8ttsEHA.3556@.TK2MSFTNGP10.phx.gbl...
>
|||What about disk queues? Is the first process creating a massive disk queue?
sp_who would not be affected as much by that. What about wait types? Try
running DBCC SQLPERF(Waitstats) or select * from sysprocesses and see what
the wait types and times are for the 2 spids.
Andrew J. Kelly SQL MVP
"Troy Murphy" <thm92630@.hotmail.com> wrote in message
news:efIBI7usEHA.3788@.TK2MSFTNGP15.phx.gbl...
> Ok, I reproduced the scenerio described below and ran SP_WHO (I was
> actually expecting this to wait for the query to complete, but it ran as
> expected). Anyway, the user who was running the long query showed a status
> of 'Ready to Run' while executing and the process that was waiting in
> limbo showed as 'Sleeping'. All values in the BLK field were 0 and each
> had a unique SPID. I had reset all the Configuration settings back to
> default. We exported the database to another server (single processor, 1
> gb Ram, SCSI) and that server did not seem to have this problem in that
> the small queries ran as expected while the large query was still running.
> HELP!!!!
> Troy
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uPahbQusEHA.2072@.tk2msftngp13.phx.gbl...
>
Connection Throttle
I have an application that where some of the queries hit the server hard (I
cannot rewrite the app). Some of the queries take 5 or more minutes to run
and during this time the CPU is pegged and SQL no longer services other
queries until the the hog query is complete. I there a way I can throttle a
connection - like not allow a connection to use more than 25% of the CPU?
Thanks,
TroyTry setting the MAXDOP to something less than the total number of
processors. But it sounds like you probably need to just tune your query.
--
Andrew J. Kelly SQL MVP
"Troy Murphy" <thm92630@.hotmail.com> wrote in message
news:O%23em8ifsEHA.224@.TK2MSFTNGP15.phx.gbl...
>I have an application that where some of the queries hit the server hard (I
>cannot rewrite the app). Some of the queries take 5 or more minutes to run
>and during this time the CPU is pegged and SQL no longer services other
>queries until the the hog query is complete. I there a way I can throttle
>a connection - like not allow a connection to use more than 25% of the CPU?
> Thanks,
> Troy
>|||Troy Murphy wrote:
> I have an application that where some of the queries hit the server
> hard (I cannot rewrite the app). Some of the queries take 5 or more
> minutes to run and during this time the CPU is pegged and SQL no
> longer services other queries until the the hog query is complete. I
> there a way I can throttle a connection - like not allow a connection
> to use more than 25% of the CPU?
> Thanks,
> Troy
Maybe you could add appropriate indexes to the database to help the
query run more efficiently without worrying about touching the
application.
--
David Gugick
Imceda Software
www.imceda.com|||Thanks, I will look into that. The queries are fairly optimized, but we
have 10's of millions of records. Even a simple select sum() takes several
minutes. However the main culprit is a morning update against a few hundred
thousand records that keeps the software unusable during that time.
Troy
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uezVitfsEHA.3572@.tk2msftngp13.phx.gbl...
> Try setting the MAXDOP to something less than the total number of
> processors. But it sounds like you probably need to just tune your
> query.
> --
> Andrew J. Kelly SQL MVP
>
> "Troy Murphy" <thm92630@.hotmail.com> wrote in message
> news:O%23em8ifsEHA.224@.TK2MSFTNGP15.phx.gbl...
>>I have an application that where some of the queries hit the server hard
>>(I cannot rewrite the app). Some of the queries take 5 or more minutes to
>>run and during this time the CPU is pegged and SQL no longer services
>>other queries until the the hog query is complete. I there a way I can
>>throttle a connection - like not allow a connection to use more than 25%
>>of the CPU?
>> Thanks,
>> Troy
>|||Several minutes for a simple SUM() is pretty slow. I suspect you lack
enough memory and have the wrong disk configurations. Any chance you can
create a covering index for that SUM()? As for the updates are you trying
to do all of them in one transaction? If so you may want to break them up
into smaller batches. It's pretty easy to lop and update say 10,000 rows at
a time and will usually be faster and certainly less intrusive.
Andrew J. Kelly SQL MVP
"Troy Murphy" <thm92630@.hotmail.com> wrote in message
news:Onn12wisEHA.2780@.TK2MSFTNGP09.phx.gbl...
> Thanks, I will look into that. The queries are fairly optimized, but we
> have 10's of millions of records. Even a simple select sum() takes
> several minutes. However the main culprit is a morning update against a
> few hundred thousand records that keeps the software unusable during that
> time.
> Troy
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uezVitfsEHA.3572@.tk2msftngp13.phx.gbl...
>> Try setting the MAXDOP to something less than the total number of
>> processors. But it sounds like you probably need to just tune your
>> query.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Troy Murphy" <thm92630@.hotmail.com> wrote in message
>> news:O%23em8ifsEHA.224@.TK2MSFTNGP15.phx.gbl...
>>I have an application that where some of the queries hit the server hard
>>(I cannot rewrite the app). Some of the queries take 5 or more minutes
>>to run and during this time the CPU is pegged and SQL no longer services
>>other queries until the the hog query is complete. I there a way I can
>>throttle a connection - like not allow a connection to use more than 25%
>>of the CPU?
>> Thanks,
>> Troy
>>
>|||On Thu, 14 Oct 2004 07:12:17 -0700, "Troy Murphy"
<thm92630@.hotmail.com> wrote:
>I have an application that where some of the queries hit the server hard (I
>cannot rewrite the app). Some of the queries take 5 or more minutes to run
>and during this time the CPU is pegged and SQL no longer services other
>queries until the the hog query is complete. I there a way I can throttle a
>connection - like not allow a connection to use more than 25% of the CPU?
No.
Better would be a way to assign a lower priority to the offending
connection/query.
But the thinking is that this kind of thing is very dangerous, as the
throttled thread may be holding resources that block other users.
Unfortunately, you can get conditions where the range of the heavy
query is logically limited, it just soaks up all the database
capability.
If your server is otherwise large enough memory-wise and your disk
usage is appropriate (log files on separate disk from data files) and
you have two or more processors, then short of recoding, which you say
you can't do, that's it.
J.|||Thanks to all for the feedback. The SUM() is just an example and the
application is a 3rd party solution so changing the source would be
difficult to say the least.
Still, the behavior seems to be that only a single query will run at a time
and queries from other connections are on hold while the query is running.
I played with the MAXDOP as well as the CURSOR THRESHOLD setting with out
any changes in behavior (reboot of server after each change).
I can reproduce this behavior using query analyzer from 2 workstations where
WS1 will run a long query and while that is running WS2 will run a very
quick query. While WS1 is running WS2 has the world turning, but as soon as
WS1 completes, the result set from WS2 is served.
The server configuration is a dual processor with hyper-threading and 2 gb
RAM. Unfortunately the drive is un-striped IDE. Windows 2003 and SQL
Server 2000 Enterprise Edition. Running Performance Monitor on Processor
Time and SqlServer Cache Pages does not indicate an overload.
Troy
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:O9GpoIksEHA.2320@.TK2MSFTNGP12.phx.gbl...
> Several minutes for a simple SUM() is pretty slow. I suspect you lack
> enough memory and have the wrong disk configurations. Any chance you can
> create a covering index for that SUM()? As for the updates are you trying
> to do all of them in one transaction? If so you may want to break them up
> into smaller batches. It's pretty easy to lop and update say 10,000 rows
> at a time and will usually be faster and certainly less intrusive.
>
> --
> Andrew J. Kelly SQL MVP
>
> "Troy Murphy" <thm92630@.hotmail.com> wrote in message
> news:Onn12wisEHA.2780@.TK2MSFTNGP09.phx.gbl...
>> Thanks, I will look into that. The queries are fairly optimized, but we
>> have 10's of millions of records. Even a simple select sum() takes
>> several minutes. However the main culprit is a morning update against a
>> few hundred thousand records that keeps the software unusable during that
>> time.
>> Troy
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:uezVitfsEHA.3572@.tk2msftngp13.phx.gbl...
>> Try setting the MAXDOP to something less than the total number of
>> processors. But it sounds like you probably need to just tune your
>> query.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Troy Murphy" <thm92630@.hotmail.com> wrote in message
>> news:O%23em8ifsEHA.224@.TK2MSFTNGP15.phx.gbl...
>>I have an application that where some of the queries hit the server hard
>>(I cannot rewrite the app). Some of the queries take 5 or more minutes
>>to run and during this time the CPU is pegged and SQL no longer services
>>other queries until the the hog query is complete. I there a way I can
>>throttle a connection - like not allow a connection to use more than 25%
>>of the CPU?
>> Thanks,
>> Troy
>>
>>
>|||Troy,
What you are describing sounds more like a blocking issue and not so much
CPU. If you set MAXDOP to 1 and the query were to use all of the first
processor the others would still be available. Have you looked at sp_who2
to see if any blocking is going on while this is happening? It also still
can be that the tables are poorly indexed. Even though the app is 3rd party
you can most likely add indexes.
--
Andrew J. Kelly SQL MVP
"Troy Murphy" <thm92630@.hotmail.com> wrote in message
news:ufFY8ttsEHA.3556@.TK2MSFTNGP10.phx.gbl...
> Thanks to all for the feedback. The SUM() is just an example and the
> application is a 3rd party solution so changing the source would be
> difficult to say the least.
> Still, the behavior seems to be that only a single query will run at a
> time and queries from other connections are on hold while the query is
> running. I played with the MAXDOP as well as the CURSOR THRESHOLD setting
> with out any changes in behavior (reboot of server after each change).
> I can reproduce this behavior using query analyzer from 2 workstations
> where WS1 will run a long query and while that is running WS2 will run a
> very quick query. While WS1 is running WS2 has the world turning, but as
> soon as WS1 completes, the result set from WS2 is served.
> The server configuration is a dual processor with hyper-threading and 2 gb
> RAM. Unfortunately the drive is un-striped IDE. Windows 2003 and SQL
> Server 2000 Enterprise Edition. Running Performance Monitor on Processor
> Time and SqlServer Cache Pages does not indicate an overload.
> Troy
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:O9GpoIksEHA.2320@.TK2MSFTNGP12.phx.gbl...
>> Several minutes for a simple SUM() is pretty slow. I suspect you lack
>> enough memory and have the wrong disk configurations. Any chance you can
>> create a covering index for that SUM()? As for the updates are you
>> trying to do all of them in one transaction? If so you may want to break
>> them up into smaller batches. It's pretty easy to lop and update say
>> 10,000 rows at a time and will usually be faster and certainly less
>> intrusive.
>>
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Troy Murphy" <thm92630@.hotmail.com> wrote in message
>> news:Onn12wisEHA.2780@.TK2MSFTNGP09.phx.gbl...
>> Thanks, I will look into that. The queries are fairly optimized, but we
>> have 10's of millions of records. Even a simple select sum() takes
>> several minutes. However the main culprit is a morning update against a
>> few hundred thousand records that keeps the software unusable during
>> that time.
>> Troy
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:uezVitfsEHA.3572@.tk2msftngp13.phx.gbl...
>> Try setting the MAXDOP to something less than the total number of
>> processors. But it sounds like you probably need to just tune your
>> query.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Troy Murphy" <thm92630@.hotmail.com> wrote in message
>> news:O%23em8ifsEHA.224@.TK2MSFTNGP15.phx.gbl...
>>I have an application that where some of the queries hit the server
>>hard (I cannot rewrite the app). Some of the queries take 5 or more
>>minutes to run and during this time the CPU is pegged and SQL no longer
>>services other queries until the the hog query is complete. I there a
>>way I can throttle a connection - like not allow a connection to use
>>more than 25% of the CPU?
>> Thanks,
>> Troy
>>
>>
>>
>|||Ok, I reproduced the scenerio described below and ran SP_WHO (I was actually
expecting this to wait for the query to complete, but it ran as expected).
Anyway, the user who was running the long query showed a status of 'Ready to
Run' while executing and the process that was waiting in limbo showed as
'Sleeping'. All values in the BLK field were 0 and each had a unique SPID.
I had reset all the Configuration settings back to default. We exported the
database to another server (single processor, 1 gb Ram, SCSI) and that
server did not seem to have this problem in that the small queries ran as
expected while the large query was still running. HELP!!!!
Troy
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uPahbQusEHA.2072@.tk2msftngp13.phx.gbl...
> Troy,
> What you are describing sounds more like a blocking issue and not so much
> CPU. If you set MAXDOP to 1 and the query were to use all of the first
> processor the others would still be available. Have you looked at sp_who2
> to see if any blocking is going on while this is happening? It also still
> can be that the tables are poorly indexed. Even though the app is 3rd
> party you can most likely add indexes.
> --
> Andrew J. Kelly SQL MVP
>
> "Troy Murphy" <thm92630@.hotmail.com> wrote in message
> news:ufFY8ttsEHA.3556@.TK2MSFTNGP10.phx.gbl...
>> Thanks to all for the feedback. The SUM() is just an example and the
>> application is a 3rd party solution so changing the source would be
>> difficult to say the least.
>> Still, the behavior seems to be that only a single query will run at a
>> time and queries from other connections are on hold while the query is
>> running. I played with the MAXDOP as well as the CURSOR THRESHOLD setting
>> with out any changes in behavior (reboot of server after each change).
>> I can reproduce this behavior using query analyzer from 2 workstations
>> where WS1 will run a long query and while that is running WS2 will run a
>> very quick query. While WS1 is running WS2 has the world turning, but as
>> soon as WS1 completes, the result set from WS2 is served.
>> The server configuration is a dual processor with hyper-threading and 2
>> gb RAM. Unfortunately the drive is un-striped IDE. Windows 2003 and SQL
>> Server 2000 Enterprise Edition. Running Performance Monitor on Processor
>> Time and SqlServer Cache Pages does not indicate an overload.
>> Troy
>>
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:O9GpoIksEHA.2320@.TK2MSFTNGP12.phx.gbl...
>> Several minutes for a simple SUM() is pretty slow. I suspect you lack
>> enough memory and have the wrong disk configurations. Any chance you can
>> create a covering index for that SUM()? As for the updates are you
>> trying to do all of them in one transaction? If so you may want to
>> break them up into smaller batches. It's pretty easy to lop and update
>> say 10,000 rows at a time and will usually be faster and certainly less
>> intrusive.
>>
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Troy Murphy" <thm92630@.hotmail.com> wrote in message
>> news:Onn12wisEHA.2780@.TK2MSFTNGP09.phx.gbl...
>> Thanks, I will look into that. The queries are fairly optimized, but
>> we have 10's of millions of records. Even a simple select sum() takes
>> several minutes. However the main culprit is a morning update against
>> a few hundred thousand records that keeps the software unusable during
>> that time.
>> Troy
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:uezVitfsEHA.3572@.tk2msftngp13.phx.gbl...
>> Try setting the MAXDOP to something less than the total number of
>> processors. But it sounds like you probably need to just tune your
>> query.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Troy Murphy" <thm92630@.hotmail.com> wrote in message
>> news:O%23em8ifsEHA.224@.TK2MSFTNGP15.phx.gbl...
>>I have an application that where some of the queries hit the server
>>hard (I cannot rewrite the app). Some of the queries take 5 or more
>>minutes to run and during this time the CPU is pegged and SQL no
>>longer services other queries until the the hog query is complete. I
>>there a way I can throttle a connection - like not allow a connection
>>to use more than 25% of the CPU?
>> Thanks,
>> Troy
>>
>>
>>
>>
>|||What about disk queues? Is the first process creating a massive disk queue?
sp_who would not be affected as much by that. What about wait types? Try
running DBCC SQLPERF(Waitstats) or select * from sysprocesses and see what
the wait types and times are for the 2 spids.
--
Andrew J. Kelly SQL MVP
"Troy Murphy" <thm92630@.hotmail.com> wrote in message
news:efIBI7usEHA.3788@.TK2MSFTNGP15.phx.gbl...
> Ok, I reproduced the scenerio described below and ran SP_WHO (I was
> actually expecting this to wait for the query to complete, but it ran as
> expected). Anyway, the user who was running the long query showed a status
> of 'Ready to Run' while executing and the process that was waiting in
> limbo showed as 'Sleeping'. All values in the BLK field were 0 and each
> had a unique SPID. I had reset all the Configuration settings back to
> default. We exported the database to another server (single processor, 1
> gb Ram, SCSI) and that server did not seem to have this problem in that
> the small queries ran as expected while the large query was still running.
> HELP!!!!
> Troy
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uPahbQusEHA.2072@.tk2msftngp13.phx.gbl...
>> Troy,
>> What you are describing sounds more like a blocking issue and not so much
>> CPU. If you set MAXDOP to 1 and the query were to use all of the first
>> processor the others would still be available. Have you looked at
>> sp_who2 to see if any blocking is going on while this is happening? It
>> also still can be that the tables are poorly indexed. Even though the
>> app is 3rd party you can most likely add indexes.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Troy Murphy" <thm92630@.hotmail.com> wrote in message
>> news:ufFY8ttsEHA.3556@.TK2MSFTNGP10.phx.gbl...
>> Thanks to all for the feedback. The SUM() is just an example and the
>> application is a 3rd party solution so changing the source would be
>> difficult to say the least.
>> Still, the behavior seems to be that only a single query will run at a
>> time and queries from other connections are on hold while the query is
>> running. I played with the MAXDOP as well as the CURSOR THRESHOLD
>> setting with out any changes in behavior (reboot of server after each
>> change).
>> I can reproduce this behavior using query analyzer from 2 workstations
>> where WS1 will run a long query and while that is running WS2 will run a
>> very quick query. While WS1 is running WS2 has the world turning, but
>> as soon as WS1 completes, the result set from WS2 is served.
>> The server configuration is a dual processor with hyper-threading and 2
>> gb RAM. Unfortunately the drive is un-striped IDE. Windows 2003 and
>> SQL Server 2000 Enterprise Edition. Running Performance Monitor on
>> Processor Time and SqlServer Cache Pages does not indicate an overload.
>> Troy
>>
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:O9GpoIksEHA.2320@.TK2MSFTNGP12.phx.gbl...
>> Several minutes for a simple SUM() is pretty slow. I suspect you lack
>> enough memory and have the wrong disk configurations. Any chance you
>> can create a covering index for that SUM()? As for the updates are you
>> trying to do all of them in one transaction? If so you may want to
>> break them up into smaller batches. It's pretty easy to lop and update
>> say 10,000 rows at a time and will usually be faster and certainly less
>> intrusive.
>>
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Troy Murphy" <thm92630@.hotmail.com> wrote in message
>> news:Onn12wisEHA.2780@.TK2MSFTNGP09.phx.gbl...
>> Thanks, I will look into that. The queries are fairly optimized, but
>> we have 10's of millions of records. Even a simple select sum() takes
>> several minutes. However the main culprit is a morning update against
>> a few hundred thousand records that keeps the software unusable during
>> that time.
>> Troy
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:uezVitfsEHA.3572@.tk2msftngp13.phx.gbl...
>> Try setting the MAXDOP to something less than the total number of
>> processors. But it sounds like you probably need to just tune your
>> query.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Troy Murphy" <thm92630@.hotmail.com> wrote in message
>> news:O%23em8ifsEHA.224@.TK2MSFTNGP15.phx.gbl...
>>>I have an application that where some of the queries hit the server
>>>hard (I cannot rewrite the app). Some of the queries take 5 or more
>>>minutes to run and during this time the CPU is pegged and SQL no
>>>longer services other queries until the the hog query is complete. I
>>>there a way I can throttle a connection - like not allow a connection
>>>to use more than 25% of the CPU?
>>>
>>> Thanks,
>>> Troy
>>>
>>
>>
>>
>>
>>
>|||Thanks for the assistance, but this is getting way over my head...
I was not planning on becomming a SQL guru but hoping for good
'out-of-the-box' performance.
Anyway, we are going to throw more hardware at it to see if the situation
resolves itself. If that does not work, we will refactor to Pervasive SQL
which seems to perform better on this application.
Troy
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OeX8qGwsEHA.3412@.TK2MSFTNGP14.phx.gbl...
> What about disk queues? Is the first process creating a massive disk
> queue? sp_who would not be affected as much by that. What about wait
> types? Try running DBCC SQLPERF(Waitstats) or select * from sysprocesses
> and see what the wait types and times are for the 2 spids.
> --
> Andrew J. Kelly SQL MVP
>
> "Troy Murphy" <thm92630@.hotmail.com> wrote in message
> news:efIBI7usEHA.3788@.TK2MSFTNGP15.phx.gbl...
>> Ok, I reproduced the scenerio described below and ran SP_WHO (I was
>> actually expecting this to wait for the query to complete, but it ran as
>> expected). Anyway, the user who was running the long query showed a
>> status of 'Ready to Run' while executing and the process that was waiting
>> in limbo showed as 'Sleeping'. All values in the BLK field were 0 and
>> each had a unique SPID. I had reset all the Configuration settings back
>> to default. We exported the database to another server (single
>> processor, 1 gb Ram, SCSI) and that server did not seem to have this
>> problem in that the small queries ran as expected while the large query
>> was still running. HELP!!!!
>> Troy
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:uPahbQusEHA.2072@.tk2msftngp13.phx.gbl...
>> Troy,
>> What you are describing sounds more like a blocking issue and not so
>> much CPU. If you set MAXDOP to 1 and the query were to use all of the
>> first processor the others would still be available. Have you looked at
>> sp_who2 to see if any blocking is going on while this is happening? It
>> also still can be that the tables are poorly indexed. Even though the
>> app is 3rd party you can most likely add indexes.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Troy Murphy" <thm92630@.hotmail.com> wrote in message
>> news:ufFY8ttsEHA.3556@.TK2MSFTNGP10.phx.gbl...
>> Thanks to all for the feedback. The SUM() is just an example and the
>> application is a 3rd party solution so changing the source would be
>> difficult to say the least.
>> Still, the behavior seems to be that only a single query will run at a
>> time and queries from other connections are on hold while the query is
>> running. I played with the MAXDOP as well as the CURSOR THRESHOLD
>> setting with out any changes in behavior (reboot of server after each
>> change).
>> I can reproduce this behavior using query analyzer from 2 workstations
>> where WS1 will run a long query and while that is running WS2 will run
>> a very quick query. While WS1 is running WS2 has the world turning,
>> but as soon as WS1 completes, the result set from WS2 is served.
>> The server configuration is a dual processor with hyper-threading and 2
>> gb RAM. Unfortunately the drive is un-striped IDE. Windows 2003 and
>> SQL Server 2000 Enterprise Edition. Running Performance Monitor on
>> Processor Time and SqlServer Cache Pages does not indicate an overload.
>> Troy
>>
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:O9GpoIksEHA.2320@.TK2MSFTNGP12.phx.gbl...
>> Several minutes for a simple SUM() is pretty slow. I suspect you lack
>> enough memory and have the wrong disk configurations. Any chance you
>> can create a covering index for that SUM()? As for the updates are
>> you trying to do all of them in one transaction? If so you may want
>> to break them up into smaller batches. It's pretty easy to lop and
>> update say 10,000 rows at a time and will usually be faster and
>> certainly less intrusive.
>>
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Troy Murphy" <thm92630@.hotmail.com> wrote in message
>> news:Onn12wisEHA.2780@.TK2MSFTNGP09.phx.gbl...
>> Thanks, I will look into that. The queries are fairly optimized, but
>> we have 10's of millions of records. Even a simple select sum()
>> takes several minutes. However the main culprit is a morning update
>> against a few hundred thousand records that keeps the software
>> unusable during that time.
>> Troy
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:uezVitfsEHA.3572@.tk2msftngp13.phx.gbl...
>>> Try setting the MAXDOP to something less than the total number of
>>> processors. But it sounds like you probably need to just tune your
>>> query.
>>>
>>> --
>>> Andrew J. Kelly SQL MVP
>>>
>>>
>>> "Troy Murphy" <thm92630@.hotmail.com> wrote in message
>>> news:O%23em8ifsEHA.224@.TK2MSFTNGP15.phx.gbl...
>>>I have an application that where some of the queries hit the server
>>>hard (I cannot rewrite the app). Some of the queries take 5 or more
>>>minutes to run and during this time the CPU is pegged and SQL no
>>>longer services other queries until the the hog query is complete.
>>>I there a way I can throttle a connection - like not allow a
>>>connection to use more than 25% of the CPU?
>>>
>>> Thanks,
>>> Troy
>>>
>>>
>>>
>>
>>
>>
>>
>>
>
cannot rewrite the app). Some of the queries take 5 or more minutes to run
and during this time the CPU is pegged and SQL no longer services other
queries until the the hog query is complete. I there a way I can throttle a
connection - like not allow a connection to use more than 25% of the CPU?
Thanks,
TroyTry setting the MAXDOP to something less than the total number of
processors. But it sounds like you probably need to just tune your query.
--
Andrew J. Kelly SQL MVP
"Troy Murphy" <thm92630@.hotmail.com> wrote in message
news:O%23em8ifsEHA.224@.TK2MSFTNGP15.phx.gbl...
>I have an application that where some of the queries hit the server hard (I
>cannot rewrite the app). Some of the queries take 5 or more minutes to run
>and during this time the CPU is pegged and SQL no longer services other
>queries until the the hog query is complete. I there a way I can throttle
>a connection - like not allow a connection to use more than 25% of the CPU?
> Thanks,
> Troy
>|||Troy Murphy wrote:
> I have an application that where some of the queries hit the server
> hard (I cannot rewrite the app). Some of the queries take 5 or more
> minutes to run and during this time the CPU is pegged and SQL no
> longer services other queries until the the hog query is complete. I
> there a way I can throttle a connection - like not allow a connection
> to use more than 25% of the CPU?
> Thanks,
> Troy
Maybe you could add appropriate indexes to the database to help the
query run more efficiently without worrying about touching the
application.
--
David Gugick
Imceda Software
www.imceda.com|||Thanks, I will look into that. The queries are fairly optimized, but we
have 10's of millions of records. Even a simple select sum() takes several
minutes. However the main culprit is a morning update against a few hundred
thousand records that keeps the software unusable during that time.
Troy
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uezVitfsEHA.3572@.tk2msftngp13.phx.gbl...
> Try setting the MAXDOP to something less than the total number of
> processors. But it sounds like you probably need to just tune your
> query.
> --
> Andrew J. Kelly SQL MVP
>
> "Troy Murphy" <thm92630@.hotmail.com> wrote in message
> news:O%23em8ifsEHA.224@.TK2MSFTNGP15.phx.gbl...
>>I have an application that where some of the queries hit the server hard
>>(I cannot rewrite the app). Some of the queries take 5 or more minutes to
>>run and during this time the CPU is pegged and SQL no longer services
>>other queries until the the hog query is complete. I there a way I can
>>throttle a connection - like not allow a connection to use more than 25%
>>of the CPU?
>> Thanks,
>> Troy
>|||Several minutes for a simple SUM() is pretty slow. I suspect you lack
enough memory and have the wrong disk configurations. Any chance you can
create a covering index for that SUM()? As for the updates are you trying
to do all of them in one transaction? If so you may want to break them up
into smaller batches. It's pretty easy to lop and update say 10,000 rows at
a time and will usually be faster and certainly less intrusive.
Andrew J. Kelly SQL MVP
"Troy Murphy" <thm92630@.hotmail.com> wrote in message
news:Onn12wisEHA.2780@.TK2MSFTNGP09.phx.gbl...
> Thanks, I will look into that. The queries are fairly optimized, but we
> have 10's of millions of records. Even a simple select sum() takes
> several minutes. However the main culprit is a morning update against a
> few hundred thousand records that keeps the software unusable during that
> time.
> Troy
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uezVitfsEHA.3572@.tk2msftngp13.phx.gbl...
>> Try setting the MAXDOP to something less than the total number of
>> processors. But it sounds like you probably need to just tune your
>> query.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Troy Murphy" <thm92630@.hotmail.com> wrote in message
>> news:O%23em8ifsEHA.224@.TK2MSFTNGP15.phx.gbl...
>>I have an application that where some of the queries hit the server hard
>>(I cannot rewrite the app). Some of the queries take 5 or more minutes
>>to run and during this time the CPU is pegged and SQL no longer services
>>other queries until the the hog query is complete. I there a way I can
>>throttle a connection - like not allow a connection to use more than 25%
>>of the CPU?
>> Thanks,
>> Troy
>>
>|||On Thu, 14 Oct 2004 07:12:17 -0700, "Troy Murphy"
<thm92630@.hotmail.com> wrote:
>I have an application that where some of the queries hit the server hard (I
>cannot rewrite the app). Some of the queries take 5 or more minutes to run
>and during this time the CPU is pegged and SQL no longer services other
>queries until the the hog query is complete. I there a way I can throttle a
>connection - like not allow a connection to use more than 25% of the CPU?
No.
Better would be a way to assign a lower priority to the offending
connection/query.
But the thinking is that this kind of thing is very dangerous, as the
throttled thread may be holding resources that block other users.
Unfortunately, you can get conditions where the range of the heavy
query is logically limited, it just soaks up all the database
capability.
If your server is otherwise large enough memory-wise and your disk
usage is appropriate (log files on separate disk from data files) and
you have two or more processors, then short of recoding, which you say
you can't do, that's it.
J.|||Thanks to all for the feedback. The SUM() is just an example and the
application is a 3rd party solution so changing the source would be
difficult to say the least.
Still, the behavior seems to be that only a single query will run at a time
and queries from other connections are on hold while the query is running.
I played with the MAXDOP as well as the CURSOR THRESHOLD setting with out
any changes in behavior (reboot of server after each change).
I can reproduce this behavior using query analyzer from 2 workstations where
WS1 will run a long query and while that is running WS2 will run a very
quick query. While WS1 is running WS2 has the world turning, but as soon as
WS1 completes, the result set from WS2 is served.
The server configuration is a dual processor with hyper-threading and 2 gb
RAM. Unfortunately the drive is un-striped IDE. Windows 2003 and SQL
Server 2000 Enterprise Edition. Running Performance Monitor on Processor
Time and SqlServer Cache Pages does not indicate an overload.
Troy
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:O9GpoIksEHA.2320@.TK2MSFTNGP12.phx.gbl...
> Several minutes for a simple SUM() is pretty slow. I suspect you lack
> enough memory and have the wrong disk configurations. Any chance you can
> create a covering index for that SUM()? As for the updates are you trying
> to do all of them in one transaction? If so you may want to break them up
> into smaller batches. It's pretty easy to lop and update say 10,000 rows
> at a time and will usually be faster and certainly less intrusive.
>
> --
> Andrew J. Kelly SQL MVP
>
> "Troy Murphy" <thm92630@.hotmail.com> wrote in message
> news:Onn12wisEHA.2780@.TK2MSFTNGP09.phx.gbl...
>> Thanks, I will look into that. The queries are fairly optimized, but we
>> have 10's of millions of records. Even a simple select sum() takes
>> several minutes. However the main culprit is a morning update against a
>> few hundred thousand records that keeps the software unusable during that
>> time.
>> Troy
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:uezVitfsEHA.3572@.tk2msftngp13.phx.gbl...
>> Try setting the MAXDOP to something less than the total number of
>> processors. But it sounds like you probably need to just tune your
>> query.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Troy Murphy" <thm92630@.hotmail.com> wrote in message
>> news:O%23em8ifsEHA.224@.TK2MSFTNGP15.phx.gbl...
>>I have an application that where some of the queries hit the server hard
>>(I cannot rewrite the app). Some of the queries take 5 or more minutes
>>to run and during this time the CPU is pegged and SQL no longer services
>>other queries until the the hog query is complete. I there a way I can
>>throttle a connection - like not allow a connection to use more than 25%
>>of the CPU?
>> Thanks,
>> Troy
>>
>>
>|||Troy,
What you are describing sounds more like a blocking issue and not so much
CPU. If you set MAXDOP to 1 and the query were to use all of the first
processor the others would still be available. Have you looked at sp_who2
to see if any blocking is going on while this is happening? It also still
can be that the tables are poorly indexed. Even though the app is 3rd party
you can most likely add indexes.
--
Andrew J. Kelly SQL MVP
"Troy Murphy" <thm92630@.hotmail.com> wrote in message
news:ufFY8ttsEHA.3556@.TK2MSFTNGP10.phx.gbl...
> Thanks to all for the feedback. The SUM() is just an example and the
> application is a 3rd party solution so changing the source would be
> difficult to say the least.
> Still, the behavior seems to be that only a single query will run at a
> time and queries from other connections are on hold while the query is
> running. I played with the MAXDOP as well as the CURSOR THRESHOLD setting
> with out any changes in behavior (reboot of server after each change).
> I can reproduce this behavior using query analyzer from 2 workstations
> where WS1 will run a long query and while that is running WS2 will run a
> very quick query. While WS1 is running WS2 has the world turning, but as
> soon as WS1 completes, the result set from WS2 is served.
> The server configuration is a dual processor with hyper-threading and 2 gb
> RAM. Unfortunately the drive is un-striped IDE. Windows 2003 and SQL
> Server 2000 Enterprise Edition. Running Performance Monitor on Processor
> Time and SqlServer Cache Pages does not indicate an overload.
> Troy
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:O9GpoIksEHA.2320@.TK2MSFTNGP12.phx.gbl...
>> Several minutes for a simple SUM() is pretty slow. I suspect you lack
>> enough memory and have the wrong disk configurations. Any chance you can
>> create a covering index for that SUM()? As for the updates are you
>> trying to do all of them in one transaction? If so you may want to break
>> them up into smaller batches. It's pretty easy to lop and update say
>> 10,000 rows at a time and will usually be faster and certainly less
>> intrusive.
>>
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Troy Murphy" <thm92630@.hotmail.com> wrote in message
>> news:Onn12wisEHA.2780@.TK2MSFTNGP09.phx.gbl...
>> Thanks, I will look into that. The queries are fairly optimized, but we
>> have 10's of millions of records. Even a simple select sum() takes
>> several minutes. However the main culprit is a morning update against a
>> few hundred thousand records that keeps the software unusable during
>> that time.
>> Troy
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:uezVitfsEHA.3572@.tk2msftngp13.phx.gbl...
>> Try setting the MAXDOP to something less than the total number of
>> processors. But it sounds like you probably need to just tune your
>> query.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Troy Murphy" <thm92630@.hotmail.com> wrote in message
>> news:O%23em8ifsEHA.224@.TK2MSFTNGP15.phx.gbl...
>>I have an application that where some of the queries hit the server
>>hard (I cannot rewrite the app). Some of the queries take 5 or more
>>minutes to run and during this time the CPU is pegged and SQL no longer
>>services other queries until the the hog query is complete. I there a
>>way I can throttle a connection - like not allow a connection to use
>>more than 25% of the CPU?
>> Thanks,
>> Troy
>>
>>
>>
>|||Ok, I reproduced the scenerio described below and ran SP_WHO (I was actually
expecting this to wait for the query to complete, but it ran as expected).
Anyway, the user who was running the long query showed a status of 'Ready to
Run' while executing and the process that was waiting in limbo showed as
'Sleeping'. All values in the BLK field were 0 and each had a unique SPID.
I had reset all the Configuration settings back to default. We exported the
database to another server (single processor, 1 gb Ram, SCSI) and that
server did not seem to have this problem in that the small queries ran as
expected while the large query was still running. HELP!!!!
Troy
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uPahbQusEHA.2072@.tk2msftngp13.phx.gbl...
> Troy,
> What you are describing sounds more like a blocking issue and not so much
> CPU. If you set MAXDOP to 1 and the query were to use all of the first
> processor the others would still be available. Have you looked at sp_who2
> to see if any blocking is going on while this is happening? It also still
> can be that the tables are poorly indexed. Even though the app is 3rd
> party you can most likely add indexes.
> --
> Andrew J. Kelly SQL MVP
>
> "Troy Murphy" <thm92630@.hotmail.com> wrote in message
> news:ufFY8ttsEHA.3556@.TK2MSFTNGP10.phx.gbl...
>> Thanks to all for the feedback. The SUM() is just an example and the
>> application is a 3rd party solution so changing the source would be
>> difficult to say the least.
>> Still, the behavior seems to be that only a single query will run at a
>> time and queries from other connections are on hold while the query is
>> running. I played with the MAXDOP as well as the CURSOR THRESHOLD setting
>> with out any changes in behavior (reboot of server after each change).
>> I can reproduce this behavior using query analyzer from 2 workstations
>> where WS1 will run a long query and while that is running WS2 will run a
>> very quick query. While WS1 is running WS2 has the world turning, but as
>> soon as WS1 completes, the result set from WS2 is served.
>> The server configuration is a dual processor with hyper-threading and 2
>> gb RAM. Unfortunately the drive is un-striped IDE. Windows 2003 and SQL
>> Server 2000 Enterprise Edition. Running Performance Monitor on Processor
>> Time and SqlServer Cache Pages does not indicate an overload.
>> Troy
>>
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:O9GpoIksEHA.2320@.TK2MSFTNGP12.phx.gbl...
>> Several minutes for a simple SUM() is pretty slow. I suspect you lack
>> enough memory and have the wrong disk configurations. Any chance you can
>> create a covering index for that SUM()? As for the updates are you
>> trying to do all of them in one transaction? If so you may want to
>> break them up into smaller batches. It's pretty easy to lop and update
>> say 10,000 rows at a time and will usually be faster and certainly less
>> intrusive.
>>
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Troy Murphy" <thm92630@.hotmail.com> wrote in message
>> news:Onn12wisEHA.2780@.TK2MSFTNGP09.phx.gbl...
>> Thanks, I will look into that. The queries are fairly optimized, but
>> we have 10's of millions of records. Even a simple select sum() takes
>> several minutes. However the main culprit is a morning update against
>> a few hundred thousand records that keeps the software unusable during
>> that time.
>> Troy
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:uezVitfsEHA.3572@.tk2msftngp13.phx.gbl...
>> Try setting the MAXDOP to something less than the total number of
>> processors. But it sounds like you probably need to just tune your
>> query.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Troy Murphy" <thm92630@.hotmail.com> wrote in message
>> news:O%23em8ifsEHA.224@.TK2MSFTNGP15.phx.gbl...
>>I have an application that where some of the queries hit the server
>>hard (I cannot rewrite the app). Some of the queries take 5 or more
>>minutes to run and during this time the CPU is pegged and SQL no
>>longer services other queries until the the hog query is complete. I
>>there a way I can throttle a connection - like not allow a connection
>>to use more than 25% of the CPU?
>> Thanks,
>> Troy
>>
>>
>>
>>
>|||What about disk queues? Is the first process creating a massive disk queue?
sp_who would not be affected as much by that. What about wait types? Try
running DBCC SQLPERF(Waitstats) or select * from sysprocesses and see what
the wait types and times are for the 2 spids.
--
Andrew J. Kelly SQL MVP
"Troy Murphy" <thm92630@.hotmail.com> wrote in message
news:efIBI7usEHA.3788@.TK2MSFTNGP15.phx.gbl...
> Ok, I reproduced the scenerio described below and ran SP_WHO (I was
> actually expecting this to wait for the query to complete, but it ran as
> expected). Anyway, the user who was running the long query showed a status
> of 'Ready to Run' while executing and the process that was waiting in
> limbo showed as 'Sleeping'. All values in the BLK field were 0 and each
> had a unique SPID. I had reset all the Configuration settings back to
> default. We exported the database to another server (single processor, 1
> gb Ram, SCSI) and that server did not seem to have this problem in that
> the small queries ran as expected while the large query was still running.
> HELP!!!!
> Troy
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uPahbQusEHA.2072@.tk2msftngp13.phx.gbl...
>> Troy,
>> What you are describing sounds more like a blocking issue and not so much
>> CPU. If you set MAXDOP to 1 and the query were to use all of the first
>> processor the others would still be available. Have you looked at
>> sp_who2 to see if any blocking is going on while this is happening? It
>> also still can be that the tables are poorly indexed. Even though the
>> app is 3rd party you can most likely add indexes.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Troy Murphy" <thm92630@.hotmail.com> wrote in message
>> news:ufFY8ttsEHA.3556@.TK2MSFTNGP10.phx.gbl...
>> Thanks to all for the feedback. The SUM() is just an example and the
>> application is a 3rd party solution so changing the source would be
>> difficult to say the least.
>> Still, the behavior seems to be that only a single query will run at a
>> time and queries from other connections are on hold while the query is
>> running. I played with the MAXDOP as well as the CURSOR THRESHOLD
>> setting with out any changes in behavior (reboot of server after each
>> change).
>> I can reproduce this behavior using query analyzer from 2 workstations
>> where WS1 will run a long query and while that is running WS2 will run a
>> very quick query. While WS1 is running WS2 has the world turning, but
>> as soon as WS1 completes, the result set from WS2 is served.
>> The server configuration is a dual processor with hyper-threading and 2
>> gb RAM. Unfortunately the drive is un-striped IDE. Windows 2003 and
>> SQL Server 2000 Enterprise Edition. Running Performance Monitor on
>> Processor Time and SqlServer Cache Pages does not indicate an overload.
>> Troy
>>
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:O9GpoIksEHA.2320@.TK2MSFTNGP12.phx.gbl...
>> Several minutes for a simple SUM() is pretty slow. I suspect you lack
>> enough memory and have the wrong disk configurations. Any chance you
>> can create a covering index for that SUM()? As for the updates are you
>> trying to do all of them in one transaction? If so you may want to
>> break them up into smaller batches. It's pretty easy to lop and update
>> say 10,000 rows at a time and will usually be faster and certainly less
>> intrusive.
>>
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Troy Murphy" <thm92630@.hotmail.com> wrote in message
>> news:Onn12wisEHA.2780@.TK2MSFTNGP09.phx.gbl...
>> Thanks, I will look into that. The queries are fairly optimized, but
>> we have 10's of millions of records. Even a simple select sum() takes
>> several minutes. However the main culprit is a morning update against
>> a few hundred thousand records that keeps the software unusable during
>> that time.
>> Troy
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:uezVitfsEHA.3572@.tk2msftngp13.phx.gbl...
>> Try setting the MAXDOP to something less than the total number of
>> processors. But it sounds like you probably need to just tune your
>> query.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Troy Murphy" <thm92630@.hotmail.com> wrote in message
>> news:O%23em8ifsEHA.224@.TK2MSFTNGP15.phx.gbl...
>>>I have an application that where some of the queries hit the server
>>>hard (I cannot rewrite the app). Some of the queries take 5 or more
>>>minutes to run and during this time the CPU is pegged and SQL no
>>>longer services other queries until the the hog query is complete. I
>>>there a way I can throttle a connection - like not allow a connection
>>>to use more than 25% of the CPU?
>>>
>>> Thanks,
>>> Troy
>>>
>>
>>
>>
>>
>>
>|||Thanks for the assistance, but this is getting way over my head...
I was not planning on becomming a SQL guru but hoping for good
'out-of-the-box' performance.
Anyway, we are going to throw more hardware at it to see if the situation
resolves itself. If that does not work, we will refactor to Pervasive SQL
which seems to perform better on this application.
Troy
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OeX8qGwsEHA.3412@.TK2MSFTNGP14.phx.gbl...
> What about disk queues? Is the first process creating a massive disk
> queue? sp_who would not be affected as much by that. What about wait
> types? Try running DBCC SQLPERF(Waitstats) or select * from sysprocesses
> and see what the wait types and times are for the 2 spids.
> --
> Andrew J. Kelly SQL MVP
>
> "Troy Murphy" <thm92630@.hotmail.com> wrote in message
> news:efIBI7usEHA.3788@.TK2MSFTNGP15.phx.gbl...
>> Ok, I reproduced the scenerio described below and ran SP_WHO (I was
>> actually expecting this to wait for the query to complete, but it ran as
>> expected). Anyway, the user who was running the long query showed a
>> status of 'Ready to Run' while executing and the process that was waiting
>> in limbo showed as 'Sleeping'. All values in the BLK field were 0 and
>> each had a unique SPID. I had reset all the Configuration settings back
>> to default. We exported the database to another server (single
>> processor, 1 gb Ram, SCSI) and that server did not seem to have this
>> problem in that the small queries ran as expected while the large query
>> was still running. HELP!!!!
>> Troy
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:uPahbQusEHA.2072@.tk2msftngp13.phx.gbl...
>> Troy,
>> What you are describing sounds more like a blocking issue and not so
>> much CPU. If you set MAXDOP to 1 and the query were to use all of the
>> first processor the others would still be available. Have you looked at
>> sp_who2 to see if any blocking is going on while this is happening? It
>> also still can be that the tables are poorly indexed. Even though the
>> app is 3rd party you can most likely add indexes.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Troy Murphy" <thm92630@.hotmail.com> wrote in message
>> news:ufFY8ttsEHA.3556@.TK2MSFTNGP10.phx.gbl...
>> Thanks to all for the feedback. The SUM() is just an example and the
>> application is a 3rd party solution so changing the source would be
>> difficult to say the least.
>> Still, the behavior seems to be that only a single query will run at a
>> time and queries from other connections are on hold while the query is
>> running. I played with the MAXDOP as well as the CURSOR THRESHOLD
>> setting with out any changes in behavior (reboot of server after each
>> change).
>> I can reproduce this behavior using query analyzer from 2 workstations
>> where WS1 will run a long query and while that is running WS2 will run
>> a very quick query. While WS1 is running WS2 has the world turning,
>> but as soon as WS1 completes, the result set from WS2 is served.
>> The server configuration is a dual processor with hyper-threading and 2
>> gb RAM. Unfortunately the drive is un-striped IDE. Windows 2003 and
>> SQL Server 2000 Enterprise Edition. Running Performance Monitor on
>> Processor Time and SqlServer Cache Pages does not indicate an overload.
>> Troy
>>
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:O9GpoIksEHA.2320@.TK2MSFTNGP12.phx.gbl...
>> Several minutes for a simple SUM() is pretty slow. I suspect you lack
>> enough memory and have the wrong disk configurations. Any chance you
>> can create a covering index for that SUM()? As for the updates are
>> you trying to do all of them in one transaction? If so you may want
>> to break them up into smaller batches. It's pretty easy to lop and
>> update say 10,000 rows at a time and will usually be faster and
>> certainly less intrusive.
>>
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Troy Murphy" <thm92630@.hotmail.com> wrote in message
>> news:Onn12wisEHA.2780@.TK2MSFTNGP09.phx.gbl...
>> Thanks, I will look into that. The queries are fairly optimized, but
>> we have 10's of millions of records. Even a simple select sum()
>> takes several minutes. However the main culprit is a morning update
>> against a few hundred thousand records that keeps the software
>> unusable during that time.
>> Troy
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:uezVitfsEHA.3572@.tk2msftngp13.phx.gbl...
>>> Try setting the MAXDOP to something less than the total number of
>>> processors. But it sounds like you probably need to just tune your
>>> query.
>>>
>>> --
>>> Andrew J. Kelly SQL MVP
>>>
>>>
>>> "Troy Murphy" <thm92630@.hotmail.com> wrote in message
>>> news:O%23em8ifsEHA.224@.TK2MSFTNGP15.phx.gbl...
>>>I have an application that where some of the queries hit the server
>>>hard (I cannot rewrite the app). Some of the queries take 5 or more
>>>minutes to run and during this time the CPU is pegged and SQL no
>>>longer services other queries until the the hog query is complete.
>>>I there a way I can throttle a connection - like not allow a
>>>connection to use more than 25% of the CPU?
>>>
>>> Thanks,
>>> Troy
>>>
>>>
>>>
>>
>>
>>
>>
>>
>
Connection Throttle
I have an application that where some of the queries hit the server hard (I
cannot rewrite the app). Some of the queries take 5 or more minutes to run
and during this time the CPU is pegged and SQL no longer services other
queries until the the hog query is complete. I there a way I can throttle a
connection - like not allow a connection to use more than 25% of the CPU?
Thanks,
TroyTry setting the MAXDOP to something less than the total number of
processors. But it sounds like you probably need to just tune your query.
Andrew J. Kelly SQL MVP
"Troy Murphy" <thm92630@.hotmail.com> wrote in message
news:O%23em8ifsEHA.224@.TK2MSFTNGP15.phx.gbl...
>I have an application that where some of the queries hit the server hard (I
>cannot rewrite the app). Some of the queries take 5 or more minutes to run
>and during this time the CPU is pegged and SQL no longer services other
>queries until the the hog query is complete. I there a way I can throttle
>a connection - like not allow a connection to use more than 25% of the CPU?
> Thanks,
> Troy
>|||Troy Murphy wrote:
> I have an application that where some of the queries hit the server
> hard (I cannot rewrite the app). Some of the queries take 5 or more
> minutes to run and during this time the CPU is pegged and SQL no
> longer services other queries until the the hog query is complete. I
> there a way I can throttle a connection - like not allow a connection
> to use more than 25% of the CPU?
> Thanks,
> Troy
Maybe you could add appropriate indexes to the database to help the
query run more efficiently without worrying about touching the
application.
David Gugick
Imceda Software
www.imceda.com|||Thanks, I will look into that. The queries are fairly optimized, but we
have 10's of millions of records. Even a simple select sum() takes several
minutes. However the main culprit is a morning update against a few hundred
thousand records that keeps the software unusable during that time.
Troy
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uezVitfsEHA.3572@.tk2msftngp13.phx.gbl...
> Try setting the MAXDOP to something less than the total number of
> processors. But it sounds like you probably need to just tune your
> query.
> --
> Andrew J. Kelly SQL MVP
>
> "Troy Murphy" <thm92630@.hotmail.com> wrote in message
> news:O%23em8ifsEHA.224@.TK2MSFTNGP15.phx.gbl...
>|||Several minutes for a simple SUM() is pretty slow. I suspect you lack
enough memory and have the wrong disk configurations. Any chance you can
create a covering index for that SUM()? As for the updates are you trying
to do all of them in one transaction? If so you may want to break them up
into smaller batches. It's pretty easy to lop and update say 10,000 rows at
a time and will usually be faster and certainly less intrusive.
Andrew J. Kelly SQL MVP
"Troy Murphy" <thm92630@.hotmail.com> wrote in message
news:Onn12wisEHA.2780@.TK2MSFTNGP09.phx.gbl...
> Thanks, I will look into that. The queries are fairly optimized, but we
> have 10's of millions of records. Even a simple select sum() takes
> several minutes. However the main culprit is a morning update against a
> few hundred thousand records that keeps the software unusable during that
> time.
> Troy
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uezVitfsEHA.3572@.tk2msftngp13.phx.gbl...
>|||On Thu, 14 Oct 2004 07:12:17 -0700, "Troy Murphy"
<thm92630@.hotmail.com> wrote:
>I have an application that where some of the queries hit the server hard (I
>cannot rewrite the app). Some of the queries take 5 or more minutes to run
>and during this time the CPU is pegged and SQL no longer services other
>queries until the the hog query is complete. I there a way I can throttle
a
>connection - like not allow a connection to use more than 25% of the CPU?
No.
Better would be a way to assign a lower priority to the offending
connection/query.
But the thinking is that this kind of thing is very dangerous, as the
throttled thread may be holding resources that block other users.
Unfortunately, you can get conditions where the range of the heavy
query is logically limited, it just soaks up all the database
capability.
If your server is otherwise large enough memory-wise and your disk
usage is appropriate (log files on separate disk from data files) and
you have two or more processors, then short of recoding, which you say
you can't do, that's it.
J.|||Thanks to all for the feedback. The SUM() is just an example and the
application is a 3rd party solution so changing the source would be
difficult to say the least.
Still, the behavior seems to be that only a single query will run at a time
and queries from other connections are on hold while the query is running.
I played with the MAXDOP as well as the CURSOR THRESHOLD setting with out
any changes in behavior (reboot of server after each change).
I can reproduce this behavior using query analyzer from 2 workstations where
WS1 will run a long query and while that is running WS2 will run a very
quick query. While WS1 is running WS2 has the world turning, but as soon as
WS1 completes, the result set from WS2 is served.
The server configuration is a dual processor with hyper-threading and 2 gb
RAM. Unfortunately the drive is un-striped IDE. Windows 2003 and SQL
Server 2000 Enterprise Edition. Running Performance Monitor on Processor
Time and SqlServer Cache Pages does not indicate an overload.
Troy
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:O9GpoIksEHA.2320@.TK2MSFTNGP12.phx.gbl...
> Several minutes for a simple SUM() is pretty slow. I suspect you lack
> enough memory and have the wrong disk configurations. Any chance you can
> create a covering index for that SUM()? As for the updates are you trying
> to do all of them in one transaction? If so you may want to break them up
> into smaller batches. It's pretty easy to lop and update say 10,000 rows
> at a time and will usually be faster and certainly less intrusive.
>
> --
> Andrew J. Kelly SQL MVP
>
> "Troy Murphy" <thm92630@.hotmail.com> wrote in message
> news:Onn12wisEHA.2780@.TK2MSFTNGP09.phx.gbl...
>|||Troy,
What you are describing sounds more like a blocking issue and not so much
CPU. If you set MAXDOP to 1 and the query were to use all of the first
processor the others would still be available. Have you looked at sp_who2
to see if any blocking is going on while this is happening? It also still
can be that the tables are poorly indexed. Even though the app is 3rd party
you can most likely add indexes.
Andrew J. Kelly SQL MVP
"Troy Murphy" <thm92630@.hotmail.com> wrote in message
news:ufFY8ttsEHA.3556@.TK2MSFTNGP10.phx.gbl...
> Thanks to all for the feedback. The SUM() is just an example and the
> application is a 3rd party solution so changing the source would be
> difficult to say the least.
> Still, the behavior seems to be that only a single query will run at a
> time and queries from other connections are on hold while the query is
> running. I played with the MAXDOP as well as the CURSOR THRESHOLD setting
> with out any changes in behavior (reboot of server after each change).
> I can reproduce this behavior using query analyzer from 2 workstations
> where WS1 will run a long query and while that is running WS2 will run a
> very quick query. While WS1 is running WS2 has the world turning, but as
> soon as WS1 completes, the result set from WS2 is served.
> The server configuration is a dual processor with hyper-threading and 2 gb
> RAM. Unfortunately the drive is un-striped IDE. Windows 2003 and SQL
> Server 2000 Enterprise Edition. Running Performance Monitor on Processor
> Time and SqlServer Cache Pages does not indicate an overload.
> Troy
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:O9GpoIksEHA.2320@.TK2MSFTNGP12.phx.gbl...
>|||Ok, I reproduced the scenerio described below and ran SP_WHO (I was actually
expecting this to wait for the query to complete, but it ran as expected).
Anyway, the user who was running the long query showed a status of 'Ready to
Run' while executing and the process that was waiting in limbo showed as
'Sleeping'. All values in the BLK field were 0 and each had a unique SPID.
I had reset all the Configuration settings back to default. We exported the
database to another server (single processor, 1 gb Ram, SCSI) and that
server did not seem to have this problem in that the small queries ran as
expected while the large query was still running. HELP!!!!
Troy
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uPahbQusEHA.2072@.tk2msftngp13.phx.gbl...
> Troy,
> What you are describing sounds more like a blocking issue and not so much
> CPU. If you set MAXDOP to 1 and the query were to use all of the first
> processor the others would still be available. Have you looked at sp_who2
> to see if any blocking is going on while this is happening? It also still
> can be that the tables are poorly indexed. Even though the app is 3rd
> party you can most likely add indexes.
> --
> Andrew J. Kelly SQL MVP
>
> "Troy Murphy" <thm92630@.hotmail.com> wrote in message
> news:ufFY8ttsEHA.3556@.TK2MSFTNGP10.phx.gbl...
>|||What about disk queues? Is the first process creating a massive disk queue?
sp_who would not be affected as much by that. What about wait types? Try
running DBCC SQLPERF(Waitstats) or select * from sysprocesses and see what
the wait types and times are for the 2 spids.
Andrew J. Kelly SQL MVP
"Troy Murphy" <thm92630@.hotmail.com> wrote in message
news:efIBI7usEHA.3788@.TK2MSFTNGP15.phx.gbl...
> Ok, I reproduced the scenerio described below and ran SP_WHO (I was
> actually expecting this to wait for the query to complete, but it ran as
> expected). Anyway, the user who was running the long query showed a status
> of 'Ready to Run' while executing and the process that was waiting in
> limbo showed as 'Sleeping'. All values in the BLK field were 0 and each
> had a unique SPID. I had reset all the Configuration settings back to
> default. We exported the database to another server (single processor, 1
> gb Ram, SCSI) and that server did not seem to have this problem in that
> the small queries ran as expected while the large query was still running.
> HELP!!!!
> Troy
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uPahbQusEHA.2072@.tk2msftngp13.phx.gbl...
>
cannot rewrite the app). Some of the queries take 5 or more minutes to run
and during this time the CPU is pegged and SQL no longer services other
queries until the the hog query is complete. I there a way I can throttle a
connection - like not allow a connection to use more than 25% of the CPU?
Thanks,
TroyTry setting the MAXDOP to something less than the total number of
processors. But it sounds like you probably need to just tune your query.
Andrew J. Kelly SQL MVP
"Troy Murphy" <thm92630@.hotmail.com> wrote in message
news:O%23em8ifsEHA.224@.TK2MSFTNGP15.phx.gbl...
>I have an application that where some of the queries hit the server hard (I
>cannot rewrite the app). Some of the queries take 5 or more minutes to run
>and during this time the CPU is pegged and SQL no longer services other
>queries until the the hog query is complete. I there a way I can throttle
>a connection - like not allow a connection to use more than 25% of the CPU?
> Thanks,
> Troy
>|||Troy Murphy wrote:
> I have an application that where some of the queries hit the server
> hard (I cannot rewrite the app). Some of the queries take 5 or more
> minutes to run and during this time the CPU is pegged and SQL no
> longer services other queries until the the hog query is complete. I
> there a way I can throttle a connection - like not allow a connection
> to use more than 25% of the CPU?
> Thanks,
> Troy
Maybe you could add appropriate indexes to the database to help the
query run more efficiently without worrying about touching the
application.
David Gugick
Imceda Software
www.imceda.com|||Thanks, I will look into that. The queries are fairly optimized, but we
have 10's of millions of records. Even a simple select sum() takes several
minutes. However the main culprit is a morning update against a few hundred
thousand records that keeps the software unusable during that time.
Troy
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uezVitfsEHA.3572@.tk2msftngp13.phx.gbl...
> Try setting the MAXDOP to something less than the total number of
> processors. But it sounds like you probably need to just tune your
> query.
> --
> Andrew J. Kelly SQL MVP
>
> "Troy Murphy" <thm92630@.hotmail.com> wrote in message
> news:O%23em8ifsEHA.224@.TK2MSFTNGP15.phx.gbl...
>|||Several minutes for a simple SUM() is pretty slow. I suspect you lack
enough memory and have the wrong disk configurations. Any chance you can
create a covering index for that SUM()? As for the updates are you trying
to do all of them in one transaction? If so you may want to break them up
into smaller batches. It's pretty easy to lop and update say 10,000 rows at
a time and will usually be faster and certainly less intrusive.
Andrew J. Kelly SQL MVP
"Troy Murphy" <thm92630@.hotmail.com> wrote in message
news:Onn12wisEHA.2780@.TK2MSFTNGP09.phx.gbl...
> Thanks, I will look into that. The queries are fairly optimized, but we
> have 10's of millions of records. Even a simple select sum() takes
> several minutes. However the main culprit is a morning update against a
> few hundred thousand records that keeps the software unusable during that
> time.
> Troy
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uezVitfsEHA.3572@.tk2msftngp13.phx.gbl...
>|||On Thu, 14 Oct 2004 07:12:17 -0700, "Troy Murphy"
<thm92630@.hotmail.com> wrote:
>I have an application that where some of the queries hit the server hard (I
>cannot rewrite the app). Some of the queries take 5 or more minutes to run
>and during this time the CPU is pegged and SQL no longer services other
>queries until the the hog query is complete. I there a way I can throttle
a
>connection - like not allow a connection to use more than 25% of the CPU?
No.
Better would be a way to assign a lower priority to the offending
connection/query.
But the thinking is that this kind of thing is very dangerous, as the
throttled thread may be holding resources that block other users.
Unfortunately, you can get conditions where the range of the heavy
query is logically limited, it just soaks up all the database
capability.
If your server is otherwise large enough memory-wise and your disk
usage is appropriate (log files on separate disk from data files) and
you have two or more processors, then short of recoding, which you say
you can't do, that's it.
J.|||Thanks to all for the feedback. The SUM() is just an example and the
application is a 3rd party solution so changing the source would be
difficult to say the least.
Still, the behavior seems to be that only a single query will run at a time
and queries from other connections are on hold while the query is running.
I played with the MAXDOP as well as the CURSOR THRESHOLD setting with out
any changes in behavior (reboot of server after each change).
I can reproduce this behavior using query analyzer from 2 workstations where
WS1 will run a long query and while that is running WS2 will run a very
quick query. While WS1 is running WS2 has the world turning, but as soon as
WS1 completes, the result set from WS2 is served.
The server configuration is a dual processor with hyper-threading and 2 gb
RAM. Unfortunately the drive is un-striped IDE. Windows 2003 and SQL
Server 2000 Enterprise Edition. Running Performance Monitor on Processor
Time and SqlServer Cache Pages does not indicate an overload.
Troy
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:O9GpoIksEHA.2320@.TK2MSFTNGP12.phx.gbl...
> Several minutes for a simple SUM() is pretty slow. I suspect you lack
> enough memory and have the wrong disk configurations. Any chance you can
> create a covering index for that SUM()? As for the updates are you trying
> to do all of them in one transaction? If so you may want to break them up
> into smaller batches. It's pretty easy to lop and update say 10,000 rows
> at a time and will usually be faster and certainly less intrusive.
>
> --
> Andrew J. Kelly SQL MVP
>
> "Troy Murphy" <thm92630@.hotmail.com> wrote in message
> news:Onn12wisEHA.2780@.TK2MSFTNGP09.phx.gbl...
>|||Troy,
What you are describing sounds more like a blocking issue and not so much
CPU. If you set MAXDOP to 1 and the query were to use all of the first
processor the others would still be available. Have you looked at sp_who2
to see if any blocking is going on while this is happening? It also still
can be that the tables are poorly indexed. Even though the app is 3rd party
you can most likely add indexes.
Andrew J. Kelly SQL MVP
"Troy Murphy" <thm92630@.hotmail.com> wrote in message
news:ufFY8ttsEHA.3556@.TK2MSFTNGP10.phx.gbl...
> Thanks to all for the feedback. The SUM() is just an example and the
> application is a 3rd party solution so changing the source would be
> difficult to say the least.
> Still, the behavior seems to be that only a single query will run at a
> time and queries from other connections are on hold while the query is
> running. I played with the MAXDOP as well as the CURSOR THRESHOLD setting
> with out any changes in behavior (reboot of server after each change).
> I can reproduce this behavior using query analyzer from 2 workstations
> where WS1 will run a long query and while that is running WS2 will run a
> very quick query. While WS1 is running WS2 has the world turning, but as
> soon as WS1 completes, the result set from WS2 is served.
> The server configuration is a dual processor with hyper-threading and 2 gb
> RAM. Unfortunately the drive is un-striped IDE. Windows 2003 and SQL
> Server 2000 Enterprise Edition. Running Performance Monitor on Processor
> Time and SqlServer Cache Pages does not indicate an overload.
> Troy
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:O9GpoIksEHA.2320@.TK2MSFTNGP12.phx.gbl...
>|||Ok, I reproduced the scenerio described below and ran SP_WHO (I was actually
expecting this to wait for the query to complete, but it ran as expected).
Anyway, the user who was running the long query showed a status of 'Ready to
Run' while executing and the process that was waiting in limbo showed as
'Sleeping'. All values in the BLK field were 0 and each had a unique SPID.
I had reset all the Configuration settings back to default. We exported the
database to another server (single processor, 1 gb Ram, SCSI) and that
server did not seem to have this problem in that the small queries ran as
expected while the large query was still running. HELP!!!!
Troy
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uPahbQusEHA.2072@.tk2msftngp13.phx.gbl...
> Troy,
> What you are describing sounds more like a blocking issue and not so much
> CPU. If you set MAXDOP to 1 and the query were to use all of the first
> processor the others would still be available. Have you looked at sp_who2
> to see if any blocking is going on while this is happening? It also still
> can be that the tables are poorly indexed. Even though the app is 3rd
> party you can most likely add indexes.
> --
> Andrew J. Kelly SQL MVP
>
> "Troy Murphy" <thm92630@.hotmail.com> wrote in message
> news:ufFY8ttsEHA.3556@.TK2MSFTNGP10.phx.gbl...
>|||What about disk queues? Is the first process creating a massive disk queue?
sp_who would not be affected as much by that. What about wait types? Try
running DBCC SQLPERF(Waitstats) or select * from sysprocesses and see what
the wait types and times are for the 2 spids.
Andrew J. Kelly SQL MVP
"Troy Murphy" <thm92630@.hotmail.com> wrote in message
news:efIBI7usEHA.3788@.TK2MSFTNGP15.phx.gbl...
> Ok, I reproduced the scenerio described below and ran SP_WHO (I was
> actually expecting this to wait for the query to complete, but it ran as
> expected). Anyway, the user who was running the long query showed a status
> of 'Ready to Run' while executing and the process that was waiting in
> limbo showed as 'Sleeping'. All values in the BLK field were 0 and each
> had a unique SPID. I had reset all the Configuration settings back to
> default. We exported the database to another server (single processor, 1
> gb Ram, SCSI) and that server did not seem to have this problem in that
> the small queries ran as expected while the large query was still running.
> HELP!!!!
> Troy
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uPahbQusEHA.2072@.tk2msftngp13.phx.gbl...
>
'Connection terminated' errors in C/S application...
Client workstations getting 'conection terminated' message through the client
app.
Happens sporadically and we can't seem to make it happen. Sometimes the
users get error messages from SQL Srvr and sometimes just the 'con term'
message. I've checked the switch, the wire, the patch level, the clients
(2000, xp pro, 98se), drivers, apps, nics, power, phases of the moon...
Have an open case w/microsoft for over a month! No help... Updated mdac,
jet, SQL, keep alives, max ports, wait time... Using named pipes and tcp.
Have done limited net monitoring (through MS worthless net monitor prog,
doing ethereal next) and can't come up with any definitive 'ah hah' and alas
a fix.
Anyone with an opinion, possible fix? This has gotten VERY old. Some days,
up to a week, no errs. Other days up to 5 or 6 an hour! YIKES!
Clients are 98se, 2k, XP Pro.
Server 2003 SBS SP1 throughout
SQL Server 2000 SP3a
Hi
I assume there is no information in the Windows Event log or SQL Server log
to help you?
Have you tried running the client on the server (with and without shared
memory)?
You may want to try setting up a separate (small) test environment to see if
it still happens with other hardware on a standalone network.
John
"drkc" wrote:
> Client workstations getting 'conection terminated' message through the client
> app.
> Happens sporadically and we can't seem to make it happen. Sometimes the
> users get error messages from SQL Srvr and sometimes just the 'con term'
> message. I've checked the switch, the wire, the patch level, the clients
> (2000, xp pro, 98se), drivers, apps, nics, power, phases of the moon...
> Have an open case w/microsoft for over a month! No help... Updated mdac,
> jet, SQL, keep alives, max ports, wait time... Using named pipes and tcp.
> Have done limited net monitoring (through MS worthless net monitor prog,
> doing ethereal next) and can't come up with any definitive 'ah hah' and alas
> a fix.
> Anyone with an opinion, possible fix? This has gotten VERY old. Some days,
> up to a week, no errs. Other days up to 5 or 6 an hour! YIKES!
> Clients are 98se, 2k, XP Pro.
> Server 2003 SBS SP1 throughout
> SQL Server 2000 SP3a
>
app.
Happens sporadically and we can't seem to make it happen. Sometimes the
users get error messages from SQL Srvr and sometimes just the 'con term'
message. I've checked the switch, the wire, the patch level, the clients
(2000, xp pro, 98se), drivers, apps, nics, power, phases of the moon...
Have an open case w/microsoft for over a month! No help... Updated mdac,
jet, SQL, keep alives, max ports, wait time... Using named pipes and tcp.
Have done limited net monitoring (through MS worthless net monitor prog,
doing ethereal next) and can't come up with any definitive 'ah hah' and alas
a fix.
Anyone with an opinion, possible fix? This has gotten VERY old. Some days,
up to a week, no errs. Other days up to 5 or 6 an hour! YIKES!
Clients are 98se, 2k, XP Pro.
Server 2003 SBS SP1 throughout
SQL Server 2000 SP3a
Hi
I assume there is no information in the Windows Event log or SQL Server log
to help you?
Have you tried running the client on the server (with and without shared
memory)?
You may want to try setting up a separate (small) test environment to see if
it still happens with other hardware on a standalone network.
John
"drkc" wrote:
> Client workstations getting 'conection terminated' message through the client
> app.
> Happens sporadically and we can't seem to make it happen. Sometimes the
> users get error messages from SQL Srvr and sometimes just the 'con term'
> message. I've checked the switch, the wire, the patch level, the clients
> (2000, xp pro, 98se), drivers, apps, nics, power, phases of the moon...
> Have an open case w/microsoft for over a month! No help... Updated mdac,
> jet, SQL, keep alives, max ports, wait time... Using named pipes and tcp.
> Have done limited net monitoring (through MS worthless net monitor prog,
> doing ethereal next) and can't come up with any definitive 'ah hah' and alas
> a fix.
> Anyone with an opinion, possible fix? This has gotten VERY old. Some days,
> up to a week, no errs. Other days up to 5 or 6 an hour! YIKES!
> Clients are 98se, 2k, XP Pro.
> Server 2003 SBS SP1 throughout
> SQL Server 2000 SP3a
>
Subscribe to:
Posts (Atom)