Slow connection on sqlsever local, but remote is fine

Question

Slow connection on sqlsever local, but remote is fine

ib

SSC Enthusiast

Points: 128
More actions
March 29, 2004 at 7:23 pm

#84061

Hi we're running sql server 2000
on multiple different servers. One instance on our development machine at the moment when you are trying to access it locally on that server(x) is very slow to connect and manage/query in enterprise manager, and query analyser.
Whereas if we are on server(y) and create a registration for server x it runs fine, we can see all the databases, and run queries perfectly.
The problems on server x however do not appear to be affecting our standalone programs we are developing on(VC#) that query, update, delete, and insert on server(x) databases. These are developed on and run on server(x) as well.
Anyone got any ideas as to wat could be going on?
Weve tried stoping the server(x) and restarting it, that didnt help at all.
Ive had no real training in sql so it could be something simple, just wondering if anyones encountered this or would know of a solution to fix it?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Antares686 SSC Guru Points: 125444 More actions · Answer 1

It is usually an effect of not enough memory. When you start EM or QA it is an external which has to take resource to be able to use. SQL is adamant about trying to keep as much memory as possible but will share with some apps generally after a period for Windows to start marshalling memory away from SQL for the other apps. However unless yo leave EM or QA open on the box it will run slow in most all cases where RAM limit is being reached. The reason it doesn't seem as slow externally to the box is because those connections are inside SQLs pool of memory for data and connection just like a local running copy of EM/QA but on a workstation it generally doesn't have to fight to get memory to begin with.

This is based on what I see with my servers where this happens, not anything official.

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 2

Also keep in mind most servers are set to prioritize background services over applications. You can check by right-clicking on My Computer and choosing Properties | Advanced tab | Performance Options... button. Most servers should be set this way, so it's the default setting. This would mean, other than the memory releasing as Antares pointed out, if you have a contention for resources the background services will get them first. So running those tools locally could be subject to a double whammy if resources are tight.

K. Brian Kelley
@kbriankelley

ib SSC Enthusiast Points: 128 More actions · Answer 3

hmm well, its running like 2gb of ram

and has dual athlon processors.

It sucks so much, because we TerminalServ to the server for development, and basically if we install the sql server apps on our local machines it would work fine, but its a pain having to - alt - tab to get a look at data in the data base.

Is there anything else you guys can suggest to look at?

ib SSC Enthusiast Points: 128 More actions · Answer 4

also why would this have started all of a sudden.

It had been operating fine for like 7 months and then came to work one week and it had started to run slowly.

Could there be another program that is slowing it down in some way, or is there any known worms/ trojans that could effect the server in such a way?

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 5

You might want to performance monitor this system, then. Yes, it is possible there is another program running, but typically you'd see the slow down across the board. Watch the processes and their CPU and memory location. Also check with perfmon the memory utilization for SQL Server, especially as you start running queries and the like. If total server memory starts sawtoothing, you probably are short on memory.

K. Brian Kelley
@kbriankelley