Re: Performance really poor if calling data from sql clients

Well you can always call MS PSS. It will cost about $225 US dollars but if
it is a bug they will refund it. This actually sounds like something in
Windows is misconfigured though and not a bug. You can try posting a new
thread with updated info and see if anyone else chimes in as well.


http://www.support.microsoft.com/default.aspx?scid=fh;EN-US;sql SQL
Support
http://www.mssqlserver.com/faq/general-pss.asp MS PSS

--
Andrew J. Kelly SQL MVP


"Stephan (Germany)" <StephanGermany@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in
message news:B6023016-1EAE-437A-92DA-3C25CC34E243@xxxxxxxxxxxxxxxx
The database properties (compatibility mode = 90, AutoClose = false) are
set.

How can I get additional help if you have no further ideas?

Thanks
Stephan.

"Andrew J. Kelly" wrote:

One last thing I can think of is do you have the AutoClose property for
that
database set to true? If so turn it off and see if that helps.

--
Andrew J. Kelly SQL MVP


"Stephan (Germany)" <StephanGermany@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in
message news:BD951B9E-85E1-4873-8B89-53670220836D@xxxxxxxxxxxxxxxx
Hello,

yes, TCP is enabled with a certain port. This port will be given in my
connection string as well. The TCP connection is running from the
client
(in
some cases but not stable). In SSMS, memory, named pipes and TCP
connections
are enabled. Server already restarted several times (MSSQLSERVER, SQL
agent
and SQL browser).

In the meantime, I have scheduled with a job on SQL server your
mentioned
task Update statistics. After the job was running, nothing really
changed
concerning the performance nor connection time. Using NETMON, I see
that
most
time is getting lost in requesting access to the server. This is
visible
in
my test: 50% of the total time will be used before the the real SQL
commands
will be performed.

Yours
Stephan.

"Andrew J. Kelly" wrote:

Is TCP enabled on the Laptop for SQL Server? Run the SQL Server
Configuration Manager and make sure you have the right protocols
enabled
for
that instance.

--
Andrew J. Kelly SQL MVP


"Stephan (Germany)" <StephanGermany@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote
in
message news:AEBB7DF6-0A74-4E62-A535-1624D0747DEB@xxxxxxxxxxxxxxxx
I have made the following SQL tests:
I'm inserting two rows in two different tables, one table first,
delete
the
rows, insert into the next table and delete the rows.

I measure the runtime.

On a computer running the test which is not the database hosting
computer,
xxx if TCP/IP connection was not established before, it takes 15 sec
xxx if I repeat the same (within 30 sec), it takes close to 0 sec

xxx named pipes: if not established before (within 30 sec), it takes
6
sec
xxx named pipes: if I repeat the same. it takes close to 0 sec
(because
of
connection pool I believe)

If I launch the program on the laptop where the database is hosted,
xxx if I choose TCP/IP: error, no SSPI context can be generated
xxx if I choose named pipes: close to 0 sec runtime

What is using so much time? The problem is: in my application, the
users
are
ongoing writing and reading from database tables. So if the user is
pausing,
he/she will get a long waiting time in order to make changes later
on.

The TCP/IP error on the laptop, do I have to use the loop back IP
address
instead of the same IP address for the TCP tests from client
computers?

Yours
Stephan

"Andrew J. Kelly" wrote:

Sorry I meant SSMS (SQL Server Management Studio).

--
Andrew J. Kelly SQL MVP


"Stephan (Germany)" <StephanGermany@xxxxxxxxxxxxxxxxxxxxxxxxx>
wrote
in
message news:187C37B8-FC91-4978-B928-FA17A21979E1@xxxxxxxxxxxxxxxx
Hello Andrew,

what do you mean with SSIS ?

I have checked DNS, WINS and address resolution. The laptop where
the
database is running can be found via ping and nslookup.
If I make another test (creating new logins in SQL management
studio),
it
is
not possible to see any other domain rather than the laptop
itself,
this
is a
bit confusing.
Maybe it has nothing to do with my problem but it is not really
good.



"Andrew J. Kelly" wrote:

If you run the same query from SSIS on the server does it run
fast
or
slow?
Sounds like network issues or problems with name resolution.

--
Andrew J. Kelly SQL MVP


"Stephan (Germany)" <StephanGermany@xxxxxxxxxxxxxxxxxxxxxxxxx>
wrote
in
message
news:E1285720-7132-4B56-B754-B98E9594F466@xxxxxxxxxxxxxxxx
Ok, the performance is not stable:

it is true that with named pipes. it is going a bit faster,
but
each
time
I
select another table to be displayed, it is still waiting for
something
I
cannot see even in the profiler. There is an overhead I can't
actually
find
out.
Any idea?

Yours
Stephan

"Stephan (Germany)" wrote:

Hello Andrew,

good hint with the profiler.

You know I'm "fighting" since a while to follow the
documented
recommendation of using TCP instead of Named Pipes to get
performance.

I see now if I using Named Pipes connection in VS2005,
everything
is
going
faster in VS2005 (and I assume this will also happen for my
application
if I
do the same).

So, what is the problem with TCP as connection type? Already
known
issues
by
Microsoft SQL developer teams?

Yours
Stephan

"Andrew J. Kelly" wrote:

I can't vouch for any of the VS components when it comes to
performance
since I don't use them for database access. But you should
be
able
to
use
profiler to see what is happening on the sql server end.
Also
what
does the
query plan look like? Is it optimal? Did you run
sp_updatestats?

--
Andrew J. Kelly SQL MVP


"Stephan (Germany)"
<StephanGermany@xxxxxxxxxxxxxxxxxxxxxxxxx>
wrote
in
message
news:FB05EA03-F277-4FBE-B1AB-A7C8C767B41D@xxxxxxxxxxxxxxxx
Hello,

I have installed SQL2005 developer edition on a laptop (2
GB
RAM),
VS2005
on
another computer. The database is quite small (nearly no
data
or
some
test
data). The performance is still really bad:
xxx each time you open the server explorer in VS2005 and
you
use
"show
table
data" (takes at least 20 sec., sometimes time out).

xxx each time I read from tables within my application
(I'm
not
reading
the
full table, I'm selecting the data, number of records <
5)

What can be done in order to determine the performance
consuming
element/object? The network the computer are attached
consists
of
less
than 5
users (1 GBit backbone).

Are there any configuration aspects which can help to
improove
the
performance?
Network pack size = 4096
Max. number of concurrent connections = 0
Boost SQL server priority is not checked
Max. server memory = 2 GB

Thanks,
Stephan

















.

Relevant Pages