I have a SP, which displays results within 15 secs on local SQL server, but when trying execute from other SQL Client, it takes more than 5 mins. And I don't find any network issues. Can anybody help me to isolate the issu
Thank
Bhagyahow much data including messages is being returned by the
sp,
if very little, then it probably is not a network issue.
if a lot, then everything points to a network issue.
transfer a large file (~100MB) between the client and
server, how long does it take? Fast Ethernet at
100Mbit/sec in full-duplex should move data between 5-
10MByte/sec depending on the number of hops.
if you are not on Fast Ethernet with Full-duplex mode,
then it may very well take forever.
given that FE switches are so inexpensive, i would not
bother trying other tricks to make this work on half-duplex
>--Original Message--
>I have a SP, which displays results within 15 secs on
local SQL server, but when trying execute from other SQL
Client, it takes more than 5 mins. And I don't find any
network issues. Can anybody help me to isolate the issue
>Thanks
>Bhagya
>.
>|||Hi
Thanks, for the reply
But the data that is returned by SP is hardly 50kb. What could be the reason for the delay
Thank
Bhagya|||Do you have SET NOCOUNT ON?
--
Andrew J. Kelly
SQL Server MVP
"Bhagya" <anonymous@.discussions.microsoft.com> wrote in message
news:DAC51A3C-550C-4163-AADB-86BDC8270F47@.microsoft.com...
> Hi,
> Thanks, for the reply.
> But the data that is returned by SP is hardly 50kb. What could be the
reason for the delay.
> Thanks
> Bhagya|||15 sec on the local server is still an expensive query.
what is it doing?
does the execution plan show a hash match or hash join?
if so, are more than 10K rows in hash ? regardless of the
final row count.
when you say local, are you running the sp from QA,
from other clients: is that QA or your own application
>--Original Message--
>Hi,
>Thanks, for the reply.
>But the data that is returned by SP is hardly 50kb. What
could be the reason for the delay.
>Thanks
>Bhagya
>.
>|||Hi Joe
Basically this SP is returning the data used in a report. It is doing Hash match / Part Aggregate join. There are about 5000000 row in the Hash
The table has about more than 10000000 rows and it is indexed, as per requirement, and columns used in joins and filter
Thank
Bhagya|||If it is doing a Hash match then you probably don't have proper indexes but
that is not always the case. How may rows are you actually returning to the
client? Sounds like a lot. The more rows you return the longer it will
take, especially if the client can't handle them fast enough.
Andrew J. Kelly
SQL Server MVP
"Bhagya" <anonymous@.discussions.microsoft.com> wrote in message
news:FD457C8C-D6E2-4AFF-A92A-3F8D19D7CC89@.microsoft.com...
> Hi Joe,
> Basically this SP is returning the data used in a report. It is doing
Hash match / Part Aggregate join. There are about 5000000 row in the Hash.
> The table has about more than 10000000 rows and it is indexed, as per
requirement, and columns used in joins and filters
> Thanks
> Bhagya|||Bhagya,
Is it possible that you are seeing this bug (does your query have a
LIKE operator, and are you using SQL Server 7.0?)
http://support.microsoft.com/default.aspx?scid=kb;en-us;814115&Product=sql2k
SK
Bhagya wrote:
>Hi Joe,
>Basically this SP is returning the data used in a report. It is doing Hash match / Part Aggregate join. There are about 5000000 row in the Hash.
>The table has about more than 10000000 rows and it is indexed, as per requirement, and columns used in joins and filters
>Thanks
>Bhagya
>sql
Showing posts with label execute. Show all posts
Showing posts with label execute. Show all posts
Wednesday, March 28, 2012
long running packages
I need to execute a long running package (it takes about 16 hours to finish) to load a data warehouse for the first time with all historical data. This package it's a master package and execute other packages; I log the start time and the finish time of the package in a table to manage future incremental loads.
I executed the package on sql server where it is saved, but after 8 hours it was running, a new package was started automatically. Then two more packages started .. each every two hours.
I set the MaxConcorrentExecutable = 4, this could affect this strange behavoir ?
Anyone could imagine wath happened ?
Thanks
Cosimo
cosimog wrote:
I need to execute a long running package (it takes about 16 hours to finish) to load a data warehouse for the first time with all historical data. This package it's a master package and execute other packages; I log the start time and the finish time of the package in a table to manage future incremental loads.
I executed the package on sql server where it is saved, but after 8 hours it was running, a new package was started automatically. Then two more packages started .. each every two hours.
I set the MaxConcorrentExecutable = 4, this could affect this strange behavoir ?
Anyone could imagine wath happened ?
Thanks
Cosimo
What mechanism are you using to start the package?
Packages will not start executing unless you tell them to.
MaxConcurrentExecutables is completely irrelevant here by the way.
-Jamie
|||
Solved.
It was just a bug ... thank
Cosimo
Wednesday, March 21, 2012
Logreader failed to construct replicated command from LSN
I have this error coming up 'The process could not execute 'sp_replcmds' on
'VDB1' which always seems to accompany 'Logreader failed to construct
replicated command from LSN {0008e253:00009ddb:0003}.'
I turned on verbose logging and it gave this:
2007-05-04 01:31:16.018 Publisher: {call sp_replcmds (500, 0, 0, , 15,
500000)}
2007-05-04 01:31:16.065 I~0x0008e25300009ddb0005~1:
Looking up that sequence number with:
exec sp_browsereplcmds @.xact_seqno_start = '0x0008e25300009ddb0005'
, @.xact_seqno_end = '0x0008e25300009ddb0005'
Returned nothing.
I also tried:
select * from MSrepl_transactions
where xact_seqno = '0x0008e25300009ddb0005'
It returned nothing also.
This same error is preventing 2 publications, but other 8 are working fine.
I have stopped and started log reader job a couple of times.
Any ideas?
Thanks,
Chuck Lathrope
www.sqlwebpedia.com
I've bumped into this from time to time. What you need to do is to run
profiler with the xactSequence number column.
Next time you get this error, look up the offending statement, for example
it could be a proc which is writing something unexpected to the database.
Last time I got this, it was an update to a text column with a ''.
"Chuck Lathrope" <computerguy_chuck@.fixmehotmail.com> wrote in message
news:5likshd2rlr9.1cei74wrqkxlw$.dlg@.40tude.net...
>I have this error coming up 'The process could not execute 'sp_replcmds' on
> 'VDB1' which always seems to accompany 'Logreader failed to construct
> replicated command from LSN {0008e253:00009ddb:0003}.'
> I turned on verbose logging and it gave this:
> 2007-05-04 01:31:16.018 Publisher: {call sp_replcmds (500, 0, 0, , 15,
> 500000)}
> 2007-05-04 01:31:16.065 I~0x0008e25300009ddb0005~1:
> Looking up that sequence number with:
> exec sp_browsereplcmds @.xact_seqno_start = '0x0008e25300009ddb0005'
> , @.xact_seqno_end = '0x0008e25300009ddb0005'
> Returned nothing.
> I also tried:
> select * from MSrepl_transactions
> where xact_seqno = '0x0008e25300009ddb0005'
> It returned nothing also.
> This same error is preventing 2 publications, but other 8 are working
> fine.
> I have stopped and started log reader job a couple of times.
> Any ideas?
> Thanks,
> Chuck Lathrope
> www.sqlwebpedia.com
|||On Fri, 4 May 2007 08:29:28 -0400, Hilary Cotter wrote:
Thanks, it only seems to be getting worse for me. The replication monitor
can't expand the server nodes now. I restarted sql service on the
distributor and that didn't help. Has anyone seen that?
I am setting the -ReadBatchsize to 1 as Jonathan suggested, hopefully it
can find to correct issue as this command in error reports looks to be
pointed to something already completed.
-Chuck
[vbcol=seagreen]
> I've bumped into this from time to time. What you need to do is to run
> profiler with the xactSequence number column.
> Next time you get this error, look up the offending statement, for example
> it could be a proc which is writing something unexpected to the database.
> Last time I got this, it was an update to a text column with a ''.
>
> "Chuck Lathrope" <computerguy_chuck@.fixmehotmail.com> wrote in message
> news:5likshd2rlr9.1cei74wrqkxlw$.dlg@.40tude.net...
|||Update:
One of the two publications started to work, not sure why or when.
The batchsize to 1 still brought back the same LSN error - which is still
returning nothing when I query it with:
select * from MSrepl_transactions
where xact_seqno = '0x0008e25300009ddb0005'
We are going to kill the publications and recreate them - luckily they are
not super critical like the others.
Still having Replication Monitor issues (Still running SP1 on server; SP2
on my local box).
-Chuck
On Fri, 4 May 2007 08:29:28 -0400, Hilary Cotter wrote:
Thanks, it only seems to be getting worse for me. The replication monitor
can't expand the server nodes now. I restarted sql service on the
distributor and that didn't help. Has anyone seen that?
I am setting the -ReadBatchsize to 1 as Jonathan suggested, hopefully it
can find to correct issue as this command in error reports looks to be
pointed to something already completed.
-Chuck
[vbcol=seagreen]
> I've bumped into this from time to time. What you need to do is to run
> profiler with the xactSequence number column.
> Next time you get this error, look up the offending statement, for example
> it could be a proc which is writing something unexpected to the database.
> Last time I got this, it was an update to a text column with a ''.
>
> "Chuck Lathrope" <computerguy_chuck@.fixmehotmail.com> wrote in message
> news:5likshd2rlr9.1cei74wrqkxlw$.dlg@.40tude.net...
|||While recreating the publications, the snapshot errored because of an
obsolete XSD for an XML table. We have the option set to false to publish
the XSD's, so not sure how this came about.
Now that these are working, the Replication Monitor server nodes can be
expanded now.
-Chuck
On Fri, 4 May 2007 10:15:26 -0700, Chuck Lathrope wrote:
[vbcol=seagreen]
> Update:
> One of the two publications started to work, not sure why or when.
> The batchsize to 1 still brought back the same LSN error - which is still
> returning nothing when I query it with:
> select * from MSrepl_transactions
> where xact_seqno = '0x0008e25300009ddb0005'
> We are going to kill the publications and recreate them - luckily they are
> not super critical like the others.
> Still having Replication Monitor issues (Still running SP1 on server; SP2
> on my local box).
> -Chuck
>
> On Fri, 4 May 2007 08:29:28 -0400, Hilary Cotter wrote:
> Thanks, it only seems to be getting worse for me. The replication monitor
> can't expand the server nodes now. I restarted sql service on the
> distributor and that didn't help. Has anyone seen that?
> I am setting the -ReadBatchsize to 1 as Jonathan suggested, hopefully it
> can find to correct issue as this command in error reports looks to be
> pointed to something already completed.
> -Chuck
'VDB1' which always seems to accompany 'Logreader failed to construct
replicated command from LSN {0008e253:00009ddb:0003}.'
I turned on verbose logging and it gave this:
2007-05-04 01:31:16.018 Publisher: {call sp_replcmds (500, 0, 0, , 15,
500000)}
2007-05-04 01:31:16.065 I~0x0008e25300009ddb0005~1:
Looking up that sequence number with:
exec sp_browsereplcmds @.xact_seqno_start = '0x0008e25300009ddb0005'
, @.xact_seqno_end = '0x0008e25300009ddb0005'
Returned nothing.
I also tried:
select * from MSrepl_transactions
where xact_seqno = '0x0008e25300009ddb0005'
It returned nothing also.
This same error is preventing 2 publications, but other 8 are working fine.
I have stopped and started log reader job a couple of times.
Any ideas?
Thanks,
Chuck Lathrope
www.sqlwebpedia.com
I've bumped into this from time to time. What you need to do is to run
profiler with the xactSequence number column.
Next time you get this error, look up the offending statement, for example
it could be a proc which is writing something unexpected to the database.
Last time I got this, it was an update to a text column with a ''.
"Chuck Lathrope" <computerguy_chuck@.fixmehotmail.com> wrote in message
news:5likshd2rlr9.1cei74wrqkxlw$.dlg@.40tude.net...
>I have this error coming up 'The process could not execute 'sp_replcmds' on
> 'VDB1' which always seems to accompany 'Logreader failed to construct
> replicated command from LSN {0008e253:00009ddb:0003}.'
> I turned on verbose logging and it gave this:
> 2007-05-04 01:31:16.018 Publisher: {call sp_replcmds (500, 0, 0, , 15,
> 500000)}
> 2007-05-04 01:31:16.065 I~0x0008e25300009ddb0005~1:
> Looking up that sequence number with:
> exec sp_browsereplcmds @.xact_seqno_start = '0x0008e25300009ddb0005'
> , @.xact_seqno_end = '0x0008e25300009ddb0005'
> Returned nothing.
> I also tried:
> select * from MSrepl_transactions
> where xact_seqno = '0x0008e25300009ddb0005'
> It returned nothing also.
> This same error is preventing 2 publications, but other 8 are working
> fine.
> I have stopped and started log reader job a couple of times.
> Any ideas?
> Thanks,
> Chuck Lathrope
> www.sqlwebpedia.com
|||On Fri, 4 May 2007 08:29:28 -0400, Hilary Cotter wrote:
Thanks, it only seems to be getting worse for me. The replication monitor
can't expand the server nodes now. I restarted sql service on the
distributor and that didn't help. Has anyone seen that?
I am setting the -ReadBatchsize to 1 as Jonathan suggested, hopefully it
can find to correct issue as this command in error reports looks to be
pointed to something already completed.
-Chuck
[vbcol=seagreen]
> I've bumped into this from time to time. What you need to do is to run
> profiler with the xactSequence number column.
> Next time you get this error, look up the offending statement, for example
> it could be a proc which is writing something unexpected to the database.
> Last time I got this, it was an update to a text column with a ''.
>
> "Chuck Lathrope" <computerguy_chuck@.fixmehotmail.com> wrote in message
> news:5likshd2rlr9.1cei74wrqkxlw$.dlg@.40tude.net...
|||Update:
One of the two publications started to work, not sure why or when.
The batchsize to 1 still brought back the same LSN error - which is still
returning nothing when I query it with:
select * from MSrepl_transactions
where xact_seqno = '0x0008e25300009ddb0005'
We are going to kill the publications and recreate them - luckily they are
not super critical like the others.
Still having Replication Monitor issues (Still running SP1 on server; SP2
on my local box).
-Chuck
On Fri, 4 May 2007 08:29:28 -0400, Hilary Cotter wrote:
Thanks, it only seems to be getting worse for me. The replication monitor
can't expand the server nodes now. I restarted sql service on the
distributor and that didn't help. Has anyone seen that?
I am setting the -ReadBatchsize to 1 as Jonathan suggested, hopefully it
can find to correct issue as this command in error reports looks to be
pointed to something already completed.
-Chuck
[vbcol=seagreen]
> I've bumped into this from time to time. What you need to do is to run
> profiler with the xactSequence number column.
> Next time you get this error, look up the offending statement, for example
> it could be a proc which is writing something unexpected to the database.
> Last time I got this, it was an update to a text column with a ''.
>
> "Chuck Lathrope" <computerguy_chuck@.fixmehotmail.com> wrote in message
> news:5likshd2rlr9.1cei74wrqkxlw$.dlg@.40tude.net...
|||While recreating the publications, the snapshot errored because of an
obsolete XSD for an XML table. We have the option set to false to publish
the XSD's, so not sure how this came about.
Now that these are working, the Replication Monitor server nodes can be
expanded now.
-Chuck
On Fri, 4 May 2007 10:15:26 -0700, Chuck Lathrope wrote:
[vbcol=seagreen]
> Update:
> One of the two publications started to work, not sure why or when.
> The batchsize to 1 still brought back the same LSN error - which is still
> returning nothing when I query it with:
> select * from MSrepl_transactions
> where xact_seqno = '0x0008e25300009ddb0005'
> We are going to kill the publications and recreate them - luckily they are
> not super critical like the others.
> Still having Replication Monitor issues (Still running SP1 on server; SP2
> on my local box).
> -Chuck
>
> On Fri, 4 May 2007 08:29:28 -0400, Hilary Cotter wrote:
> Thanks, it only seems to be getting worse for me. The replication monitor
> can't expand the server nodes now. I restarted sql service on the
> distributor and that didn't help. Has anyone seen that?
> I am setting the -ReadBatchsize to 1 as Jonathan suggested, hopefully it
> can find to correct issue as this command in error reports looks to be
> pointed to something already completed.
> -Chuck
Monday, March 19, 2012
Logon to DTS from a different domain
I have a series of DTS's that need to be executed in a certain order and therefore have created a VB app that would execute the DTS's and perform a few administrative funtions for the user if successful.
The DBA for the SQL Server has given me a certain Windows Logon and I must logon via Terminal Server when I need to gain access to the SQL Server Manager. This is where I have created the DTS's - all created and owned by that user logon with certain rights. My VB application will be run from a different user machine on a different domain and therefore has a different logon- normal Windows logon. This user does not have access to the SQL Server and the DTS's need to logon with the same logon as under which it was created.
BUT...
LoadFromSQLServer offers only a SQL or Trusted connection by which one can let the user connect. By using the Windows connection flag, the LoadFromSQLServer function now ignores the logon username & password and posts the normal Windows logon. Obviously, the logon now fails for the user.
How can I let the LoadFromSQLServer funtion logon to the DTS Package with a different domain, username & password than the local Windows Logon information ?
GrahamAnybody out there ?
The DBA for the SQL Server has given me a certain Windows Logon and I must logon via Terminal Server when I need to gain access to the SQL Server Manager. This is where I have created the DTS's - all created and owned by that user logon with certain rights. My VB application will be run from a different user machine on a different domain and therefore has a different logon- normal Windows logon. This user does not have access to the SQL Server and the DTS's need to logon with the same logon as under which it was created.
BUT...
LoadFromSQLServer offers only a SQL or Trusted connection by which one can let the user connect. By using the Windows connection flag, the LoadFromSQLServer function now ignores the logon username & password and posts the normal Windows logon. Obviously, the logon now fails for the user.
How can I let the LoadFromSQLServer funtion logon to the DTS Package with a different domain, username & password than the local Windows Logon information ?
GrahamAnybody out there ?
Subscribe to:
Posts (Atom)