SQL Batch - Slow Response - Remote DB Query

Oct 16, 2006

Hello.
I have a slow response with a system that I am setting up. The OS is
Win 2000 Server with SQL Server 2000. My first execution of the SQL
procedure is slow (about 40 seconds), while the second execution is
faster (approx. 3 seconds and what I expect).
Using query analyser I can leave the window up and come back ( after
say 10 minutes ) and my next response is will be slow (and faster
straight after this).

The query itself is the "TOP 10" rows from a table of a remote DB (MSDE
2000 - NT station).
This is similar to;
---------------------
DECLARE @SelectRowsFromRemoteDB NVARCHAR(400)
SET @SelectRowsFromRemoteDB = "SELCT a.* FROM OPENROWSET
('SQLOLEDB', 'Remote', ....) a"

EXEC sp_executesql @SelectRowsFromRemoteDB
---------------------

The CPU usage goes high (with Task Manager), but oddly the task with
most usage is the "Idle Process".

Please help with an answer
or a good starting point to known where the resources are being used.

Regards JC.....

View 2 Replies


ADVERTISEMENT

Slow Query Response - Is It The Hosting.

Mar 16, 2006

Hi all,
I have a website www.searchcontracts.com which has a search feature on the front page. The problem is that whenever a search is submitted (without and filters) it processes extremely slow (if at all, sometime throws 'general network' errors). If working properly it will return about 500+ records. As far as i know if there should be no issues returning a recordset of this size. I have the site hosted in a shared environment and it uses a SQL2000 db. If you filter the search resutls, it's more reponsive (still fairly slow).
If you believe that the hosting server i'm using should be upgraded, what would be the best solution? Keep in mind that i want to be able to return way more than 500+ records in one go.
Thanks
Jakehttp://www.searchcontracts.comhttp://www.custommethod.comhttp://www.boozetour.com
 
 

View 3 Replies View Related

Slow Query Response From A Linked Server

Oct 17, 2006

Hello,

I have 2 servers (say MAINSRV e SECSRV) running SQL2000 Standard SP3 on Windows 2000 Advanced within a NT (!) domain and each server is linked to the other.

My problem is that if I run a query returning few dozens of rows like:

SELECT * FROM MAINSRV.DbName.dbo.TblName TBLA
WHERE Fieldx = 'anyval'

from a client connected to the SECSRV server, it takes something like 35 minutes to complete, while the same query completes in no time when run on clients connected to MAINSRV.

Even the simplest SELECT Count(*) FROM... takes more than one minute from SECSRV while completing in a fraction of second from MAINSRV.

I tried to change the linked server security options (SQL/Windows), but the remote query remains slow.

There are no locks active on the table, both the servers have almost no load (CPU less than 10%, when tested) and the query returns just a few KBytes, so communication overhead will not be the problem.

Any suggestions will be very appreciated, thank you!!!

View 2 Replies View Related

RESPONSE TIME IS TOO SLOW

Aug 21, 1998

Hi
I have Problem, My response time is too Low. Is Any one Know how to improve my response time. My DATABASE SIZE IS 11 GB. I didn’t change any configuration parameter after installing SQL Server. Right my server Having default configuration parameters. Whether I have to change any parameters or not. My queries will generate lot of temporary tables.

Thanks

JK

View 2 Replies View Related

Slow Client Response

Aug 10, 1998

At my location we are running Great Plains on SQL server with the Great Plains client on the front end communicating over IP Sockets. Occasionally the client will stop responding for up to 15 minutes. This occurs to all clients at the same time. While this is happening I have ran performance monitor on the SQL server using the SQL counters and everything looks fine. Performance on the server and the network does not degrade. Only the Great Plains client is affected. From this I feel this is not a network problem. I would like some suggestions on what could possible cause this type of problem. I am really just looking for a starting point on where the problem may be originating from. Any suggestions will be greatly appreciated.

thanks.

View 1 Replies View Related

Slow Response From A 32 Bit To 64 Bit Connection.

Feb 29, 2008

Here is the configuration we have at our site:
64 bit virtual server - Clustered 64-bit SQL Server 2005 Standard SP2 listening on port 1433.

32 bit virtual server - 32-bit application that connects to above SQL Server 2005 via standard OLEDB driver installed with windows (String utilized: Provider=SQLOLEDB.1;Persist Security Info=False;Initial Catalog=applicationdb;Data Source=ServerInstanceName).

We are experiencing a slow reponse of 45 sec to more than a minute on a simple select statement on a table having 5000 rows.

The same select statement on the Server locally in Studio returns results back in 2 seconds or less. But from Stuido on any other desktop (remote) it takes 45 secs or more. Same slow response is seen by the application which is using OLEDB.

I've looked into some of the Server parameters but didn't find any clues.

Any help is greatly appreciated.

View 2 Replies View Related

Slow Response From SQL 2005

Sep 28, 2007

hi,

I ran into a situation that if I don't use sql server for a while, in MS SQL Server Management Studio, when I run some query again, it takes a long time to respond. I looked into it with Activity Monitor, seems like the query was waiting for something, in the process view tab, it shows that:
WaitType: LATCH_EX
Resource: LOG_MANAGER(112F88C8)
What is the latch-ex? What should I check next to find out the problem?

Thanks.

View 4 Replies View Related

Slow Response Time After Converting DB From SQL 6.5 To 7.0

Feb 10, 2000

We did an in place convertion of our data base from MS SQL Server 6.5 to 7.0.
Our application is much slower now on SQL 7.0. Any idea why?
The following is a sample SQL statement that runs quickly on SQL 6.5 and takes a long time on SQL 7.0 I also attached the query plans from SQL 6.5 and 7.0.




SELECT Person_Name.PerNam_Person_Name_PK ,
Person_Name.PerNam_Row_Status ,
Person_Name.PerNam_Last_Name_Sndx ,
Person_Name.PerNam_Last_Name ,
Person_Name.PerNam_Name_Suffix ,
Person_Name.PerNam_First_Name ,
Person_Name.PerNam_Name_Prefix ,
Person_Name.PerNam_Middle_Name ,
Person_Name.PerNam_Event_Person_FK ,
Event.Evn_Event_Nbr ,
Event.Evn_Event_Type ,
Event_Person.EvnPer_Last_Name ,
Event_Person.EvnPer_First_Name ,
Event_Person.EvnPer_Middle_Name ,
Event_Person.EvnPer_Name_Prefix ,
Event_Person.EvnPer_Name_Suffix
FROM Person_Name , Event , Event_Person
WHERE (Person_Name.PerNam_Agency_ID = "CL")
AND ( Person_Name.PerNam_Event_Person_FK = Event_Person.EvnPer_Event_Person_PK )
and ( Event_Person.EvnPer_Event_FK = Event.Evn_Event_PK )
and (Person_Name.PerNam_Person_Name_PK = 0 or ( Person_Name.PerNam_Event_Person_FK = 581541) )
and ( Person_Name.PerNam_Row_Status <> "D" )




Query plan in SQL 6.5

SQL Server Execution Times:
cpu time = 0 ms. elapsed time = 31250 ms.
STEP 1
The type of query is INSERT
The update mode is direct
Worktable created for REFORMATTING
FROM TABLE
Person_Name
Nested iteration
Index : PK_Person_Name
FROM TABLE
Person_Name
Nested iteration
Index : PerNam_Event_Person_FK
FROM TABLE
Person_Name
Nested iteration
Using Dynamic Index
FROM TABLE
Event_Person
Nested iteration
Index : PK_Event_Person
TO TABLE
Worktable 1
STEP 2
The type of query is SELECT
FROM TABLE
Worktable 1
Nested iteration
Table Scan
FROM TABLE
Event
Nested iteration
Index : PK_Event
SQL Server Parse and Compile Time:
cpu time = 0 ms.
Table: Person_Name scan count 2, logical reads: 6, physical reads: 5, read ahead reads: 0
Table: Event scan count 0, logical reads: 0, physical reads: 0, read ahead reads: 0
Table: Event_Person scan count 0, logical reads: 0, physical reads: 0, read ahead reads: 0
Table: Worktable scan count 0, logical reads: 0, physical reads: 0, read ahead reads: 0
Table: Worktable scan count 1, logical reads: 1, physical reads: 0, read ahead reads: 0

SQL Server Execution Times:
cpu time = 0 ms. elapsed time = 62 ms.



Query plan on SQL 7.0

SQL Server parse and compile time:
CPU time = 431 ms, elapsed time = 535 ms.
.................
Table 'Event'. Scan count 1, logical reads 3, physical reads 3, read-ahead reads 0.
Table 'Event_Person'. Scan count 1, logical reads 6, physical reads 5, read-ahead reads 0.
Table 'Person_Name'. Scan count 1, logical reads 4588, physical reads 2, read-ahead reads 4092.

SQL Server Execution Times:
CPU time = 7921 ms, elapsed time = 13519 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 13781 ms.

View 2 Replies View Related

Slow DB Response After Server Reboots

Oct 31, 2007



I am working with a client that after every reboot of there SQL 2000 DB server, they experience slow response time for a couple of hours. The server has 12 GB RAM and a Dual 3.8 processor. It is believed that the slow response is due to as queries run after the reboot, they are re-building information in memory and after the memory is built up, it goes back to the normal performance utilizing the memory for speed. Is this an accurate assumption or is there something else to be looking at after the server is rebooted?

Thanks,
JB

View 3 Replies View Related

SSIS Response Time Very Slow.

Apr 5, 2007

Hi

i had a problem with the SSIS response time - its very slow.

When I try to open the solution and than click to open the Pakage it's take at least 6-10 minutes until it's open.



Please advice what can be the reason

10x



View 4 Replies View Related

SQL 2012 :: SSMS Slow Response Time Editing

Jun 26, 2015

Using SSMS 2012, we are experiencing extremely slow response times when opening SQL job steps to edit and also deploying SSIS Pkg's. Sysadmins have no problem. Users in the ssis_admin role have no problem. It's the rest of the users who have issues.

View 1 Replies View Related

Newbie: Slow Response Time Querying Systemjobhistory

Apr 29, 2008

Hi,

I have a problem with querying systemjobhistory data. Response time is slow and it is vary from time to time, sometime it takes few seconds and sometime it takes more than 2 minutes. I understand that there is quite a number of jobs in DB server and which might result in slow response time.

Is it possible to shorten the response time? like using index? My application is always look like hang when the query take very long time to run.

Any help is greatly appreciated. Thank you.


Regards,
maxChin

View 4 Replies View Related

Outlook Runs Very Slow Frequently Non-response, SQL Server Using Up To 1GB Memory - VISTA &&amp; Office 2007

May 14, 2008

When I launch Outlook, it takes forever for the program to finally open. With any inbound email, it stops processing whatever is underway at the time....and frequently there is a 2-3 second lag between keyboard input and what appears on the screen. SQLserver is usually consuming upwards of 1-gb of memory....help. Mike

View 1 Replies View Related

Remote Installation Create Response File

May 17, 2007

Hi,



Actaully i want to install SQL SERVER 2005 Enterprise edition in the silent mode and I was tyring to create a response file (setup.iss), but from the document provided i saw that SQL SERVER 2005 Enterprise edition doesnt create response file, and doesnt support setup.iss. So please provide me some solution where i can generate response file or some thing which is similar to that.

View 4 Replies View Related

Osql Batch Slow

Jun 3, 2008

Have a osql batch when i run it on my desktop its fast. then when i run it on the server is very slow.
Any ideas? how to solve it?

MCTP

View 3 Replies View Related

Execute Command Process - Remote Batch File?

Feb 23, 2006

Good afternoon-

Can a batch file that resides on another server be executed from a different machine? I have a batch file that resides on a server that I would like to run using SQL 2005 Integration Services. Is there anything I can do that would allow me to remotely execute this batch file and have it run in that environment.

BATCH FILE:

cd C:Trandev
otrun -at OTRecogn.att -DINPUT_FILE=%1 -tl 1 -cs dv -lg mylog -I
C:Trandev represents the remote environment


I have tried mapping the remote machine to a network drive on my local machine and using that drive to execute the batch file in an Execute Process Task, but it does not work.

SSIS:

I have a FOR EACH loop grabbing files and writing fileName to a variable that is passed to the Process Task as an argument through an expression(%1 in the batch file above). The Working Directory is a mapped network drive. The Executable is also a network drive plus batch file name.


Any help would be appreciated.

My computer is a HP Compaq dc7100, 512mb RAM, WindowsXP

View 7 Replies View Related

Calling Remote Batch File Using 'Execute Process Task'

Oct 11, 2006

I have a remote batch file on machine B that I need to execute using 'Execute process task' control from a package on machine A. The batch file uses pgp software and encrypts a file sitting on machine B itself. The reason why my batch file is sitting on machine B, is because the PGP software is on machine B.

If I execute the batch file by itself from machine B, the script runs fine. I refer the same batch file as a UNC path from my package on machine A. But that does not work since the 'Working directory' is still machine A. I can not set machine B's folder as the working dir because it does not accept UNC path. So I say, ok , let me map a path to that UNC location and map it as drive 'Z:'. Certainly if I do so, I will be running the process on machine A and the batch file will look for the pgp software on machine A, and hence fail.

I have tried third party remote batch execution tools (PSEXEC) but have not had success, not because of SSIS limitations, but simply because the PGP executable when run through the PSEXEC tool, does not identify the location of the public keys on machine B and hence gives an encrytion failure.

How do I get the remote batch file to execute such that it executes with its own env? Is there a better remote execution tool I can try or are there any other features of SSIS I can use to get around this issue? I need the results of the batch file and hence do not want to make it an asyncronous process.

Thanks

Sumeet

View 3 Replies View Related

SQL 2012 :: Calling Remote Batch File Using (Execute Process Task)

Jan 7, 2015

I have a remote batch file on machine B that I need to execute using 'Execute process task' control from a package on machine A.

How Can I achieve this....

View 2 Replies View Related

SQL Query Response Time

Mar 30, 2001

Why is the response time to run a query faster when the output is displayed in grid format as compared to text format?

View 1 Replies View Related

Query With Multiple ID To Check And Response...

Jan 27, 2007

Hi there, I tried to make it different as usual but i´m stacked into this problem:Supose TABLE DetailsID_DET    ID_CAR    DETAILS1              3             1,2,3,4,5,62              4             2,43              5             5,6,7,8andTABLE  Details_ItemsID_DI       DETAIL_NAME1             Stereo HiFi CD2             Alarm3             AirConditioning4             LeatherSeats5             Pro Wires6             Aluminium WheelsThe problem appears when i need to bring CAR DETAILS (NAME) from TABLE DETAIL_ITEMS.Mi guess i that I should make something like:SELECT * FROM DETAILS_NAME WHERE id_di = ((  Array(i) FROM  Details  )) one by one...I really dont know how to face it.First I thought in bringing ALL details_Items (datafieldtext = id_di and datavaluetext=Details_names) into a dataview.And then "somohow?" filter this dataview according with the Array previuosly splited by me with a For each function.Then I thought "Perhaps" there is a simpliest way to do that using SQL views, o advanced SQL QUERYS.and Finally I thought that creating a VIEW in for both TABLES would be great.The point is that, neither 1,2,3 options, honestly , I dont know how to face them.Thanks in advance, apologise my "rude" English grammar.LUCAS ( From Argentina )
 
 
 

View 2 Replies View Related

Remove Limit On Query Response

Mar 26, 2007

Is there a way I can remove the 30 seconds limit response time on sql server 2005?

View 1 Replies View Related

Query Response Time Check

Sep 28, 2006

Is there a global variable or something of the sort that would tell me how long it took to execute a query??

I need to monitor my DB response times and we have a query that runs in under 2 seconds. So we want to run this query every couple of minutes and if it takes more than 12 sec to run, we want to send an email to our DB staff...

I know that I could take a time stamp before and after then subtract but I wanted to know if there was an easier way to do it..

Thanks.

View 3 Replies View Related

No Response To Code And Query Analyser Cannot Read Statement

Feb 20, 2004

hi

have a problem ...
I tried to test my statement in QA as it returns no dataset ,it gives me empty columns in return,I though it will return all the columns and some records of (lmeyer) as the current user ,who logged in on windows auth,but it return only the columns,is that means my query analyser cannot read the login1 ,column which has the username as (lmeyer)

select * from tstudents where (login1='@param1')

if not what could me wrong in my code ,because I get no response to the sql server




Public Function login(ByVal login1 as string) as dataset

Dim myconnection as new sqlconnection("server=G103-TT03;database=CampusLANDB;Trusted_Connection=yes")
dim mycommand as new sqlcommand ("Select * from tStudents Where login1 = @param1",myconnection)
mycommand.parameters.add(new SQLClient.SqlParameter("@param1",login1))

dim DS as new dataset()


try
myconnection.open

dim adpt as new sqldataadapter
adpt.selectcommand = mycommand
adpt.fill(DS,"tStudents")


catch ex as exception
throw new exception(ex.message)
return nothing
finally
myconnection.close
end try

try
mydatagrid.datasource=DS.Tables("tStudents")
mydatagrid.databind()
catch ex as exception
errorlabel.text=ex.message
end try

return DS

End Function





thankx in advance
pamela mkosana

View 12 Replies View Related

SSMS Performing Slow Over Remote Connection?

Mar 25, 2013

One of the instance is hosted in a cluster node with 128GB of ram and 80 processors allocated to this instance...And last week, we had an issue with temp DB as it was full.We increased the drive space over the weekend.

Now whenever we connect this instance from other server through remote SSMS, we see, refreshing the object including connecting to this instance is very very slow...It takes more than 10 sec. Initially this was not the case. Even hear from end user that the application is performing gradually slow.

I check the network, fregmentation, settings across this instance, and all looks good.

View 2 Replies View Related

Queries Are Slow When Accessed From Remote Machine

May 30, 2007

Hi,

I have succesfully created a Stored Procedure which runs under 2 seconds locally.

However when i run the same proc from another machine in the LAN, the response times vary from 5 sec to over 40 Secs and even occassionally times out.

My server is SQL 2005 Dev Edition (32 Bit) running on a Dual Core Box with 2GB memory.



Any Ideas why this would be happening?

View 6 Replies View Related

SSRS 2005 With Remote Catalog Very Slow - Any Ideas?

Nov 6, 2007



Hi,

Just wondering if anybody has any idea why when using RS 2005, remote catalog, asp.net 2.0 and using the .net 2.0 reportviewer control on an aspx page it seem to run 15 - 20 times slower than if we use a local catalog. The two machine in the remote catalog setup have a 2gb network connect between them, the doesn't apear to be any network load on either machine.

Any ideas will be greatfully received.

Cheers

Jon

View 1 Replies View Related

Remote Scan On Linked Server - Fast SELECT/Slow UPDATE

Aug 14, 2001

In an ASP, I have a dynamically created SQL statement that amounts to "SELECT * FROM Server1.myDB.dbo.myTable WHERE Col1 = 1" (Col1 is the table's primary key). It returns the data immediately when executed.

However, when the same record is updated with "UPDATE Server1.myDB.dbo.myTable SET Comments = 'blah blah blah' WHERE Col1 = 1", the page times out before the query can complete.

I watched the program in Profiler, and I saw on the update that sp_cursorfetch was being executed as an RPC once per each row in the table. In a table of 78000 records, the timeout occurs well before the last record is fetched, and the update bombs.

I can run the same statements in Query Analyzer from a linked server and have the same results. The execution plan shows that a Remote Query is occurring on the select that returns 1 row, and a Remote Scan is taking place on the update scanning 78000 rows (I guess this is where all the sp_cursorfetch calls are happening...?).

How can I prevent the Remote Scan? How can I prevent the execution of the RPC sp_cursorfetch for each row in the remote table?

Thank you!

View 2 Replies View Related

Remote Access Server Configuration Option And Remote Query Timeout?

Jun 2, 2015

- When I disable "allow remote connections to this server" from server properties>connection page, I can still remotely connect to the server from SSMS...so what is the impact of enable/disabling it?

- what is the impact of changing the remote query timeout (on the same page) from default value?

View 4 Replies View Related

Batch File To Run A Query

Jan 1, 2005

Dear All,

i need to put a query in a batch file to be easy to use by the user.
jusy only double click on it and it will work.

can any one help?

View 10 Replies View Related

Export A Query To CSV From Batch File

Sep 19, 2014

I'm trying to export a query to a csv file from a batch file.

batch file:
sqlcmd -S loni-sbs -d lonikahn0207 -E -Q "select * from ael" -o "AELORI.csv" -h-1 -s"," -w 700

BUT, I'm getting a user name and password issue inside the csv file:

Cannot open database "lonikahn0207" requested by the login. The login failed.
Msg 18456
Login failed for user 'LONIBOOKSAdministrator'.

View 1 Replies View Related

SQL Query In Batch File Problem

Aug 11, 2006

Hi,

I am trying to run this query in a batch file but am getting an "Incorrect syntax near keyword 'and'" error. Any help would be much appreciated. Thanks, Jeff

ECHO OFF
set GatewayDB="SybaseHDR_Prod"
set DBUser="user"
set DBPswd="pswd"
set SQLServer="nnn.nn.nnn.nn"
set HdrctlNbr1 ="20060714000000000000"
set HdrctlNbr2 ="20060727999999999999"

ECHO ========================================================================
ECHO Start of Bulk Copy Process from Production Tables to Output Text Files
ECHO Database is : %GatewayDB%
ECHO Server : %SQLServer%
ECHO File AuthNbr: %AuthNbr%
ECHO ========================================================================

set TableName="hdr_ctl_nbr_status"
set outfile="C:BCPhdr_ctl_nbr_status.txt"
If exist %outfile% del %outfile%
bcp "SELECT * FROM %GatewayDB%..%TableName% where hdr_ctl_nbr between %HdrctlNbr1% and %HdrctlNbr2%" queryout %outfile% -b1000 -c -t~ -U %DBUser% -P %DBPswd% -S%SQLServer%
REM pause


:END

ECHO ================================
ECHO Bulk Copy process is completed !
ECHO ================================

pause

View 5 Replies View Related

A Query Runs Fast In Query Analuser But Slow In APplication

Jul 23, 2005

I am able to run a query which runs FAst in QA but slow in theapplication.It takes about 16 m in QA but 1000 ms on theApplication.What I wanted to know is why would the query take a longtime in the application when it runs fast on SQL server?How should we try debugging it?Ajay

View 2 Replies View Related

Running SQL Query From Batch File- MS SQl 2000

Mar 25, 2008

Hello,
I am looking for some way to run a sql query from a batch file on the SQL server. The query needs a user entered parameter, if that can be accomodated it would be great.
I read about some osql command & tried to run it but it kept saying "Server does not exist or access denied."
Please give me the steps to go about this.
Thanks,
-R

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved