January 20, 2019 at 11:10 am
Hello.
Has anybody come across any issues with large numbers of Users in a database - i.e. >40,000.
We hit 32767 Users some time ago and the only thing that it seemed to break was Solarwinds DPA because it used the deprecated sys.sysprocesses - now resolved.
I have a call in to a vendor with a bunch of questions for known issues but one thing that I do get is when using SSMS to look at a User properties it takes @40 seconds for the dialog box to appear.
By this I mean expanding the database, expanding Security, expanding Users, right-clicking on the User and selecting Properties.
If I do the same thing on another database, in the same cluster instance, with only 3500 users the Properties dialog box opens in @3 seconds.
I don't know if this in itself is a problem - or even if there is anything that can be done as there is nothing in the DB that can be 'tuned' in respect of these entries - but it does look like the number of users in the DB affects the time taken for the dialog box to open and wondered if anybody else has experienced this.
Would this impact anything else other than SSMS response?
Would the impact be any different if the DB was MSDB for instance?
Seems to have no impact on syslogins for example - >40,000 users - Properties displayed sub-second.
I got so far with looking at system views (like sys.sysusers, sys,databse_principals) but these views reference other sys. entries that are neither tables nor views, that I can find anyway, so thought I'd ask here..
TIA.
Steve O.
January 21, 2019 at 2:14 pm
I don't believe that SSMS is ever going to be efficient in displaying that many users in Object Explorer. You are far better off looking at that kind of data in a query window.
January 22, 2019 at 1:57 am
TUellner - Monday, January 21, 2019 2:14 PMI don't believe that SSMS is ever going to be efficient in displaying that many users in Object Explorer. You are far better off looking at that kind of data in a query window.
Thanks for the reply Tom.
Oddly SSMS has no issue building the list of users as that part is always instantaneous. It is the right click to get properties on an individual user that takes the time.
Likewise the list of Syslogin Users is built instantaneously and although the user count is even higher the right click properties is also almost instantaneous.
The vendor has confirmed that they don't use Syslogins in current versions so I will not be messing with this unless I have to.
Not concerned about SSMS access but rather if it is an indication of something that may be contributing to DB performance issues.
Steve O.
January 22, 2019 at 8:22 am
SteveOC - Tuesday, January 22, 2019 1:57 AMThanks for the reply Tom.
Oddly SSMS has no issue building the list of users as that part is always instantaneous. It is the right click to get properties on an individual user that takes the time.
Likewise the list of Syslogin Users is built instantaneously and although the user count is even higher the right click properties is also almost instantaneous.
The vendor has confirmed that they don't use Syslogins in current versions so I will not be messing with this unless I have to.
Not concerned about SSMS access but rather if it is an indication of something that may be contributing to DB performance issues.Steve O.
Ah, sorry Steve, I misunderstood. That is indeed strange that the properties dialog is the issue and not creating the list itself. I'll be interested to hear what you find out.
-Tom
January 22, 2019 at 1:39 pm
SteveOC - Tuesday, January 22, 2019 1:57 AMTUellner - Monday, January 21, 2019 2:14 PMI don't believe that SSMS is ever going to be efficient in displaying that many users in Object Explorer. You are far better off looking at that kind of data in a query window.Thanks for the reply Tom.
Oddly SSMS has no issue building the list of users as that part is always instantaneous. It is the right click to get properties on an individual user that takes the time.
Likewise the list of Syslogin Users is built instantaneously and although the user count is even higher the right click properties is also almost instantaneous.
The vendor has confirmed that they don't use Syslogins in current versions so I will not be messing with this unless I have to.
Not concerned about SSMS access but rather if it is an indication of something that may be contributing to DB performance issues.Steve O.
I would still think it's just some of how SSMS queries things for the display. You could run a trace or extended events sessions while viewing the properties in SSMS to see which statement(s) is causing the slow down. That might give you an idea of the issues.
They do address issues frequently in the different versions of SSMS so trying a newer version to see if it improves at all may be an option.
Sue
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply