We have sql server 2000 ,we r dong transactional replication,out distribution
agent latency is around 1000000 from last 2 days before that,it was around
4000,please suggest us the solution immediately.
Thank you.
Assuming there hasn't been a massive increase in replicated commands, have a
look for blocking issues on the subscriber when the distribution agent runs
(sp_who2).
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
|||Have you verified your distribution agent is running? You also need to make
sure it is not stuck in the initializing state.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Rupesh Mondal" <RupeshMondal@.discussions.microsoft.com> wrote in message
news:80D4C0B5-0D51-438F-8674-8E8FE9DEFAE9@.microsoft.com...
> We have sql server 2000 ,we r dong transactional replication,out
> distribution
> agent latency is around 1000000 from last 2 days before that,it was around
> 4000,please suggest us the solution immediately.
> Thank you.
|||Our distribution agent is running properly Actually there are another
subscriber where the latency is 4000,so what will be the possible reason of
increased latencyin the other server.plese reply imediately.
"Hilary Cotter" wrote:
> Have you verified your distribution agent is running? You also need to make
> sure it is not stuck in the initializing state.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Rupesh Mondal" <RupeshMondal@.discussions.microsoft.com> wrote in message
> news:80D4C0B5-0D51-438F-8674-8E8FE9DEFAE9@.microsoft.com...
>
>
|||Sir Thank you for your reply
"Paul Ibison" wrote:
> Assuming there hasn't been a massive increase in replicated commands, have a
> look for blocking issues on the subscriber when the distribution agent runs
> (sp_who2).
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
>
>
Friday, March 30, 2012
Increased performance using 2 servers and a SAN
Hi
I need to increase performance of our SQL enviroment and i dont really want
to put one BIG server in.
What would be the best way of load balancing 1 SQL database over 2 servers?
My first thought was 2 servers clustered with a SAN and use SQL replication
between two differant Instances, is this possible or is there a better way?
Thanks
Andy
SQL 2000 can't not be load balanced for a single database. A database can
only be run from one server at a time. Sorry.
Cheers,
Rod
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering
http://www.msmvps.com/clustering - Blog
"Andrew Gilbert" <AndrewGilbert@.discussions.microsoft.com> wrote in message
news:7CCD89F6-9AAA-4F49-A77B-095B809384AC@.microsoft.com...
> Hi
> I need to increase performance of our SQL enviroment and i dont really
> want
> to put one BIG server in.
> What would be the best way of load balancing 1 SQL database over 2
> servers?
> My first thought was 2 servers clustered with a SAN and use SQL
> replication
> between two differant Instances, is this possible or is there a better
> way?
> Thanks
> Andy
|||Hi
Using replication is possible, but your application has to understand that
records can be inserted in different databases, so if any identity columns
exist, expect issues.
SQL Server can not be directly load balanced. Don't forget, a SAN may not
make IO quicker, it might actually be a lot slower (compared to local
storage).
What is your current hardware setup, DB sizes, transactions per minute and
what performance are you expecting?
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/
"Andrew Gilbert" <AndrewGilbert@.discussions.microsoft.com> wrote in message
news:7CCD89F6-9AAA-4F49-A77B-095B809384AC@.microsoft.com...
> Hi
> I need to increase performance of our SQL enviroment and i dont really
want
> to put one BIG server in.
> What would be the best way of load balancing 1 SQL database over 2
servers?
> My first thought was 2 servers clustered with a SAN and use SQL
replication
> between two differant Instances, is this possible or is there a better
way?
> Thanks
> Andy
|||Hi
We currently have a HP DL380 with 2x Zeaon 2.8Ghz processors and 2GB RAM
we have 2 DB and all Logs on one set of RAID 5 10k DIsks and the Main DB on
its own Mirrored set of 10K Disks.
We are experiancing High Disk Queue lengths on the first set of Disk, so we
was looking to use a HP MSA500 SAN and split all DB and Logs onto seperate
Mirrored Disks?
The Main DB is 500mb and the other DB which is used for referance info is
1.3GB
We have about 180 users using the system and we seem to be getting slow
query times in peak load, i.e 400 - 1000ms query times.
If you have any suggestion then please let me know, would a MSA500 be a bad
idea?
Andy
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> Using replication is possible, but your application has to understand that
> records can be inserted in different databases, so if any identity columns
> exist, expect issues.
> SQL Server can not be directly load balanced. Don't forget, a SAN may not
> make IO quicker, it might actually be a lot slower (compared to local
> storage).
> What is your current hardware setup, DB sizes, transactions per minute and
> what performance are you expecting?
> 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/
> "Andrew Gilbert" <AndrewGilbert@.discussions.microsoft.com> wrote in message
> news:7CCD89F6-9AAA-4F49-A77B-095B809384AC@.microsoft.com...
> want
> servers?
> replication
> way?
>
>
|||While I am unfamiliar with the specific hardware you are considering, the
general idea of separate physical devices for Data and Log is a fundamental
key for increasing performance. Avoid RAID-5 even in a SAN for transaction
log devices. Larger write cache in a SAN device will lead to increased SQL
performance. Check and see if the high queue lengths are for read or for
write operations. If they are for read requests, increasing the RAM (and
moving to Enterprise SQL) may result in more performance improvements than
you would get through disk subsystem changes. Of course, the ideas are not
mutually exclusive.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Andrew Gilbert" <AndrewGilbert@.discussions.microsoft.com> wrote in message
news:7B0503A3-DB49-4E1B-8D12-E2542575690B@.microsoft.com...
> Hi
> We currently have a HP DL380 with 2x Zeaon 2.8Ghz processors and 2GB RAM
> we have 2 DB and all Logs on one set of RAID 5 10k DIsks and the Main DB
on
> its own Mirrored set of 10K Disks.
> We are experiancing High Disk Queue lengths on the first set of Disk, so
we
> was looking to use a HP MSA500 SAN and split all DB and Logs onto seperate
> Mirrored Disks?
> The Main DB is 500mb and the other DB which is used for referance info is
> 1.3GB
> We have about 180 users using the system and we seem to be getting slow
> query times in peak load, i.e 400 - 1000ms query times.
> If you have any suggestion then please let me know, would a MSA500 be a
bad[vbcol=seagreen]
> idea?
> Andy
> "Mike Epprecht (SQL MVP)" wrote:
that[vbcol=seagreen]
columns[vbcol=seagreen]
not[vbcol=seagreen]
and[vbcol=seagreen]
message[vbcol=seagreen]
sql
I need to increase performance of our SQL enviroment and i dont really want
to put one BIG server in.
What would be the best way of load balancing 1 SQL database over 2 servers?
My first thought was 2 servers clustered with a SAN and use SQL replication
between two differant Instances, is this possible or is there a better way?
Thanks
Andy
SQL 2000 can't not be load balanced for a single database. A database can
only be run from one server at a time. Sorry.
Cheers,
Rod
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering
http://www.msmvps.com/clustering - Blog
"Andrew Gilbert" <AndrewGilbert@.discussions.microsoft.com> wrote in message
news:7CCD89F6-9AAA-4F49-A77B-095B809384AC@.microsoft.com...
> Hi
> I need to increase performance of our SQL enviroment and i dont really
> want
> to put one BIG server in.
> What would be the best way of load balancing 1 SQL database over 2
> servers?
> My first thought was 2 servers clustered with a SAN and use SQL
> replication
> between two differant Instances, is this possible or is there a better
> way?
> Thanks
> Andy
|||Hi
Using replication is possible, but your application has to understand that
records can be inserted in different databases, so if any identity columns
exist, expect issues.
SQL Server can not be directly load balanced. Don't forget, a SAN may not
make IO quicker, it might actually be a lot slower (compared to local
storage).
What is your current hardware setup, DB sizes, transactions per minute and
what performance are you expecting?
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/
"Andrew Gilbert" <AndrewGilbert@.discussions.microsoft.com> wrote in message
news:7CCD89F6-9AAA-4F49-A77B-095B809384AC@.microsoft.com...
> Hi
> I need to increase performance of our SQL enviroment and i dont really
want
> to put one BIG server in.
> What would be the best way of load balancing 1 SQL database over 2
servers?
> My first thought was 2 servers clustered with a SAN and use SQL
replication
> between two differant Instances, is this possible or is there a better
way?
> Thanks
> Andy
|||Hi
We currently have a HP DL380 with 2x Zeaon 2.8Ghz processors and 2GB RAM
we have 2 DB and all Logs on one set of RAID 5 10k DIsks and the Main DB on
its own Mirrored set of 10K Disks.
We are experiancing High Disk Queue lengths on the first set of Disk, so we
was looking to use a HP MSA500 SAN and split all DB and Logs onto seperate
Mirrored Disks?
The Main DB is 500mb and the other DB which is used for referance info is
1.3GB
We have about 180 users using the system and we seem to be getting slow
query times in peak load, i.e 400 - 1000ms query times.
If you have any suggestion then please let me know, would a MSA500 be a bad
idea?
Andy
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> Using replication is possible, but your application has to understand that
> records can be inserted in different databases, so if any identity columns
> exist, expect issues.
> SQL Server can not be directly load balanced. Don't forget, a SAN may not
> make IO quicker, it might actually be a lot slower (compared to local
> storage).
> What is your current hardware setup, DB sizes, transactions per minute and
> what performance are you expecting?
> 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/
> "Andrew Gilbert" <AndrewGilbert@.discussions.microsoft.com> wrote in message
> news:7CCD89F6-9AAA-4F49-A77B-095B809384AC@.microsoft.com...
> want
> servers?
> replication
> way?
>
>
|||While I am unfamiliar with the specific hardware you are considering, the
general idea of separate physical devices for Data and Log is a fundamental
key for increasing performance. Avoid RAID-5 even in a SAN for transaction
log devices. Larger write cache in a SAN device will lead to increased SQL
performance. Check and see if the high queue lengths are for read or for
write operations. If they are for read requests, increasing the RAM (and
moving to Enterprise SQL) may result in more performance improvements than
you would get through disk subsystem changes. Of course, the ideas are not
mutually exclusive.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Andrew Gilbert" <AndrewGilbert@.discussions.microsoft.com> wrote in message
news:7B0503A3-DB49-4E1B-8D12-E2542575690B@.microsoft.com...
> Hi
> We currently have a HP DL380 with 2x Zeaon 2.8Ghz processors and 2GB RAM
> we have 2 DB and all Logs on one set of RAID 5 10k DIsks and the Main DB
on
> its own Mirrored set of 10K Disks.
> We are experiancing High Disk Queue lengths on the first set of Disk, so
we
> was looking to use a HP MSA500 SAN and split all DB and Logs onto seperate
> Mirrored Disks?
> The Main DB is 500mb and the other DB which is used for referance info is
> 1.3GB
> We have about 180 users using the system and we seem to be getting slow
> query times in peak load, i.e 400 - 1000ms query times.
> If you have any suggestion then please let me know, would a MSA500 be a
bad[vbcol=seagreen]
> idea?
> Andy
> "Mike Epprecht (SQL MVP)" wrote:
that[vbcol=seagreen]
columns[vbcol=seagreen]
not[vbcol=seagreen]
and[vbcol=seagreen]
message[vbcol=seagreen]
sql
Increased Datetime Precision
Any ideas on how to get greater than the 3.33 ms precision currently
available in the DataTime type?
Thanks,
Randy LRandy,
What are your requirements? What do you need a better precision for?|||What are you trying to do? DateTime has 1ms increments. DateTime.Now is
something like +- 50ms accurate.
William Stacey [MVP]
"Randy L." <randy.luchsinger@.community.nospam> wrote in message
news:uTZQiIXPGHA.1088@.tk2msftngp13.phx.gbl...
| Any ideas on how to get greater than the 3.33 ms precision currently
| available in the DataTime type?
|
| Thanks,
| Randy L
|
||||Store the milliseconds separately, in a SMALLINT column perhaps. To balance
the increased storage you could use SMALLDATETIME and then the number of
seconds and milliseconds separately.
"Randy L." <randy.luchsinger@.community.nospam> wrote in message
news:uTZQiIXPGHA.1088@.tk2msftngp13.phx.gbl...
> Any ideas on how to get greater than the 3.33 ms precision currently
> available in the DataTime type?
> Thanks,
> Randy L
>|||The precision you mention is the maximum precision of the datetime data
type.
If you need to store dates with times of greater precision in the time
portion, then you will have to program a solution yourself. One approach
is to use a smalldatetime (provided your dates fall within the range
supported by smalldatetime), and store the second and subsecond portion
of the timestamp in an int column as number of (remaining) milliseconds.
For example, to store the datetime 2006-03-01T22:19:03.999, you could
store it as:
CAST('2006-03-01T22:19:03.999' AS smalldatetime) AS
MostSignificantDateTimePart
,CAST(CAST(SUBSTRING('2006-03-01T22:19:03.999',18,5) AS decimal(5,3)) *
1000 AS int) AS LeastSignificantDateTimePart
HTH,
Gert-Jan
"Randy L." wrote:
> Any ideas on how to get greater than the 3.33 ms precision currently
> available in the DataTime type?
> Thanks,
> Randy L|||The transactions for which we are collecting this data occur very rapidly.
When we then store that time in SQL, the times are then truncated further.
As a result, ordering data by datetime does not always give the true order
of the transaction.
For example:
Trans# Actual Time Stored Time
-- -- --
--
1 2006-03-01 16:00:00.590014 2006-03-01 16:00:00.590
2 2006-03-01 16:00:00.590018 2006-03-01 16:00:00.590
3 2006-03-01 16:00:00.590030 2006-03-01 16:00:00.590
The result in SQL would be 3 times that are exactly the same.
Thanks,
Randy L
"Alexander Kuznetsov" <AK_TIREDOFSPAM@.hotmail.COM> wrote in message
news:1141247397.749764.161370@.u72g2000cwu.googlegroups.com...
> Randy,
> What are your requirements? What do you need a better precision for?
>|||Sorry, wrong ng. Thought I was on c# ng.
William Stacey [MVP]|||Randy,
just from the top of my head: I guess an identity column will give you
the true order of inserts...|||> just from the top of my head: I guess an identity column will give you
> the true order of inserts...
But not necessarily the true order of transaction date/time. The exact
moment in time may be coming from different systems, without synchronized
atomic precision (which is arguably more important in this case than in the
general case), and different system components might have lag times in which
the transactions are recorded (either because they are farther away or on
slower connections, or because they queue up their inserts, etc). Also, if
the table is populated via INSERT...SELECT...FROM there is no guarantee that
the IDENTITY values will be assigned in the 'expected' order.
A|||Alexander,
The transactions we track do not get inserted into SQL as they occur.
They occur across a variety of other processes and are then delivered to the
database. So the order by which they are inserted into the DB has no
significance.
Thanks,
Randy L
"Alexander Kuznetsov" <AK_TIREDOFSPAM@.hotmail.COM> wrote in message
news:1141248868.318368.227020@.i39g2000cwa.googlegroups.com...
> Randy,
> just from the top of my head: I guess an identity column will give you
> the true order of inserts...
>
available in the DataTime type?
Thanks,
Randy LRandy,
What are your requirements? What do you need a better precision for?|||What are you trying to do? DateTime has 1ms increments. DateTime.Now is
something like +- 50ms accurate.
William Stacey [MVP]
"Randy L." <randy.luchsinger@.community.nospam> wrote in message
news:uTZQiIXPGHA.1088@.tk2msftngp13.phx.gbl...
| Any ideas on how to get greater than the 3.33 ms precision currently
| available in the DataTime type?
|
| Thanks,
| Randy L
|
||||Store the milliseconds separately, in a SMALLINT column perhaps. To balance
the increased storage you could use SMALLDATETIME and then the number of
seconds and milliseconds separately.
"Randy L." <randy.luchsinger@.community.nospam> wrote in message
news:uTZQiIXPGHA.1088@.tk2msftngp13.phx.gbl...
> Any ideas on how to get greater than the 3.33 ms precision currently
> available in the DataTime type?
> Thanks,
> Randy L
>|||The precision you mention is the maximum precision of the datetime data
type.
If you need to store dates with times of greater precision in the time
portion, then you will have to program a solution yourself. One approach
is to use a smalldatetime (provided your dates fall within the range
supported by smalldatetime), and store the second and subsecond portion
of the timestamp in an int column as number of (remaining) milliseconds.
For example, to store the datetime 2006-03-01T22:19:03.999, you could
store it as:
CAST('2006-03-01T22:19:03.999' AS smalldatetime) AS
MostSignificantDateTimePart
,CAST(CAST(SUBSTRING('2006-03-01T22:19:03.999',18,5) AS decimal(5,3)) *
1000 AS int) AS LeastSignificantDateTimePart
HTH,
Gert-Jan
"Randy L." wrote:
> Any ideas on how to get greater than the 3.33 ms precision currently
> available in the DataTime type?
> Thanks,
> Randy L|||The transactions for which we are collecting this data occur very rapidly.
When we then store that time in SQL, the times are then truncated further.
As a result, ordering data by datetime does not always give the true order
of the transaction.
For example:
Trans# Actual Time Stored Time
-- -- --
--
1 2006-03-01 16:00:00.590014 2006-03-01 16:00:00.590
2 2006-03-01 16:00:00.590018 2006-03-01 16:00:00.590
3 2006-03-01 16:00:00.590030 2006-03-01 16:00:00.590
The result in SQL would be 3 times that are exactly the same.
Thanks,
Randy L
"Alexander Kuznetsov" <AK_TIREDOFSPAM@.hotmail.COM> wrote in message
news:1141247397.749764.161370@.u72g2000cwu.googlegroups.com...
> Randy,
> What are your requirements? What do you need a better precision for?
>|||Sorry, wrong ng. Thought I was on c# ng.
William Stacey [MVP]|||Randy,
just from the top of my head: I guess an identity column will give you
the true order of inserts...|||> just from the top of my head: I guess an identity column will give you
> the true order of inserts...
But not necessarily the true order of transaction date/time. The exact
moment in time may be coming from different systems, without synchronized
atomic precision (which is arguably more important in this case than in the
general case), and different system components might have lag times in which
the transactions are recorded (either because they are farther away or on
slower connections, or because they queue up their inserts, etc). Also, if
the table is populated via INSERT...SELECT...FROM there is no guarantee that
the IDENTITY values will be assigned in the 'expected' order.
A|||Alexander,
The transactions we track do not get inserted into SQL as they occur.
They occur across a variety of other processes and are then delivered to the
database. So the order by which they are inserted into the DB has no
significance.
Thanks,
Randy L
"Alexander Kuznetsov" <AK_TIREDOFSPAM@.hotmail.COM> wrote in message
news:1141248868.318368.227020@.i39g2000cwa.googlegroups.com...
> Randy,
> just from the top of my head: I guess an identity column will give you
> the true order of inserts...
>
Increased Backup size for unknown reason
Does any one know what could be making the size of a backup get so big in SQL server?
I noticed if you back-up the same database in Enterprise Manager over and over (without making any changes to the database), the size of the backup gets bigger and bigger. To get around this I simply erase the backup and create a new one.
Now I'm experiencing the same kind of problem, different situation. I decided to make very few changes to my database. If anything, I shrunk the size of the tables and stored procedures... Now all of a sudden my database backup is 7 times larger.
What could be increasing the size so much, if I haven't increased the amount of tables or stored procedures?
What is the log file about? Mine is huge? Is there a way to reset it or clear it?
any help would be great.
Thank you,
AlecIf you're sure you're not making any changes are you adding/appending to the backup rather than overwriting?
Increase User connections
Can i increase the max user connections above 32767 ?
Thanks> Can i increase the max user connections above 32767 ?
What on earth for? Can your apps not utilize connection pooling? > 32K
unique users really need to maintain a persistent and active connection
indefinitely? Sounds like an architecture and/or design problem to me.|||Aaron, thats why i asked ;)
Can you please answer the other post on connection pooling for me ?
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eb3cj1Z6HHA.5424@.TK2MSFTNGP02.phx.gbl...
>> Can i increase the max user connections above 32767 ?
>
> What on earth for? Can your apps not utilize connection pooling? > 32K
> unique users really need to maintain a persistent and active connection
> indefinitely? Sounds like an architecture and/or design problem to me.
>|||> Can you please answer the other post on connection pooling for me ?
I really don't know how to answer the question. It sounds like a theory
problem to me, not an actual problem you are experiencing. Personally, I
don't do anything special with connection pooling. It is enabled on the web
application, and unless every single web user establishes a connection to
SQL Server using a different connection string (e.g. with asp session ID
embedded), it should just work.
Thanks> Can i increase the max user connections above 32767 ?
What on earth for? Can your apps not utilize connection pooling? > 32K
unique users really need to maintain a persistent and active connection
indefinitely? Sounds like an architecture and/or design problem to me.|||Aaron, thats why i asked ;)
Can you please answer the other post on connection pooling for me ?
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eb3cj1Z6HHA.5424@.TK2MSFTNGP02.phx.gbl...
>> Can i increase the max user connections above 32767 ?
>
> What on earth for? Can your apps not utilize connection pooling? > 32K
> unique users really need to maintain a persistent and active connection
> indefinitely? Sounds like an architecture and/or design problem to me.
>|||> Can you please answer the other post on connection pooling for me ?
I really don't know how to answer the question. It sounds like a theory
problem to me, not an actual problem you are experiencing. Personally, I
don't do anything special with connection pooling. It is enabled on the web
application, and unless every single web user establishes a connection to
SQL Server using a different connection string (e.g. with asp session ID
embedded), it should just work.
Increase Tooltip display time?
Hi All,
Is it possible to increase the amount of time a tooltip is displayed within report manager?
Regards,
JonNo I don′t think that this is customizable within the report.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de|||
Hello
can u tell me
How to put tool tip in My report.
thanx
ki
|||There is a setting for controls naming tooltip which can define any tooltip displayed if you hover over the specified item.HTH, jens Suessmeyer.
http://www.sqlserver2005.de|||Hi Ki,
In the properties box all objects, in the "General" tab is an option
for "Tooltip". You can put an expression in here to define a
tooltip.
Regards,
Jon
Increase Timeout ?
I am using VB.net to execute a stored procedure... Sometimes I get a
timeout, other times I do not. How can I increase the time out ? Is
this a server setting ?Can;t help you on the timeout...but would it makes sense to look at the
performance of your procedure?
How long is the VB.net timeout currently?
--
Kevin Hill
IC3 North Texas
www.ChristianCycling.com
Please support me in the 2008 MS150:
http://www.ms150.org/dallas/donate/donate.cfm?id=208000
"Rob" <robc1@.yahoo.com> wrote in message
news:dZGdnVKfG_9Qlx3anZ2dnUVZ_s2tnZ2d@.comcast.com...
>I am using VB.net to execute a stored procedure... Sometimes I get a
>timeout, other times I do not. How can I increase the time out ? Is
>this a server setting ?
>|||That doesn't really sound like a "fix" to me at all. You can increase the
timeout to infinity, but users are still going to get bored at some point.
Have you considered looking at the stored procedure itself, and making it
faster, instead of trying to find ways to make its slowness more acceptable?
"Rob" <robc1@.yahoo.com> wrote in message
news:dZGdnVKfG_9Qlx3anZ2dnUVZ_s2tnZ2d@.comcast.com...
>I am using VB.net to execute a stored procedure... Sometimes I get a
>timeout, other times I do not. How can I increase the time out ? Is
>this a server setting ?
>|||Hi,
The stored procedure is very straightforward. I am currently using an under
powered test server. I never get the timeout on the production server...
So how do I increase the timeout ?
Thanks
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uOunlH$TIHA.4440@.TK2MSFTNGP06.phx.gbl...
> That doesn't really sound like a "fix" to me at all. You can increase the
> timeout to infinity, but users are still going to get bored at some point.
> Have you considered looking at the stored procedure itself, and making it
> faster, instead of trying to find ways to make its slowness more
> acceptable?
>
>
> "Rob" <robc1@.yahoo.com> wrote in message
> news:dZGdnVKfG_9Qlx3anZ2dnUVZ_s2tnZ2d@.comcast.com...
>>I am using VB.net to execute a stored procedure... Sometimes I get a
>>timeout, other times I do not. How can I increase the time out ? Is
>>this a server setting ?
>|||Rob
In VB sourcer when you connect to the server there's method called
CommandTimeout
Set Acn = New ADODB.Connection
Acn.CommandTimeout = 0
"Rob" <robc1@.yahoo.com> wrote in message
news:Y6WdnRf7neuvgh3anZ2dnUVZ_s6mnZ2d@.comcast.com...
> Hi,
> The stored procedure is very straightforward. I am currently using an
> under powered test server. I never get the timeout on the production
> server...
> So how do I increase the timeout ?
> Thanks
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
> message news:uOunlH$TIHA.4440@.TK2MSFTNGP06.phx.gbl...
>> That doesn't really sound like a "fix" to me at all. You can increase
>> the timeout to infinity, but users are still going to get bored at some
>> point. Have you considered looking at the stored procedure itself, and
>> making it faster, instead of trying to find ways to make its slowness
>> more acceptable?
>>
>>
>> "Rob" <robc1@.yahoo.com> wrote in message
>> news:dZGdnVKfG_9Qlx3anZ2dnUVZ_s2tnZ2d@.comcast.com...
>>I am using VB.net to execute a stored procedure... Sometimes I get a
>>timeout, other times I do not. How can I increase the time out ? Is
>>this a server setting ?
>>
>|||The default ADO.NET timeout is 30 seconds. This can be changed with the
SqlCommand object CommandTimeout property. For example:
myCommand.CommandTimeout = 60
However, I agree with the others that the timeout should be changed only
after ensuring proper index and query tuning is done. It could be that the
production server simply has enough power to compensate for inefficiencies.
Developing on an underpowered server can provide the motivation to improve
performance ;-)
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Rob" <robc1@.yahoo.com> wrote in message
news:Y6WdnRf7neuvgh3anZ2dnUVZ_s6mnZ2d@.comcast.com...
> Hi,
> The stored procedure is very straightforward. I am currently using an
> under powered test server. I never get the timeout on the production
> server...
> So how do I increase the timeout ?
> Thanks
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
> message news:uOunlH$TIHA.4440@.TK2MSFTNGP06.phx.gbl...
>> That doesn't really sound like a "fix" to me at all. You can increase
>> the timeout to infinity, but users are still going to get bored at some
>> point. Have you considered looking at the stored procedure itself, and
>> making it faster, instead of trying to find ways to make its slowness
>> more acceptable?
>>
>>
>> "Rob" <robc1@.yahoo.com> wrote in message
>> news:dZGdnVKfG_9Qlx3anZ2dnUVZ_s2tnZ2d@.comcast.com...
>>I am using VB.net to execute a stored procedure... Sometimes I get a
>>timeout, other times I do not. How can I increase the time out ? Is
>>this a server setting ?
>>
>|||"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:701FF8B7-52A8-4431-98EB-D4F2E5F1C20F@.microsoft.com...
> The default ADO.NET timeout is 30 seconds. This can be changed with the
> SqlCommand object CommandTimeout property. For example:
> myCommand.CommandTimeout = 60
> However, I agree with the others that the timeout should be changed only
> after ensuring proper index and query tuning is done. It could be that
> the production server simply has enough power to compensate for
> inefficiencies. Developing on an underpowered server can provide the
> motivation to improve performance ;-)
I just had to learn this one where I am. A query that basically returns the
DB as an XML feed (don't ask) now takes longer than 30 seconds. And even if
we improve it again, it's just going to get worse, again. Arrgh...
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
>
--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||Thanks !
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:701FF8B7-52A8-4431-98EB-D4F2E5F1C20F@.microsoft.com...
> The default ADO.NET timeout is 30 seconds. This can be changed with the
> SqlCommand object CommandTimeout property. For example:
> myCommand.CommandTimeout = 60
> However, I agree with the others that the timeout should be changed only
> after ensuring proper index and query tuning is done. It could be that
> the production server simply has enough power to compensate for
> inefficiencies. Developing on an underpowered server can provide the
> motivation to improve performance ;-)
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Rob" <robc1@.yahoo.com> wrote in message
> news:Y6WdnRf7neuvgh3anZ2dnUVZ_s6mnZ2d@.comcast.com...
>> Hi,
>> The stored procedure is very straightforward. I am currently using an
>> under powered test server. I never get the timeout on the production
>> server...
>> So how do I increase the timeout ?
>> Thanks
>> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
>> message news:uOunlH$TIHA.4440@.TK2MSFTNGP06.phx.gbl...
>> That doesn't really sound like a "fix" to me at all. You can increase
>> the timeout to infinity, but users are still going to get bored at some
>> point. Have you considered looking at the stored procedure itself, and
>> making it faster, instead of trying to find ways to make its slowness
>> more acceptable?
>>
>>
>> "Rob" <robc1@.yahoo.com> wrote in message
>> news:dZGdnVKfG_9Qlx3anZ2dnUVZ_s2tnZ2d@.comcast.com...
>>I am using VB.net to execute a stored procedure... Sometimes I get a
>>timeout, other times I do not. How can I increase the time out ? Is
>>this a server setting ?
>>
>>
>sql
timeout, other times I do not. How can I increase the time out ? Is
this a server setting ?Can;t help you on the timeout...but would it makes sense to look at the
performance of your procedure?
How long is the VB.net timeout currently?
--
Kevin Hill
IC3 North Texas
www.ChristianCycling.com
Please support me in the 2008 MS150:
http://www.ms150.org/dallas/donate/donate.cfm?id=208000
"Rob" <robc1@.yahoo.com> wrote in message
news:dZGdnVKfG_9Qlx3anZ2dnUVZ_s2tnZ2d@.comcast.com...
>I am using VB.net to execute a stored procedure... Sometimes I get a
>timeout, other times I do not. How can I increase the time out ? Is
>this a server setting ?
>|||That doesn't really sound like a "fix" to me at all. You can increase the
timeout to infinity, but users are still going to get bored at some point.
Have you considered looking at the stored procedure itself, and making it
faster, instead of trying to find ways to make its slowness more acceptable?
"Rob" <robc1@.yahoo.com> wrote in message
news:dZGdnVKfG_9Qlx3anZ2dnUVZ_s2tnZ2d@.comcast.com...
>I am using VB.net to execute a stored procedure... Sometimes I get a
>timeout, other times I do not. How can I increase the time out ? Is
>this a server setting ?
>|||Hi,
The stored procedure is very straightforward. I am currently using an under
powered test server. I never get the timeout on the production server...
So how do I increase the timeout ?
Thanks
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uOunlH$TIHA.4440@.TK2MSFTNGP06.phx.gbl...
> That doesn't really sound like a "fix" to me at all. You can increase the
> timeout to infinity, but users are still going to get bored at some point.
> Have you considered looking at the stored procedure itself, and making it
> faster, instead of trying to find ways to make its slowness more
> acceptable?
>
>
> "Rob" <robc1@.yahoo.com> wrote in message
> news:dZGdnVKfG_9Qlx3anZ2dnUVZ_s2tnZ2d@.comcast.com...
>>I am using VB.net to execute a stored procedure... Sometimes I get a
>>timeout, other times I do not. How can I increase the time out ? Is
>>this a server setting ?
>|||Rob
In VB sourcer when you connect to the server there's method called
CommandTimeout
Set Acn = New ADODB.Connection
Acn.CommandTimeout = 0
"Rob" <robc1@.yahoo.com> wrote in message
news:Y6WdnRf7neuvgh3anZ2dnUVZ_s6mnZ2d@.comcast.com...
> Hi,
> The stored procedure is very straightforward. I am currently using an
> under powered test server. I never get the timeout on the production
> server...
> So how do I increase the timeout ?
> Thanks
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
> message news:uOunlH$TIHA.4440@.TK2MSFTNGP06.phx.gbl...
>> That doesn't really sound like a "fix" to me at all. You can increase
>> the timeout to infinity, but users are still going to get bored at some
>> point. Have you considered looking at the stored procedure itself, and
>> making it faster, instead of trying to find ways to make its slowness
>> more acceptable?
>>
>>
>> "Rob" <robc1@.yahoo.com> wrote in message
>> news:dZGdnVKfG_9Qlx3anZ2dnUVZ_s2tnZ2d@.comcast.com...
>>I am using VB.net to execute a stored procedure... Sometimes I get a
>>timeout, other times I do not. How can I increase the time out ? Is
>>this a server setting ?
>>
>|||The default ADO.NET timeout is 30 seconds. This can be changed with the
SqlCommand object CommandTimeout property. For example:
myCommand.CommandTimeout = 60
However, I agree with the others that the timeout should be changed only
after ensuring proper index and query tuning is done. It could be that the
production server simply has enough power to compensate for inefficiencies.
Developing on an underpowered server can provide the motivation to improve
performance ;-)
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Rob" <robc1@.yahoo.com> wrote in message
news:Y6WdnRf7neuvgh3anZ2dnUVZ_s6mnZ2d@.comcast.com...
> Hi,
> The stored procedure is very straightforward. I am currently using an
> under powered test server. I never get the timeout on the production
> server...
> So how do I increase the timeout ?
> Thanks
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
> message news:uOunlH$TIHA.4440@.TK2MSFTNGP06.phx.gbl...
>> That doesn't really sound like a "fix" to me at all. You can increase
>> the timeout to infinity, but users are still going to get bored at some
>> point. Have you considered looking at the stored procedure itself, and
>> making it faster, instead of trying to find ways to make its slowness
>> more acceptable?
>>
>>
>> "Rob" <robc1@.yahoo.com> wrote in message
>> news:dZGdnVKfG_9Qlx3anZ2dnUVZ_s2tnZ2d@.comcast.com...
>>I am using VB.net to execute a stored procedure... Sometimes I get a
>>timeout, other times I do not. How can I increase the time out ? Is
>>this a server setting ?
>>
>|||"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:701FF8B7-52A8-4431-98EB-D4F2E5F1C20F@.microsoft.com...
> The default ADO.NET timeout is 30 seconds. This can be changed with the
> SqlCommand object CommandTimeout property. For example:
> myCommand.CommandTimeout = 60
> However, I agree with the others that the timeout should be changed only
> after ensuring proper index and query tuning is done. It could be that
> the production server simply has enough power to compensate for
> inefficiencies. Developing on an underpowered server can provide the
> motivation to improve performance ;-)
I just had to learn this one where I am. A query that basically returns the
DB as an XML feed (don't ask) now takes longer than 30 seconds. And even if
we improve it again, it's just going to get worse, again. Arrgh...
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
>
--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||Thanks !
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:701FF8B7-52A8-4431-98EB-D4F2E5F1C20F@.microsoft.com...
> The default ADO.NET timeout is 30 seconds. This can be changed with the
> SqlCommand object CommandTimeout property. For example:
> myCommand.CommandTimeout = 60
> However, I agree with the others that the timeout should be changed only
> after ensuring proper index and query tuning is done. It could be that
> the production server simply has enough power to compensate for
> inefficiencies. Developing on an underpowered server can provide the
> motivation to improve performance ;-)
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Rob" <robc1@.yahoo.com> wrote in message
> news:Y6WdnRf7neuvgh3anZ2dnUVZ_s6mnZ2d@.comcast.com...
>> Hi,
>> The stored procedure is very straightforward. I am currently using an
>> under powered test server. I never get the timeout on the production
>> server...
>> So how do I increase the timeout ?
>> Thanks
>> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
>> message news:uOunlH$TIHA.4440@.TK2MSFTNGP06.phx.gbl...
>> That doesn't really sound like a "fix" to me at all. You can increase
>> the timeout to infinity, but users are still going to get bored at some
>> point. Have you considered looking at the stored procedure itself, and
>> making it faster, instead of trying to find ways to make its slowness
>> more acceptable?
>>
>>
>> "Rob" <robc1@.yahoo.com> wrote in message
>> news:dZGdnVKfG_9Qlx3anZ2dnUVZ_s2tnZ2d@.comcast.com...
>>I am using VB.net to execute a stored procedure... Sometimes I get a
>>timeout, other times I do not. How can I increase the time out ? Is
>>this a server setting ?
>>
>>
>sql
Subscribe to:
Posts (Atom)