Performance really poor if calling data from sql clients

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
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@.discussions.microsoft.com> wrote in
message news:FB05EA03-F277-4FBE-B1AB-A7C8C767B41D@.microsoft.com...
> 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
|||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@.discussions.microsoft.com> wrote in
> message news:FB05EA03-F277-4FBE-B1AB-A7C8C767B41D@.microsoft.com...
>
>
|||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:
[vbcol=seagreen]
> 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:
|||Running of sp_updatestats fails. Error: stats_ver_current is not a built-in
function.
"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@.discussions.microsoft.com> wrote in
> message news:FB05EA03-F277-4FBE-B1AB-A7C8C767B41D@.microsoft.com...
>
>
|||What is the compatibility level of the db? If it was a db that was upgraded
it may be in 80 compatibility mode.
Andrew J. Kelly SQL MVP
"Stephan (Germany)" <StephanGermany@.discussions.microsoft.com> wrote in
message news:19B125B0-21FA-45E4-AD16-E464D6FFC3E6@.microsoft.com...[vbcol=seagreen]
> Running of sp_updatestats fails. Error: stats_ver_current is not a
> built-in
> function.
> "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@.discussions.microsoft.com> wrote in
message news:E1285720-7132-4B56-B754-B98E9594F466@.microsoft.com...[vbcol=seagreen]
> 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,
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@.discussions.microsoft.com> wrote in
> message news:E1285720-7132-4B56-B754-B98E9594F466@.microsoft.com...
>
>
|||The db was installed completely new. I have uninstalled SQL 2000 first.
But how can I find out finally which compatibility mode the SQL server is
running now? I see no property in management studio nor server configuration
manager.
Yours
Stephan
"Andrew J. Kelly" wrote:

> What is the compatibility level of the db? If it was a db that was upgraded
> it may be in 80 compatibility mode.
> --
> Andrew J. Kelly SQL MVP
>
> "Stephan (Germany)" <StephanGermany@.discussions.microsoft.com> wrote in
> message news:19B125B0-21FA-45E4-AD16-E464D6FFC3E6@.microsoft.com...
>
>
|||Maybe this can help you:
the most time consuming period (waiting time) will arise if I connect the
server. If I pause a while and send another SQL command, the reestablishing
of the connection (same connecting string) needs again a while and then I can
fire different sql commands which will be executed in an acceptable speed.
Yours
Stephan
"Andrew J. Kelly" wrote:

> What is the compatibility level of the db? If it was a db that was upgraded
> it may be in 80 compatibility mode.
> --
> Andrew J. Kelly SQL MVP
>
> "Stephan (Germany)" <StephanGermany@.discussions.microsoft.com> wrote in
> message news:19B125B0-21FA-45E4-AD16-E464D6FFC3E6@.microsoft.com...
>
>
sql