Hi @Rajesh_Allagandula - Welcome to the community.
Yes, when you are selecting “Direct Query” method in QuickSight, essentially user is requesting QuickSight to connect to the target and execute the query and provide the update results. So every time it will hit the database. But I do not think the connection remains open once the data is retrieved. You can monitor the performance of the redshift load by the QuickSight user as well.
So, in your case, if you are going to have ~2K concurrent users on the dashboard requesting for data from redshift, it might exceed the limit on workgroup.
One of the option you can explore is to use SPICE. If the data is refreshed in batch mode, you can bring the cut off ( filter version of the data) to SPICE and this way you can avoid hitting redshift directly.