July 31, 2024 at 11:36 am
SQL server is installed in Win Server and accessed via SSMS from local.
I noticed that even after I closed SSMS, the sqlserver's services didn't stop.
I recently ran IndexOptimize against 1 DB and saw the memory being used upto 95%. But the process is done and SSMS is too closed from local. Its been 2 days, still the server with no connection from local SSMS shows 95% usage in Task Manager.
Why the services for sqlserver is still on? how to stop those sessions?
July 31, 2024 at 12:26 pm
SSMS is, as its name suggests, simply a management tool for SQL Server. The actual SQL Server engine and services run independently of SSMS and continue running whether there is a connection to them or not. Any processes that have been started using SSMS must be explicitly stopped, if they're still running, even when SSMS itself is closed. If you try to close SSMS when there are T-SQL queries running, you'll get a warning message checking whether or not you really want to do so. The query can continue to run without SSMS being open. SSMS isn't not actually needed for SQL Server to operate, there are many other ways of passing in queries and maintaining your system. However, SSMS is one of the most widely used and complete.
Think of SSMS like the dashboard of a car. You can take it away and you'll still have the engine but you won't be able to control things as easily.
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
July 31, 2024 at 12:42 pm
Thank you sir! Got what SSMS's role is.
But, is there a solution where we can explicitly close such sessions? which made the server performance usage to 95%?
Kindly let me know - Thank you!
July 31, 2024 at 12:55 pm
Are these sessions actually causing problems? Stopping them could do more harm than good and may not be the quick fix you hope it will be. It's likely that any work that's been done will have to be rolled back and that could take as long, if not longer, than waiting them out.
Is "the memory being used upto 95%", the memory available to SQL Server or the total memory available? SQL Server will take all the memory it can. If there is no cap on the amount of memory allocated to SQL Server it will take all the memory available on the host and leave nothing for the OS.
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
July 31, 2024 at 8:33 pm
Another thing to note - SQL Server, with the default configuration, will use up to 2 PB of memory or whatever is available in the system. In order to reduce the memory used by SQL Server, the best option is to configure it to use less max memory.
SQL Server will use whatever memory is available to it if it needs it unless you explicitly tell it not to. And once it has the memory, it isn't going to give it back. This is by design. Requesting memory from the OS is a slow operation (comparatively), so the fewer requests you have for more memory, the faster the overall performance of the system can be. And freeing memory up is also a slow operation. If SQL needed 10 GB of memory at one point, it expects that it will need that again soon, so to SQL Server, there is no benefit to freeing the memory up.
There are ways to force SQL to give the memory back (such as restarting the service), but, like shrinking a database or log file, they are not recommended. A better approach is to dedicate SOME server memory to SQL Server and then configure SQL to use that amount and let it do it's own thing with the memory.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply