SQL Administration - Max Users connected

Question

SQL Administration - Max Users connected

gareth.thompson

SSC Enthusiast

Points: 123
More actions
August 11, 2010 at 4:37 am

#225279

Hi Guys / Girls,
Im having a look but cant find anything around.
Im looking to try and get the Maximum number of users that have ever connected to my SQL instance at one time over a periode or even better its life time.
Is there any easy way to do this?
Reason being is im going to merge to instances on one server and want to ensure the server can handle the load without too much of a issue.
Thanks a ton for any and all help.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

David Sorauer SSCrazy Points: 2874 More actions · Answer 1

I don't think this is persistently stored within SQL Server, but you can gather the information from Perfmon. What we do is plot a count of the current connections every 5 minutes and upload to a website where we keep this information ongoing for trending.

The number of connections however may not be a good indicator of load...

Mohit K. Gupta SSChampion Points: 12130 More actions · Answer 2

As David suggested that is not possible nor is it proper way to do consolidation. Things you need to evaluate are Processor Usage, Disk I/O, Network Usage, and Memory usage on each SQL Server Instance. If you don't have any kind of history of the server performance by means of SCOM, or other monitoring tools first thing you should do is implement Perfmon logging for fixed period when your server is at its peak usage; after which point you should combine the stats from both servers to see if the new hardware can handle the load.

For example you do KPI tracking for each server (Server A has 2GB memory) and (Server B has 6GB memory) and you find from KPI that the usage on memory is always 70% on A and 90% on B; then your new consolidated server must have at least 7GB ram to handle current load, or preferably 8GB+ to account for growth.

This also answers the CPU question a bit, that is you need to consider looking into going to 64-bit to better address the memory requirements.

Consolidation exercises are always fun, there any many considerations. This was just a small example... *cheers*

[font="Arial"]---

Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE

* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]

How to ask for help .. Read Best Practices here[/url].

gareth.thompson SSC Enthusiast Points: 123 More actions · Answer 3

Thanks all,

I realize from server load view point, I was a Server Admin before I (by means of cosmic chewing gum) became a DBA, and it is due to this that I was given the project for consolidation.

I have confirmed server loads, but what Im more concerned with is how many users are on at its peak times.

I want to establish if its user access that is pushing up the server performance logs, or if it is perhaps a single user that is causing the issue.

If I can pull details down to a there are all 50 registered account logged on at the tiime server responce peeked then I have a better Idea on what the hardware should be like.

If however my max users is all 50 but during the peek times only 5 users are on, I can look at what they are doing during these times and see if it can not perhaps be improved from their side, before spending on hardware becomes a huge issue.

we are going 64-bit, this was at my recomendation as it is a intelligent move.

Thanks though for the feedback.

I will have to just pull a current users logged in maintenance plan and run it on a hour basis with mail notification.

Change processes to load applications will take time that i would rather spend working on other issues.

Thanks again for all the assistance.