Recently we had a post by a reader stating that with 2000 tables in a
database it was taking 45 seconds to login it. He also indicated that adding
them to the db_reader role made the problem go away. To see the entire thread
(which is so far unresolved), follow this link:
Poor login speed with 2000+ tables
This seems like a good thread to explore. At first glance I'd expect
Enterprise Manager to be slow because it would need to load first all 2000
tables, then select the permissions on all 2000 objects when you go to view
permissions on a user. It's not slow to select 2000 rows out of sysobjects, but
DMO is object based, so you lose most of the advantages of doing set
based work as it loads one object at a time.
But just to log in to the db? The reader indicates that a different owner
owns the objects, so maybe some overhead from having to follow all the ownership
From what I can see in Profiler, if I connect as a user to master and then
change to a test database, the only procedure that affects security is
sp_MShasdbaccess. Looking at that proc, we see it's not very complicated:
;select name as 'dbname', owner = substring(suser_sname(sid), 1, 24), DATABASEPROPERTY(name, N'IsDboOnly') as 'DboOnly', DATABASEPROPERTY(name, N'IsReadOnly') as 'ReadOnly', DATABASEPROPERTY(name, N'IsSingleUser') as 'SingleUser', DATABASEPROPERTY(name, N'IsDetached') as 'Detached', DATABASEPROPERTY(name, N'IsSuspect') as 'Suspect', DATABASEPROPERTY(name, N'IsOffline') as 'Offline', DATABASEPROPERTY(name, N'IsInLoad') as 'InLoad', DATABASEPROPERTY(name, N'IsEmergencyMode') as 'EmergencyMode', DATABASEPROPERTY(name, N'IsInStandBy') as 'StandBy', DATABASEPROPERTY(name, N'IsShutdown') as 'ShutDown', DATABASEPROPERTY(name, N'IsInRecovery') as 'InRecovery', DATABASEPROPERTY(name, N'IsNotRecovered') as 'NotRecovered' from master.dbo.sysdatabases where has_dbaccess(name) = 1 order by name
Of course we can't see what the has_dbaccess function is doing (or at least
I'm not profiling the correct events). So...it would seem the thing to do is try
to reproduce the problem. I created 500 tables using the following code. You can
download the complete solution file here. This is
VB.Net 2003 code, but overall it's very much like what you would do using VB6
other than the error handling. Even with 500 tables I saw no noticeable delay
when switching to the database via Query Analyzer.
Sub CreateTables(ByVal HowMany As Integer, ByVal UseRoleForTesting As Boolean) Dim oserver As SQLDMO.SQLServer Dim odb As SQLDMO.Database Dim oTable As SQLDMO.Table Dim oCol As SQLDMO.Column Dim J As Integer Dim oLogin As SQLDMO.Login Dim bLoginFound As Boolean Dim oUser As SQLDMO.User Dim oRole As SQLDMO.DatabaseRole Const TBL_NAME As String = "TEST" Try 'connect to local using a trusted connection oserver = New SQLDMO.SQLServer oserver.LoginSecure = True oserver.Connect(".") 'drop previous test db if it exists For Each odb In oserver.Databases If odb.Name = DB_NAME Then oserver.KillDatabase(DB_NAME) End If Next 'create a testing database odb = New SQLDMO.Database odb.Name = DB_NAME oserver.Databases.Add(odb) 'add a test login if it doesnt exist For Each oLogin In oserver.Logins If oLogin.Name = DB_NAME & "USER" Then bLoginFound = True Exit For End If Next If bLoginFound = False Then oLogin = New SQLDMO.Login oLogin.Name = DB_NAME & "USER" oLogin.SetPassword("", PASSWORD) oserver.Logins.Add(oLogin) End If 'clunky, but repeat for the object owner bLoginFound = False For Each oLogin In oserver.Logins If oLogin.Name = DB_NAME & "USEROBJ" Then bLoginFound = True Exit For End If Next If bLoginFound = False Then oLogin = New SQLDMO.Login oLogin.Name = DB_NAME & "USEROBJ" oserver.Logins.Add(oLogin) End If 'add user to db oUser = New SQLDMO.User oUser.Name = DB_NAME & "USER" oUser.Login = DB_NAME & "USER" odb.Users.Add(oUser) 'add user to db oUser = New SQLDMO.User oUser.Name = DB_NAME & "USEROBJ" oUser.Login = DB_NAME & "USEROBJ" odb.Users.Add(oUser) 'add role, then add user to role oRole = New SQLDMO.DatabaseRole oRole.Name = "SSCTESTROLE" odb.DatabaseRoles.Add(oRole) oRole.AddMember(DB_NAME & "USER") 'create a lot of tables For J = 1 To HowMany 'name the tables sequentially oTable = New SQLDMO.Table oTable.Name = TBL_NAME & J.ToString oTable.Owner = DB_NAME & "USEROBJ" 'just do a simple one column table oCol = New SQLDMO.Column oCol.Datatype = "int" oCol.Identity = True oCol.Name = "TestColumn" oTable.Columns.Add(oCol) odb.Tables.Add(oTable) If UseRoleForTesting = True Then oTable.Grant(SQLDMO.SQLDMO_PRIVILEGE_TYPE.SQLDMOPriv_Select, "SSCTESTROLE") Else 'grant access to our test user oTable.Grant(SQLDMO.SQLDMO_PRIVILEGE_TYPE.SQLDMOPriv_Select, DB_NAME & "USER") End If Next Catch ex As Exception MessageBox.Show(ex.Message) Finally oserver.DisConnect() oserver = Nothing End Try
It's been a while since we've posted any DMO code, this combines a lot of
different objects to give you a basic framework for creating test environments.
To use it, you just call it like this:
So, I'm stumped. Is hardware masking the problem? Maybe I haven't recreated
the situation exactly enough to see the problem? What's the largest number of
tables you've worked with and have you noticed similar issues? Where would you
look next to solve the problem?