With the help of Sitecore support, we finally found root cause.
They provided a patch for "SC_TICKET entries are never cleared from Properties table." (#223702), this issue is fixed in Sitecore 9.1
More information on this from the blog post https://hishaamn.wordpress.com/2016/04/26/sitecore-authentication-ticket-cleanup/
Sitecore create a ticket for each user when login onto the CMS. This ticket entry is stored in the Properties table of the Core database.
Login into the CMS may become slow with time and also based on the number of users accessing the CMS. This is because of the large number of ticket records being stored in the Properties table. There is currently an agent which performs the cleanup.
However, the agent is not so efficient. We had this issue on our Content Management Environment whereby the login was taking about more than 5 mins to get into the CMS. For some users, they received a timeout message.
When we ran the below query, against Core database, we can see there are thousands of records,
SELECT Count(ID) FROM [dbo].[Properties]
WHERE [Key] LIKE '%TICKET%'
AND [VALUE] LIKE '%sitecore\virtualssuser%'
After deleting the records, slowness and timeout issues disappeared.
Permanent Fix: Apply the patch provided by the Support team, which contains a config file and an assembly.