Query Analyzer slow and spor attic
-
We are running SQL SERVER 2000 on a Windows 2000 Advanced server with 1 gig of memory. When we try to connect to SQL Query Analyzer the connection is slow or never takes place. I see in the task manager that the Kernel peaks and then drops, but query analyzer still does not repond. Why is this happening??????:
-
Anything in the event log? o/w have you tried connecting using Enterprise Manager or just using eg a .udl file to make a connection string? That might give you some clues as to what's going on. Have had similar sounding problems in the past which were authentication problems. o/w are you using a cluster? In which case, could be something to do with the way that it's set up... Think you have to connect to the active node rather than the virtual one if you are.
jt
-
Everything jt said sounds like solid possibilities. Also consider that it could be a networking issue. Most times I've noticed someone post an issue like this in here (I've only been here a month) they start out saying their network people swear up and down that it isn't a network problem and then they discover a bad NIC or driver, fix that, and the problem goes away. Trying pinging the box and try sniffing the wire to look for faults. Check your driver version. Since jt's stuff is probably quicker and easier to check you might better start there, though. Try connecting via Query Analyzer from the server machine itself. Depending on your authentication method you may still hit the LAN for that, but it at least minimizes the dependancy.
-
This is occurring on 2 servers. I do not have them clustered. I use both Query Analyzer and Enterprise Manager and it works the same for both. It occurs when I try to connect to the server and in Ent. Manager when I try to expand Database it is very slow. I have 2 other servers and it is instantious.
Thanks for you responses,
Dan
-
I would suggest making sure that AUTO_CLOSE is not set for the databases in question. See http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_03_6ohf.asp
Regards,
Mike
-
I have updated the NIC card drivers and it did not change a thing. Where is the authentication for SQL Server 2000??
Dan
-
Dan
did you try accessing the db locally on the server with the same problems? o/w when you connect in EM, you can choose between win auth and sql server auth - I'd suggest trying both and if only win auth causes problems then the lag might be going from the server to the dc (think that's right - i'm only a dev - not meant to know about such things
) - we actually had that the other day, but the evil-network-people never told me what actual problem was - some problem with the routing jt
-
Just to give you some more information. I get the same thing when accessing EM or Query Manager on the server itself. This may not be a network issue.
Dan
-
Can you confirm that the database is not auto-closing as mbaker mentioned and when you logon are you using windows authentication or SQL Server authentication as jt-75 previously asked? If the former, could you try connecting using SQL Server authentication? You may have to create a logon if one doesn't already exist (does SA always exist SQL Server side?).
-
In addition to the other replies : check your ODBC-settings (clientside). If ODBC-logging is active, everything slows downdramaticaly.
are your problem sqlservers dedicated servers ?
Do they contain tables with eat up all you sql memory ? Maybe even forcing you server to page.
how much memory is being used by sqlserver at the dbservers ?
Do all clients suffer this latency ?
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
I would also try "TCP" instead of "named pipes". And check the virus scanner on the server to make sure it doesn't block anything. Maybe exclude the SQL server directories from scanning.
-
The OBDC is fine, no logging. The servers have 1 gig of memory. No large table to eat up memory. The SQL servers min. memory is 512 and 1024 max. Yes, all clients suffer this and it does this on the actual server itself.
Dan
-
Can you confirm that the databases are not auto-closing. This would have the database close and release all resources when it is not being used. When someone connects to the database, SQL Server would need to allocate resources. If auto_close is off, SQL Server has less work to perform when a user connects to the database.
You can check this setting with the sp_dboption stored procedure, or via EM (right click on the database and select properties, options tab).
HTH,
Mike
-
The databases are not auto closing.
-
what's sp_who/sp_who2/sp_lock stating ?
is msdtc running ?
is SQLAgent running ? (and is it running any jobs ?)
Do you connect using SQL-authentication or using windows-authentication ?
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply