May I know if SQL Server is able to show the historical number of sessions connected to the database? My database suffered a sudden performance issue when suddenly almost 3000+ idle sessions connected to the database and hog up the CPU resources to 100%. I check with the application team and confirm that at most there's only 1000 connections will open a session to the DB. To resolve this problem I had to restart the database instance & clear of all the idle sessions.
Now, my management requested to know when did this session spike happens in order for them to troubleshoot further. Coming from Oracle background, I understand Oracle kept it's session growth in a view called DBA_HIST_RESOURCE_LIMIT, I can query this table and see the growth of the sessions hourly basis.
Is there a similar table / view / DMV from SQL server ?