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

performance problem

every 3-4 days I have to restart the sql service because some users are
complaining of poor performance in a specific form ( where they are
intensively add some small info but constantly with speed, and the process
of introduction is always behind where it should be ).
I made a test db where I restored the original db and I noticed that it's
working with normal speed in this parallel database.
My initial supposition was that it should work with the same low speed in
this new database, but it doesn't.
So I think this situation it's somewhat related with the database that it's
intensively worked with, and not with the memory of the server.
In 3-4 days the sql server process grows to about 500MB and if I let the
process for a week it goes to 800MB.
So to resume :
there is poor performance in 3-4 days from restart on a database that is
intensively worked on ,
but a normal performance on a duplicate database on the same server.
The server is a P4 at 2GHz with 1280MB of RAM and a scsi hard disk for
storing the databases.
Thanks for any clues
Fatboyslimro wrote:
> every 3-4 days I have to restart the sql service because some users
> are complaining of poor performance in a specific form ( where they
> are intensively add some small info but constantly with speed, and
> the process of introduction is always behind where it should be ).
> I made a test db where I restored the original db and I noticed that
> it's working with normal speed in this parallel database.
> My initial supposition was that it should work with the same low
> speed in this new database, but it doesn't.
> So I think this situation it's somewhat related with the database
> that it's intensively worked with, and not with the memory of the
> server.
> In 3-4 days the sql server process grows to about 500MB and if I let
> the process for a week it goes to 800MB.
>
> So to resume :
> there is poor performance in 3-4 days from restart on a database that
> is intensively worked on ,
> but a normal performance on a duplicate database on the same server.
>
> The server is a P4 at 2GHz with 1280MB of RAM and a scsi hard disk for
> storing the databases.
> Thanks for any clues
Memory issue sounds fine. SQL Server uses memory as needed up to its set
maximum or what's available in the OS and does not release memory
without a struggle. As more data is read into cache, the memory
footprint will increase. If you are running into a situation where the
OS needs more memory or you have other applications running on the same
server (lie IIS) and they are struggling, you need to set SQL Server to
only use a maximum amount of memory. You can do this easily from SQL
Enterprise Manager.
There could be other problems that are causing the slowness, however.
Report back to us whether you have other application running on the
server besides SQL Server.
David Gugick
Imceda Software
www.imceda.com
|||The server is also a file server and a DC.
But the thing is that in the same time that this database is running slow,
on a duplicate database on the same server the applications are running ok.
the sql server is limited to 800MB of RAM from a total of 1280MB
( before I made this duplicate database I thought that when it's going slow
on a database it will run slow on all the databases on the server - and I
wanted to do some maintenance operations on the duplicate db to see if this
is the problem, but if it runs slow on all the databases it isn't a problem
of indexing or something like this ).
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:e7H8kE64EHA.2964@.TK2MSFTNGP15.phx.gbl...
> Fatboyslimro wrote:
> Memory issue sounds fine. SQL Server uses memory as needed up to its set
> maximum or what's available in the OS and does not release memory
> without a struggle. As more data is read into cache, the memory
> footprint will increase. If you are running into a situation where the
> OS needs more memory or you have other applications running on the same
> server (lie IIS) and they are struggling, you need to set SQL Server to
> only use a maximum amount of memory. You can do this easily from SQL
> Enterprise Manager.
> There could be other problems that are causing the slowness, however.
> Report back to us whether you have other application running on the
> server besides SQL Server.
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>
|||Fatboyslimro wrote:
> The server is also a file server and a DC.
> But the thing is that in the same time that this database is running
> slow, on a duplicate database on the same server the applications are
> running ok.
> the sql server is limited to 800MB of RAM from a total of 1280MB
> ( before I made this duplicate database I thought that when it's
> going slow on a database it will run slow on all the databases on the
> server - and I wanted to do some maintenance operations on the
> duplicate db to see if this is the problem, but if it runs slow on
> all the databases it isn't a problem of indexing or something like
> this ).
>
By adding a second database (for testing?) you are putting further
strain on you memory resources. Why clog up the cache with duplicate
data from a second database?
You may want to update statistics on the database in question. It's not
clear what's going on, but it is clear that you are using your server
for file services and a domain controller and SQL Server and only have
1.2GB RAM. That may be enough (don't know your network and SQL specs),
but why not add more memory since it's so cheap. Putting 2GB RAM in
there would give you a little more headroom and then you could give the
server a little more than 400MB (which is not very much for a server).
David Gugick
Imceda Software
www.imceda.com
|||I don't think the problem is related to how much memory is in the server,
since 4 months ago when the server had only 512MB of RAM everything was ok.
Also, I don't think the problem is related to how much memory is in the
server because the same thing is happening when there is a lot of unused
memory ( say sql with 400MB and the rest for the system - which is a lot ).
I think we got to focus on the main issue: why on all the databases is
working ok, and only on one of them is working slowly after a couple of
days.
thanks for reply
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:e2EoHkm5EHA.3756@.TK2MSFTNGP14.phx.gbl...
> Fatboyslimro wrote:
> By adding a second database (for testing?) you are putting further
> strain on you memory resources. Why clog up the cache with duplicate
> data from a second database?
> You may want to update statistics on the database in question. It's not
> clear what's going on, but it is clear that you are using your server
> for file services and a domain controller and SQL Server and only have
> 1.2GB RAM. That may be enough (don't know your network and SQL specs),
> but why not add more memory since it's so cheap. Putting 2GB RAM in
> there would give you a little more headroom and then you could give the
> server a little more than 400MB (which is not very much for a server).
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>

performance problem

every 3-4 days I have to restart the sql service because some users are
complaining of poor performance in a specific form ( where they are
intensively add some small info but constantly with speed, and the process
of introduction is always behind where it should be ).
I made a test db where I restored the original db and I noticed that it's
working with normal speed in this parallel database.
My initial supposition was that it should work with the same low speed in
this new database, but it doesn't.
So I think this situation it's somewhat related with the database that it's
intensively worked with, and not with the memory of the server.
In 3-4 days the sql server process grows to about 500MB and if I let the
process for a week it goes to 800MB.
So to resume :
there is poor performance in 3-4 days from restart on a database that is
intensively worked on ,
but a normal performance on a duplicate database on the same server.
The server is a P4 at 2GHz with 1280MB of RAM and a scsi hard disk for
storing the databases.
Thanks for any cluesFatboyslimro wrote:
> every 3-4 days I have to restart the sql service because some users
> are complaining of poor performance in a specific form ( where they
> are intensively add some small info but constantly with speed, and
> the process of introduction is always behind where it should be ).
> I made a test db where I restored the original db and I noticed that
> it's working with normal speed in this parallel database.
> My initial supposition was that it should work with the same low
> speed in this new database, but it doesn't.
> So I think this situation it's somewhat related with the database
> that it's intensively worked with, and not with the memory of the
> server.
> In 3-4 days the sql server process grows to about 500MB and if I let
> the process for a week it goes to 800MB.
>
> So to resume :
> there is poor performance in 3-4 days from restart on a database that
> is intensively worked on ,
> but a normal performance on a duplicate database on the same server.
>
> The server is a P4 at 2GHz with 1280MB of RAM and a scsi hard disk for
> storing the databases.
> Thanks for any clues
Memory issue sounds fine. SQL Server uses memory as needed up to its set
maximum or what's available in the OS and does not release memory
without a struggle. As more data is read into cache, the memory
footprint will increase. If you are running into a situation where the
OS needs more memory or you have other applications running on the same
server (lie IIS) and they are struggling, you need to set SQL Server to
only use a maximum amount of memory. You can do this easily from SQL
Enterprise Manager.
There could be other problems that are causing the slowness, however.
Report back to us whether you have other application running on the
server besides SQL Server.
David Gugick
Imceda Software
www.imceda.com|||The server is also a file server and a DC.
But the thing is that in the same time that this database is running slow,
on a duplicate database on the same server the applications are running ok.
the sql server is limited to 800MB of RAM from a total of 1280MB
( before I made this duplicate database I thought that when it's going slow
on a database it will run slow on all the databases on the server - and I
wanted to do some maintenance operations on the duplicate db to see if this
is the problem, but if it runs slow on all the databases it isn't a problem
of indexing or something like this ).
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:e7H8kE64EHA.2964@.TK2MSFTNGP15.phx.gbl...
> Fatboyslimro wrote:
> > every 3-4 days I have to restart the sql service because some users
> > are complaining of poor performance in a specific form ( where they
> > are intensively add some small info but constantly with speed, and
> > the process of introduction is always behind where it should be ).
> > I made a test db where I restored the original db and I noticed that
> > it's working with normal speed in this parallel database.
> > My initial supposition was that it should work with the same low
> > speed in this new database, but it doesn't.
> > So I think this situation it's somewhat related with the database
> > that it's intensively worked with, and not with the memory of the
> > server.
> > In 3-4 days the sql server process grows to about 500MB and if I let
> > the process for a week it goes to 800MB.
> >
> >
> > So to resume :
> >
> > there is poor performance in 3-4 days from restart on a database that
> > is intensively worked on ,
> > but a normal performance on a duplicate database on the same server.
> >
> >
> > The server is a P4 at 2GHz with 1280MB of RAM and a scsi hard disk for
> > storing the databases.
> >
> > Thanks for any clues
> Memory issue sounds fine. SQL Server uses memory as needed up to its set
> maximum or what's available in the OS and does not release memory
> without a struggle. As more data is read into cache, the memory
> footprint will increase. If you are running into a situation where the
> OS needs more memory or you have other applications running on the same
> server (lie IIS) and they are struggling, you need to set SQL Server to
> only use a maximum amount of memory. You can do this easily from SQL
> Enterprise Manager.
> There could be other problems that are causing the slowness, however.
> Report back to us whether you have other application running on the
> server besides SQL Server.
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>|||Fatboyslimro wrote:
> The server is also a file server and a DC.
> But the thing is that in the same time that this database is running
> slow, on a duplicate database on the same server the applications are
> running ok.
> the sql server is limited to 800MB of RAM from a total of 1280MB
> ( before I made this duplicate database I thought that when it's
> going slow on a database it will run slow on all the databases on the
> server - and I wanted to do some maintenance operations on the
> duplicate db to see if this is the problem, but if it runs slow on
> all the databases it isn't a problem of indexing or something like
> this ).
>
By adding a second database (for testing?) you are putting further
strain on you memory resources. Why clog up the cache with duplicate
data from a second database?
You may want to update statistics on the database in question. It's not
clear what's going on, but it is clear that you are using your server
for file services and a domain controller and SQL Server and only have
1.2GB RAM. That may be enough (don't know your network and SQL specs),
but why not add more memory since it's so cheap. Putting 2GB RAM in
there would give you a little more headroom and then you could give the
server a little more than 400MB (which is not very much for a server).
David Gugick
Imceda Software
www.imceda.com|||I don't think the problem is related to how much memory is in the server,
since 4 months ago when the server had only 512MB of RAM everything was ok.
Also, I don't think the problem is related to how much memory is in the
server because the same thing is happening when there is a lot of unused
memory ( say sql with 400MB and the rest for the system - which is a lot ).
I think we got to focus on the main issue: why on all the databases is
working ok, and only on one of them is working slowly after a couple of
days.
thanks for reply
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:e2EoHkm5EHA.3756@.TK2MSFTNGP14.phx.gbl...
> Fatboyslimro wrote:
> > The server is also a file server and a DC.
> >
> > But the thing is that in the same time that this database is running
> > slow, on a duplicate database on the same server the applications are
> > running ok.
> >
> > the sql server is limited to 800MB of RAM from a total of 1280MB
> >
> > ( before I made this duplicate database I thought that when it's
> > going slow on a database it will run slow on all the databases on the
> > server - and I wanted to do some maintenance operations on the
> > duplicate db to see if this is the problem, but if it runs slow on
> > all the databases it isn't a problem of indexing or something like
> > this ).
> >
> By adding a second database (for testing?) you are putting further
> strain on you memory resources. Why clog up the cache with duplicate
> data from a second database?
> You may want to update statistics on the database in question. It's not
> clear what's going on, but it is clear that you are using your server
> for file services and a domain controller and SQL Server and only have
> 1.2GB RAM. That may be enough (don't know your network and SQL specs),
> but why not add more memory since it's so cheap. Putting 2GB RAM in
> there would give you a little more headroom and then you could give the
> server a little more than 400MB (which is not very much for a server).
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>

Performance on wan

Hi,I have 2 server SQL 2000, 15 days ago everything worked well,

but days back, the performance on WAN is poor, it takes long time in login and make querys, in the local net everything work well, my problem is only on WAN, i dont have problem with Memory and CPU, the use of CPU never exceed 25 %, although i have poor performance on WAN local its fine, my bandwith is excelent, with other services i dont have problem(Terminal Services, Ftp, etc)

Some idea?

Thanks.

Javier Muro

When you connect, can you confirm your network library is TCP/IP? You can query against sysprocesses to find this out:
SELECT loginame, net_library
FROM sysprocesses
WHERE loginame = '<your login>'
If it's not, run cliconfg.exe and check the order of your enabled protocols. If you've got TCP/IP first, was the SQL Server restarted recently? If so, check the SQL Server log to ensure it was able to listen on TCP/IP.
If none of that, were any changes made on the networking equipment for the WAN? For instance, did the network engineers adjust QoS or anything of that sort?