June 6, 2003 at 3:01 am
Hi all,
we have performance problems using the following configuration:
- SQL Server 2000 SP3
- OS: Windows 2000 Server SP2
- Terminal Server (running on the same computer, on which runs the SQL Server)
- virus scanner is already disabled
- Hardware-Platform : 4 * 1800 Mhz (I guess XEON), 2 GB Ram, the SQL Server uses 1 GB
The queries (table contains about 12 Mio records) have been executed with the local Query Analyser. The same SQL-Statements sometimes takes 0.5 seconds and sometimes 14 seconds.
This phenomenon occurs at random, even if there is no terminal session running.
Is there anyone who has the same kind of problems and knows a solution or can help us to resolve this problem ?
Thx!
June 6, 2003 at 3:05 am
Might be unrelated but SP 3 for 2K is out.
When viewing the execution plan, does it use indexes? Are there indexes?
Other than that, I can't be much help.
How big is the query?
Cheers,
Crispin
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
June 6, 2003 at 4:52 am
The indeces are optimised.
The important fact is, that there is a hard performance fluctuaion.
The Performance in general is ok.:-)
June 6, 2003 at 7:28 am
Maybe it goes fast just after am auto update statistics.
But you really need more data. There must be a bottleneck: cpu, i/o, memory, network (the last sounds unlikely, but if you use tcp/ip the program might try to go over the network the reach the server on the same machine) or locks.
Use performance monitor to find the bottleneck.
June 9, 2003 at 7:32 am
We have almost the exact same configuration deployed at a client's site with one major difference: the terminal server is on a completely separate machine.
This is an OLTP situation with 2 dozen heavy users runnning a processing plant 24/7 and no issues to speak of (HW & config related, anyhow). We had a problem with a memory bottleneck in the past, but doubling the RAM to 2 GB did the trick.
What are the memory requirements of the app you are running vs. the SQL resources required to serve up the data? Seems to me that splitting the server -- task-wise, between SQL server & terminal services, may be an issue. Also, if you have SQL server constrained to 1 GB, that leaves only 1 GB for the O/S and terminal sessions.
Just my 2 cents. Good luck.
Vik
June 10, 2003 at 12:19 am
Hi
I think the time taken will vary because if the pages are loded in to the memory(Cache) it takes less time. In this case, when you run first time it will take 14 secs and if you run immediately after that, it will take only .5 secs because the pages may be existing in the cache. After many transactions happen, if you run the same query, it should take 14 secs. Is is working this way ?
Regards
Fred
June 12, 2003 at 6:45 am
We are also experiencing this same situation with certain transactions. Once the transaction was identified, we were able to correct the problems within the programming, but they still occur. Please let me know if you have any new ideas.
June 12, 2003 at 10:56 am
Shot in the dark...........
When you see that query is taking more than 2 seconds run sp_who and sp_lock and see what for query is waiting!!!!!!!!
Cheers..Prakash
Prakash
Prakash Heda
Lead DBA Team - www.sqlfeatures.com
Video sessions on Performance Tuning and SQL 2012 HA
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply