one ado connection object - multiple spids?

Hi,
We have asp application (ado connection) which connects to the sql server
2000 (sp3). There is one asp page which sequentially executes set of 6
stored procedures. When I execute same set of stored procedures in query
analyzer, I get response in less than 1 second.When application does that,
response gets back in 15-20 seconds (only when we have problem, otherwise in
2-3 seconds). In the trace, I noticed that each of these 6 stored procedures
(which btw use 1 connection object) gets a different SPID. Why is that?
Shouldn't they use the same SPID, if they use the same connection object
(executed sequentially!)?
Also, the trace shows that each of them gets executed almost instantly
(1ms), but between end of previous, and beginning of the next one, there is
delay of 2-3 seconds. During these 2-3 seconds, there is nothing going on on
the database server (very few events).
Does anybody have an idea?
ThanksPedja wrote:
> Hi,
> We have asp application (ado connection) which connects to the sql
> server 2000 (sp3). There is one asp page which sequentially executes
> set of 6 stored procedures. When I execute same set of stored
> procedures in query analyzer, I get response in less than 1
> second.When application does that, response gets back in 15-20
> seconds (only when we have problem, otherwise in 2-3 seconds). In the
> trace, I noticed that each of these 6 stored procedures (which btw
> use 1 connection object) gets a different SPID. Why is that?
> Shouldn't they use the same SPID, if they use the same connection
> object (executed sequentially!)? Also, the trace shows that each of them
> gets executed almost instantly
> (1ms), but between end of previous, and beginning of the next one,
> there is delay of 2-3 seconds. During these 2-3 seconds, there is
> nothing going on on the database server (very few events).
> Does anybody have an idea?
> Thanks
Are you sure you are not closing the connection and opening it up each time
your ASP code executed some SQL?
David Gugick
Quest Softwaresql

OLEDB connection error

Hi,
I'm having a problem connecting to a SQL Server 2k (SP3) db using oledb
provider in a VBS. Running on a Win2k server. This is what happens.
If I use a localhost sql server, and have in my connection string
data source=(local)
I can connect to the database. But when I'm using a server
installation, and use a connectionstring in the form:
data source=server\db
I all of a sudden get an error: "error connecting to data source"
So it looks like it makes a difference where the sql server is
installed: on a laptop/desktop it works fine, on a server, in a more
complex setting, it stops working.
I tried a lot of things already (like Trusted_Connection, different
username/pwd combinations, different ways of putting the server name in
the connection string), but nothing solved the problem so far. And the
project deadline is... well too soon. ;-)
Thanks for your help/suggestions.
Regards,
Stef
Create a udl file and build the connection string .
Here's how;
create a text file called sample.txt on your desktop.
Rename sample.txt to sample.udl.
Doubleclick sample.udl and select the Provider for SQL and Test the
connection.
Then right click on the graphic and open it as text. It will appear like
this;
Provider=SQLOLEDB.1;Persist Security Info=False;User ID=UserNameHere
;Initial Catalog=Northwind;Data Source=SQLServerNameHere,1433;Network
Library=DBMSSOCN;Packet Size=2048;Use Encryption for Data=False
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.

OLEDB connection error

Hi,
I'm having a problem connecting to a SQL Server 2k (SP3) db using oledb
provider in a VBS. Running on a Win2k server. This is what happens.
If I use a localhost sql server, and have in my connection string
data source=(local)
I can connect to the database. But when I'm using a server
installation, and use a connectionstring in the form:
data source=server\db
I all of a sudden get an error: "error connecting to data source"
So it looks like it makes a difference where the sql server is
installed: on a laptop/desktop it works fine, on a server, in a more
complex setting, it stops working.
I tried a lot of things already (like Trusted_Connection, different
username/pwd combinations, different ways of putting the server name in
the connection string), but nothing solved the problem so far. And the
project deadline is... well too soon. ;-)
Thanks for your help/suggestions.
Regards,
StefCreate a udl file and build the connection string .
Here's how;
create a text file called sample.txt on your desktop.
Rename sample.txt to sample.udl.
Doubleclick sample.udl and select the Provider for SQL and Test the
connection.
Then right click on the graphic and open it as text. It will appear like
this;
Provider=SQLOLEDB.1;Persist Security Info=False;User ID=UserNameHere
;Initial Catalog=Northwind;Data Source=SQLServerNameHere,1433;Network
Library=DBMSSOCN;Packet Size=2048;Use Encryption for Data=False
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.

Old machines works better than the higher-end new machine

Hi all,
My setup is : SQL Server 2000 SP3 on Windows 2000.
I used the profiler to run an sql script with 33,000 commands (mostly
inserts). I ran it on 4 setups and the results were strange. Lower-end
machines ran twice as fast as higher-end machines. I also ran out system
and not the script on all machines and got the same results.
Lower-end ? Motorola Pentium III 500. with 256 MB memory, one slow disk and
.
High-end machines had 1 Xeon processor, 1GB RAM, 3 fast disks.
Any help is greatly appreciated.
AviAssuming each insert is in a separate transaction, a likely cause of the
performance difference is that write caching is enabled on the older
machines but not on the high-end boxes. A synchronous log write is
required for each transaction so you'll need to wait for the i/o to
physically complete.
Write caching improve performance but the controller must guarantee that
data will eventually get written to disk with non-volatile (e.g. battery
backup) memory. Loss of log data may result in a corrupt database.
--
Hope this helps.
Dan Guzman
SQL Server MVP
--
SQL FAQ links (courtesy Neil Pike):
http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--
"Avi" <rememberoti@.yahoo.com> wrote in message
news:uAoQT02rDHA.4092@.tk2msftngp13.phx.gbl...
> Hi all,
>
> My setup is : SQL Server 2000 SP3 on Windows 2000.
>
> I used the profiler to run an sql script with 33,000 commands (mostly
> inserts). I ran it on 4 setups and the results were strange.
Lower-end
> machines ran twice as fast as higher-end machines. I also ran out
system
> and not the script on all machines and got the same results.
>
> Lower-end - Motorola Pentium III 500. with 256 MB memory, one slow
disk and
> .
> High-end machines had 1 Xeon processor, 1GB RAM, 3 fast disks.
>
> Any help is greatly appreciated.
> Avi
>|||Hi,
Thanks for the reply. It is enabled on both machines..
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:eMdns82rDHA.2260@.TK2MSFTNGP09.phx.gbl...
> Assuming each insert is in a separate transaction, a likely cause of the
> performance difference is that write caching is enabled on the older
> machines but not on the high-end boxes. A synchronous log write is
> required for each transaction so you'll need to wait for the i/o to
> physically complete.
> Write caching improve performance but the controller must guarantee that
> data will eventually get written to disk with non-volatile (e.g. battery
> backup) memory. Loss of log data may result in a corrupt database.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> --
> SQL FAQ links (courtesy Neil Pike):
> http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
> http://www.sqlserverfaq.com
> http://www.mssqlserver.com/faq
> --
> "Avi" <rememberoti@.yahoo.com> wrote in message
> news:uAoQT02rDHA.4092@.tk2msftngp13.phx.gbl...
> > Hi all,
> >
> >
> >
> > My setup is : SQL Server 2000 SP3 on Windows 2000.
> >
> >
> >
> > I used the profiler to run an sql script with 33,000 commands (mostly
> > inserts). I ran it on 4 setups and the results were strange.
> Lower-end
> > machines ran twice as fast as higher-end machines. I also ran out
> system
> > and not the script on all machines and got the same results.
> >
> >
> >
> > Lower-end - Motorola Pentium III 500. with 256 MB memory, one slow
> disk and
> > .
> >
> > High-end machines had 1 Xeon processor, 1GB RAM, 3 fast disks.
> >
> >
> >
> > Any help is greatly appreciated.
> >
> > Avi
> >
> >
>|||Different RAID levels?
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Avi" <rememberoti@.yahoo.com> wrote in message news:uh6ZtM5rDHA.2400@.tk2msftngp13.phx.gbl...
> Hi,
> Thanks for the reply. It is enabled on both machines..
> "Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
> news:eMdns82rDHA.2260@.TK2MSFTNGP09.phx.gbl...
> > Assuming each insert is in a separate transaction, a likely cause of the
> > performance difference is that write caching is enabled on the older
> > machines but not on the high-end boxes. A synchronous log write is
> > required for each transaction so you'll need to wait for the i/o to
> > physically complete.
> >
> > Write caching improve performance but the controller must guarantee that
> > data will eventually get written to disk with non-volatile (e.g. battery
> > backup) memory. Loss of log data may result in a corrupt database.
> >
> > --
> > Hope this helps.
> >
> > Dan Guzman
> > SQL Server MVP
> >
> > --
> > SQL FAQ links (courtesy Neil Pike):
> >
> > http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
> > http://www.sqlserverfaq.com
> > http://www.mssqlserver.com/faq
> > --
> >
> > "Avi" <rememberoti@.yahoo.com> wrote in message
> > news:uAoQT02rDHA.4092@.tk2msftngp13.phx.gbl...
> > > Hi all,
> > >
> > >
> > >
> > > My setup is : SQL Server 2000 SP3 on Windows 2000.
> > >
> > >
> > >
> > > I used the profiler to run an sql script with 33,000 commands (mostly
> > > inserts). I ran it on 4 setups and the results were strange.
> > Lower-end
> > > machines ran twice as fast as higher-end machines. I also ran out
> > system
> > > and not the script on all machines and got the same results.
> > >
> > >
> > >
> > > Lower-end - Motorola Pentium III 500. with 256 MB memory, one slow
> > disk and
> > > .
> > >
> > > High-end machines had 1 Xeon processor, 1GB RAM, 3 fast disks.
> > >
> > >
> > >
> > > Any help is greatly appreciated.
> > >
> > > Avi
> > >
> > >
> >
> >
>|||Thanks for the reply!
On both machines the Write Caching is enabled.
Any other ideas?
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:eMdns82rDHA.2260@.TK2MSFTNGP09.phx.gbl...
> Assuming each insert is in a separate transaction, a likely cause of the
> performance difference is that write caching is enabled on the older
> machines but not on the high-end boxes. A synchronous log write is
> required for each transaction so you'll need to wait for the i/o to
> physically complete.
> Write caching improve performance but the controller must guarantee that
> data will eventually get written to disk with non-volatile (e.g. battery
> backup) memory. Loss of log data may result in a corrupt database.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> --
> SQL FAQ links (courtesy Neil Pike):
> http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
> http://www.sqlserverfaq.com
> http://www.mssqlserver.com/faq
> --
> "Avi" <rememberoti@.yahoo.com> wrote in message
> news:uAoQT02rDHA.4092@.tk2msftngp13.phx.gbl...
> > Hi all,
> >
> >
> >
> > My setup is : SQL Server 2000 SP3 on Windows 2000.
> >
> >
> >
> > I used the profiler to run an sql script with 33,000 commands (mostly
> > inserts). I ran it on 4 setups and the results were strange.
> Lower-end
> > machines ran twice as fast as higher-end machines. I also ran out
> system
> > and not the script on all machines and got the same results.
> >
> >
> >
> > Lower-end - Motorola Pentium III 500. with 256 MB memory, one slow
> disk and
> > .
> >
> > High-end machines had 1 Xeon processor, 1GB RAM, 3 fast disks.
> >
> >
> >
> > Any help is greatly appreciated.
> >
> > Avi
> >
> >
>|||Thanks for the reply,
The disks are not RAID configured. I even disabled two of them just to make
sure of that. I still get bad response.
Any other ideas are welocme!
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:u0G$Bo6rDHA.3436@.tk2msftngp13.phx.gbl...
> Different RAID levels?
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "Avi" <rememberoti@.yahoo.com> wrote in message
news:uh6ZtM5rDHA.2400@.tk2msftngp13.phx.gbl...
> > Hi,
> >
> > Thanks for the reply. It is enabled on both machines..
> >
> > "Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
> > news:eMdns82rDHA.2260@.TK2MSFTNGP09.phx.gbl...
> > > Assuming each insert is in a separate transaction, a likely cause of
the
> > > performance difference is that write caching is enabled on the older
> > > machines but not on the high-end boxes. A synchronous log write is
> > > required for each transaction so you'll need to wait for the i/o to
> > > physically complete.
> > >
> > > Write caching improve performance but the controller must guarantee
that
> > > data will eventually get written to disk with non-volatile (e.g.
battery
> > > backup) memory. Loss of log data may result in a corrupt database.
> > >
> > > --
> > > Hope this helps.
> > >
> > > Dan Guzman
> > > SQL Server MVP
> > >
> > > --
> > > SQL FAQ links (courtesy Neil Pike):
> > >
> > > http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
> > > http://www.sqlserverfaq.com
> > > http://www.mssqlserver.com/faq
> > > --
> > >
> > > "Avi" <rememberoti@.yahoo.com> wrote in message
> > > news:uAoQT02rDHA.4092@.tk2msftngp13.phx.gbl...
> > > > Hi all,
> > > >
> > > >
> > > >
> > > > My setup is : SQL Server 2000 SP3 on Windows 2000.
> > > >
> > > >
> > > >
> > > > I used the profiler to run an sql script with 33,000 commands
(mostly
> > > > inserts). I ran it on 4 setups and the results were strange.
> > > Lower-end
> > > > machines ran twice as fast as higher-end machines. I also ran out
> > > system
> > > > and not the script on all machines and got the same results.
> > > >
> > > >
> > > >
> > > > Lower-end - Motorola Pentium III 500. with 256 MB memory, one slow
> > > disk and
> > > > .
> > > >
> > > > High-end machines had 1 Xeon processor, 1GB RAM, 3 fast disks.
> > > >
> > > >
> > > >
> > > > Any help is greatly appreciated.
> > > >
> > > > Avi
> > > >
> > > >
> > >
> > >
> >
> >
>|||Avi wrote:
> Hi all,
> My setup is : SQL Server 2000 SP3 on Windows 2000.
> I used the profiler to run an sql script with 33,000 commands (mostly
> inserts). I ran it on 4 setups and the results were strange. Lower-end
> machines ran twice as fast as higher-end machines. I also ran out system
> and not the script on all machines and got the same results.
> Lower-end ? Motorola Pentium III 500. with 256 MB memory, one slow disk and
> High-end machines had 1 Xeon processor, 1GB RAM, 3 fast disks.
> Any help is greatly appreciated.
> Avi
Remember that Google is your friend. You are not alone in having this
kind of problem:
http://www.google.com/groups?as_q=new+machine+slower&num=10&as_scoring=r&hl=en&ie=UTF-8&oe=UTF-8&btnG=Google+Search&as_epq=&as_oq=&as_eq=&as_ugroup=microsoft.public.sqlserver.*&as_usubject=&as_uauthors=&as_umsgid=&lr=&as_drrb=q&as_qdr=&as_mind=12&as_minm=5&as_miny=1981&as_maxd=23&as_maxm=11&as_maxy=2003&safe=images
HARDWARE:
Ensure that there are no undetected hardware differences affecting your
benchmarks, e.g., that all memory is available to the processors, that
the hardware and network component interfaces to the systems are
equivalent, that you have exclusive use of the network while testing
(don't let Joe the Programmer download MP3s from your server during
tests). Once you have eliminated possible hardware variations then
reconsider the software element.
FILE I/O:
Test the machines on non-SQL I/O processing, i.e., file I/O. Perhaps the
"fast disks" aren't as fast as claimed. There are some posts here that
might be relevant:
http://www.google.com/groups?as_q=disk+formatting&num=10&as_scoring=r&hl=en&ie=UTF-8&oe=UTF-8&btnG=Google+Search&as_epq=&as_oq=&as_eq=&as_ugroup=microsoft.public.sqlserver.*&as_usubject=&as_uauthors=&as_umsgid=&lr=&as_drrb=q&as_qdr=&as_mind=12&as_minm=5&as_miny=1981&as_maxd=23&as_maxm=11&as_maxy=2003&safe=images
Good Luck,
Michael D. Kersey

old connections

All,
Sql 2000 sp3, Server 2000 sp4
I have a cold fusion front end that hooks to my db and it
never seems to close a connection. The problem has only
gotten worse in the last 2 weeks. I go through and kill
everything older than a day, going by last batch date. Is
there a clean up proc available? Anyone else have this
problem, resolutions?
Thanks,
Gary
When you say close a connection , do you mean a transaction? What's the
behaviour you see on the SQL side?
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.

old connections

All,
Sql 2000 sp3, Server 2000 sp4
I have a cold fusion front end that hooks to my db and it
never seems to close a connection. The problem has only
gotten worse in the last 2 weeks. I go through and kill
everything older than a day, going by last batch date. Is
there a clean up proc available? Anyone else have this
problem, resolutions?
Thanks,
GaryWhen you say close a connection , do you mean a transaction? What's the
behaviour you see on the SQL side?
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.