SQL Server Performance Forum – Threads Archive
Terminal client on SQL server
Does anybody know how to improve performance if I get 100 Terminal clients on SQL server? Thanks,HJ
I think to answer this we’d need more info. Why are there 100 terminal clients connecting? What are they doing?
Are they connecting only to query a database, or is the SQL server used also for something else? If they are just querying the database, could you not just connect over LAN or maybe use VPN over the internet via some other tool (Query analyser, or a custom written piece of s/w). 100 Terminal clients is always going to place a big load on the server.
Just to clarify my answer, I assumed you meant Terminal Services clients, rather than 100 database clients. Sorry if i misunderstood!
Like Chappy suggests, we need more information about your current setup and more specifically what your performance problems are. Where I work, almost all user connections are from Terminal Service/Citrix clients, and I have not seen any particular performance issue because connections are coming from Terminal Service/Citrix clients.
——————
Brad M. McGehee
Webmaster
SQL-Server-Performance.Com
200 LAN users and 100 Terminal(Windows 2000) users from diff provinces/states. They all access one database, all of activities are OLTP. Thanks again,
HJ
So essentially, you may have to up 300 users online at the same time, both LAN and via Terminal sessions. There is no difference between these type of users when tuning SQL Server. All connections, wherever they come from, all appear the same to SQL Server. By default, SQL Server will create up to 255 user connections, and then after this point, will start sharing connections. Depending on the load of the server, leaving this default may be OK. But you also may want to consider bumping up the default value 0f 255 connections to a greater value, assuming that over 255 connections will all exist at the same time. On one of my server, I have over 500 user, but at any one time, the maximum number of connections rarely tops 125. This may also be true in your case, depending on how the application works and is used. I probably wouldn’t change this setting unless you have identified a specific performance problem. Trying to guess the future with tuning is very hard to do.
——————
Brad M. McGehee
Webmaster
SQL-Server-Performance.Com
Thanks for reply.
My maximum is almost 225 sometimes.
The performance issues happened serveral times within a week sometime. After a reboot, it works fine again.
Sometimes the server runs OK without reboot over 2 months.
As a rule of thumb, (and this may not apply to you), if you have to reboot a SQL Server to reclaim memory, this is an indication of a memory leak, which should not be happening. Be sure to check that your SQL Server has the latest SQL Server service pack. If you do have the latest SP, the check to see if your server has the latest MDAC. Sometimes, new versions of MDAC (with memory leaks fixed) before new versions of SPs. If you find that your SP is the latest, but your MDAC is not, you can install the new MDAC, or perhaps wait until the next SP, which will have the newest MDAC with it. Also, does your server run other software other than SQL Server? If it does, then perhaps one of these other programs is causing the memory leak. So if you are running other applications, check them carefully for potential memory leaks. Have you performed a Performance Monitor log during this the time period when this performance problems occurs in order to help identify the bottleneck causing the problem? In regards to connections, if your maximum is almost 255 sometimes, you might experiment with a higer value than the default, say 275 or 300 and see if this helpd. If you do this, watch your buffer hit cache ratio to see that is remains the same. If it drops after increasing the number of connections, you may need to go back to the default value, or add more RAM to your server.
——————
Brad M. McGehee
Webmaster
SQL-Server-Performance.Com
I read some articles before.
It addressed about re-boot SQL will clear RAM
The problem is because of the some code-issue mess up the temp DB & RAM.
I think it’s a little bit difficult to find the problem within code. Any other ideas? Thanks again.
Other than fixing the code (if that is the problem), I guess you will have to reboot. I don’t know of any other work-arounds. You mention problems with tempdb and RAM. These are two separate issues, and are not really related. Perhaps you can offer some more specific details on these issues and we can then perhaps some suggestions.
——————
Brad M. McGehee
Webmaster
SQL-Server-Performance.Com
]]>