June 30, 2010 at 7:52 am
We are running SQL Server 2008 Enterprise edition on a 4 cpu/8 Gb Windows 2003 Enterprise Server box. There are a couple of developers who remote terminal into the server and open up Management Studio to do work or investigate Trouble Reports. I've suggested that it is detrimental to performance when they do this and it would be better to connect to the database from the SSMS instance installed on their own machine.
Do you have experience regarding this practice and the knowledge of the real impact it has on performance? I'd love to hear more about this. Perhaps I am barking up the wrong tree?
Thanks!
Even as a mother protects with her life
Her child, her only child,
So with a boundless heart
Should one cherish all living beings;
June 30, 2010 at 8:02 am
I don't know that you would see much of a performance difference. SSMS is retty light on resources and any Querires they submit are run on the server side regardless of where they are run.
From my expirence the bigger issue is one of concern from a security audit standpoint. Having developers connect to the server directly is not something auditors would normally approve of for security reasons.
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
June 30, 2010 at 8:29 am
I am currently in the process of moving our developers to using SSMS from their desktop, and I try to not remote into my production servers whenever possible. It's good to not have your database servers doing anything else but SQL. Depending on the size of your databases with 8GB of RAM I would try to keep as much memory for SQL Server as possible. I have one server with an 11GB DB on it and 8 GB of RAM and I constantly see performance issues on that box.
Security is a big issue with this as well, particularly depending on what type of data is on that server. I told my developers that it's best to keep them from possibly being blamed for issues on the box and let me take the heat for any. They were fine with that. I let them have enough rights to do what they need to do, at least in the new 2008 environment we are migrating to.
June 30, 2010 at 8:39 am
Another simple exemple. I once remoted on a server and ALMOST hit the shutdown button rather than log off.
I caught myself in time but it's been known to happen. Now I don't know just how critical that server is but it can certainly give you one of those "bad days".
June 30, 2010 at 8:51 am
This morning I watched ssms.exe in the task manager. It's initial memory usage is 56 Mb and it has 285 reads and 112 writes just opening it. The memory usage goes up as to reads and write as queries are run on it and released when the query windows are closed. It is the second highest memory user on the box after sqlserver.exe which is using around 200Mb. I have SQL Server running in High priority mode. I believe an instance of sqlmangr.exe also opens up with ssms.exe
CPU Cycles on our server are usualy up around 75%. On days like today hitting 95 - 100 isn't unusual. Having ssms open doesn't seem to effect cpu cycles much though. I'm not sure because I'm not able to tie cpu usage to ssms. i have to tread lightly since today timesheets are being done and i don't want to stress the system anymore than it already is.
Even as a mother protects with her life
Her child, her only child,
So with a boundless heart
Should one cherish all living beings;
June 30, 2010 at 8:54 am
Yeah Ninja's_RGR'us that is a scary prospect. I don't use the mouse very much to avoid the wiggles and I just draw in a deep breath when I'm going to log off. Hitting shutdown always goes through my mind even after 10 years of doing this. This db server is the heart of our operations..absolutely critical.
Even as a mother protects with her life
Her child, her only child,
So with a boundless heart
Should one cherish all living beings;
June 30, 2010 at 9:15 am
From my stand point of view even DBA should not remote in to server, provided if it is needed. All the dba activities can be performed locally by installing a client.
the following some reasons for not remote into server
1.Security risk: giving developers to TS into machine
2.Terminal Server licenses are limited in case if they close server without logging of properly others has wait until free slot is available on the server.
3.High possibility of shutdown the server.
4.Memory usages.
Regards
Hema.,
Regards
Hema.,
June 30, 2010 at 9:50 am
I've also been in the situation where we can only have two people remoted into a server at one time and I wasn't able to remote into one of my DB servers because developers were remoted into it. I had to login to another server and do a remote log off. Developers remoting in can cause those problems as well.
June 30, 2010 at 4:51 pm
Ninja's_RGR'us (6/30/2010)
Another simple exemple. I once remoted on a server and ALMOST hit the shutdown button rather than log off.I caught myself in time but it's been known to happen. Now I don't know just how critical that server is but it can certainly give you one of those "bad days".
Hehe, try running
xp_cmdshell 'shutdown -s' you might get lucky (or fired) 😀
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply